NPOI生成excel表格

NPOI生成excel表格

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

简单的说，NPOI就是一个功能强大的用来读写excel和word文档的类库。

<!--[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;
        }