前言
有时会出现列表中动态列的情况，导出时就需要横向导出了。
一、引入包
引入easypoi的相关包
二、代码实现
代码如下（示例）：
@Override
public void export(RawMaterialMonthConst rawMaterialMonthConst, HttpServletRequest request, HttpServletResponse response) {
String fileName = "";
String templatePath = "";
List<Map<String, Object>> list = Lists.newArrayList();
if(StringUtils.isNotBlank(rawMaterialMonthConst.getExportType())){
// 原材料消耗 使用id
if(StringUtils.equals("1", rawMaterialMonthConst.getExportType())){
list = findRawMonthConstData(rawMaterialMonthConst);
fileName = "原材耗用.xlsx";
templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator + "month/exportRawCost.xlsx";
}
// 消耗按强度 使用强度id
if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
list = findYfMonthConstList(rawMaterialMonthConst);
fileName = "研发耗用.xlsx";
templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator + "month/exportYfCost.xlsx";
}
// 消耗按配比 使用配比id
if(StringUtils.equals("3", rawMaterialMonthConst.getExportType())){
list = findNailMonthConstList(rawMaterialMonthConst);
fileName = "甲供材耗用.xlsx";
templatePath = upLoadPath + File.separator + TEMPLE_URL + File.separator + "month/exportNailCost.xlsx";
}
if(CollectionUtil.isNotEmpty(list)){
int num = 1;
for(Map<String, Object> le : list){
le.put("numIndex", num);
num++;
}
}
StudyTopicSetting setting = new StudyTopicSetting();
setting.setCompanyId(rawMaterialMonthConst.getCompanyId());
List<StudyTopicSetting> cloumns = findCloumnData(setting);
// 添加合计金额、累计金额
StudyTopicSetting set = new StudyTopicSetting();
set.setTopicName("合计金额");
cloumns.add(set);
StudyTopicSetting set2 = new StudyTopicSetting();
set2.setTopicName("累计金额");
cloumns.add(set2);
StudyTopicSetting set3 = new StudyTopicSetting();
set3.setTopicName("备注");
cloumns.add(set3);
exportCommon(rawMaterialMonthConst, list, fileName, templatePath, response, cloumns);
}
}
public void exportCommon(RawMaterialMonthConst rawMaterialMonthConst, List<Map<String, Object>> list, String fileName, String templatePath, HttpServletResponse response, List<StudyTopicSetting> cloumns){
String companyIdHg = baseAPI.translateDict("sys_dept_id", "1");
String companyIdWy = baseAPI.translateDict("sys_dept_id", "2");
if(StringUtils.isBlank(companyIdHg)){
throw new JeecgBootException("请联系管理员配置sys_dept_id字典");
}
if(StringUtils.isBlank(companyIdWy)){
throw new JeecgBootException("请联系管理员配置sys_dept_id字典");
}
String name = "";
if(StringUtils.equals(companyIdHg, rawMaterialMonthConst.getCompanyId())){
name = "测试1";
}
if(StringUtils.equals(companyIdWy, rawMaterialMonthConst.getCompanyId())){
name = "测试2";
}
// 定义导出内容
Map<String, Object> paramMap = Maps.newHashMap();
paramMap.put("name", name);
paramMap.put("startTime", rawMaterialMonthConst.getStartTime());
paramMap.put("endTime", rawMaterialMonthConst.getEndTime());
paramMap.put("time", DateUtil.format(DateUtil.parse(rawMaterialMonthConst.getEndTime()),"yyyy年MM月"));
if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
//设置导出的表头列
paramMap.put("cloumns", cloumns);
if(CollectionUtil.isNotEmpty(cloumns)){
int num = 1;
for(StudyTopicSetting omap : cloumns){
// 设置列对应的数据，列表查询的数据 为key1 key2......keyn 这里设置值 导表格上就会显示t.key1......
omap.setMaterialName("t.key"+num);
// 由于动态列在固定列的中间显示 而动态列直接添加的话 会覆盖掉后边的固定列 所以改成往后拼接的方式拼接固定列
if(StringUtils.equals("合计金额", omap.getTopicName())){
omap.setMaterialName("t.sum");
}
if(StringUtils.equals("累计金额", omap.getTopicName())){
omap.setMaterialName("t.total");
}
if(StringUtils.equals("备注", omap.getTopicName())){
omap.setMaterialName("t.remark");
}
num++;
}
}
}
if(StringUtils.equals("3", rawMaterialMonthConst.getExportType())){
Map map = getStatic(rawMaterialMonthConst);
paramMap.put("monthSlSum",map.get("monthSlSum"));
paramMap.put("monthAmountSum",map.get("monthAmountSum"));
paramMap.put("slSum",map.get("slSum"));
paramMap.put("amountSum",map.get("amountSum"));
}
paramMap.put("list", list);
// 增加一个空的list防止模板空指针异常
if(paramMap.get("list") == null){
ArrayList<String> emptyList = new ArrayList<>();
emptyList.add("");
paramMap.put("list",emptyList);
}
File folder = new File(upLoadPath + File.separator + TEMPLE_URL + File.separator + "month");
// 文件夹不存在则创建文件夹
if(!folder.exists()){
folder.mkdirs();
}
// 文件不存在 则联系管理员添加导出模板
File file = new File(templatePath);
if(!file.exists()){
throw new JeecgBootException("导出模板不存在，请联系管理员在【" + templatePath + "】路径添加导出模板！");
}
try {
TemplateExportParams exportParams = new TemplateExportParams(templatePath);
exportParams.setColForEach(true);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, paramMap);
int[] relationColl = new int[]{
1};
Map<Integer,int[]> mergeMap = new HashMap<>();
mergeMap.put(1, relationColl);
PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0),mergeMap,1);
// 自定义标题和时间 - 研发耗用
// 由于动态列 标题写死不能动态的合并单元格，所以下边内容为设置标题的样式 及根据动态列数自动合并单元格
if(StringUtils.equals("2", rawMaterialMonthConst.getExportType())){
CellStyle headStyle = workbook.createCellStyle();
Font headFont = workbook.createFont();
headFont.setFontHeightInPoints((short) 12);
headFont.setBold(true);
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Sheet sheet = workbook.getSheetAt(0);
// 定义表头行
Row headRow = sheet.createRow(0);
headRow.setHeightInPoints(30);
// 创建表头单元格
Cell headCell = headRow.createCell(0);
headCell.setCellValue(DateUtil.format(DateUtil.parse(rawMaterialMonthConst.getEndTime()),"yyyy年MM月")+"生产材料研发耗用表");
headCell.setCellStyle(headStyle);
// 合并表头单元格
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2 + cloumns.size());
sheet.addMergedRegion(region);
// 设置表头单元格边框
RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
// 第二标题
Sheet sheet2 = workbook.getSheetAt(0);
CellStyle rowStyle = workbook.createCellStyle();
Font rowFont = workbook.createFont();
rowFont.setFontHeightInPoints((short) 12);
rowFont.setBold(false);
rowStyle.setFont(rowFont);
rowStyle.setAlignment(HorizontalAlignment.CENTER);
rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// rowStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
rowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 开始到结束时间
Row timeRow = sheet2.createRow(1);
timeRow.setHeightInPoints(30);
Cell timeCell = timeRow.createCell(2 + cloumns.size());
timeCell.setCellValue(rawMaterialMonthConst.getStartTime() + " - " + rawMaterialMonthConst.getEndTime());
timeCell.setCellStyle(rowStyle);
rowStyle.setBorderRight(BorderStyle.THIN);
Cell titleCell = timeRow.createCell(0);
titleCell.setCellValue("编制单位：测试-"+name+"项目部");
titleCell.setCellStyle(rowStyle);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, 3);
sheet2.addMergedRegion(region2);
RegionUtil.setBorderTop(BorderStyle.THIN, region2, sheet2);
RegionUtil.setBorderBottom(BorderStyle.THIN, region2, sheet2);
RegionUtil.setBorderLeft(BorderStyle.THIN, region2, sheet2);
RegionUtil.setBorderRight(BorderStyle.THIN, region2, sheet2);
}
response.addHeader("filename", URLEncoder.encode(fileName,"utf-8"));
response.addHeader("Access-Control-Expose-Headers","filename");
response.setHeader("Content-disposition", "attachment; fileName=" + URLEncoder.encode(fileName, "utf-8"));
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
