敖汉旗网站建设,江苏大才建设集团网站,为一个网站设计一个推广方案,WordPress开网店前言:
本来我已经很久没做java的项目了#xff0c;最近手头的项目没啥事又被拉过去搞java了#xff0c;但是看到这帮人写的代码#xff0c;心凉了一截#xff0c;写一个Excel的导入写的 都有很多问题#xff0c; 写个示范吧#xff1a; ExcelUtil util new ExcelUtil()最近手头的项目没啥事又被拉过去搞java了但是看到这帮人写的代码心凉了一截写一个Excel的导入写的 都有很多问题 写个示范吧 ExcelUtil util new ExcelUtil() util.import(xxx); 看似没啥问题也不知到搭建项目的是哪个“脑瘫”这么写如果是多sheet页 每个sheet映射的Entity不一致这个就用不了因为这个工具栏上面就用了泛型限制在使用解析方法时始终是用某一个Entity结构根本对应不是其他的实体然后“聪明的这帮人”就有几个sheet页就new几个ExcelUtil然后我哭了创建了几次ExcelUtil这个方法就调用了WorkbookFactory.create(inputStream);这个文件流创建n次的WorkBook,这么搞 内存不搞爆了。 还有就是在文件类型和excel类型一致才设置值这个脑残啊你是把问题给“包住了”。一但发现数据少了从哪里去导呢要是跟其他数据绑定了 怎么办 所以写这个我也是没办法项目有这么个前行者。可能很多开发者说 啥时代了 为啥不用EasyExcel或EasyPoi,中国的系统 大家懂的 都懂 一个excel导入 都能玩出花一会要合并单元 一会要取颜色标记的哪些值。所以只能用POI。
需要的原料 Excel映射的主要注解类 import com.jysoft.common.utils.poi.ExcelHandlerAdapter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.lang.annotation.*;
import java.math.BigDecimal;/*** 自定义导出Excel数据注解* author 程序员ken*/
Retention(RetentionPolicy.RUNTIME)
Target(ElementType.FIELD)
Repeatable(Excel.List.class)
public interface Excel
{ /*** excel对应表头名称*/public String name() default ;/*** 当值为空时,字段的默认值*/public String defaultValue() default ;Target(ElementType.FIELD)Retention(RetentionPolicy.RUNTIME)Documentedpublic interface List {Excel[] value();}
}校验的注解类 import java.lang.annotation.*;Repeatable(Verify.List.class)
Retention(RetentionPolicy.RUNTIME)
Target(ElementType.FIELD)
public interface Verify {/// summary///错误信息提示 仅正则格式匹配的提示/// /summaryString errorMsg() default ;/*** 是否可以为空* return*/boolean nullable() default false;/// summary/// 文本最大长度 默认是99999/// /summaryint maxLength() default 99999;/// summary/// 内容格式校验 默认为空/// /summaryString patternReg() default ;/// summary///业务区分 or 业务名称/// /summaryString[] businessDiff() default {};Target(ElementType.FIELD)Retention(RetentionPolicy.RUNTIME)public interface List {Verify[] value();}
}public class ReflectUtils
{//本来也想分享出来 感觉项目带的很糟糕 还携带了很多项目信息 就不分享了 网上也能找到很多反射相关的工具类
}校验结果 public class VerifyResultE {private ListE records;private String errorInfo;private boolean existError;private boolean isNull;private String sheetName;public VerifyResult(ListE records, String errorInfo) {this.records records;this.errorInfo errorInfo;this.existError StringUtils.isNotEmpty(errorInfo);}public VerifyResult(ListE records, String errorInfo,String sheetName) {this.records records;this.errorInfo errorInfo;this.existError StringUtils.isNotEmpty(errorInfo);this.sheetName sheetName;}public ListE getRecords() {return records;}public void setRecords(ListE records) {this.records records;}public String getErrorInfo() {return errorInfo;}public void setErrorInfo(String errorInfo) {this.errorInfo errorInfo;}public boolean getIsNull() {this.isNull ObjectUtil.isEmpty(records);return isNull;}public void setIsNull(boolean isNull) {this.isNull isNull;}public boolean getExistError() {if(ObjectUtil.isEmpty(records)){this.errorInfo 导入的excel里数据为空;this.isNull true;return true;}return existError;}public void setExistError(boolean existError) {this.existError existError;}public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {this.sheetName sheetName;}}读取解析工具类
import cn.hutool.core.util.ObjectUtil;
import xxxxx.annotation.Excel;
import xxxxx.annotation.Verify;
import xxxxx.funnctions.BiFFunction;
import xxxxx.utils.DateUtils;
import xxxxx.utils.StringUtils;
import xxxxx.utils.reflect.ReflectUtils;
import xxxxx.vo.EntityExcelVo;
import xxxxx.vo.VerifyResult;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.BiFunction;
import java.util.function.Function;/*** Excel相关处理--仅用导入*/
public class ExcelReadUtil {private static final Logger log LoggerFactory.getLogger(ExcelImportUtil.class);/*** 工作薄对象*/private Workbook wb;/*** 字段 转化映射表*/private static MapClass?, FunctionString,Object convertValMap new HashMap();/*** 需要排除列属性*/public String[] excludeFields;/*** 加入集合前 前置回调处理 T,Row,Boolean*/private BiFunction beforeAddFunc null;static {convertValMap.put(String.class, String::valueOf);convertValMap.put(Integer.class, Integer::parseInt);convertValMap.put(int.class, Integer::parseInt);convertValMap.put(Float.class, Float::parseFloat);convertValMap.put(float.class, Float::parseFloat);convertValMap.put(Short.class, Short::parseShort);convertValMap.put(short.class, Short::parseShort);convertValMap.put(Long.class, Long::parseLong);convertValMap.put(long.class, Long::parseLong);convertValMap.put(BigDecimal.class, BigDecimal::new);convertValMap.put(Boolean.class, Boolean::parseBoolean);convertValMap.put(boolean.class, Boolean::parseBoolean);convertValMap.put(Date.class, DateUtils::parseDate);convertValMap.put(LocalDate.class, LocalDate::parse);}/*** 初始化 workbook** param is* throws IOException*/public ExcelImportUtil(InputStream is) throws IOException {this.wb WorkbookFactory.create(is);}/*** param beforeAddFuncT,Row,Boolean T 当前数据 Row 当前行 Boolean是否加入集合* param classz 为了让编译器知道当前操作的对象是哪个* param T*public T void setBeforeAddFunc(BiFunctionT, Row, Boolean beforeAddFunc,ClassT classz) {this.beforeAddFunc beforeAddFunc;}/*** 读取所有sheet** param titleNum 标题列位置* param businessDiff 业务区分 用于校验 如果 没有区分 填null* param clazz* param T* return* throws Exception*/public T VerifyResultT importExcelAll(int titleNum, String businessDiff, ClassT clazz) throws Exception {return importExcel(titleNum, businessDiff, null, clazz);}/*** 获取sheet总数** return* throws Exception*/public int getSheetTotal() throws Exception {return this.wb.getNumberOfSheets();}/*** param titleNum 标题列位置* param businessDiff 业务区分 用于校验 如果 没有区分 填null* param clazz* param T* throws Exception*/public T VerifyResultT importExcelBySheetIndex(int titleNum, String businessDiff, int sheetIndex, ClassT clazz) throws Exception {String sheetName this.wb.getSheetName(sheetIndex);VerifyResultT tVerifyResult importExcel(titleNum, businessDiff, sheetName, clazz);tVerifyResult.setSheetName(sheetName);return tVerifyResult;}/*** 对excel表单指定表格索引名转换成list** param titleNum 标题占用行数* param businessDiff 业务校验区分 可以为null* return 转换后集合*/public T VerifyResultT importExcel(int titleNum, String businessDiff, String parseSheetName, ClassT clazz) throws Exception {ListT list new ArrayListT();MapString, PictureData pictures new HashMap();IteratorSheet sheetIterator wb.sheetIterator();if (!sheetIterator.hasNext()) {throw new IOException(文件sheet页 parseSheetName 不存在);}//仅操作一次MapString, EntityExcelVo entityExcelMap getEntityExcelMap(businessDiff, clazz);MapInteger, EntityExcelVo entityExcelPointMap null;SetString strings entityExcelMap.keySet();//行错误信息StringBuffer rowErrorSbf new StringBuffer();StringBuffer errorSbf new StringBuffer();//sheetName行号MapString, StringBuffer errorRow new HashedMap();//当前sheet 的合并列信息MapInteger, String curSheetMergeMap null;Sheet curSheet;String sheetName;int rows;while (sheetIterator.hasNext()) {//当前sheetcurSheet sheetIterator.next();sheetName curSheet.getSheetName();curSheetMergeMap getCurSheetMergeMap(curSheet);entityExcelPointMap new HashedMap();//如果parseSheetName不为空则解析所有sheetif (parseSheetName ! null !sheetName.equals(parseSheetName)) {continue;}// 获取最后一个非空行的行下标比如总行数为n则返回的为n-1rows curSheet.getLastRowNum();if (rows 0) {// 定义一个map用于存放excel列的序号和field.MapString, Integer cellMap new HashMapString, Integer();// 获取表头Row heard curSheet.getRow(titleNum);for (int i 0; i heard.getPhysicalNumberOfCells(); i) {Cell cell heard.getCell(i);if (StringUtils.isNotNull(cell)) {String value this.getCellValue(heard, i).toString();cellMap.put(value.replaceAll(\n, ).trim(), i);} else {cellMap.put(null, i);}}for (String title : strings) {Integer column cellMap.get(title);if (column ! null) {entityExcelPointMap.put(column, entityExcelMap.get(title));}}int num titleNum 1;for (int i num; i rows; i) {// 从第2行开始取数据,默认第一行是表头.Row row curSheet.getRow(i);// 判断当前行是否是空行if (isRowEmpty(row)) {continue;}T entity null;for (Map.EntryInteger, EntityExcelVo entry : entityExcelPointMap.entrySet()) {EntityExcelVo excelVo entry.getValue();// 从map中得到对应列的field.Field field excelVo.getCurField();Excel attr excelVo.getExcel();Verify verify excelVo.getVerify();Object val this.getCellValue(row, entry.getKey());OptionalT first list.stream().findFirst();//是空 且行匹配合并单元格if ((val null || StringUtils.isEmpty(val.toString())) curSheetMergeMap.containsKey(entry.getKey()) (list.stream().findAny().isPresent()) {val ReflectUtils.invokeGetter(list.get(list.size()-1), field.getName()); }// 如果不存在实例则新建.entity (entity null ? (T) clazz.newInstance() : entity);// 取得类型,并根据对象类型设置值.Class? fieldType field.getType();if(val!null convertValMap.containsKey(fieldType)){val convertValMap.get(fieldType).apply(val.toString());}String propertyName field.getName();if (verify ! null) {String prefix sheetName: curSheet.getSheetName() ,;if (!verify.nullable() ObjectUtil.isEmpty(val)) {appendIfAbsent(errorRow, sheetName i, String.format(%s第%d行,%s不能为空\n, prefix, i 1, attr.name()));} else if (val ! null val instanceof String ((String) val).length() verify.maxLength()) {appendIfAbsent(errorRow, sheetName i, String.format(%s第%d行%s不得超过%d个字符\n, prefix, i 1, attr.name(), verify.maxLength()));} else if (val ! null StringUtils.isNotEmpty(verify.patternReg()) String.valueOf(val).matches(verify.patternReg())) {appendIfAbsent(errorRow, sheetName i, String.format(%s第%d行%s%s, prefix, i 1, attr.name(),StringUtils.isNotEmpty(verify.errorMsg()) ? 格式错误\n : verify.errorMsg()));}}ReflectUtils.invokeSetter(entity, propertyName, val);}//加入集合前的回调if (this.beforeAddFunc ! null) {//返回false则不加入集合(list)if (!((BiFunctionT, Row, Boolean) this.beforeAddFunc).apply(entity, row)) {errorRow.remove(sheetName i);continue;}}if (!errorRow.isEmpty()) {errorRow.entrySet().stream().forEach(key - {if (errorRow.get(key) ! null) {errorSbf.append(errorRow.get(key));}});}list.add(entity);}}}return new VerifyResult(list, errorSbf.toString());}/*** 追加内容** param errorRow* param sheetRowTxt 所在行* param msg 错误消息*/private void appendIfAbsent(MapString, StringBuffer errorRow, String sheetRowTxt, String msg) {if (!errorRow.containsKey(sheetRowTxt)) {errorRow.put(sheetRowTxt, new StringBuffer());}errorRow.get(sheetRowTxt).append(msg);}/*** 获取字段注解信息*/public MapString, EntityExcelVo getEntityExcelMap(String businessId, Class? clazz) {MapString, EntityExcelVo map new HashMap();ListField tempFields new ArrayList();tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));//Excel attr null;Verify[] verifys null;OptionalVerify first null;EntityExcelVo entityExcelVo null;Excel[] repeatExcels null;for (Field field : tempFields) {if (!ArrayUtils.contains(this.excludeFields, field.getName())) {//多注解 校验repeatExcels field.getAnnotationsByType(Excel.class);if (ObjectUtil.isNotEmpty(repeatExcels)) {field.setAccessible(true);for (Excel repeatExcel : repeatExcels) {entityExcelVo new EntityExcelVo();entityExcelVo.setExcel(repeatExcel);entityExcelVo.setCurField(field);map.putIfAbsent(repeatExcel.name(), entityExcelVo);//如果有校验规则 添加上verifys field.getAnnotationsByType(Verify.class);if (verifys ! null verifys.length 0) {first Arrays.stream(verifys).filter(p - businessId null || p.businessDiff() null ||Arrays.stream(p.businessDiff()).filter(o - businessId.equals(o)).count() 0).findAny();map.get(repeatExcel.name()).setVerify(first.get());}}}}}return map;}/*** 获取单元格值** param row 获取的行* param column 获取单元格列号* return 单元格值*/public Object getCellValue(Row row, int column) {if (row null) {return row;}Object val ;try {Cell cell row.getCell(column);if (StringUtils.isNotNull(cell)) {if (cell.getCellType() CellType.NUMERIC || cell.getCellType() CellType.FORMULA) {val cell.getNumericCellValue();if (DateUtil.isCellDateFormatted(cell)) {val DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换} else {if ((Double) val % 1 ! 0) {val new BigDecimal(val.toString());} else {val new DecimalFormat(0).format(val);}}} else if (cell.getCellType() CellType.STRING) {val cell.getStringCellValue();} else if (cell.getCellType() CellType.BOOLEAN) {val cell.getBooleanCellValue();} else if (cell.getCellType() CellType.ERROR) {val cell.getErrorCellValue();}}} catch (Exception e) {return val;}return val;}/*** 判断是否是空行** param row 判断的行* return*/private boolean isRowEmpty(Row row) {if (row null) {return true;}for (int i row.getFirstCellNum(); i row.getLastCellNum(); i) {Cell cell row.getCell(i);if (cell ! null cell.getCellType() ! CellType.BLANK) {return false;}}return true;}/*** 格式化不同类型的日期对象** param dateFormat 日期格式* param val 被格式化的日期对象* return 格式化后的日期字符*/public String parseDateToStr(String dateFormat, Object val) {if (val null) {return ;}String str;if (val instanceof Date) {str DateUtils.parseDateToStr(dateFormat, (Date) val);} else if (val instanceof LocalDateTime) {str DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDateTime) val));} else if (val instanceof LocalDate) {str DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));} else {str val.toString();}return str;}/*** 获取当前sheet单元格合并信息 仅记录列的范围** param sheet*/public MapInteger, String getCurSheetMergeMap(Sheet sheet) {// 获取所有的合并区域ListCellRangeAddress mergedRegions sheet.getMergedRegions();MapInteger, String map new HashedMap();for (CellRangeAddress mergedRegion : mergedRegions) {for (int i mergedRegion.getFirstColumn(); i mergedRegion.getLastColumn(); i) {map.put(i, null);}}return map;}/*** 关闭workbook (不用也会自动关闭流)*/public void close() {if (this.wb ! null) {try {this.wb.close();} catch (IOException e) {e.printStackTrace();}}}}在这个里面我加了个setBeforeAddFunc方法其实是函数式接口就是将当前的Entity加入集合前的操作如果返回false则不加入集合。作用是对数据格式做一些处理。
使用方法
//伪代码 这边弄一个简单Book类 在
public class Book{private Long id;Excel(name 作者)Verify(maxLength 120)private String author;Excel(name 价格)Verify(patternReg ^\\d{2,10}(.)?\\d{2,10})private BigDecimal price;Excel(name 发布时间)Excel(name 出版时间)private Date pushTime;
}上面author校验了字符长度price使用了正则 现在是数字可以小数当然这个写的不是很精准 在pushTime上使用两个Excel 不过name的值不一样 意味着不同模板中表头是“发布时间”或出版时间都映射的是pushTime。
public static void main(String[] args) throws Exception {ExcelReadUtil util new ExcelReadUtil (Files.newInputStream(Paths.get(D:\\desktop-data\\book.xlsx)));VerifyResultBook verifyResult util.importExcelAll(1, null,Book.class);}
