From 0c479f17e5f9ff3ad638f3af783cf22b0a77fbd0 Mon Sep 17 00:00:00 2001
From: 刘小明 <liuxm_a@163.com>
Date: 星期一, 26 八月 2024 16:04:43 +0800
Subject: [PATCH] 排班导出优化
---
pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ScheduleCtrl.java | 24 ++++++--
pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/CustomCellWriteHandler.java | 82 +++++++++++++++++++++++++++
pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ExcelVo.java | 51 +++--------------
pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/util/QrCodeUtil.java | 2
pms-parent/pms-global/src/main/resources/mapper/PrScheduleMapper.xml | 1
5 files changed, 111 insertions(+), 49 deletions(-)
diff --git a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/util/QrCodeUtil.java b/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/util/QrCodeUtil.java
index 4fae5bf..b14a3ae 100644
--- a/pms-parent/pms-global/src/main/java/com/dy/pmsGlobal/util/QrCodeUtil.java
+++ b/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);
}
}
}
\ No newline at end of file
diff --git a/pms-parent/pms-global/src/main/resources/mapper/PrScheduleMapper.xml b/pms-parent/pms-global/src/main/resources/mapper/PrScheduleMapper.xml
index a225d6d..745d8ee 100644
--- a/pms-parent/pms-global/src/main/resources/mapper/PrScheduleMapper.xml
+++ b/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>
\ No newline at end of file
diff --git a/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/CustomCellWriteHandler.java b/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/CustomCellWriteHandler.java
new file mode 100644
index 0000000..dbed23c
--- /dev/null
+++ b/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/CustomCellWriteHandler.java
@@ -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);
+ }
+ }
+ }
+
+}
\ No newline at end of file
diff --git a/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ExcelVo.java b/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ExcelVo.java
index b4f23b8..aac96cc 100644
--- a/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ExcelVo.java
+++ b/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());
- }
-
}
diff --git a/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ScheduleCtrl.java b/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ScheduleCtrl.java
index d583cb9..962f7d2 100644
--- a/pms-parent/pms-web-product/src/main/java/com/dy/pmsProduct/schedule/ScheduleCtrl.java
+++ b/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);
+ }
}
}
--
Gitblit v1.8.0