主页 > 软件开发 >

动态表头报表的绘制与导出

软件开发
2025-09-15 10:57:01

一、效果图

二、整体思路

三、代码区

一、效果图

根据选择的日期范围动态生成表头（eg：2025.2.24--2025.03.03）每个日期又分为白班、夜班；数据列表中对产线合并单元格。支持按原格式导出对应的报表excel。

点击空白区可新增工时数据；点击蓝色字体可以编辑工时数据。

二、整体思路

前端绘制报表，分成两部分，一部分固定列头，一部分动态列头。固定列头就按照table正常情况绘制；动态且多级列头部分：根据选择的日期范围在前端获取该范围内日期列表，另外对于后端传来的数据进行处理（生成动态字段（日期_班次），并将数据跟生成的动态表头字段一一对应），参考el-table中多级表头的实现（Table 表格 | Element Plus）

后端引入NPOI，创建XSSFWorkbook工作簿，填充每个单元格的数据，生成excel表格。点击导出按钮，调用后端接口，生成报表excel。

三、代码区

html

<el-tabs type="border-card">  <el-tab-pane label="人力统计报表"> <el-form :model="queryParams" label-position="right" inline ref="queryRef" :rules="rulesQueryReport">  <el-form-item label="生产线" prop="lineCode"> <el-select clearable v-model="queryParams.lineCode" placeholder="请选择生产线" filterable @change="queryLineSelectChange" style="width: 100%;"> <el-option v-for="item in options.mes_line_list" :key="item.id" :label="item.lineName" :value="item.lineCode"> <span class="fl">{{ item.innerLineCode }}</span> <span class="fr" style="color: var(--el-text-color-secondary);">{{ item.lineName }}</span> </el-option> </el-select> </el-form-item> <el-form-item label="日期" prop="dateRangeWorkDate"> <el-date-picker v-model="dateRangeWorkDate" type="daterange" range-separator="到" start-placeholder="开始日期" end-placeholder="结束日期" value-format="YYYY-MM-DD" :shortcuts="shortcuts" @change="handleDateChange" :clearable="false" /> </el-form-item> <el-form-item> <el-button icon="search" type="primary" @click="handleQuery">{{ $t('btn.search') }}</el-button> <el-button icon="refresh" @click="resetQuery">{{ $t('btn.reset') }}</el-button> </el-form-item> </el-form> <el-row :gutter="15" class="mb10"> <el-col :span="1.5"> <el-button type="primary" v-hasPermi="['datareport:meshumanreport:add']" plain icon="plus" @click="handleAdd"> {{ $t('btn.add') }} </el-button> </el-col> <el-col :span="1.5"> <el-button type="warning" plain icon="download" @click="handleExport" v-hasPermi="['datareport:meshumanreport:export']"> {{ $t('btn.export') }} </el-button> </el-col> </el-row>  <div style="max-height:600px; overflow: hidden; position: relative;">  <div style="max-height: 600px; overflow-y: auto;">  <div style="min-width: 100%; display: inline-block;">  <el-table :data="dataList" :span-method="spanMethod" v-loading="loading" ref="table" border header-cell-class-name="el-table-header-cell" highlight-current-row :max-height="600" :cell-style="cellStyle" :header-cell-style="headerStyle" @cell-click="handleCellClick"> <el-table-column prop="lineName" label="产线名称" fixed width="120" align="center" /> <el-table-column prop="userName" label="姓名" fixed width="120" align="center" /> <el-table-column prop="total" label="合计" fixed width="100" align="center" /> <el-table-column prop="postNames" label="岗位" fixed width="100" align="center" /> <el-table-column prop="userLaborAttribute" label="劳务属性" fixed width="100" align="center" />  <el-table-column v-for="(dateCol, index) in dynamicHeaders" :key="index" :label="dateCol.date" align="center"> <el-table-column v-for="shift in ['白班', '夜班']" :key="shift" :prop="`${dateCol.date}_${shift}`" :label="shift" width="100" align="center"> </el-table-column> </el-table-column> </el-table> </div> </div>  <div v-show="showHorizontalScrollHint" style="position: absolute; bottom: 0; width: 100%; height: 6px; background: linear-gradient(to top, rgba(0,0,0,0.1), transparent);"> </div> </div> </el-tab-pane> </el-tabs>

