先上效果图：
先声明一个IWorkbook对象：
IWorkbook workbook;//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式string fileExt = Path.GetExtension(fileName).ToLower();if (fileExt == ".xlsx") {workbook = new XSSFWorkboo}else if (fileExt == ".xls") {workbook = new HSSFWorkbook();} else {workbook = null;}if (workbook == null) {return;}
标题合并单元格，字体居中加粗，先定义单元格样式：
ICellStyle cellStyle = workbook.CreateCellStyle();//声明样式cellStyle.Alignment = HorizontalAlignment.Center;//水平居中cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中IFont font = workbook.CreateFont();//声明字体font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗font.FontHeightInPoints = 18;//字体大小cellStyle.SetFont(font);//加入单元格
其次实现单元格合并：
IRow row0 = sheet.CreateRow(0);//创建行row0.HeightInPoints = 35;//行高ICell cell0 = row0.CreateCell(0);//创建单元格cell0.SetCellValue("XXXX年XX月对账单");//赋值cell0.CellStyle = cellStyle;//设置样式sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtBody.Columns.Count-1));//合并单元格（第几行，到第几行，第几列，到第几列）
这样第一列的标题效果完成，接下来是一行的多个单元格合并，步骤如上，需要注意的是，单元格赋值的时候，下标要写每个合并单元格域的第一列，不然会被覆盖。
接下来是一个DataTable的读取，可以先计算每一列的列宽：
int[] arrColWidth = new int[dtBody.Columns.Count];//声明列宽数组foreach (DataColumn item in dtBody.Columns)//先存储列头的宽度{arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;}for (int i = 0; i < dtBody.Rows.Count; i++)//遍历整张表，如果宽度大于列头宽度，则替换{for (int j = 0; j < dtBody.Columns.Count; j++){int intTemp = Encoding.GetEncoding(936).GetBytes(dtBody.Rows[i][j].ToString()).Length;if (intTemp > arrColWidth[j]){arrColWidth[j] = intTemp;}}}
接下来填充数据：
#region 填充表头IRow row = sheet.CreateRow(2);//先前占用了2行，表头从3开始for (int i = 0; i < dtBody.Columns.Count; i++){ICell cell = row.CreateCell(i);cell.SetCellValue(dtBody.Columns[i].ColumnName);sheet.SetColumnWidth(i, arrColWidth[i] * 300);cell.CellStyle = dateStyle;}#endregion#region 填充表身for (int i = 0; i < dtBody.Rows.Count; i++){IRow row1 = sheet.CreateRow(i + 3);//表身从3开始for (int j = 0; j < dtBody.Columns.Count; j++){ICell cell = row1.CreateCell(j);if (dtBody.Columns[j].DataType.FullName == "System.Decimal")//数字类型的值单独的样式{cell.SetCellType(CellType.Numeric);if (dtBody.Rows[i][j].ToString() == ""){cell.SetCellValue(0);}else{cell.SetCellValue(double.Parse(dtBody.Rows[i][j].ToString()));}cell.CellStyle = numberStyle;//数字样式}else{cell.SetCellValue(dtBody.Rows[i][j].ToString());cell.CellStyle = initStyle;}}#region 统计行if (i == dtBody.Rows.Count - 1)//最后一行{IRow rowCount = sheet.CreateRow(i + 4);//追加一行统计列sheet.AddMergedRegion(new CellRangeAddress(i+ 4, i+4 , 0, 5));//合并单元格（第几行，到第几行，第几列，到第几列）ICell cellEnd1 = rowCount.CreateCell(6);//创建单元格ICell cellEnd2 = rowCount.CreateCell(7);ICell cellEnd3 = rowCount.CreateCell(8);ICell cellEnd4 = rowCount.CreateCell(9);;cellEnd1.SetCellValue("对帐总金额：");cellEnd3.SetCellValue("确认总金额：");cellEnd1.CellStyle = textStyle;cellEnd3.CellStyle = textStyle;cellEnd2.CellStyle = moneyStyle;cellEnd4.CellStyle = moneyStyle;sheet.SetColumnWidth(6, 16 * 256);//设置单元格宽度sheet.SetColumnWidth(7, 14 * 256);sheet.SetColumnWidth(8, 16 * 256);sheet.SetColumnWidth(9, 14 * 256);string CellFormulaString1= "sum(I4:I"+ (dtBody.Rows.Count + 3) + ")"; //计算表达式，行统计： sum（A1:C1）,列统计：sum(B1:B10)string CellFormulaString2= "sum(J4:J"+ (dtBody.Rows.Count + 3) + ")"; //计算表达式sheet.GetRow(dtBody.Rows.Count + 3).GetCell(7).SetCellFormula(CellFormulaString1);//赋值表达式sheet.GetRow(dtBody.Rows.Count + 3).GetCell(9).SetCellFormula(CellFormulaString2);}#endregion}#endregion
统计行中moneyStyle单元格样式中需要添加“￥”字符，网上搜了很久的方法没有找到，自己无意中试出来的：
ICellStyle moneyStyle= workbook.CreateCellStyle();IFont font1 = workbook.CreateFont();font1.Color = (Int16)FontColor.Red;moneyStyle.SetFont(font1);IDataFormat dataformat = workbook.CreateDataFormat();style3.DataFormat = dataformat.GetFormat("￥0.00"); ;
填充完数据之后，需要将其写入文件中，然后导出：
MemoryStream stream = new MemoryStream(); workbook.Write(stream);var buf = stream.ToArray();//转为字节数组 //创建文件using (FileStream fs = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write))//savePath为文件保存路径{fs.Write(buf, 0, buf.Length);fs.Flush();}
至此，后端完成
