/// <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
}
