pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/util/QrCodeUtil.java
@@ -82,7 +82,7 @@ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).sheet(sheetName).doWrite(list); }catch (Exception e){ log.error("导出产品信息异常", e); log.error("导出{}异常",sheetName,e); } } } pms-parent/pms-global/src/main/resources/mapper/PrScheduleMapper.xml
@@ -136,5 +136,6 @@ <if test="scheduleDate != null"> and schedule_date = #{scheduleDate} </if> order by schedule_date desc </select> </mapper> pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/CustomCellWriteHandler.java
New file @@ -0,0 +1,82 @@ package com.dy.pmsProduct.schedule; import com.alibaba.excel.util.BooleanUtils; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.handler.context.CellWriteHandlerContext; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.stereotype.Component; import java.util.List; /** * 自定义拦截器。 * 根据排班,设置横跨几行 */ @Slf4j @Component public class CustomCellWriteHandler implements CellWriteHandler { // 设置合并相同名称的单元列 private int[] mergeColumnIndex = {1,2,3}; // 从哪行开始合并 private int mergeRowIndex = 3; @Override public void afterCellDispose(CellWriteHandlerContext context) { Cell cell = context.getCell(); //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); if (curRowIndex >= mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { Object curId = cell.getSheet().getRow(curRowIndex).getCell(0).getStringCellValue(); Object preId = cell.getSheet().getRow(curRowIndex-1).getCell(0).getStringCellValue(); // 比较当前行的id值与上一行是否相同，相同合并当前单元格与上一行 if (curId.equals(preId)) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } } pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ExcelVo.java
@@ -3,21 +3,13 @@ import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.*; import com.alibaba.excel.converters.Converter; import com.alibaba.excel.converters.WriteConverterContext; import com.alibaba.excel.enums.BooleanEnum; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.enums.poi.BorderStyleEnum; import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum; import com.alibaba.excel.enums.poi.VerticalAlignmentEnum; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.data.ImageData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.metadata.property.ExcelContentProperty; import lombok.Data; import java.util.ArrayList; import java.util.Date; import java.util.List; //参考： https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write @Data @@ -27,27 +19,28 @@ horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, leftBorderColor = 8, rightBorderColor = 8, topBorderColor = 8, bottomBorderColor = 8) //IndexedColors.BLACK @ContentRowHeight(100) //设置内容高度 @ContentFontStyle(fontName="宋体", fontHeightInPoints = 14) @ContentRowHeight(30) //设置内容高度 @ContentFontStyle(fontName="宋体", fontHeightInPoints = 11) @ContentStyle(wrapped = BooleanEnum.TRUE, shrinkToFit = BooleanEnum.TRUE, horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, leftBorderColor = 8, rightBorderColor = 8, topBorderColor = 8, bottomBorderColor = 8) //IndexedColors.BLACK @ColumnWidth(16) //设置列宽 public class ExcelVo implements Converter<byte[]> { @ColumnWidth(40) // @ContentLoopMerge(eachRow=2) @ColumnWidth(0)//设置列宽为0,不显示 @ExcelProperty("ID") public String id ; @ColumnWidth(15) @ExcelProperty("用户名") public String userName ; @ColumnWidth(40) @ColumnWidth(20) @ExcelProperty("排班日期") // @ContentLoopMerge(eachRow=2) public String scheduleDate ; @ColumnWidth(40) @ColumnWidth(28) @ExcelProperty("创建时间") // @ContentLoopMerge(eachRow=2) public Date dt ; @ColumnWidth(40) @@ -65,30 +58,4 @@ @ColumnWidth(40) @ExcelProperty({"排班内容","工作内容"}) public String workDetails ; public WriteCellData<?> convertToExcelData(byte[] value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { WriteCellData cellData = new WriteCellData() ; if(value != null && value.length >0){ List<ImageData> list = new ArrayList<>(); ImageData imd = new ImageData(); imd.setImage(value); imd.setImageType(ImageData.ImageType.PICTURE_TYPE_PICT); imd.setLeft(10); imd.setTop(10); imd.setRight(10); imd.setBottom(10); list.add(imd) ; cellData.setImageDataList(list); cellData.setType(CellDataTypeEnum.EMPTY); }else{ cellData.setStringValue(""); cellData.setType(CellDataTypeEnum.STRING); } return cellData; } public WriteCellData<?> convertToExcelData(WriteConverterContext<byte[]> context) throws Exception { return this.convertToExcelData(context.getValue(), context.getContentProperty(), context.getWriteContext().currentWriteHolder().globalConfiguration()); } } pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ScheduleCtrl.java
@@ -1,5 +1,6 @@ package com.dy.pmsProduct.schedule; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.converters.Converter; import com.dy.common.aop.SsoPowerAop; import com.dy.common.webUtil.BaseResponse; @@ -14,6 +15,7 @@ import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.net.URLEncoder; import java.util.ArrayList; import java.util.List; import java.util.Map; @@ -25,8 +27,7 @@ @RestController @RequestMapping(path="schedule") public class ScheduleCtrl { private static final String fileName = "排班信息" ; private static final String sheetName = "排班信息" ; private ScheduleSv sv; @Autowired public void setScheduleSv(ScheduleSv scheduleSv) { @@ -99,7 +100,8 @@ return BaseResponseUtils.buildSuccess(list); } private String fileName = "排班信息" ; private String sheetName = "排班信息" ; @PostMapping(path="export") @SsoPowerAop(power = "-1") @Log("导出排班") @@ -107,10 +109,10 @@ List<Converter> list = new ArrayList<>() ; List<PrSchedule> schList = sv.selectAll(queryVo); // 使用并行流提高性能 schList.parallelStream().forEach(sch -> { schList.forEach(sch -> { sch.relList.forEach(rel -> { ExcelVo vo = new ExcelVo(); vo.id = sch.id.toString(); vo.userName = sch.userName; vo.scheduleDate = sch.scheduleDate; vo.dt = sch.dt; @@ -121,6 +123,16 @@ list.add(vo); }); }); QrCodeUtil.downloadExcel(response, fileName,sheetName,list); try{ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).sheet(sheetName) .registerWriteHandler(new CustomCellWriteHandler()).doWrite(list); }catch (Exception e){ log.error("导出排班信息异常", e); } } }
