关于NPOI导出Excel,设置表格样式(合并单元格,表格线,批注)方法
/// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName) { using (MemoryStream ms = DataTableToExcel(dtSource, strHeaderText)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "合理化建议平台";//填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; SetCellRangeAddress(sheet, 0, 0, 0, 11); SetCellRangeAddress(sheet, 0, 0, 12, 18); SetCellRangeAddress(sheet, 0, 0, 19, 24); headerRow.CreateCell(0).SetCellValue("第1阶段:建议征集表"); headerRow.CreateCell(12).SetCellValue("第2阶段:建议答复及评比推荐表"); headerRow.CreateCell(19).SetCellValue("第3阶段:建议答复反馈与实施"); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); //headStyle.WrapText = true;//自动换行 //headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index; headerRow.GetCell(0).CellStyle = headStyle; headerRow.GetCell(12).CellStyle = headStyle; headerRow.GetCell(19).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); headStyle.WrapText = true;//自动换行 headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//表格线 headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; headStyle.Alignment = HorizontalAlignment.Center;//水平居中 //添加批注 HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFComment comment12 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3));//批注显示定位 comment12.String = new HSSFRichTextString("请填写完整部门名称!"); HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);//将批注给予单元格 cell12.CellComment = comment12; foreach (DataColumn column in dtSource.Columns) { //设置列宽 写死 string Names = ""; switch (column.ColumnName) { case "TOPICID": Names = "序列号"; sheet.SetColumnWidth(0, 16 * 256); break; case "TYPE": Names = "类型"; sheet.SetColumnWidth(1, 10 * 256); break; case "ZTSX": Names = "专题属性"; sheet.SetColumnWidth(2, 12 * 256); break; case "TA_TYPE": Names = "类别"; sheet.SetColumnWidth(3, 12 * 256); break; case "AUTHORDEPTNAME": Names = "部门"; sheet.SetColumnWidth(4, 10 * 256); break; case "AUTHORID": Names = "岗位编码"; sheet.SetColumnWidth(5, 10 * 256); break; case "AUTHOR": Names = "提议人"; sheet.SetColumnWidth(6, 10 * 256); break; case "FYR_PEOPLE": Names = "附议人"; sheet.SetColumnWidth(7, 10 * 256); break; case "TITLE": Names = "主题"; sheet.SetColumnWidth(8, 18 * 256); break; case "MISSING": Names = "原有缺失"; sheet.SetColumnWidth(9, 40 * 256); break; case "IMPROVE": Names = "改进办法"; sheet.SetColumnWidth(10, 40 * 256); break; case "RESULT": Names = "预期效果"; sheet.SetColumnWidth(11, 28 * 256); break; case "ZN_DEPT": Names = "答复部门"; sheet.SetColumnWidth(12, 10 * 256); break; case "DFR": Names = "答复人"; sheet.SetColumnWidth(13, 10 * 256); break; case "ZXJY": Names = "是否属于专项建议"; sheet.SetColumnWidth(14, 10 * 256); break; case "SFCN": Names = "是否采纳"; sheet.SetColumnWidth(15, 10 * 256); break; case "DFYJ": Names = "答复意见"; sheet.SetColumnWidth(16, 30 * 256); break; case "TA_YX": Names = "是否推荐为优秀建议"; sheet.SetColumnWidth(17, 10 * 256); break; case "TJJX": Names = "推荐奖项"; sheet.SetColumnWidth(18, 10 * 256); break; case "APPRAISAL_RESULTS": Names = "提议人评价意见"; sheet.SetColumnWidth(19, 12 * 256); break; case "SSBM": Names = "实施部门"; sheet.SetColumnWidth(20, 12 * 256); break; case "SSZRR": Names = "实施责任人"; sheet.SetColumnWidth(21, 8 * 256); break; case "SSQK": Names = "实施情况"; sheet.SetColumnWidth(22, 22 * 256); break; case "WGZT": Names = "是否完工"; sheet.SetColumnWidth(23, 8 * 256); break; case "YQWGSJ": Names = "预期完工时间"; sheet.SetColumnWidth(24, 12 * 256); break; } headerRow.CreateCell(column.Ordinal).SetCellValue(Names); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //采用自动列宽,根据列名字符长度 //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); //if (arrColWidth[column.Ordinal] > 255) //{ // arrColWidth[column.Ordinal] = 254; //} //else //{ // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); //} } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); HSSFCellStyle contextStyle = (HSSFCellStyle)workbook.CreateCellStyle(); contextStyle.WrapText = true;//自动换行 contextStyle.Alignment = HorizontalAlignment.Center;//水平居中 contextStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 contextStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //表格线 contextStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; contextStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; contextStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); dataRow.GetCell(column.Ordinal).CellStyle = contextStyle; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); 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 rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return ms; } } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); }