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.代码中字体设置错误。（我遇到的）
