/// <summary> /// 创建Excel标题行 /// </summary> /// <param name="workbook">工作簿对象</param> /// <param name="sheet">Excel单页</param> /// <param name="strHeaderText">标题行文本</param> /// <param name="dtSource">DataTable数据源</param> public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource) { #region 表头及样式 { Row headerRow = sheet.CreateRow(startRowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; Font font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headStyle.WrapText = true; headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { int[] arrColWidth = GetColumnWidth(dtSource); Row headerRow = sheet.CreateRow(startRowIndex+1); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; Font font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion }
/// <summary> /// 填充Excel数据行 /// </summary> /// <param name="workbook">工作簿对象</param> /// <param name="sheet">Excel单页</param> /// <param name="startRowIndex">数据行开始索引,默认为2</param> /// <param name="dtSource">DataTable数据源</param> /// <param name="formatOptions">格式化选项</param> /// <param name="footerAvgColumns">平均汇总的列</param> /// <param name="footerSumColumns">总和汇总的列</param> /// <param name="mergeColumnIndexs">合并的列</param> public void FillRowData(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, DataTable dtSource, Dictionary<int, FormatStyle> formatOptions, List<int> footerAvgColumns, List<int> footerSumColumns, List<int> mergeColumnIndexs) { #region 格式化选项 CellStyle dateTimeStyle = workbook.CreateCellStyle(); DataFormat dateTimeFormat = workbook.CreateDataFormat(); dateTimeStyle.DataFormat = dateTimeFormat.GetFormat("yyyy-mm-dd hh:mm:ss"); CellStyle dateStyle = workbook.CreateCellStyle(); DataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); CellStyle percentStyle = workbook.CreateCellStyle(); percentStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); CellStyle toFix2CellStyle = workbook.CreateCellStyle(); toFix2CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); #endregion for (int i = 0; i < dtSource.Rows.Count; i++) { Row dataRow = sheet.CreateRow(startRowIndex); #region 填充内容 for (int j = 0; j < dtSource.Columns.Count; j++) { Cell newCell = dataRow.CreateCell(j); string drValue = dtSource.Rows[i][j].ToString(); switch (dtSource.Columns[j].DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); if (formatOptions != null && formatOptions.Count > 0) { // 格式化设置 if (formatOptions.ContainsKey(j)) { switch (formatOptions[j]) { case FormatStyle.Date: newCell.CellStyle = dateStyle; break; default: newCell.CellStyle = dateTimeStyle; break; } } } else { newCell.CellStyle = dateTimeStyle; } newCell.SetCellValue(dateV); 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); if (formatOptions != null && formatOptions.Count > 0) { // 格式化设置 if (formatOptions.ContainsKey(j)) { switch (formatOptions[j]) { case FormatStyle.Percent: newCell.CellStyle = percentStyle; break; case FormatStyle.ToFix2: newCell.CellStyle = toFix2CellStyle; break; } } } newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion startRowIndex++; } #region 合并单元格 if (mergeColumnIndexs != null) { foreach (int startIndex in mergeColumnIndexs) { int t1, t2, initSpan; string temp; for (t1 = 2; t1 <= sheet.LastRowNum; t1++) { initSpan = 1; if (sheet.GetRow(t1).GetCell(startIndex) != null) { Cell cell = sheet.GetRow(t1).GetCell(startIndex); switch (cell.CellType) { case CellType.NUMERIC: temp = sheet.GetRow(t1).GetCell(startIndex).DateCellValue.ToString(); break; case CellType.STRING: temp = sheet.GetRow(t1).GetCell(startIndex).StringCellValue; break; default: temp = sheet.GetRow(t1).GetCell(startIndex).StringCellValue; break; } for (t2 = t1 + 1; t2 <= sheet.LastRowNum; t2++) { if (sheet.GetRow(t2).GetCell(startIndex) != null) { Cell cellCol = sheet.GetRow(t2).GetCell(startIndex); string current = string.Empty; switch (cellCol.CellType) { case CellType.NUMERIC: current = sheet.GetRow(t2).GetCell(startIndex).DateCellValue.ToString(); break; case CellType.STRING: current = sheet.GetRow(t2).GetCell(startIndex).StringCellValue; break; default: current = sheet.GetRow(t2).GetCell(startIndex).StringCellValue; break; } if (String.Compare(temp, current) == 0) { initSpan++; CellRangeAddress region = new CellRangeAddress(t1, t2, startIndex, startIndex); sheet.AddMergedRegion(region); CellStyle style = workbook.CreateCellStyle(); style.VerticalAlignment = VerticalAlignment.BOTTOM; sheet.GetRow(t1).GetCell(startIndex).CellStyle = style; } else { break; } } } t1 = t2 - 1; } } } } #endregion #region 汇总运算 string[] alphabet = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; CellStyle footerRowStyle = workbook.CreateCellStyle(); #region 平均值汇总运算 if (footerAvgColumns != null && footerAvgColumns.Count > 0) { startRowIndex++; Row footerAvgTitleRow = sheet.CreateRow(startRowIndex++); footerAvgTitleRow.RowStyle = footerRowStyle; Cell footerAvgTitleCell = footerAvgTitleRow.CreateCell(0); footerAvgTitleCell.SetCellValue("平均:"); Row footerAvgRow = sheet.CreateRow(startRowIndex++); for (int i = 0; i < footerAvgColumns.Count; i++) { Cell footerAvg5Cell = footerAvgRow.CreateCell(footerAvgColumns[i]); switch (formatOptions[footerAvgColumns[i]]) { case FormatStyle.Percent: footerAvg5Cell.CellStyle = percentStyle; break; case FormatStyle.ToFix2: footerAvg5Cell.CellStyle = toFix2CellStyle; break; } string num = alphabet[footerAvgColumns[i]]; string formulaExpr = string.Format("AVERAGE(${0}3:${0}{1})", num, dtSource.Rows.Count + 3); footerAvg5Cell.CellFormula = formulaExpr; } } #endregion #region 总和汇总运算 if (footerSumColumns != null && footerSumColumns.Count > 0) { startRowIndex++; Row footerSumTitleRow = sheet.CreateRow(startRowIndex++); footerSumTitleRow.RowStyle = footerRowStyle; Cell footerSumTitleCell = footerSumTitleRow.CreateCell(0); footerSumTitleCell.SetCellValue("总计:"); Row footerSumRow = sheet.CreateRow(startRowIndex++); for (int i = 0; i < footerSumColumns.Count; i++) { Cell footerSumCell = footerSumRow.CreateCell(footerSumColumns[i]); switch (formatOptions[footerSumColumns[i]]) { case FormatStyle.Percent: footerSumCell.CellStyle = percentStyle; break; case FormatStyle.ToFix2: footerSumCell.CellStyle = toFix2CellStyle; break; } string num = alphabet[footerSumColumns[i]]; string formulaExpr = string.Format("SUM(${0}3:${0}{1})", num, dtSource.Rows.Count + 3 + 1); footerSumCell.CellFormula = formulaExpr; } } #endregion #endregion }