Service层导出功能

/// <summary> /// 导出人力报工报表数据 /// </summary> /// <param name="parm"></param> /// <returns></returns> public (string, string) ExportMesHumanReport(MesHumanReportQueryDto parm) { var data = GetMesHumanReportResponse(parm); List<DateTime> dateList = new List<DateTime>(); var start = parm.BeginWorkDate.Value.Date; var end = parm.EndWorkDate.Value.Date; while (start <= end) { dateList.Add(start); start = start.AddDays(1); } //创建工作簿 IWorkbook workbook = new XSSFWorkbook(); //创建工作表 ISheet sheet = workbook.CreateSheet("人力报工统计"); #region 表头 //创建第一行表头 IRow headerRow1 = sheet.CreateRow(0); headerRow1.CreateCell(0).SetCellValue("产线"); headerRow1.CreateCell(1).SetCellValue("姓名"); headerRow1.CreateCell(2).SetCellValue("合计(h)"); headerRow1.CreateCell(3).SetCellValue("岗位"); headerRow1.CreateCell(4).SetCellValue("劳务属性"); int fixedColumnCount = 5; for (int i = 0, j = 0; i < dateList.Count; i++, j = j + 2) { headerRow1.CreateCell(fixedColumnCount + j).SetCellValue(dateList[i].ToCommonDateString()); headerRow1.CreateCell(fixedColumnCount + j + 1).SetCellValue(string.Empty); //合并表头列单元格 sheet.AddMergedRegion(new CellRangeAddress(0, 0, fixedColumnCount + j, fixedColumnCount + j + 1)); } //创建第二行表头 IRow headerRow2 = sheet.CreateRow(1); for (int i = 0, j = 0; i < dateList.Count; i++, j = j + 2) { headerRow2.CreateCell(fixedColumnCount + j).SetCellValue("白班"); headerRow2.CreateCell(fixedColumnCount + j + 1).SetCellValue("夜班"); } //合并表头行单元格 for (int i = 0; i < fixedColumnCount; i++) { sheet.AddMergedRegion(new CellRangeAddress(0, 1, i, i)); } #endregion #region 表体 //创建数据行 int rowIndex = 2; //记录当前产线的起始行索引和结束行索引，为合并产线做准备 int startRowIndex = 2; int lineCode = data.Count > 0 ? data.FirstOrDefault().LineCode ?? 0 : 0; int endRowIndex = 2; foreach (var item in data) { IRow dataRow = sheet.CreateRow(rowIndex); dataRow.CreateCell(0).SetCellValue(item.LineName); dataRow.CreateCell(1).SetCellValue(item.UserName); dataRow.CreateCell(2).SetCellValue(item.TotalHours.ToString()); dataRow.CreateCell(3).SetCellValue(item.PostNames); dataRow.CreateCell(4).SetCellValue(item.UserLaborAttribute); for (int i = 0, j = 0; i < dateList.Count; i++, j = j + 2) { var dateData = item.Details.Where(x => x.Date == dateList[i]).ToList(); if (dateData != null && dateData.Count > 0) { var dayShift = dateData.FirstOrDefault(x => x.DayShift != null)?.DayShift; var nightShift = dateData.FirstOrDefault(x => x.NightShift != null)?.NightShift; dataRow.CreateCell(fixedColumnCount + j).SetCellValue(dayShift == null ? string.Empty : dayShift.ToString()); dataRow.CreateCell(fixedColumnCount + j + 1).SetCellValue(nightShift == null ? string.Empty : nightShift.ToString()); } else { dataRow.CreateCell(fixedColumnCount + j).SetCellValue(string.Empty); dataRow.CreateCell(fixedColumnCount + j + 1).SetCellValue(string.Empty); } } //合并产线单元格 if (lineCode == item.LineCode) { endRowIndex++; } else { if (endRowIndex - startRowIndex > 1) { sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex - 1, 0, 0)); } startRowIndex = rowIndex; endRowIndex = rowIndex + 1; // 移到下一行 lineCode = item.LineCode ?? 0; } rowIndex++; } // 处理最后一组合并 if (endRowIndex - startRowIndex > 1) { sheet.AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex - 1, 0, 0)); } #endregion //调整列宽,设置第一列自动宽度 //sheet.AutoSizeColumn(0); //写入到内存流 //MemoryStream ms = new MemoryStream(); //workbook.Write(ms); //ms.Flush(); //return ms; var fileName = "人力报工统计"; IWebHostEnvironment webHostEnvironment = (IWebHostEnvironment)App.ServiceProvider.GetService(typeof(IWebHostEnvironment)); var sFileName = $"{fileName}{DateTime.Now:MM-dd-HHmmss}.xlsx"; string fullPath = Path.Combine(webHostEnvironment.WebRootPath, "export", sFileName); Directory.CreateDirectory(Path.GetDirectoryName(fullPath)); using (var fileStream = new FileStream(fullPath, FileMode.Create)) { workbook.Write(fileStream); } return (sFileName, fullPath); }

