1.引入依赖Aspose.Cells
2.设计报表样式及数据填充
3.详细见代码
using Microsoft.AspNetCore.Mvc;using Microsoft.EntityFrameworkCore.Infrastructure;using Aspose.Cells; //excel报表控件using System;using System.IO;using NPOI.SS.UserModel;using System.Drawing;using System.Data;using NPOI.POIFS.Properties;namespace SMS.Controllers{public class GreinerFactoryController : Controller{//产能报表前端页面public IActionResult ProductivityReport(){return View();}/// <summary>/// 每日产能监控/// </summary>/// <returns></returns>[HttpPost]public FileStreamResult EverydayProduction_Excel(){string fileName = "片材日产出监控表" + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; // 文件名称string filePath = AppContext.BaseDirectory + "upload\\download\\" + fileName; //文件路径//实列化一个excel对象Workbook workbook = new Workbook();//实例化sheet页Worksheet worksheet = workbook.Worksheets[0];//获取单元格对象Cells cells = worksheet.Cells;//第一行：标题cells.Merge(0, 0, 1, 55); //合并单元格，第0行，第0列，合并1行，55列cells["A1"].PutValue("吴江片材车间生产计划 Production plan of Extru"); //赋值Style style1 = workbook.CreateStyle(); //创建一个新的样式对象style1.Font.Size = 18; //字体18号style1.Font.Name = "宋体"; //字体style1.Font.IsBold = true; //粗体 style1.HorizontalAlignment = TextAlignmentType.Center; //水平居中style1.VerticalAlignment = TextAlignmentType.Center; //垂直居中style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界线cells["A1"].SetStyle(style1);//设置A1单元格样式cells.SetRowHeight(0, 36.75);//设置行高 cells.SetColumnWidth(0, 18); //设置列宽 cells.SetColumnWidth(1, 18);cells.SetColumnWidth(6, 18);cells.SetColumnWidth(11, 18);cells.SetColumnWidth(16, 18);cells.SetColumnWidth(21, 18);cells.SetColumnWidth(26, 18);cells.SetColumnWidth(31, 18);cells.SetColumnWidth(36, 18);cells.SetColumnWidth(40, 18);//第二行：表头-机台cells.SetRowHeight(1, 33.75); //设置行高 cells["A2"].PutValue("设备Mac.\r\n\\日期Date");cells.Merge(1, 1, 1, 5);cells["B2"].PutValue("1#");cells.Merge(1, 6, 1, 5);cells["G2"].PutValue("2#");cells.Merge(1, 11, 1, 5);cells["L2"].PutValue("3#");cells.Merge(1, 16, 1, 5);cells["Q2"].PutValue("9# PET连线");cells.Merge(1, 21, 1, 5);cells["V2"].PutValue("5#");cells.Merge(1, 26, 1, 5);cells["AA2"].PutValue("6#");cells.Merge(1, 31, 1, 5);cells["AF2"].PutValue("7#");cells.Merge(1, 36, 1, 5);cells["AK2"].PutValue("8#");cells["AP2"].PutValue("片材");cells["AQ2"].PutValue("片材");cells["AR2"].PutValue("9# PET");cells["AS2"].PutValue("9# PET");//第二行整体样式Style style2 = workbook.CreateStyle();style2.Font.Size = 11;style2.Font.Name = "宋体";style2.Font.IsBold = true;style2.IsTextWrapped = true; //单元格内容自动换行style2.HorizontalAlignment = TextAlignmentType.Center; //水平居中style2.VerticalAlignment = TextAlignmentType.Center;//垂直居中style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界线//给第2行第1列到第2行第45列总共45个单元格设置批量样式Aspose.Cells.Range range2 = worksheet.Cells.CreateRange(1, 0, 1, 45); //批量设置单元格样式range2.ApplyStyle(style2, new StyleFlag() { All = true });//获取第二行原有样式，并在此基础上在Q2单元格上追加样式Style styleQ2 = cells["Q2"].GetStyle();styleQ2.ForegroundColor = Color.FromArgb(141, 180, 226); //设置背景颜色styleQ2.Pattern = BackgroundType.Solid; //设置背景色需要加这行cells["Q2"].SetStyle(styleQ2);//第三行：表头cells.SetRowHeight(2, 36.75);//设置行高 cells["B3"].PutValue("片材名称\r\nSKU");cells["C3"].PutValue("计划产量kg\r\nPlan");cells["D3"].PutValue("实际产量kg\r\nActual");cells["E3"].PutValue("完成率\r\ncompletion");cells["F3"].PutValue("差异\r\nVariance");cells["G3"].PutValue("片材名称\r\nSKU");cells["H3"].PutValue("计划产量kg\r\nPlan");cells["I3"].PutValue("实际产量kg\r\nActual");cells["J3"].PutValue("完成率\r\ncompletion");cells["K3"].PutValue("差异\r\nVariance");cells["L3"].PutValue("片材名称\r\nSKU");cells["M3"].PutValue("计划产量kg\r\nPlan");cells["N3"].PutValue("实际产量kg\r\nActual");cells["O3"].PutValue("完成率\r\ncompletion");cells["P3"].PutValue("差异\r\nVariance");cells["Q3"].PutValue("76纸芯管天津恒瑞PP\\PS");cells["R3"].PutValue("计划产量（万只）");cells["S3"].PutValue("实际产量（万只）");cells["T3"].PutValue("完成率\r\ncompletion");cells["U3"].PutValue("差异\r\nVariance");cells["V3"].PutValue("片材名称\r\nSKU");cells["W3"].PutValue("计划产量kg\r\nPlan");cells["X3"].PutValue("实际产量kg\r\nActual");cells["Y3"].PutValue("完成率\r\ncompletion");cells["Z3"].PutValue("差异\r\nVariance");cells["AA3"].PutValue("片材名称\r\nSKU");cells["AB3"].PutValue("计划产量kg\r\nPlan");cells["AC3"].PutValue("实际产量kg\r\nActual");cells["AD3"].PutValue("完成率\r\ncompletion");cells["AE3"].PutValue("差异\r\nVariance");cells["AF3"].PutValue("片材名称\r\nSKU");cells["AG3"].PutValue("计划产量kg\r\nPlan");cells["AH3"].PutValue("实际产量kg\r\nActual");cells["AI3"].PutValue("完成率\r\ncompletion");cells["AJ3"].PutValue("差异\r\nVariance");cells["AK3"].PutValue("片材名称\r\nSKU");cells["AL3"].PutValue("计划产量kg\r\nPlan");cells["AM3"].PutValue("实际产量kg\r\nActual");cells["AN3"].PutValue("完成率\r\ncompletion");cells["AO3"].PutValue("差异\r\nVariance");cells["AP3"].PutValue("班计划产量（kg）\r\nPlan/shift");cells["AQ3"].PutValue("实际产量（kg）\r\nAct./shift");cells["AR3"].PutValue("班计划产量（万只）");cells["AS3"].PutValue("实际产量（万只）");cells["AT3"].PutValue("计划开机\r\nPlaned runing no.");cells["AU3"].PutValue("实际开机\r\nAct. running no.");cells["AV3"].PutValue("每日完成率（%）\r\nCompletion");cells["AW3"].PutValue("周计划产量（kg）\r\nWeekly plan");cells["AX3"].PutValue("周实际产量（kg）\r\nWeekly act.");cells["AY3"].PutValue("周计划完成率(%)\r\nweekly completion");//表头及主体样式，都用style3Style style3 = workbook.CreateStyle();style3.Font.Size = 10;style3.Font.Name = "宋体";style3.Font.IsBold = true;style3.IsTextWrapped = true;//单元格内容自动换行style3.HorizontalAlignment = TextAlignmentType.Center; //水平居中style3.VerticalAlignment = TextAlignmentType.Center; //垂直居中style3.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线 style3.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线 style3.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线 style3.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;//应用边界线 下边界线//给第3行第1列到第3行第51列总共51个单元格设置样式：多单元格批量样式Aspose.Cells.Range range3 = worksheet.Cells.CreateRange(2, 0, 2, 51);range3.ApplyStyle(style3, new StyleFlag() { All = true });//片材1string sql = @"select t1.shift_date 班别日期,t1.shift 班别,t3.description 机台,t5.description 产品名称,t2.spare3计划产量,t1.qty 实际产量,t1.wo_id 工单from (select sum(pkg_qty) qty, line, wo_id, shift, shift_datefrom h_chk_cartongroup by line, wo_id, shift, shift_date) t1left join c_line_wo_plan t2on t1.line = t2.lineand t1.wo_id = t2.wo_idand t1.shift = t2.spare4and t1.shift_date = t2.shift_dateleft join c_fac_line t3on t1.line = t3.lineleft join r_wom_mawo t4on t1.wo_id = t4.wo_idleft join (select part_no, descriptionfrom c_rou_partunion allselect material part_no, descriptionfrom c_mat_material) t5on t4.part_no = t5.part_nowhere is_hold = 'N'and (t1.shift_date between to_date('-03-01', 'yyyy-mm-dd') andto_date('-03-20', 'yyyy-mm-dd'))and t3.area = 'PianCai'and t1.line = 'PC_01'order by t1.shift_date desc, t3.description asc";DataSet ds_p1 = SqlHelper.Query_Mes(sql);//数据填充excelfor (int i = 0; i < ds_p1.Tables[0].Rows.Count; i++){string bbrq = ds_p1.Tables[0].Rows[i]["班别日期"].ToString().Substring(0,9).Replace("/","-");cells[i + 3, 0].PutValue(bbrq);cells[i + 3, 1].PutValue(ds_p1.Tables[0].Rows[i]["产品名称"]);cells[i + 3, 2].PutValue(ds_p1.Tables[0].Rows[i]["计划产量"]);cells[i + 3, 3].PutValue(ds_p1.Tables[0].Rows[i]["实际产量"]);double wcl = 0; //完成率double cy = 0; //差异if (cells[i + 3, 2].Value != null && cells[i + 3, 3].Value != null && cells[i + 3, 2].Value.ToString() != "0"){wcl = Convert.ToDouble((cells[i + 3, 3].Value).ToString()) / Convert.ToDouble((cells[i + 3, 2].Value).ToString());cy = Convert.ToDouble((cells[i + 3, 3].Value).ToString()) - Convert.ToDouble((cells[i + 3, 2].Value).ToString());}string wcl_Percent = Math.Round(wcl, 2).ToString("P");//保留两位小数，并转换百分比cells[i + 3, 4].PutValue(wcl_Percent);cells[i + 3, 5].PutValue(cy);}//上样式：第3行，第0列开始，到第i行，第5列Aspose.Cells.Range range_pc1 = worksheet.Cells.CreateRange(3, 0, ds_p1.Tables[0].Rows.Count, 6);range_pc1.ApplyStyle(style3, new StyleFlag() { All = true });//设置有数据的行的行高for (int i = 3; i < ds_p1.Tables[0].Rows.Count + 3; i++){cells.SetRowHeight(i, 29);}//自适应列宽worksheet.AutoFitColumns(worksheet.Cells.MinDataColumn, worksheet.Cells.MaxDataColumn);//自适应行高//worksheet.AutoFitRows(worksheet.Cells.MinDataRow, worksheet.Cells.MaxDataRow);//workbook.CalculateFormula(true); //忽略计算错误workbook.Save(filePath); //保存到指定位置//返回下载文件var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);return File(fileStream, "application/ms-excel", fileName); //三个参数：文件路径、格式、文件名}}}
