////// DataTable导出到Excel文件/// /// 源DataTable/// 表头文本/// 保存位置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();}}}////// DataTable导出到Excel的MemoryStream/// /// 源DataTable/// 表头文本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;}#endregionHSSFCellStyle 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();}#endregionrowIndex = 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;}}#endregionrowIndex++;}using (MemoryStream ms = new MemoryStream()){workbook.Write(ms);ms.Flush();ms.Position = 0;//sheet.Dispose();//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheetreturn ms;}}////// 合并单元格/// /// 要合并单元格所在的sheet/// 开始行的索引/// 结束行的索引/// 开始列的索引/// 结束列的索引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);}