查询接口返回实体结构

/// <summary> /// 报表单条产线数据 /// </summary> public class MesHumanReportLineResponse : MesHumanReportDetailDto { /// <summary> /// 总工时 /// </summary> public decimal? TotalHours { get; set; } /// <summary> /// 每日工时详情 /// </summary> public List<MesHumanReportDetailResponse> Details { get; set; } } public class MesHumanReportDetailResponse { /// <summary> /// 日期 /// </summary> public DateTime? Date { get; set; } /// <summary> /// 白班工时 /// </summary> public decimal? DayShift { get; set; } /// <summary> /// 夜班工时 /// </summary> public decimal? NightShift { get; set; } // 夜班 } /// <summary> /// 人力报工-明细输入输出对象 /// </summary> public class MesHumanReportDetailDto { /// <summary> /// 主键Id /// </summary> [ExcelIgnore] public int Id { get; set; } /// <summary> /// 主表id /// </summary> [ExcelIgnore] public int MasterId { get; set; } /// <summary> /// 车间名称 /// </summary> [ExcelColumn(Name = "车间名称")] [ExcelColumnName("车间名称")] public string WorkShopName { get; set; } /// <summary> /// 产线编号 /// </summary> [ExcelColumn(Name = "产线编号")] [ExcelColumnName("产线编号")] public int? LineCode { get; set; } /// <summary> /// 产线名称 /// </summary> [ExcelColumn(Name = "产线名称")] [ExcelColumnName("产线名称")] public string LineName { get; set; } /// <summary> /// 开始时间 /// </summary> [ExcelColumn(Name = "开始时间", Format = "yyyy-MM-dd HH:mm:ss")] [ExcelColumnName("开始时间")] public DateTime? StartTime { get; set; } /// <summary> /// 结束时间 /// </summary> [ExcelColumn(Name = "结束时间", Format = "yyyy-MM-dd HH:mm:ss")] [ExcelColumnName("结束时间")] public DateTime? EndTime { get; set; } /// <summary> /// 日期 /// </summary> [ExcelColumn(Name = "日期")] [ExcelColumnName("日期")] public DateTime? WorkDate { get; set; } /// <summary> /// 班次 /// </summary> [ExcelColumn(Name = "班次")] [ExcelColumnName("班次")] public string WorkShift { get; set; } /// <summary> /// 实际工时(h) /// </summary> [ExcelColumn(Name = "实际工时(h)")] [ExcelColumnName("实际工时(h)")] public decimal? ActualWorkHours { get; set; } /// <summary> /// 标准工时(h) /// </summary> [ExcelColumn(Name = "标准工时(h)")] [ExcelColumnName("标准工时(h)")] public decimal? StandardWorkHours { get; set; } /// <summary> /// 用户id /// </summary> [ExcelIgnore] public long UserId { get; set; } /// <summary> /// 用户工号 /// </summary> [ExcelColumn(Name = "用户工号")] [ExcelColumnName("用户工号")] public string UserCode { get; set; } /// <summary> /// 用户姓名 /// </summary> [ExcelColumn(Name = "用户姓名")] [ExcelColumnName("用户姓名")] public string UserName { get; set; } /// <summary> /// 部门id /// </summary> [ExcelIgnore] public long? DeptId { get; set; } /// <summary> /// 部门名称 /// </summary> [ExcelIgnore] public string DeptName { get; set; } /// <summary> /// 岗位id (用逗号拼接) /// </summary> [ExcelIgnore] public string PostIds { get; set; } /// <summary> /// 岗位名称 (用逗号拼接) /// </summary> [ExcelColumn(Name = "岗位名称")] [ExcelColumnName("岗位名称")] public string PostNames { get; set; } /// <summary> /// 用户劳务属性（合同工、劳务工、第三方） /// </summary> [ExcelIgnore] public string UserLaborAttribute { get; set; } /// <summary> /// 用户划分（分摊、专属） /// </summary> [ExcelIgnore] public string UserDivide { get; set; } /// <summary> /// 创建日期 /// </summary> [ExcelIgnore] public DateTime? CreateTime { get; set; } /// <summary> /// 创建者 /// </summary> [ExcelIgnore] public string CreateBy { get; set; } /// <summary> /// 更新时间 /// </summary> [ExcelIgnore] public DateTime? UpdateTime { get; set; } /// <summary> /// 更新者 /// </summary> [ExcelIgnore] public string UpdateBy { get; set; } /// <summary> /// 备注信息 /// </summary> [ExcelColumn(Name = "备注信息")] [ExcelColumnName("备注信息")] public string Remark { get; set; } /// <summary> /// 是否删除（软删除标志） /// </summary> [ExcelIgnore] public bool IsDeleted { get; set; } }

