NPOI 生成Excel 应用

 1.自定义复杂表头的表格

        public override OperateResult GenerateTableFile(string start, string end, int siteId, string whereType, string whereSql)
        {
            OperateResult rlt = new OperateResult { isSuccess = true };

            string userDir;
            string fileDir;
            string sType;
            DataTable dt = GetExportData(start, end, siteId, whereType, whereSql, out userDir, out fileDir, out sType);
            if (dt.Rows.Count == 0)
            {
                rlt.isSuccess = false;
                rlt.errorMesg = "无数据，请统计后再导出";
                return rlt;
            }

            var fileName = "混凝土试件抗压记录.xlsx";
            var filePath = fileDir + "\\" + fileName;
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            CreateTableExcel(filePath, dt, "混凝土试件抗压记录", start, end, sType);

            rlt.model = string.Format("http://{0}/UserResource/BizImages/YaliJi/{1}/{2}", ConfigurationManager.AppSettings["DoMain"], userDir, fileName);
            return rlt;
        }

 

#region 生成Excel

        public void CreateTableExcel(string filepath, DataTable dt, string title, string start, string end, string sType)
        {
            try
            {
                var workbook = new XSSFWorkbook();
                //日期格式
                ICellStyle dateStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                ISheet sheet = workbook.CreateSheet();
                int rowIndex = 0;

                rowIndex = BuildTitleRows((dt.Columns.Count - 1), title, start, end, sType, workbook, sheet, rowIndex);

                rowIndex = BuildHeadRows(dt, workbook, sheet, rowIndex);

                //普通Cell的样式
                ICellStyle style = sheet.Workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.BorderBottom = style.BorderLeft = style.BorderRight = style.BorderTop = BorderStyle.Thin;
                foreach (DataRow row in dt.Rows)
                {
                    WriteDataRowToExcelSheet(sheet, row, rowIndex, style, dateStyle, 0);
                    ++rowIndex;
                }

                //列宽自适应，只对英文和数字有效.此操作太耗时间了
                //for (int i = 0; i <= sourceTable.Columns.Count; ++i)
                //    sheet.AutoSizeColumn(i);

                using (var sw = File.Create(filepath))
                    workbook.Write(sw);

            }
            catch (Exception ex)
            {
                throw ex;
            }

            //打开Excel文件
            //if (File.Exists(filepath))
            //{
            //    System.Diagnostics.Process.Start(filepath);
            //}
        }

        //标题及摘要
        private static int BuildTitleRows(int colCount, string title, string start, string end, string sType, XSSFWorkbook workbook, ISheet sheet, int rowIndex)
        {
            if (!string.IsNullOrEmpty(title))
            {
                IRow titleRow = sheet.CreateRow(rowIndex);
                titleRow.HeightInPoints = 24;//高度
                titleRow.CreateCell(0).SetCellValue(title);

                ICellStyle titleStyle = workbook.CreateCellStyle();
                titleStyle.Alignment = HorizontalAlignment.Center;
                IFont tFont = GetFontStyle(workbook, "", null, 20);
                titleStyle.SetFont(tFont);
                titleStyle.BorderBottom = titleStyle.BorderLeft = titleStyle.BorderRight = titleStyle.BorderTop = BorderStyle.Thin;

                titleRow.GetCell(0).CellStyle = titleStyle;
                SetCellRangeAddress(sheet, 0, 0, 0, colCount);
                rowIndex++;
            }

            int leftCol = (colCount / 3) * 2;
            int rightIndex = leftCol + 1;
            string zStr = "起始时间：" + start + "，截止时间：" + end + "，类型：" + sType;
            IRow zRow = sheet.CreateRow(rowIndex);
            zRow.HeightInPoints = 20;
            zRow.CreateCell(0).SetCellValue(zStr);

            ICellStyle zStyle = workbook.CreateCellStyle();
            zStyle.Alignment = HorizontalAlignment.Left;
            IFont zFont = GetFontStyle(workbook, "", null, 16);
            zStyle.SetFont(zFont);
            zRow.GetCell(0).CellStyle = zStyle;
            SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, leftCol);

            zRow.CreateCell(rightIndex).SetCellValue("导出时间: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            ICellStyle zStyle1 = workbook.CreateCellStyle();
            zStyle1.Alignment = HorizontalAlignment.Right;
            zStyle1.SetFont(zFont);
            zRow.GetCell(rightIndex).CellStyle = zStyle1;
            SetCellRangeAddress(sheet, rowIndex, rowIndex, rightIndex, colCount);

            rowIndex++;

            return rowIndex;
        }

        //列头 
        private static int BuildHeadRows(DataTable dt, XSSFWorkbook workbook, ISheet sheet, int rowIndex)
        {
            Dictionary<string, string> columnsList1 = GetColumnRow1(dt);
            Dictionary<string, string> columnsList2 = GetColumnRow2(dt);

            string[] arrVRange = { "编号", "成型日期", "工程", "部位", "标号", "水", "水泥", "矿粉", "煤灰", "砂", "石", "减水剂", "试压日期3", "试压日期7", "试压日期28" };
            string[] arrHRange = { "Y31", "Y71", "Y281" };

            IRow headRow = sheet.CreateRow(rowIndex);
            //列头样式设置
            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            headStyle.BorderBottom = headStyle.BorderLeft = headStyle.BorderRight = headStyle.BorderTop = BorderStyle.Thin;
            headStyle.SetFont(font);

            foreach (DataColumn column in dt.Columns)
            {
                var colName = column.ColumnName;
                if (columnsList1 != null && columnsList1.ContainsKey(column.ColumnName))
                    colName = columnsList1[column.ColumnName];

                headRow.CreateCell(column.Ordinal).SetCellValue(colName);
                headRow.GetCell(column.Ordinal).CellStyle = headStyle;
                //列宽设置
                var colWidth = Encoding.GetEncoding(936).GetBytes(colName).Length;
                colWidth++;
                colWidth = Math.Max(12, colWidth);
                if (colName.Contains("工程") || colName.Contains("部位"))
                {
                    colWidth = colWidth * 3;
                }
                if (colWidth * 256 > 30000)
                    sheet.SetColumnWidth(column.Ordinal, 10000);
                else
                    sheet.SetColumnWidth(column.Ordinal, colWidth * 256);

                if (arrVRange != null && arrHRange.Contains(column.ColumnName))
                {
                    SetCellRangeAddress(sheet, rowIndex, rowIndex, column.Ordinal, (column.Ordinal + 2));
                }
            }
            rowIndex++;

            IRow headRow1 = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dt.Columns)
            {
                var colName = column.ColumnName;
                if (columnsList2 != null && columnsList2.ContainsKey(column.ColumnName))
                    colName = columnsList2[column.ColumnName];

                headRow1.CreateCell(column.Ordinal).SetCellValue(colName);
                headRow1.GetCell(column.Ordinal).CellStyle = headStyle;

                if (arrVRange != null && arrVRange.Contains(column.ColumnName))
                {
                    SetCellRangeAddress(sheet, (rowIndex - 1), rowIndex, column.Ordinal, column.Ordinal);
                }
            }
            rowIndex++;

            return rowIndex;
        }


        private static Dictionary<string, string> GetColumnRow1(DataTable dt)
        {
            Dictionary<string, string> columnsNameList = new Dictionary<string, string>();
            var qdColName = "3d/Mpa";
            foreach (DataColumn item in dt.Columns)
            {
                if (item.ColumnName.Contains("试压日期"))
                {
                    qdColName = item.ColumnName.Replace("试压日期", "") + "d/Mpa";
                    columnsNameList.Add(item.ColumnName, "试压日期");
                }
                else if (item.ColumnName.Contains("Y"))
                {
                    columnsNameList.Add(item.ColumnName, "强度值");
                }
                else if (item.ColumnName.Contains("强度"))
                {
                    columnsNameList.Add(item.ColumnName, qdColName);
                }
                else
                {
                    columnsNameList.Add(item.ColumnName, item.ColumnName);
                }
            }
            return columnsNameList;
        }

        private static Dictionary<string, string> GetColumnRow2(DataTable dt)
        {
            Dictionary<string, string> columnsNameList = new Dictionary<string, string>();
            foreach (DataColumn item in dt.Columns)
            {
                if (item.ColumnName.Contains("Y"))
                {
                    columnsNameList.Add(item.ColumnName, item.ColumnName.Substring(item.ColumnName.Length - 1, 1));
                }
                else if (item.ColumnName.Contains("强度"))
                {
                    columnsNameList.Add(item.ColumnName, "强度");
                }
                else
                {
                    columnsNameList.Add(item.ColumnName, "");
                }
            }
            return columnsNameList;
        }

        /// <summary>
        /// 将数据行写入到Excel sheet中
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        /// <param name="rowIndex"></param>
        /// <param name="cellstyle"></param>
        /// <param name="dateStyle"></param>
        /// <param name="startColumn"></param>
        private void WriteDataRowToExcelSheet(ISheet sheet, DataRow row, int rowIndex, ICellStyle cellstyle, ICellStyle dateStyle, int startColumn)
        {
            var dataRow = sheet.CreateRow(rowIndex);
            foreach (DataColumn column in row.Table.Columns)
            {
                string drValue = row[column].ToString();
                var colNum = column.Ordinal;
                if (startColumn > 1)
                    colNum += startColumn - 1;

                ICell newCell = dataRow.CreateCell(colNum);
                newCell.CellStyle = cellstyle;
                switch (column.DataType.ToString())
                {
                    case "System.String": //字符串类型   
                        if (drValue.ToUpper() == "TRUE")
                            newCell.SetCellValue("是");
                        else if (drValue.ToUpper() == "FALSE")
                            newCell.SetCellValue("否");
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime": //日期类型    
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);
                        //日期格式
                        if (dateStyle == null)
                        {
                            dateStyle = sheet.Workbook.CreateCellStyle();
                            IDataFormat format = sheet.Workbook.CreateDataFormat();
                            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                        }
                        newCell.CellStyle = dateStyle; //格式化显示    
                        break;
                    case "System.Boolean": //布尔型    
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        if (boolV)
                            newCell.SetCellValue("是");
                        else
                            newCell.SetCellValue("否");
                        break;
                    case "System.Int16": //整型    
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal": //浮点型    
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull": //空值处理    
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
            }
        }

        #endregion

        #region 格式设置

        // <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet">要合并单元格所在的sheet</param>
        /// <param name="rowstart">开始行的索引</param>
        /// <param name="rowend">结束行的索引</param>
        /// <param name="colstart">开始列的索引</param>
        /// <param name="colend">结束列的索引</param>
        private static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            var cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 获取字体样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="fontname">字体名</param>
        /// <param name="fontcolor">字体颜色</param>
        /// <param name="fontsize">字体大小</param>
        /// <returns></returns>
        private static IFont GetFontStyle(IWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize)
        {
            IFont font = hssfworkbook.CreateFont();
            if (!string.IsNullOrEmpty(fontfamily))
            {
                font.FontName = fontfamily;
            }
            if (fontcolor != null)
            {
                font.Color = fontcolor.Indexed; //.GetIndex();
            }
            font.FontHeightInPoints = (short)fontsize;
            return font;
        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="font">单元格字体</param>
        /// <param name="fillForegroundColor">图案的颜色</param>
        /// <param name="fillPattern">图案样式</param>
        /// <param name="fillBackgroundColor">单元格背景</param>
        /// <param name="ha">垂直对齐方式</param>
        /// <param name="va">垂直对齐方式</param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font,
                                              HSSFColor fillForegroundColor, FillPattern fillPattern,
                                              HSSFColor fillBackgroundColor, HorizontalAlignment ha,
                                              VerticalAlignment va)
        {
            ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
            cellstyle.FillPattern = fillPattern;
            cellstyle.Alignment = ha;
            cellstyle.VerticalAlignment = va;
            if (fillForegroundColor != null)
            {
                cellstyle.FillForegroundColor = fillForegroundColor.Indexed; //.GetIndex();
            }
            if (fillBackgroundColor != null)
            {
                cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed; //.GetIndex();
            }
            if (font != null)
            {
                cellstyle.SetFont(font);
            }
            //有边框
            cellstyle.BorderBottom = BorderStyle.Thin; // CellBorderType.THIN;
            cellstyle.BorderLeft = BorderStyle.Thin;
            cellstyle.BorderRight = BorderStyle.Thin;
            cellstyle.BorderTop = BorderStyle.Thin;
            return cellstyle;
        }

        #endregion

 

 

1.记录一个错误，出现“发现'xxx.xlsx'中的部分内容有问题，是否尽量尝试恢复”。

这个错误可能有多种原因引起，网上遇到的原因：

1.在获得 workbook.Write(ms) 生成的 MemoryStream 后，使用了 ms.GetBuffer() 返回文件内容，导致生成的 Excel 文件结尾处有大量的 00（空字节），改为 ms.ToArray() 即可得到正常的文件了。

2.因为在导出前excel是由模板读取的，可能会导致结果出现

3.将Npoi的nuget包降级，之前用的是 2.5.1的版本，降级到2.4.1后，不再提示

4.将Npoi的nuget包升级，NPOI 由 v2.5.1 升级到 v2.5.2，将 SharpZipLib 由 v1.2.0 升级到 v1.3.1 后，不再提示 

5.添加“Content-Length” 参数时，使用的length，是byte数组的长度，并非流的长度，把bytes.Length.ToString()改为dataStream.Length.ToString()，问题就解决了

6.代码中字体设置错误。（我遇到的）