From cc01f01a9ff12f4167d7d5d40bc3cff2ffb366c6 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期二, 15 十一月 2022 15:51:20 +0800
Subject: [PATCH] Excel数据操作
---
/dev/null | 13 --
src/main/java/com/lf/server/config/InitConfig.java | 11 ++
src/main/java/com/lf/server/annotation/ExcelHead.java | 25 +++++
src/main/java/com/lf/server/entity/bs/Zxcg.java | 20 ++-
src/main/java/com/lf/server/entity/bs/Gxcg.java | 111 ++++++++++++++-------
src/main/java/com/lf/server/helper/ExcelHelper.java | 66 +++++++++++++
6 files changed, 187 insertions(+), 59 deletions(-)
diff --git a/src/main/java/com/lf/server/annotation/ExcelHead.java b/src/main/java/com/lf/server/annotation/ExcelHead.java
new file mode 100644
index 0000000..b583354
--- /dev/null
+++ b/src/main/java/com/lf/server/annotation/ExcelHead.java
@@ -0,0 +1,25 @@
+package com.lf.server.annotation;
+
+import java.lang.annotation.*;
+
+/**
+ * Excel澶存敞瑙ｇ被
+ * @author WWW
+ */
+@Target(ElementType.TYPE)
+@Retention(RetentionPolicy.RUNTIME)
+public @interface ExcelHead {
+ /**
+ * Excel澶磋鏁�
+ *
+ * @return 澶磋鏁�
+ */
+ int headRows() default 1;
+
+ /**
+ * 鎺掗櫎鐨凷heet鍚嶇О锛堝涓敤閫楀彿闅斿紑锛�
+ *
+ * @return
+ */
+ String excludeSheets() default "";
+}
diff --git a/src/main/java/com/lf/server/annotation/HeadRowNumber.java b/src/main/java/com/lf/server/annotation/HeadRowNumber.java
deleted file mode 100644
index 267dec3..0000000
--- a/src/main/java/com/lf/server/annotation/HeadRowNumber.java
+++ /dev/null
@@ -1,13 +0,0 @@
-package com.lf.server.annotation;
-
-import java.lang.annotation.*;
-
-/**
- * 澶磋鏁版敞瑙ｇ被
- * @author WWW
- */
-@Target({ElementType.TYPE})
-@Retention(RetentionPolicy.SOURCE)
-public @interface HeadRowNumber {
- int value() default 1;
-}
diff --git a/src/main/java/com/lf/server/config/InitConfig.java b/src/main/java/com/lf/server/config/InitConfig.java
index ae4237c..e3680a4 100644
--- a/src/main/java/com/lf/server/config/InitConfig.java
+++ b/src/main/java/com/lf/server/config/InitConfig.java
@@ -9,6 +9,8 @@
import com.lf.server.entity.all.PermsAuthEntity;
import com.lf.server.entity.all.ResAuthEntity;
import com.lf.server.entity.bd.DlgAgnpEntity;
+import com.lf.server.entity.bs.Gxcg;
+import com.lf.server.entity.bs.Zxcg;
import com.lf.server.helper.*;
import com.lf.server.mapper.bd.DlgAgnpMapper;
import com.lf.server.service.all.BaseQueryService;
@@ -73,6 +75,7 @@
//testPerms();
//testBlacklist();
//FloatServerController.test();
+ //testReadExcel();
//boolean f1 = ZipHelper.unzip("D:\\LF\\data\\resources.zip", "D:\\LF\\data\\unzip");
//boolean f2 = ZipHelper.zip("D:\\LF\\data\\res.zip", "D:\\LF\\data\\unzip\\resources");
@@ -184,4 +187,12 @@
blacklistService.clearCache();
List<String> list5 = blacklistService.selectIpList(1);
}
+
+ private void testReadExcel() {
+ List<Zxcg> listZxcg = ExcelHelper.readExcel(Zxcg.class, "D:\\LF\\data\\xls\\娴嬬粯\\1-椤圭洰鎴愭灉-瑗夸笁涓璡\1-1涓嚎鎴愭灉琛╘\娴峰師鍘夸腑绾挎垚鏋滆〃.xlsx");
+ int r1 = listZxcg.size();
+
+ List<Gxcg> listGxcg = ExcelHelper.readExcel(Gxcg.class, "D:\\LF\\data\\xls\\娴嬬粯\\2-鍦颁笅绠＄嚎鎺㈡祴鎴愭灉\\2-2 绠＄嚎鎴愭灉琛�.xlsx");
+ int r2 = listGxcg.size();
+ }
}
diff --git a/src/main/java/com/lf/server/entity/bs/Gxcg.java b/src/main/java/com/lf/server/entity/bs/Gxcg.java
index 772fec1..4aae1ec 100644
--- a/src/main/java/com/lf/server/entity/bs/Gxcg.java
+++ b/src/main/java/com/lf/server/entity/bs/Gxcg.java
@@ -1,15 +1,19 @@
package com.lf.server.entity.bs;
import com.alibaba.excel.annotation.ExcelProperty;
-import com.lf.server.annotation.HeadRowNumber;
+import com.lf.server.annotation.ExcelHead;
+import lombok.AllArgsConstructor;
import lombok.Data;
+import lombok.NoArgsConstructor;
/**
* 绠￠亾鎴愭灉琛�
* @author WWW
*/
@Data
-@HeadRowNumber(value = 3)
+@NoArgsConstructor
+@AllArgsConstructor
+@ExcelHead(headRows = 3,excludeSheets = "灏侀潰,鐩綍")
public class Gxcg {
@ExcelProperty(index = 0)
private String bh;
@@ -18,42 +22,51 @@
private String gxdh;
@ExcelProperty(index = 2)
- private String bh2;
+ private String ljdh;
@ExcelProperty(index = 3)
private String msfs;
@ExcelProperty(index = 4)
- private String chiChun;
+ private String gxcl;
@ExcelProperty(index = 5)
- private String tz;
+ private String gjcc;
@ExcelProperty(index = 6)
- private String fsw;
+ private String tz;
@ExcelProperty(index = 7)
- private double x;
+ private String fsw;
@ExcelProperty(index = 8)
- private double y;
+ private Double x;
@ExcelProperty(index = 9)
- private String gnd;
+ private Double y;
@ExcelProperty(index = 10)
- private double ms;
+ private Double dm;
@ExcelProperty(index = 11)
- private String zks;
+ private Double gd;
@ExcelProperty(index = 12)
- private String gkpl;
+ private Double gnd;
@ExcelProperty(index = 13)
- private String dy;
+ private Double ms;
@ExcelProperty(index = 14)
+ private String dngs;
+
+ @ExcelProperty(index = 15)
+ private String gkpl;
+
+ @ExcelProperty(index = 16)
+ private String dldy;
+
+ @ExcelProperty(index = 17)
private String bz;
public String getBh() {
@@ -72,12 +85,12 @@
this.gxdh = gxdh;
}
- public String getBh2() {
- return bh2;
+ public String getLjdh() {
+ return ljdh;
}
- public void setBh2(String bh2) {
- this.bh2 = bh2;
+ public void setLjdh(String ljdh) {
+ this.ljdh = ljdh;
}
public String getMsfs() {
@@ -88,12 +101,20 @@
this.msfs = msfs;
}
- public String getChiChun() {
- return chiChun;
+ public String getGxcl() {
+ return gxcl;
}
- public void setChiChun(String chiChun) {
- this.chiChun = chiChun;
+ public void setGxcl(String gxcl) {
+ this.gxcl = gxcl;
+ }
+
+ public String getGjcc() {
+ return gjcc;
+ }
+
+ public void setGjcc(String gjcc) {
+ this.gjcc = gjcc;
}
public String getTz() {
@@ -112,44 +133,60 @@
this.fsw = fsw;
}
- public double getX() {
+ public Double getX() {
return x;
}
- public void setX(double x) {
+ public void setX(Double x) {
this.x = x;
}
- public double getY() {
+ public Double getY() {
return y;
}
- public void setY(double y) {
+ public void setY(Double y) {
this.y = y;
}
- public String getGnd() {
+ public Double getDm() {
+ return dm;
+ }
+
+ public void setDm(Double dm) {
+ this.dm = dm;
+ }
+
+ public Double getGd() {
+ return gd;
+ }
+
+ public void setGd(Double gd) {
+ this.gd = gd;
+ }
+
+ public Double getGnd() {
return gnd;
}
- public void setGnd(String gnd) {
+ public void setGnd(Double gnd) {
this.gnd = gnd;
}
- public double getMs() {
+ public Double getMs() {
return ms;
}
- public void setMs(double ms) {
+ public void setMs(Double ms) {
this.ms = ms;
}
- public String getZks() {
- return zks;
+ public String getDngs() {
+ return dngs;
}
- public void setZks(String zks) {
- this.zks = zks;
+ public void setDngs(String dngs) {
+ this.dngs = dngs;
}
public String getGkpl() {
@@ -160,12 +197,12 @@
this.gkpl = gkpl;
}
- public String getDy() {
- return dy;
+ public String getDldy() {
+ return dldy;
}
- public void setDy(String dy) {
- this.dy = dy;
+ public void setDldy(String dldy) {
+ this.dldy = dldy;
}
public String getBz() {
diff --git a/src/main/java/com/lf/server/entity/bs/Zxcg.java b/src/main/java/com/lf/server/entity/bs/Zxcg.java
index c8e72ef..e123b47 100644
--- a/src/main/java/com/lf/server/entity/bs/Zxcg.java
+++ b/src/main/java/com/lf/server/entity/bs/Zxcg.java
@@ -1,32 +1,36 @@
package com.lf.server.entity.bs;
import com.alibaba.excel.annotation.ExcelProperty;
-import com.lf.server.annotation.HeadRowNumber;
+import com.lf.server.annotation.ExcelHead;
+import lombok.AllArgsConstructor;
import lombok.Data;
+import lombok.NoArgsConstructor;
/**
* 涓嚎鎴愭灉琛�
* @author WWW
*/
@Data
-@HeadRowNumber(value = 1)
+@NoArgsConstructor
+@AllArgsConstructor
+@ExcelHead(headRows = 1)
public class Zxcg {
- @ExcelProperty(value = "妗╁彿")
+ @ExcelProperty(index = 0)
private String zhuangHao;
- @ExcelProperty(value = "杞")
+ @ExcelProperty(index = 1)
private Double zhuanJiao;
- @ExcelProperty(value = "閲岀▼(m)")
+ @ExcelProperty(index = 2)
private double liCheng;
- @ExcelProperty(value = "鍖楀潗鏍�(X)")
+ @ExcelProperty(index = 3)
private double x;
- @ExcelProperty(value = "涓滃潗鏍�(Y)")
+ @ExcelProperty(index = 4)
private double y;
- @ExcelProperty(value = "楂樼▼(m)")
+ @ExcelProperty(index = 5)
private double z;
public String getZhuangHao() {
diff --git a/src/main/java/com/lf/server/helper/ExcelHelper.java b/src/main/java/com/lf/server/helper/ExcelHelper.java
index cd93f3e..6eba6ad 100644
--- a/src/main/java/com/lf/server/helper/ExcelHelper.java
+++ b/src/main/java/com/lf/server/helper/ExcelHelper.java
@@ -1,9 +1,73 @@
package com.lf.server.helper;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.ExcelReader;
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.event.AnalysisEventListener;
+import com.alibaba.excel.read.metadata.ReadSheet;
+import com.lf.server.annotation.ExcelHead;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+
/**
* Excel甯姪绫�
* @author WWW
*/
public class ExcelHelper {
- //
+ /**
+ * 璇诲彇Excel
+ *
+ * @param pathName 鏂囦欢璺緞
+ * @param <T> 娉涘瀷绫�
+ * @return 娉涘瀷绫婚泦鍚�
+ */
+ public static <T> List<T> readExcel(Class<?> clazz, String pathName) {
+ ExcelHead head = getExcelHead(clazz);
+ int headRowNumber = head == null ? 1 : head.headRows();
+ String[] strs = null == head || StringHelper.isEmpty(head.excludeSheets()) ? null : head.excludeSheets().split(",");
+ List<String> excludeSheets = null == strs ? null : Arrays.asList(strs);
+
+ List<T> list = new ArrayList<T>();
+ ExcelReader reader = EasyExcel.read(pathName, clazz, new AnalysisEventListener<T>() {
+ @Override
+ public void invoke(T t, AnalysisContext context) {
+ list.add(t);
+ }
+
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext analysisContext) {
+ //
+ }
+ }).headRowNumber(headRowNumber).build();
+
+ List<ReadSheet> sheets = reader.excelExecutor().sheetList();
+ for (ReadSheet sheet : sheets) {
+ if (strs != null && excludeSheets.contains(sheet.getSheetName())) {
+ continue;
+ }
+
+ reader.read(sheet);
+ }
+
+
+ return list;
+ }
+
+ /**
+ * 鑾峰彇Excel澶存敞瑙ｇ被
+ *
+ * @param clazz Class
+ * @param <T> 娉涘瀷绫�
+ * @return 澶磋鏁�
+ */
+ public static <T> ExcelHead getExcelHead(Class<?> clazz) {
+ ExcelHead head = clazz.getAnnotation(ExcelHead.class);
+ if (head != null) {
+ return head;
+ }
+
+ return null;
+ }
}
--
Gitblit v1.9.3