From 5fe820b18055b317cd5928d8fecd056f4c9e0115 Mon Sep 17 00:00:00 2001
From: wjli <591616088@qq.com>
Date: 星期三, 17 五月 2023 11:10:57 +0800
Subject: [PATCH] 1.根据区域进行模糊查询
---
xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/common/ExcelFillCellMergeStrategy.java | 105 +++++++++++++++++++++++++++++++++++
xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/entity/OrderStatusCount.java | 10 ++-
xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/controller/SignCountController.java | 7 +
3 files changed, 116 insertions(+), 6 deletions(-)
diff --git a/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/common/ExcelFillCellMergeStrategy.java b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/common/ExcelFillCellMergeStrategy.java
new file mode 100644
index 0000000..751b462
--- /dev/null
+++ b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/common/ExcelFillCellMergeStrategy.java
@@ -0,0 +1,105 @@
+package cn.exrick.xboot.your.common;
+
+
+import com.alibaba.excel.metadata.CellData;
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.write.handler.CellWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
+import lombok.Data;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.util.List;
+
+
+@Data
+public class ExcelFillCellMergeStrategy implements CellWriteHandler {
+ /**
+ * 鍚堝苟瀛楁鐨勪笅鏍囷紝濡傜涓�鍒颁簲鍒梟ew int[]{0,1,2,3,4}
+ */
+ private int[] mergeColumnIndex;
+ /**
+ * 浠庣鍑犺寮�濮嬪悎骞讹紝濡傛灉琛ㄥご鍗犱袱琛岋紝杩欎釜鏁板瓧灏辨槸2
+ */
+ private int mergeRowIndex;
+
+ public ExcelFillCellMergeStrategy() {
+ }
+
+ public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
+ this.mergeRowIndex = mergeRowIndex;
+ this.mergeColumnIndex = mergeColumnIndex;
+ }
+
+ @Override
+ public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
+ Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
+ Head head, Integer integer, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
+ CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
+ List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+//褰撳墠琛�
+ int curRowIndex = cell.getRowIndex();
+ //褰撳墠鍒�
+ int curColIndex = cell.getColumnIndex();
+
+ if (curRowIndex > mergeRowIndex) {
+ for (int i = 0; i < mergeColumnIndex.length; i++) {
+ if (curColIndex == mergeColumnIndex[i]) {
+ mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
+ break;
+ }
+ }
+ }
+ }
+
+ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
+ //鑾峰彇褰撳墠琛岀殑褰撳墠鍒楃殑鏁版嵁鍜屼笂涓�琛岀殑褰撳墠鍒楀垪鏁版嵁锛岄�氳繃涓婁竴琛屾暟鎹槸鍚︾浉鍚岃繘琛屽悎骞�
+ Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
+ cell.getNumericCellValue();
+ Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
+ Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
+ preCell.getNumericCellValue();
+ // 姣旇緝褰撳墠琛岀殑绗竴鍒楃殑鍗曞厓鏍间笌涓婁竴琛屾槸鍚︾浉鍚岋紝鐩稿悓鍚堝苟褰撳墠鍗曞厓鏍间笌涓婁竴琛�
+ //
+ if (curData.equals(preData)) {
+ 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/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/controller/SignCountController.java b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/controller/SignCountController.java
index bce41af..04ba2c7 100644
--- a/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/controller/SignCountController.java
+++ b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/controller/SignCountController.java
@@ -4,6 +4,7 @@
import cn.exrick.xboot.core.common.utils.ResultUtil;
import cn.exrick.xboot.core.common.vo.PageVo;
import cn.exrick.xboot.core.common.vo.Result;
+import cn.exrick.xboot.your.common.ExcelFillCellMergeStrategy;
import cn.exrick.xboot.your.entity.OrderStatusCount;
import cn.exrick.xboot.your.entity.QueryRequest;
import cn.exrick.xboot.your.entity.SignCount;
@@ -85,14 +86,16 @@
@ApiOperation(value = "瀵煎嚭绛炬敹缁熻淇℃伅琛�", notes = "瀵煎嚭绛炬敹缁熻淇℃伅琛�")
public void export1(OrderStatusCount orderStatusCount, HttpServletResponse response) throws IOException {
List<OrderStatusCount> orderStatus = this.iSignCountService.signCounts2New(orderStatusCount);
+ System.out.println(orderStatus);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("绛炬敹鏁伴噺缁熻鎶ヨ〃", "UTF-8");
String fileName2 = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
// response.setHeader("Content-dispostion", "attachment;filename="+fileName+".xls");
response.setHeader("Content-disposition", "attachment;filename=" + fileName2 + ".xlsx");
- EasyExcel.write(response.getOutputStream(),OrderStatusCount.class).
- sheet(0).doWrite(orderStatus);
+ EasyExcel.write(response.getOutputStream(),OrderStatusCount.class)
+ .registerWriteHandler(new ExcelFillCellMergeStrategy(1,new int[]{0,1,2,9})) // 鍩轰簬 column 闀垮害锛岃嚜鍔ㄩ�傞厤銆傛渶澶� 255 瀹藉害
+ .sheet(0).doWrite(orderStatus);
}
@GetMapping(value = "/selectBySign")
diff --git a/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/entity/OrderStatusCount.java b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/entity/OrderStatusCount.java
index 780d760..55973dc 100644
--- a/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/entity/OrderStatusCount.java
+++ b/xboot-modules/xboot-your/src/main/java/cn/exrick/xboot/your/entity/OrderStatusCount.java
@@ -2,9 +2,11 @@
import cn.exrick.xboot.core.base.XbootBaseEntity;
import com.alibaba.excel.annotation.ExcelIgnore;
+import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
+import com.alibaba.excel.annotation.write.style.OnceAbsoluteMerge;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import org.hibernate.annotations.DynamicInsert;
@@ -14,19 +16,19 @@
@Data
//@Excel("绛炬敹鐘舵�佺粺璁℃姤琛�")
+@ExcelIgnoreUnannotated
public class OrderStatusCount {
- @ContentLoopMerge(eachRow = 5)
@ExcelProperty(value = "杞﹀彿")
@TableField(value = "carName")
private String carName;
- @ContentLoopMerge(eachRow = 5)
+ //@ContentLoopMerge(eachRow = 5)
@ExcelProperty("濮撳悕")
@TableField(value = "userName")
//@ExcelIgnore
private String userName;
// @ContentLoopMerge(eachRow = 5)
- @ContentLoopMerge(eachRow = 5)
+ //@ContentLoopMerge(eachRow = 5)
@ExcelProperty("绾胯矾")
@TableField(value = "line")
private String line;
@@ -50,7 +52,7 @@
@TableField(value = "proportion")
private String proportion;
- @ContentLoopMerge(eachRow = 5)
+ // @ContentLoopMerge(eachRow = 5)
@ExcelProperty("骞冲潎绛炬敹鐜�")
@TableField(value = "name")
//@ExcelIgnore
--
Gitblit v1.9.1