欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

NPOI生成excel表格 博客分类: C# NPOIEXCELC#表格 

程序员文章站 2024-03-25 15:05:52
...

NPOI生成excel表格

1.什么是NPOI?百度百科给出的定义是:NPOI是指构建在POI 3.x版本之上的一个程序,NPOI可以在没有安装Office的情况下对WordExcel文档进行读写操作。

简单的说,NPOI就是一个功能强大的用来读写excelword文档的类库。

<!--[if !supportLists]-->2.    2.NPOI生成EXCEL表格的基本过程:获取数据>创建表格>将数据写入表格

 

<!--[if !supportLists]-->3.    3.代码示例

   创建两个类:学生类和员工类

    public class Student
    {
        public String no;
        public String name;
    }
public class Worker
    {
        public String age;
        public String job;
        public String name;
    }

 表格两个sheet页的表头

            /// <summary>
        /// 获取学生表头
        /// </summary>
        /// <returns></returns>
        public List<String> GetStuHeaderList()
        {
            var stuHeaderList = new List<String>();
            stuHeaderList.Add("学号");
            stuHeaderList.Add("姓名");
            return stuHeaderList;
        }
        /// <summary>
        /// 获取工人表头
        /// </summary>
        /// <returns></returns>
        public List<String> GetWorHeaderList()
        {
            var worHeaderList = new List<String>();
            worHeaderList.Add("姓名");
            worHeaderList.Add("年龄");
            worHeaderList.Add("工作");
            return worHeaderList;
        }

 获取数据:项目中一般会在数据库中获取数据,这里为了简化,直接向列表中添加内容

        public void GetData(List<Student> stuList, List<Worker> workList)
        {
            Student stu1 = new Student();
            stu1.no = "1";
            stu1.name = "小A";
            stuList.Add(stu1);
            Student stu2 = new Student();
            stu2.no = "2";
            stu2.name = "小B";
            stuList.Add(stu2);
            Worker work1 = new Worker();
            work1.age = "20";
            work1.job = "工程师";
            work1.name = "小C";
            workList.Add(work1);
            Worker work2 = new Worker();
            work2.age = "22";
            work2.job = "技术员";
            work2.name = "小D";
            workList.Add(work2);
        }

 创建EXCEL文件并写入数据

     public String CreateExcelFile(String filePath, String fileName)
        {
            String result = "S";
            //为列表添加数据
            List<Student> stuList = new List<Student>();
            List<Worker> workList = new List<Worker>();
            this.GetData(stuList, workList);

            if (!string.IsNullOrEmpty(filePath))
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");

                //设置标题样式
                ICellStyle style1 = book.CreateCellStyle();
                style1.Alignment = HorizontalAlignment.Center;
                IFont font = book.CreateFont();
                font.Boldweight = short.MaxValue;
                font.FontHeight = 280;
                style1.SetFont(font);
                //写标题
                NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
                row1.Height = 420;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));//合并单元格
                ICell cell1 = row1.CreateCell(0);
                cell1.CellStyle = style1;
                cell1.SetCellValue("学生信息表");
                //设置表头字体
                ICellStyle style2 = book.CreateCellStyle();
                style2.Alignment = HorizontalAlignment.Center;
                IFont font3 = book.CreateFont();
                font3.Boldweight = short.MaxValue;
                style2.SetFont(font3);
                style2.WrapText = true;
                style2.VerticalAlignment = VerticalAlignment.Center;
                //写文件头
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
                List<String> headerList = this.GetStuHeaderList();
                for (int i = 0; i < headerList.Count; i++)
                {
                    ICell cell5 = row2.CreateCell(i);
                    cell5.CellStyle = style2;
                    cell5.SetCellValue(headerList[i]);
                }
                //写数据
                for (int i = 0; i < stuList.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(i + 2);
                    row3.CreateCell(0).SetCellValue(stuList[i].no);
                    row3.CreateCell(1).SetCellValue(stuList[i].name);
                }
                //生成员工表
                book = WriteWorkerFileToExcel(book, filePath + @"\" + fileName + ".xls", workList);
                // 写入到客户端  
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath +@"\" + fileName + ".xls", FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
            return result;
        }
        /// <summary>
        /// 生成sheet2
        /// </summary>
        /// <param name="book"></param>
        /// <param name="filePath"></param>
        /// <param name="workList"></param>
        /// <returns></returns>
        public NPOI.HSSF.UserModel.HSSFWorkbook WriteWorkerFileToExcel(NPOI.HSSF.UserModel.HSSFWorkbook book, String filePath,List<Worker> workList)
        {
            if (!string.IsNullOrEmpty(filePath))
            {

                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet2");
                List<String> headerList = this.GetWorHeaderList();
                //设置标题样式
                ICellStyle style1 = book.CreateCellStyle();
                style1.Alignment = HorizontalAlignment.Center;
                IFont font = book.CreateFont();
                font.Boldweight = short.MaxValue;
                font.FontHeight = 280;
                style1.SetFont(font);
                //写标题
                NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
                row1.Height = 420;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2));
                ICell cell1 = row1.CreateCell(0);
                cell1.CellStyle = style1;
                cell1.SetCellValue("员工信息表");

                //设置表头字体
                ICellStyle style2 = book.CreateCellStyle();
                style2.Alignment = HorizontalAlignment.Center;
                IFont font3 = book.CreateFont();
                font3.Boldweight = short.MaxValue;
                style2.SetFont(font3);
                style2.WrapText = true;
                style2.VerticalAlignment = VerticalAlignment.Center;
                //写文件头
                NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(1);
                for (int i = 0; i < headerList.Count; i++)
                {
                    ICell cell5 = row2.CreateCell(i);
                    cell5.CellStyle = style2;
                    cell5.SetCellValue(headerList[i]);
                }
                //写数据
                for (int i = 0; i < workList.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row3 = sheet.CreateRow(i + 2);
                    row3.CreateCell(0).SetCellValue(workList[i].name);
                    row3.CreateCell(1).SetCellValue(workList[i].job);
                    row3.CreateCell(2).SetCellValue(workList[i].age);
                }
                return book;
            }
            return null;
        }