Service层查询功能（该部分可以跳过，其中逻辑跟源项目相关，仅做记录）

/// <summary> /// 获取人力报工报表数据 /// </summary> /// <param name="parm"></param> /// <returns></returns> public List<MesHumanReportLineResponse> GetMesHumanReportResponse(MesHumanReportQueryDto parm) { var resultList = new List<MesHumanReportLineResponse>(); try { if (parm.BeginWorkDate == null || parm.EndWorkDate == null) { throw new ArgumentException("开始日期和结束日期不能为空!"); } #region 条件查询 var list = Queryable().Where(x => x.WorkDate >= parm.BeginWorkDate.Value.Date && x.WorkDate <= parm.EndWorkDate.Value.Date && !x.IsDeleted) .WhereIF(parm.LineCode != null, x => x.LineCode == parm.LineCode) .OrderBy(x => x.WorkDate) .Includes(x => x.MesHumanReportDetailNav.Where(w => !w.IsDeleted).ToList()) .ToList(); var detaiList = list.SelectMany(x => x.MesHumanReportDetailNav).ToList(); //根据产线分组 var lineGroup = detaiList.GroupBy(x => x.LineCode).ToList(); foreach (var item in lineGroup) { //根据人员分组 var userGroup = item.GroupBy(x => x.UserCode).ToList(); foreach (var user in userGroup) { var data = new MesHumanReportLineResponse(); data.LineCode = item.Key; data.LineName = item.FirstOrDefault().LineName; data.UserCode = user.Key; data.UserId = user.FirstOrDefault().UserId; data.UserName = user.FirstOrDefault().UserName; data.DeptId = user.FirstOrDefault().DeptId; data.DeptName = user.FirstOrDefault().DeptName; data.PostIds = user.FirstOrDefault().PostIds; data.PostNames = user.FirstOrDefault().PostNames; List<MesHumanReportDetailResponse> detailList = new List<MesHumanReportDetailResponse>(); data.Details = detailList; data.TotalHours = user.Sum(x => x.ActualWorkHours ?? 0); //根据日期分组 var dateGroup = user.GroupBy(x => x.WorkDate).OrderBy(x => x.Key).ToList(); foreach (var date in dateGroup) { //根据班次分组 var shiftGroup = date.GroupBy(x => x.WorkShift).OrderBy(x => x.Key).ToList(); foreach (var shift in shiftGroup) { //计算总工时 var totalHours = shift.Sum(x => x.StandardWorkHours); //计算实际工时 var actualHours = shift.Sum(x => x.ActualWorkHours); //计算缺勤工时 //var absenceHours = shift.Sum(x => x.StandardWorkHours - x.ActualWorkHours); //计算出勤率 //var attendanceRate = (actualHours / totalHours) * 100; //计算缺勤率 //var absenceRate = (absenceHours / totalHours) * 100; var detail = new MesHumanReportDetailResponse(); detail.Date = date.Key; if (shift.Key == "白班") { detail.DayShift = actualHours; } else { detail.NightShift = actualHours; } detailList.Add(detail); } } resultList.Add(data); } } //return resultList; #endregion } catch (Exception ex) { logger.Error(ex, "获取人力报工报表数据异常!"); throw ex; } try { //查询智能制造平台用户列表 int pageIndex = 1; // 设置页码 int pageSize = 9999; // 每页大小 var apiResult = HttpHelper.HttpGet($"{_apiUrl}api/common/getUserListByPost?pageNum={pageIndex}&pageSize={pageSize}"); //获取apiResult中data中的数据 if (!string.IsNullOrEmpty(apiResult)) { var json = JObject.Parse(apiResult); if ((int)json["code"] == 200) { JArray resultArray = (JArray)json["data"]["result"]; // 👈 直接获取结果数组 // 遍历 resultArray，并映射到 SysUserDto 列表 var userList = new List<SysUserDto>(); foreach (var item in resultArray) { var user = new SysUserDto { UserId = (int)item["userId"], UserName = item["userName"].ToString(), NickName = item["nickName"].ToString(), Email = (string)item["email"], // 可能为 null Remark = (string)item["remark"], // 可能为 null Phonenumber = (string)item["phonenumber"], // 可能为 null Sex = (int)item["sex"], DeptId = (int)item["deptId"], DeptName = item["deptName"].ToString(), PostNames = item["postNames"].ToString(), UserLaborAttribute = item["userLaborAttribute"].ToString(), UserDivide = item["userDivide"].ToString() // 其他属性... }; userList.Add(user); } //用户基础信息映射到报表数据 resultList.ForEach(w => { var user = userList.FirstOrDefault(x => w.UserId == x.UserId || w.UserCode == x.UserName); if (user != null) { w.PostNames = user.PostNames; w.UserLaborAttribute = user.UserLaborAttribute; w.UserDivide = user.UserDivide; } }); } } return resultList; } catch (Exception ex) { logger.Error(ex, "获取智能制造平台用户列表异常!"); throw ex; } }

标签：

动态表头报表的绘制与导出由讯客互联软件开发栏目发布，感谢您对讯客互联的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人网站或者朋友圈，但转载请说明文章出处“动态表头报表的绘制与导出”

上一篇
SpringBoot监听器（Listeners）详细教程

下一篇
有关Java中的集合(2):Map＜T＞(底层源码分析)