效果图：
代码：
/// <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 > 0) { 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 = 2; #region 如果为第一行 IRow IRow = sheet.CreateRow(0); for (int h = 0; h < 10; 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 = 18; //设置字体加粗样式 font.Boldweight = (short)FontBoldWeight.BOLD; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 Icell.CellStyle = style; //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 9)); } #endregion #region 表头 IRow Irows2 = sheet.CreateRow(1); for (int j = 0; j < 10; 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 = 11; //设置字体加粗样式 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 + 1; //求和的开始行 //集合转换为DataTable DataTable table = ConvtToDataTable.ToDataTable<CostApplyExcel>(DicItem.Value); for (int i = 0; i <= DicItem.Value.Count; i++) { IRow row = sheet.CreateRow(RowIndex); if (i == DicItem.Value.Count) { for (int j = 0; j < 10; j++) { if (j == 3) { #region 汇总求和文字 ICell cell = row.CreateCell(j); DataRow TableRow = table.Rows[i - 1]; string subName = TableRow[5].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 = 11; //设置字体加粗样式 font.Boldweight = (short)FontBoldWeight.BOLD; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 cell.CellStyle = style; cell.SetCellValue(subName + " 汇总"); #endregion } else if (j == 9) //合计 { #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 = 11; //使用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 = 11; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 cell.CellStyle = style; #endregion } } } else if (i < DicItem.Value.Count) { #region 插入值 DataRow TableRow = table.Rows[i]; for (int j = 0; j < 10; 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 = 11; //使用SetFont方法将字体样式添加到单元格样式中 style.SetFont(font); //将新的样式赋给单元格 cell.CellStyle = style; string val = TableRow[ColumnName[j]].ToString(); if (j == 9) { double cost = double.Parse(val); cell.SetCellValue(cost); } else { cell.SetCellValue(val); } } #endregion } RowIndex++; } } for (int h = 0; h < 9; 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(); }
