使用NPOI生成Excel文档

环境：net framework 4.5.2

/// <summary>
        /// Excel模板自定义常用设置 当前方法仅支持xlsx版本XSSFWorkbook，百度大多都是HSSFWorkbook（xls）
        /// </summary>
        /// <param name="b"></param>
        /// <param name="relativePath"></param>
        /// <param name="rename"></param>
        /// <returns></returns>
        public override string Down(ControllerBase b, string relativeName, string rename) {
            //路径验证
            DirectoryInfo dir = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory + this.BaseDirectory);
            if (!dir.Exists) dir.Create();
            relativeName = AppDomain.CurrentDomain.BaseDirectory + this.BaseDirectory + relativeName;
            FileInfo file = new FileInfo(relativeName);
            if (file.Exists) file.Delete();
            //创建文件流
            using (FileStream fs = new FileStream(relativeName, FileMode.Create)) {
                //创建空对象
                //IWorkbook book = Path.GetExtension(relativeName).ToLower() == ".xlsx" ? new XSSFWorkbook() : new HSSFWorkbook();
                IWorkbook book = new XSSFWorkbook();
                //创建模板
                ISheet et = book.CreateSheet("Drmwe");
                //表格线
                et.DisplayGridlines = false;
                //设置表头
                for (int i = 0; i < 4; i++) {
                    var irow = et.CreateRow(i);
                    for (int j = 0; j < 6; j++) {
                        var str = irow.CreateCell(j);
                        XSSFCellStyle style = (XSSFCellStyle)book.CreateCellStyle();
                        //填充默认背景色
                        style.SetFillForegroundColor(new XSSFColor(Color.Black));
                        style.FillPattern = FillPattern.SolidForeground;
                        //这个填充的是鼠标编辑才变颜色 - 不编辑不会出现设置的颜色
                        //style.SetFillBackgroundColor(new XSSFColor(Color.Black));
                        str.CellStyle = style;
                    }
                }
                //合并单元格
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 3, 0, 5));
                //创建路径文件流读取
                var path = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + "Photo/wemes.png");
                FileStream titleFs = path.OpenRead();
                //FileStream titleFs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "Photo/wemes.png", FileMode.Open, FileAccess.Read);
                int filelength = (int)titleFs.Length; //获得文件长度
                Byte[] image = new Byte[filelength]; //建立一个字节数组 
                titleFs.Read(image, 0, filelength); //按字节流读
                //创建绘画基类
                var patriarch = (XSSFDrawing)et.CreateDrawingPatriarch();
                //图片定位
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 6, 4);
                //等比缩小
                anchor.AnchorType = AnchorType.MoveAndResize;
                //把图片插到相应的位置
                patriarch.CreatePicture(anchor, book.AddPicture(image, PictureType.PNG));
                //关闭标题文件流读取
                titleFs.Close();
                //创建标题
                for (int i = 4; i < 7; i++) {
                    var irow = et.CreateRow(i);
                    for (int j = 0; j < 6; j++) {
                        irow.CreateCell(j);
                    }
                }
                //合并
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 6, 0, 5));
                //设置标题
                var title = et.GetRow(4).GetCell(0);
                title.SetCellValue("Demo测试模板");
                XSSFCellStyle titleStyle = (XSSFCellStyle)book.CreateCellStyle();
                var titleFont = (XSSFFont)book.CreateFont();
                //字体大小
                titleFont.FontHeightInPoints = 20;
                titleFont.IsBold = true;
                titleStyle.SetFont(titleFont);
                //居中
                titleStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 方法1 
                titleStyle.Alignment = HorizontalAlignment.Center;//水平居中 方法2 
                //titleStyle.Alignment = HorizontalAlignment.CenterSelection;//水平居中 方法3
                title.CellStyle = titleStyle;
                //副标题创建
                for (int i = 7; i < 8; i++) {
                    var irow = et.CreateRow(i);
                    //设置行高
                    //在Excel中，每一行的高度也是要求一致的，所以设置单元格的高度，其实就是设置行的高度，所以相关的属性也应该在HSSFRow上，它就是HSSFRow.Height和HeightInPoints，
                    //这两个属性的区别在于HeightInPoints的单位是点，而Height的单位是1 / 20个点，所以Height的值永远是HeightInPoints的20倍。
                    //irow.Height = 25 * 20;
                    irow.HeightInPoints = 25;
                    for (int j = 0; j < 6; j++) {
                        irow.CreateCell(j);
                    }
                }
                //合并
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 0, 1));
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 2, 3));
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 4, 5));
                //设置副标题
                et.GetRow(7).GetCell(0).SetCellValue("编号1：");
                et.GetRow(7).GetCell(2).SetCellValue("编号2：");
                et.GetRow(7).GetCell(4).SetCellValue("编号3：");
                //第九行行高设置小点
                var irow_9 = et.CreateRow(8);
                irow_9.Height = 5 * 20;
                //为了保证数据准确性创建 查询数据库
                var sfc = new BaseDataAccess("sfc");
                var dataSource = sfc.db.SqlQueryable<object>(@"select top(10) a.NAME as '产品名称',b.LOT_NAME as '工单名称',c.NAME as '工艺流程',d.CUSTOMER_NAME as '客户名称',b.QUANTITY as '工单数量' from 
                QS_PRODUCTS a join QS_LOTS b on a.PRODUCT_ID = b.PRODUCT_ID join QS_ROUTES c on a.ROUTE_ID = c.ROUTE_ID 
                join QS_CUSTOMERS d on a.CUSTOMER_CODE = d.CUSTOMER_CODE order by a.CREATE_TIME desc").ToDataTable();
                //留出一行 所有行 + 自己的标题
                for (int i = 9; i < (dataSource.Rows.Count + 10); i++) {
                    var irow = et.CreateRow(i);
                    irow.HeightInPoints = 20;
                    for (int j = 0; j < dataSource.Columns.Count + 1; j++) {
                        var icell = irow.CreateCell(j);
                        var dataStyle = book.CreateCellStyle();
                        dataStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;    //下边框线
                        dataStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;      //左边框线
                        dataStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;     //右边框线
                        dataStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;       //上边框线
                        //内容垂直 水平居中
                        dataStyle.VerticalAlignment = VerticalAlignment.Center;
                        dataStyle.Alignment = HorizontalAlignment.Center;
                        //文字换行
                        dataStyle.WrapText = true;
                        icell.CellStyle = dataStyle;
                        //设置标题
                        if (i == 9 && j == 0) icell.SetCellValue("序号");
                        else if (i == 9) icell.SetCellValue(dataSource.Columns[j - 1].ColumnName);
                        else {
                            //设置下标
                            int dataIndex = i - dataSource.Rows.Count;
                            //插入数据源
                            if (j == 0) icell.SetCellValue((dataIndex + 1).ToString());
                            else icell.SetCellValue(dataSource.Rows[dataIndex][j - 1].ToString());
                        }
                    }
                }
                //获取最后一行
                var lastIndex = et.LastRowNum + 1;
                //填充标脚
                var footerRow = et.CreateRow(lastIndex);
                //创建列
                for (int i = 0; i < 6; i++) footerRow.CreateCell(i);
                //设置当前行高
                footerRow.HeightInPoints = 25;
                //合并
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastIndex, lastIndex, 1, 3));
                et.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(lastIndex, lastIndex, 4, 5));
                //填充内容
                var dateCell = footerRow.GetCell(1);
                dateCell.SetCellValue($"Date:{DateTime.Now.ToString("yyyy-MM-dd")}");
                var userCell = footerRow.GetCell(4);
                userCell.SetCellValue("User:梁非凡");
                //设置通用样式
                var footerCellStyle = book.CreateCellStyle();
                var footerCellFont = book.CreateFont();
                footerCellFont.FontName = "仿宋";
                footerCellFont.Boldweight = (short)400;
                footerCellFont.FontHeightInPoints = (short)14;
                footerCellStyle.Alignment = HorizontalAlignment.Left;
                footerCellStyle.VerticalAlignment = VerticalAlignment.Center;
                footerCellStyle.SetFont(footerCellFont);
                dateCell.CellStyle = footerCellStyle;
                userCell.CellStyle = footerCellStyle;
                //设置表格是否加锁 IsLocked默认为true，需要配合表格方法ProtectSheet才能生效，如果指定行不想加锁，需设置行的IsLocked = false
                //footerCellStyle.IsLocked = true;
                et.ProtectSheet("lff");
                book.Write(fs);
                //习惯性关闭
                fs.Close();
            }
            return "";
        }