效果图：
代码：
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="DeptId"></param>
[HttpPost]
public void ExportToExcel(int DeptId,List<FM_CostApply> CostApplyList, int beginYear, int beginMonth, int endYear, int endMonth)
{
foreach (var Item in CostApplyList)
{
CostApplyItem.AddRange(Item.FM_CostApplyItem);
}
var Project = CostApplyItem.GroupBy(a => a.FM_Project.ProjectName).ToList();
//创建工作簿
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
string[] headName = { "年度", "月", "日", "申请类型", "新科目名称", "部门名称", "项目名称", "凭证号", "摘要", "金额" };
string[] ColumnName = { "Year", "Month", "Day", "Type", "SubJectName", "DeptName", "ProjectName", "CardNum", "Summary", "Cost" };
//创建Sheet页
if (Project.Count > )
{
foreach (var proc in Project)
{
//该项目下申请的所有的科目
var SubjectName = CostApplyItem.Where(a => a.FM_Project.ProjectName == proc.Key).GroupBy(a => a.FM_SecondSubject.SubjectName).ToList();
try
{
//创建Sheet页
ISheet sheet = hssfworkbook.CreateSheet(proc.Key); //获取项目下的费用明细
List<CostApplyExcel> model = GetCostApply(proc.Key, CostApplyList); var Dic = model.GroupBy(a => a.SubJectName).ToDictionary(w => w.Key, r => r.ToList()); //集合转换为DataTable
DataTable dt = ConvtToDataTable.ToDataTable<CostApplyExcel>(model); int RowIndex = ; #region 如果为第一行
IRow IRow = sheet.CreateRow();
for (int h = ; h < ; h++)
{
ICell Icell = IRow.CreateCell(h);
Icell.SetCellValue(BeginDate.ToString("yyyy.MM") + "-" + EndDate.ToString("yyyy.MM") + " " + proc.Key + "项目汇总表"); ICellStyle style = hssfworkbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.CENTER;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = ;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
Icell.CellStyle = style;
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(, , , ));
}
#endregion #region 表头
IRow Irows2 = sheet.CreateRow();
for (int j = ; j < ; j++)
{
ICell Icell2 = Irows2.CreateCell(j);
ICellStyle Istyle2 = hssfworkbook.CreateCellStyle();
//设置边框
Istyle2.BorderTop = BorderStyle.THIN;
Istyle2.BorderBottom = BorderStyle.THIN;
Istyle2.BorderLeft = BorderStyle.THIN;
Istyle2.BorderRight = BorderStyle.THIN;
//设置单元格的样式：水平对齐居中
Istyle2.Alignment = HorizontalAlignment.CENTER;
//新建一个字体样式对象
IFont Ifont2 = hssfworkbook.CreateFont();
Ifont2.FontName = "宋体";
Ifont2.FontHeightInPoints = ;
//设置字体加粗样式
Ifont2.Boldweight = (short)FontBoldWeight.BOLD;
//使用SetFont方法将字体样式添加到单元格样式中
Istyle2.SetFont(Ifont2);
//将新的样式赋给单元格
Icell2.CellStyle = Istyle2;
Icell2.SetCellValue(headName[j]);
}
#endregion foreach (var DicItem in Dic)
{
int SumStartRows = RowIndex + ; //求和的开始行
//集合转换为DataTable
DataTable table = ConvtToDataTable.ToDataTable<CostApplyExcel>(DicItem.Value);
for (int i = ; i <= DicItem.Value.Count; i++)
{
IRow row = sheet.CreateRow(RowIndex); if (i == DicItem.Value.Count)
{
for (int j = ; j < ; j++)
{
if (j == )
{
#region 汇总求和文字
ICell cell = row.CreateCell(j); DataRow TableRow = table.Rows[i - ];
string subName = TableRow[].ToString(); ICellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = BorderStyle.THIN;
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.CENTER;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = ;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style;
cell.SetCellValue(subName + " 汇总");
#endregion
}
else if (j == ) //合计
{
#region 汇总求和公式插入
ICell cell = row.CreateCell(j);
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = BorderStyle.THIN;
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.CENTER;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = ;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style; string format = "sum(";
for (int s = SumStartRows; s < (DicItem.Value.Count + SumStartRows); s++)
{
format += ("J" + s + ",");
}
format += ")"; cell.SetCellFormula(format); #endregion 汇总求和
}
else
{
#region 汇总求和-普通单元格
ICell cell = row.CreateCell(j);
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = BorderStyle.THIN;
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.CENTER;
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = ;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style;
#endregion
}
}
}
else if (i < DicItem.Value.Count)
{
#region 插入值
DataRow TableRow = table.Rows[i];
for (int j = ; j < ; j++)
{
ICell cell = row.CreateCell(j);
ICellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = BorderStyle.THIN;
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.CENTER;
//设置单元格属性为文本
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//新建一个字体样式对象
IFont font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = ;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//将新的样式赋给单元格
cell.CellStyle = style;
string val = TableRow[ColumnName[j]].ToString();
if (j == )
{
double cost = double.Parse(val);
cell.SetCellValue(cost);
}
else
{
cell.SetCellValue(val);
}
}
#endregion
}
RowIndex++;
}
}
for (int h = ; h < ; h++)
{
sheet.AutoSizeColumn(h); //会按照值的长短 自动调节列的大小
}
}
catch (Exception ex) { }
}
}
else
{
//创建Sheet页
ISheet sheet = hssfworkbook.CreateSheet();
}
string Path = Server.MapPath("~/upload/财务导出");
if (!System.IO.Directory.Exists(Path))
System.IO.Directory.CreateDirectory(Path);
string fileName = DateTime.Now.ToFileTime() + ".xls";
using (FileStream file = new FileStream(Path + "\\" + fileName, FileMode.Create))
{
hssfworkbook.Write(file); //创建test.xls文件。
file.Close();
result = ConfigurationManager.AppSettings["Websitet"] + "upload/财务导出/" + fileName;
}
HttpContext context = System.Web.HttpContext.Current;
context.Response.Write(result);
context.Response.End();
}