Java自定義注解導入和導出合并一對多單元格工具類方式
更新時間：2026年04月07日 09:19:16 作者：無了_wule
文章主要介紹了excel工具類對poi的封裝,包括導入依賴、創(chuàng)建注解、IExcelUtils工具類代碼等內(nèi)容,示例通過訂單、訂單明細、追溯碼三類實體進行導入和導出測試,展示了日期轉(zhuǎn)換、排序、隱藏字段等功能的應用
excel工具類對poi的封裝,所以需要導入poi的依賴，我用的是4.1.0版本，如果是4以下，工具里面的poi的相關(guān)api需要改變；
導入/導出支持：一對多合并（不限制層級數(shù)，但每個類只能有一個子集）、必填字段檢測（讀取的時候能用到）、排序（導出能用到）、動態(tài)隱藏字段（導入導出會忽略該字段）
1、導入依賴
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>2、創(chuàng)建注解IExcel注解
name: 用作excel列名isSon：用作判斷是否是子集（默認false,如果有子集，在子集成員變量上標注： @IExcel(isSon = true)）hiddenField：隱藏字段（默認為：false,當為true時，導入導出會忽略該字段，用法: IExcelUtils.hideColumn( clazz,columnName, target),參數(shù)依次為：需要修改的class對象、需要修改的字段名、修改的值,也就是true/false），注意 ：IExcelUtils.hideColumn()方法需要用在導入導出前才有效sortNo： 排序號（默認為0,不進行排序，如果需要排序，在字段上標注@IExcel(sortNo=正整數(shù)),從1開始排序）- 注意：如果有子集，子集也是從1開始排序
isNotBlank: 設置字段不為空（默認false;在導入時，如果希望某個字段必填不然該條數(shù)據(jù)就不要讀取,就需要在該字段上標注：@IExcel(isNotBlank=true)；如需要獲取沒有讀取的字段信息，用 IExcelUtils.getErrRowInfo()方法可以獲取到）booleanFormatter：布爾類型字段轉(zhuǎn)換（處理布爾值的轉(zhuǎn)換，注意：請將表示true的標識寫在分割符"|“的左邊，表示false的標識寫在分割符“|”的右邊 ；默認"是|否”）dateFormat：日期轉(zhuǎn)換（默認：“yyyy/MM/dd”;導入導出時，日期格式化）
package com.ph.excel.annotation;
import com.ph.excel.IExcelUtils;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* excel
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface IExcel {
/**
* 列名稱,不能重復
* @return
*/
String name() default "";
/**
* 是否是子集
* @return
*/
boolean isSon() default false;
/**
* 隱藏字段
* @return
*/
boolean hiddenField() default false;
/**
* 排序號
* @return
*/
int sortNo() default 0;
/**
* 字段必填,不為空
* @return
*/
boolean isNotBlank() default false;
//處理布爾值的轉(zhuǎn)換，注意：請將表示true的標識寫在分割符"|"的左邊，表示false的標識寫在分割符“|”的右邊 ；默認"是|否"
String booleanFormatter() default "是|否";
/**
* 日期格式化
* @return
*/
String dateFormat() default IExcelUtils.IEXCEL_DATE_DEFAULT_FORMAT;
}3、IExcelUtils工具類代碼
package com.ph.excel;
import com.ph.excel.annotation.IExcel;
import com.sun.istack.internal.NotNull;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* @ClassName: IExcelUtils
* @Description: 讀取或?qū)С鰁xcel工具類
* @Date: 2023/6/1 9:57
* @author: ph9527
* @version: 1.0
*/
public class IExcelUtils {
/**
* 獲取獲取大標題行數(shù)時循環(huán)的行數(shù)
*/
private final static Integer FIND_TITLE_SIZE = 10;
/**
* IExcel與當前exceldate類型共有的日期格式
*/
public final static String IEXCEL_DATE_DEFAULT_FORMAT = "yyyy/MM/dd";
/**
* 處理布爾值的轉(zhuǎn)換，注意：請將表示true的標識寫在分割符"|"的左邊，表示false的標識寫在分割符“|”的右邊 ；默認"是|否"
*/
public final static String IEXCEL_BOOLEAN_TRUE_DEFAULT_FLAG = "\\|";
/**
* 默認標題合并列
*/
public final static int Excel_DEFAULT_TITLE_MERGERNUM = 6;
/**
* 因缺失必填字段為讀取的數(shù)據(jù)信息
*/
private static List<BlankRowInfo> errRowInfo;
/**
* excel映射頂級對象class
*/
private static Class clazz;
static class ClassVoIExcelEntity {
/**
* 當前所屬對象class
*/
private Class clazz;
private Class parentClass;
//當前等級
private Integer levelIndex;
/**
* 所有標注IExcel注解的各屬性值
*/
private List<ClassVoFieldIExcel> fields;
/**
* 標注IExcel的子集
*/
private ClassVoIExcelEntity sonClassVo;
}
static class ClassVoFieldIExcel {
private String classVoFieldName;
private IExcel iExcel;
}
private static class CellTitleValueEntity {
private String cellTitleName;
private Integer cellIndex;
//在當前行字段對應對象的第一個元素
private boolean isExcelObjFirstField;
//在當前行字段對應對象的最后一個元素
private boolean isExcelObjLastField;
}
private static class MergedRowInfo {
private Boolean isMerge;
private Integer firstRow;
private Integer lastRow;
}
public static class BlankRowInfo {
//缺失信息的行號
public Long rowNo;
//當前對象級別
public Integer objLevel;
//缺失信息的列名
public List<String> blankTitleNames;
}
private static class ExportColumn {
private String excelColumnName;
private String value;
}
private static class ExportRow {
private List<ExportColumn> values;
private List<ExportRow> sons;
private Integer levelIndex;
private Integer mergeNum;
}
private static class TitleEntity {
private String name;
private Integer sortNo;
}
/**
* 讀取excel文件
*
* @param inputStream 前端上傳到后端的文件用的是MultipartFile接收，免得轉(zhuǎn)File,就用inputStream做參數(shù)
* @param fileName 文件名，必填，需要根據(jù)文件名判斷是否是excel文件以及判斷是xlsx或者xls，直接用file.getName()就行了
* @param sourceClass
* @param <T>
* @return
*/
public static <T> List<T> readExcel(@NotNull InputStream inputStream, @NotNull String fileName, @NotNull Class<T> sourceClass) {
//初始化
clazz = null;
List<T> data = new ArrayList<>();
Workbook wb = null;
try {
//檢測是否是excel文件
if (!isExcelFile(fileName)) {
throw new RuntimeException("當前文件不是excel文件");
}
//檢測是否是xlsx,如果文件名為空，則默認xlsx
wb = isXlsx(fileName) ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
} catch (Exception e) {
data = new ArrayList<>();
throw new RuntimeException("excel文件解析錯誤：" + e);
}
if (wb != null) {
//給頂級對象賦值
clazz = sourceClass;
errRowInfo = new ArrayList<>();
Sheet sheet0 = wb.getSheetAt(0);
int lastRowNum = sheet0.getLastRowNum();
//總行數(shù)小于等于標題行則代表沒有數(shù)據(jù)，直接返回；
int rowCount = lastRowNum + 1;
//獲取映射對象字段值
List<String> iExcelNames = new ArrayList<>();
ClassVoIExcelEntity classVoIExcelEntity = new ClassVoIExcelEntity();
classVoIExcelEntity.clazz = clazz;
classVoIExcelEntity.levelIndex = 0;
setAllClassVoIExcelEntity(classVoIExcelEntity, iExcelNames);
int titleCount = getTitleCount(iExcelNames, sheet0);
if (rowCount <= titleCount) {
return data;
}
//讀取數(shù)據(jù)
try {
readData(sheet0, titleCount, classVoIExcelEntity, data);
} catch (Exception e) {
throw new RuntimeException("讀取excel數(shù)據(jù)錯誤:" + e);
}
}
return data;
}
/**
* 導出excel
*
* @param fileName
* @param titleName
* @param response
* @param data
* @param sourceClass
*/
public static void exportExcel(@NotNull String fileName, @NotNull String titleName, @NotNull HttpServletResponse response, @NotNull List data, Class sourceClass) throws FileNotFoundException {
int rowIndex = 0;
Workbook workbook = null;
OutputStream outputStream = null;
try {
// 創(chuàng)建一個Excel工作簿
workbook = new XSSFWorkbook();
// 創(chuàng)建一個工作表
XSSFSheet sheet = (XSSFSheet) workbook.createSheet();
clazz = sourceClass;
//獲取映射對象字段值
ClassVoIExcelEntity classVoIExcelEntity = new ClassVoIExcelEntity();
classVoIExcelEntity.clazz = clazz;
classVoIExcelEntity.levelIndex = 0;
setAllClassVoIExcelEntity(classVoIExcelEntity, new ArrayList<>());
//獲取排序好的所有列名
List<String> iExcelNames = new ArrayList<>();
getIexcelNames(classVoIExcelEntity, iExcelNames);
//獲取最后的層級，從0開始的
Integer lastIndex = getLastLevel(classVoIExcelEntity);
sheet.setDefaultColumnWidth(15);
//設置大標題
if (strIsNotBlank(titleName)) {
createBigTitle(sheet, iExcelNames, titleName, rowIndex);
rowIndex++;
}
if (listIsNotEmpty(iExcelNames)) {
//設置小標題
createSmallTitles(sheet, iExcelNames, rowIndex);
rowIndex++;
if (listIsNotEmpty(data)) {
List<ExportRow> exportRows = new ArrayList<>();
//設置數(shù)據(jù)與ExportRow的對應關(guān)系
setExportDataMapping(classVoIExcelEntity, data, exportRows);
//設置數(shù)據(jù)合并行數(shù)
setMergeRowForExportRow(exportRows, lastIndex);
//將數(shù)據(jù)輸入到excel表格中
setDataToExcel(exportRows, sheet, iExcelNames, rowIndex);
}
}
// 告訴瀏覽器用什么軟件可以打開此文件
response.setHeader("content-Type", "application/octet-stream;charset=utf-8");
// 下載文件的默認名稱
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
}
private static void setDataToExcel(List<ExportRow> exportRows, XSSFSheet sheet, List<String> iExcelNames, int startRowIndex) {
for (ExportRow exportRow : exportRows) {
Integer mergeNum = exportRow.mergeNum;
List<ExportColumn> values = exportRow.values;
XSSFRow row = nextRow(sheet, startRowIndex);
int cellIndex = 0;
boolean mergeFlag = !(mergeNum == 0 || mergeNum == 1);
for (; cellIndex < values.size(); cellIndex++) {
XSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(getValueFromExportColumn(iExcelNames.get(cellIndex), values));
if (mergeFlag) {
int endIndex = startRowIndex + mergeNum - 1;
sheet.addMergedRegion(new CellRangeAddress(startRowIndex, endIndex, cellIndex, cellIndex));
}
}
List<ExportRow> sons = exportRow.sons;
if (listIsNotEmpty(sons)) {
setSonDataToExcel(sons, sheet, iExcelNames, startRowIndex, cellIndex);
}
if (mergeFlag) {
startRowIndex += mergeNum;
} else {
startRowIndex += 1;
}
}
}
/**
* 設置子集的值到excel中
*
* @param sons
* @param sheet
* @param startRowIndex
* @param startCellIndex
*/
private static void setSonDataToExcel(List<ExportRow> sons, XSSFSheet sheet, List<String> iExcelNames, int startRowIndex, int startCellIndex) {
Integer rowIndex = startRowIndex;
for (ExportRow exportRow : sons) {
Integer cellIndex = startCellIndex;
Integer mergeNum = exportRow.mergeNum;
List<ExportColumn> values = exportRow.values;
XSSFRow row = null;
if (sheet.getRow(rowIndex) == null) {
row = nextRow(sheet, rowIndex);
} else {
row = sheet.getRow(rowIndex);
}
Integer endCellIndex = values.size() + startCellIndex;
//合并標識
boolean mergeFlag = !(mergeNum == 0 || mergeNum == 1);
for (; cellIndex < endCellIndex; cellIndex++) {
XSSFCell cell = row.createCell(cellIndex);
cell.setCellValue(getValueFromExportColumn(iExcelNames.get(cellIndex), values));
if (mergeFlag) {
int endIndex = rowIndex + mergeNum - 1;
sheet.addMergedRegion(new CellRangeAddress(rowIndex, endIndex, cellIndex, cellIndex));
}
}
List<ExportRow> sons1 = exportRow.sons;
if (mergeFlag) {
rowIndex += mergeNum;
} else {
rowIndex += 1;
}
if (listIsNotEmpty(sons1)) {
setSonDataToExcel(sons1, sheet, iExcelNames, row.getRowNum(), startCellIndex + values.size());
}
}
}
/**
* 根據(jù)列名獲取值
*
* @param name
* @param values
* @return
*/
private static String getValueFromExportColumn(String name, List<ExportColumn> values) {
for (ExportColumn exportColumn : values) {
if (name.equals(exportColumn.excelColumnName)) {
return exportColumn.value;
}
}
return null;
}
/**
* 獲取最后的層級數(shù)
*
* @param classVoIExcelEntity
* @return
*/
private static Integer getLastLevel(ClassVoIExcelEntity classVoIExcelEntity) {
ClassVoIExcelEntity sonClassVo = classVoIExcelEntity.sonClassVo;
if (sonClassVo == null) {
return classVoIExcelEntity.levelIndex;
} else {
return getLastLevel(sonClassVo);
}
}
/**
* 設置合并行數(shù)
*
* @param exportRows
* @param lastIndex
*/
private static void setMergeRowForExportRow(List<ExportRow> exportRows, Integer lastIndex) {
for (ExportRow exportRow : exportRows) {
List<ExportRow> sons = exportRow.sons;
if (lastIndex == 0) {
exportRow.mergeNum = 1;
} else {
exportRow.mergeNum = getMergeNum(sons, lastIndex);
if (listIsNotEmpty(sons)) {
setMergeRowForExportRow(sons, lastIndex);
}
}
}
}
private static int getMergeNum(List<ExportRow> sons, Integer lastIndex) {
if (listIsNotEmpty(sons)) {
ExportRow exportRow = sons.get(0);
if (exportRow.levelIndex == lastIndex - 1) {
AtomicInteger count = new AtomicInteger();
sons.forEach(item -> {
List<ExportRow> sons1 = item.sons;
if (listIsNotEmpty(sons1)) {
count.addAndGet(sons1.size());
}
});
return count.get();
} else if (exportRow.levelIndex == lastIndex) {
return sons.size();
} else {
AtomicInteger flag = new AtomicInteger();
sons.forEach(item -> {
flag.addAndGet(getMergeNum(item.sons, lastIndex));
});
return flag.get();
}
}
return 0;
}
/**
* 獲取所有excel列名，并按順序排列
*
* @param classVoIExcelEntity
* @return
*/
private static void getIexcelNames(ClassVoIExcelEntity classVoIExcelEntity, List<String> names) {
if (classVoIExcelEntity != null) {
List<ClassVoFieldIExcel> fields = classVoIExcelEntity.fields;
ClassVoIExcelEntity sonClassVo = classVoIExcelEntity.sonClassVo;
if (listIsNotEmpty(fields)) {
List<IExcel> excelsSorted = new ArrayList<>();
List<IExcel> excels = new ArrayList<>();
for (int i = 0; i < fields.size(); i++) {
ClassVoFieldIExcel classVoFieldIExcel = fields.get(i);
IExcel iExcel = classVoFieldIExcel.iExcel;
if (iExcel != null && !iExcel.isSon()) {
if (iExcel.sortNo() == 0) {
excels.add(iExcel);
} else {
excelsSorted.add(iExcel);
}
}
}
//合并
List<String> newNames = Stream.concat(
excelsSorted.stream().sorted(Comparator.comparingInt(IExcel::sortNo)).map(item -> item.name()),
excels.stream().map(item -> item.name())
).collect(Collectors.toList());
names.addAll(newNames);
}
//是否是有集
if (sonClassVo != null) {
getIexcelNames(classVoIExcelEntity.sonClassVo, names);
}
}
}
/**
* 設置數(shù)據(jù)與excel每列的對應關(guān)系
*
* @param classVoIExcelEntity
* @param data
* @param exportRows
*/
private static void setExportDataMapping(ClassVoIExcelEntity classVoIExcelEntity, List data, List<ExportRow> exportRows) {
for (int i = 0; i < data.size(); i++) {
ExportRow exportRow = new ExportRow();
List<ExportRow> sonExportRow = null;
Object obj = data.get(i);
Class<?> objClass = obj.getClass();
ClassVoIExcelEntity classEntity = getClassEntityByClass(classVoIExcelEntity, objClass);
if (classEntity != null) {
List<ClassVoFieldIExcel> fieldIExcels = classEntity.fields;
List<ExportColumn> columnList = new ArrayList<>();
fieldIExcels.forEach(fieldIExcel -> {
try {
if (!fieldIExcel.iExcel.isSon()) {
Field field = objClass.getDeclaredField(fieldIExcel.classVoFieldName);
if (field != null) {
field.setAccessible(true);
ExportColumn exportColumn = new ExportColumn();
exportColumn.value = getFieldValue(obj, field, fieldIExcel.iExcel);
exportColumn.excelColumnName = fieldIExcel.iExcel.name();
columnList.add(exportColumn);
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
});
exportRow.values = columnList;
exportRow.levelIndex = classEntity.levelIndex;
ClassVoIExcelEntity sonClassVo = classEntity.sonClassVo;
if (sonClassVo != null) {
sonExportRow = new ArrayList<>();
List sonData = getSonData(obj);
if (listIsNotEmpty(sonData)) {
setExportDataMapping(classVoIExcelEntity, sonData, sonExportRow);
exportRow.sons = sonExportRow;
}
}
exportRows.add(exportRow);
}
}
}
/**
* 獲取對象的屬性值
*
* @param obj
* @param <T>
* @return
*/
private static <T> String getFieldValue(Object obj, Field field, IExcel annotation) throws Exception {
String value = null;
field.setAccessible(true);
if (field.getType() == Boolean.class || field.getType() == boolean.class) {
if (annotation != null) {
String s = annotation.booleanFormatter();
String[] split = s.split(IEXCEL_BOOLEAN_TRUE_DEFAULT_FLAG);
Object o = field.get(obj);
if (o != null) {
Boolean aBoolean = (Boolean) field.get(obj);
value = aBoolean ? split[0] : split[1];
} else {
value = "";
}
}
} else if (field.getType() == Date.class) {
if (annotation != null) {
String dateForMatter = annotation.dateFormat();
Object o = field.get(obj);
if (o != null) {
Date date = (Date) field.get(obj);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateForMatter);
value = simpleDateFormat.format(date);
} else {
value = "";
}
}
} else {
Object o = field.get(obj);
value = o != null ? o.toString() : "";
}
return value;
}
/**
* 獲取子集
*
* @param obj
* @return
*/
private static List getSonData(Object obj) {
Field[] declaredFields = obj.getClass().getDeclaredFields();
for (Field field : declaredFields) {
Class<?> type = field.getType();
if (type == List.class) {
IExcel annotation = field.getAnnotation(IExcel.class);
if (annotation != null && annotation.isSon()) {
try {
field.setAccessible(true);
return (List) field.get(obj);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
}
return null;
}
/**
* 根據(jù)class獲取對應的classVoIExcelEntity
*
* @param classVoIExcelEntity
* @param aClass
* @return
*/
private static ClassVoIExcelEntity getClassEntityByClass(ClassVoIExcelEntity classVoIExcelEntity, Class<?> aClass) {
if (classVoIExcelEntity == null) return null;
Class clazz1 = classVoIExcelEntity.clazz;
if (clazz1.getTypeName().equals(aClass.getTypeName())) return classVoIExcelEntity;
ClassVoIExcelEntity sonClassVo = classVoIExcelEntity.sonClassVo;
return getClassEntityByClass(sonClassVo, aClass);
}
/**
* 創(chuàng)建大標題樣式
*
* @param wb
* @return
*/
public static XSSFCellStyle createBigTitleCellStyle(XSSFWorkbook wb) {
// 標題樣式（加粗，垂直居中）
XSSFCellStyle titleCellStyle = wb.createCellStyle();
titleCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
XSSFFont fontStyle = wb.createFont();
fontStyle.setBold(true); //加粗
fontStyle.setFontHeightInPoints((short) 16); //設置標題字體大小
titleCellStyle.setFont(fontStyle);
return titleCellStyle;
}
/**
* 創(chuàng)建小標題樣式
*
* @param wb
* @return
*/
public static XSSFCellStyle createSmallTitleCellStyle(XSSFWorkbook wb) {
//設置表頭樣式，表頭居中
XSSFCellStyle style = wb.createCellStyle();
//設置單元格樣式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//設置字體
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 14);
style.setFont(font);
return style;
}
/**
* 設置大標題
*
* @param sheet
* @param iExcelNames
* @param titleName
* @param rowIndex
*/
private static void createBigTitle(XSSFSheet sheet, List<String> iExcelNames, String titleName, int rowIndex) {
XSSFCellStyle style = createBigTitleCellStyle(sheet.getWorkbook());
XSSFRow rowBigTitle = nextRow(sheet, rowIndex);
//大標題
XSSFCell cell = rowBigTitle.createCell(0);
cell.setCellValue(titleName);
cell.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listIsNotEmpty(iExcelNames) ? iExcelNames.size() - 1 : Excel_DEFAULT_TITLE_MERGERNUM));
}
/**
* 設置小標題
*
* @param sheet
* @param iExcelNames
* @param rowIndex
*/
private static void createSmallTitles(XSSFSheet sheet, List<String> iExcelNames, int rowIndex) {
XSSFCellStyle style = createSmallTitleCellStyle(sheet.getWorkbook());
XSSFRow rowSmallTitle = nextRow(sheet, rowIndex);
for (int i = 0; i < iExcelNames.size(); i++) {
XSSFCell cell = rowSmallTitle.createCell(i);
cell.setCellValue(iExcelNames.get(i));
cell.setCellStyle(style);
}
}
/**
* 獲取下一行
*
* @param sheet
* @param rowIndex
* @return
*/
private static XSSFRow nextRow(XSSFSheet sheet, int rowIndex) {
XSSFRow row = sheet.createRow(rowIndex);
return row;
}
/**
* 讀取數(shù)據(jù)
*
* @param sheet
* @param titleCount
* @param classVoIExcelEntity
* @param data
* @param <T>
*/
private static <T> void readData(Sheet sheet, Integer titleCount, ClassVoIExcelEntity classVoIExcelEntity, List<T> data) throws Exception {
//定義一個數(shù)組，存放各個層級的當前對象
List levelsObjs = new ArrayList();
//獲取excel的所有小標題
List<CellTitleValueEntity> cellTitles = getCellTitles(sheet, titleCount);
if (listIsNotEmpty(cellTitles)) {
//設置哪些是當前行的第一個元素
setFirstAndLastField(cellTitles, classVoIExcelEntity);
//開始讀取數(shù)據(jù)
int startDataIndex = titleCount + 1;
int allRowNums = sheet.getLastRowNum() + 1;
try {
//循環(huán)excel每一行
for (int i = startDataIndex; i < allRowNums; i++) {
Row row = sheet.getRow(i);
if (row != null && checkRowDataIsNotBlank(cellTitles, row)) {
for (int j = 0; j < cellTitles.size(); j++) {
CellTitleValueEntity cellTitleValueEntity = cellTitles.get(j);
Integer nowCellIndex = cellTitleValueEntity.cellIndex;
Cell cell = row.getCell(nowCellIndex);
if (cell == null) {
cell = row.createCell(nowCellIndex);
}
String cellValue = getCellValue(cell);
//獲取當前字段的所屬class
ClassVoIExcelEntity classVoIExcelEntityNow = getClassVoExcelEntityByExcelCellTitle(cellTitleValueEntity.cellTitleName, classVoIExcelEntity);
Integer levelIndex = classVoIExcelEntityNow.levelIndex;
if (classVoIExcelEntityNow != null) {
//當前單元格是否為第一個字段
boolean isExcelObjFirstField = cellTitleValueEntity.isExcelObjFirstField;
boolean isExcelObjLastFieldField = cellTitleValueEntity.isExcelObjLastField;
//獲取合并信息
MergedRowInfo mergedRowInfo = getMergedRowInfo(cell, sheet);
Boolean isMerge = mergedRowInfo.isMerge;
Integer firstRow = mergedRowInfo.firstRow;
Integer lastRow = mergedRowInfo.lastRow;
//判斷是否需要新建對象
if (isExcelObjFirstField && (!isMerge || i == firstRow)) {
if (!listIsNotEmpty(levelsObjs) || levelsObjs.size() < levelIndex + 1) {
levelsObjs.add(classVoIExcelEntityNow.clazz.newInstance());
} else {
levelsObjs.set(levelIndex, classVoIExcelEntityNow.clazz.newInstance());
}
}
//獲取當前對象
Object nowObj = levelsObjs.get(classVoIExcelEntityNow.levelIndex);
//獲取當前單元格對應對象的字段名
String fieldName = getFieldNameByCellTitleName(cellTitleValueEntity.cellTitleName, classVoIExcelEntityNow);
//給屬性設置值
if (strIsNotBlank(cellValue)) {
setCellValueToObjField(nowObj, fieldName, cellValue);
}
//獲取當前對象的上級對象
Object parentObj = getParentObj(classVoIExcelEntityNow, levelsObjs);
List sonList = getSonListByParentObj(parentObj);
//判斷當前單元格是否是所屬對象最后的一個對應，借此判斷當前行當前對象是否填充完成
if (isExcelObjLastFieldField && (!isMerge || i == lastRow)) {
//判斷必填字段是否都有值
BlankRowInfo blankRowInfo = checkObjIsNotBlankField(classVoIExcelEntityNow, nowObj, (long) (i + 1));
if (blankRowInfo == null) {
if (parentObj != null) {
sonList.add(nowObj);
} else {
data.add((T) nowObj);
}
} else {
errRowInfo.add(blankRowInfo);
setSonFieldValueToNull(nowObj);
break;
}
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 獲取未被讀取的錯誤信息
*
* @return
*/
public static List<BlankRowInfo> getErrRowInfo() {
return errRowInfo;
}
/**
* 設置子集值為空
*
* @param nowObj
*/
private static void setSonFieldValueToNull(Object nowObj) {
if (nowObj != null) {
Class<?> parentObjClass = nowObj.getClass();
Field[] declaredFields = parentObjClass.getDeclaredFields();
for (Field field : declaredFields) {
IExcel annotation = field.getAnnotation(IExcel.class);
if (annotation != null) {
if (annotation.isSon()) {
field.setAccessible(true);
Class<?> type = field.getType();
if (type == List.class) {
try {
field.set(nowObj, null);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
}
}
}
}
/**
* 檢查當前對象必填字段是否都有值
*
* @param classVoIExcelEntityNow
* @param nowObj
*/
private static BlankRowInfo checkObjIsNotBlankField(ClassVoIExcelEntity classVoIExcelEntityNow, Object nowObj, Long rowNo) {
AtomicReference<BlankRowInfo> blankRowInfo = new AtomicReference<>();
List<ClassVoFieldIExcel> fields = classVoIExcelEntityNow.fields;
if (nowObj != null && listIsNotEmpty(fields)) {
Class<?> clazz = nowObj.getClass();
Field[] declaredFields = clazz.getDeclaredFields();
Arrays.stream(declaredFields).forEach(field -> {
field.setAccessible(true);
try {
Object fieldValueObj = field.get(nowObj);
IExcel iExcel = field.getAnnotation(IExcel.class);
if (fieldValueObj == null && iExcel != null && iExcel.isNotBlank()) {
if (blankRowInfo.get() == null) {
blankRowInfo.set(new BlankRowInfo());
blankRowInfo.get().rowNo = rowNo;
blankRowInfo.get().objLevel = classVoIExcelEntityNow.levelIndex;
blankRowInfo.get().blankTitleNames = new ArrayList<>();
}
blankRowInfo.get().blankTitleNames.add(iExcel.name());
}
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
});
}
return blankRowInfo.get();
}
/**
* 獲取上級對象的子集
*
* @param parentObj
* @return
*/
private static List getSonListByParentObj(Object parentObj) {
if (parentObj != null) {
Class<?> parentObjClass = parentObj.getClass();
Field[] declaredFields = parentObjClass.getDeclaredFields();
for (Field field : declaredFields) {
IExcel annotation = field.getAnnotation(IExcel.class);
if (annotation != null) {
if (annotation.isSon()) {
field.setAccessible(true);
Class<?> type = field.getType();
if (type == List.class) {
try {
Object son = field.get(parentObj);
if (son != null) {
return (List) son;
}
List sonList = new ArrayList<>();
field.set(parentObj, sonList);
return sonList;
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
}
}
}
return null;
}
/**
* 獲取父級對象
*
* @param classVoIExcelEntityNow
* @param levelsObjs
* @return
*/
private static Object getParentObj(ClassVoIExcelEntity classVoIExcelEntityNow, List levelsObjs) {
Object parentObj = null;
Class parentClass = classVoIExcelEntityNow.parentClass;
if (parentClass != null) {
String typeName = parentClass.getTypeName();
for (Object item : levelsObjs) {
if (typeName.equals(item.getClass().getTypeName())) {
parentObj = item;
break;
}
}
}
return parentObj;
}
/**
* 獲取當前單元格對應對象的字段名
*
* @param cellTitleName
* @param classVoIExcelEntityNow
* @return
*/
private static String getFieldNameByCellTitleName(String cellTitleName, ClassVoIExcelEntity classVoIExcelEntityNow) {
List<ClassVoFieldIExcel> fields = classVoIExcelEntityNow.fields;
for (ClassVoFieldIExcel classVoFieldIExcel : fields) {
if (classVoFieldIExcel.iExcel.name().equals(cellTitleName)) {
return classVoFieldIExcel.classVoFieldName;
}
}
return null;
}
/**
* 給屬性設置值
*
* @param obj
* @param filedName
* @param cellValue
* @param <T>
*/
private static <T> void setCellValueToObjField(Object obj, String filedName, Object cellValue) throws Exception {
Class<?> clazz = obj.getClass();
Field declaredField = clazz.getDeclaredField(filedName);
declaredField.setAccessible(true);
Class<?> type = declaredField.getType();
if (type == Integer.class || type == int.class) {
declaredField.set(obj, Integer.parseInt(cellValue.toString()));
} else if (type == Long.class || type == long.class) {
declaredField.set(obj, Long.parseLong(cellValue.toString()));
} else if (type == boolean.class || type == Boolean.class) {
IExcel annotation = declaredField.getAnnotation(IExcel.class);
String s = annotation.booleanFormatter();
String[] split = s.split(IEXCEL_BOOLEAN_TRUE_DEFAULT_FLAG);
declaredField.set(obj, split[0].trim().equals(cellValue.toString().trim()) ? true : false);
} else if (type == Date.class) {
IExcel annotation = declaredField.getAnnotation(IExcel.class);
String s = annotation.dateFormat();
SimpleDateFormat format = new SimpleDateFormat(s);
declaredField.set(obj, format.parse(cellValue.toString()));
} else if (type == Double.class || type == double.class) {
declaredField.set(obj, Double.valueOf(cellValue.toString()));
} else if (type == List.class) {
declaredField.set(obj, cellValue);
} else if (type == BigDecimal.class) {
BigDecimal bigDecimal = BigDecimal.valueOf(Long.valueOf(cellValue.toString()));
declaredField.set(obj, bigDecimal);
} else {
declaredField.set(obj, cellValue);
}
}
/**
* 設置哪些是當前行的第一個元素和最后一個元素 （注意：這里要求不能出現(xiàn)重復的列名）
*
* @param cellTitles
* @param classVoIExcelEntity
*/
private static void setFirstAndLastField(List<CellTitleValueEntity> cellTitles, ClassVoIExcelEntity classVoIExcelEntity) {
final ClassVoIExcelEntity[] previousClassVo = {null};
for (int i = 0; i < cellTitles.size(); i++) {
CellTitleValueEntity item = cellTitles.get(i);
String cellTitleName = item.cellTitleName;
ClassVoIExcelEntity nowClassVoEntity = getClassEntityByCellTitleName(cellTitleName, classVoIExcelEntity);
if (nowClassVoEntity != null) {
//設置是否是第一個元素
if (previousClassVo[0] == null) {
item.isExcelObjFirstField = true;
previousClassVo[0] = nowClassVoEntity;
} else if (!previousClassVo[0].clazz.getTypeName().equals(nowClassVoEntity.clazz.getTypeName())) {
item.isExcelObjFirstField = true;
previousClassVo[0] = nowClassVoEntity;
}
//設置是否是最后一個元素
if (i < cellTitles.size() - 1) {
CellTitleValueEntity nextItem = cellTitles.get(i + 1);
String nextCellTitleName = nextItem.cellTitleName;
ClassVoIExcelEntity nextClassVoEntity = getClassEntityByCellTitleName(nextCellTitleName, classVoIExcelEntity);
if (nextClassVoEntity != null) {
if (!nowClassVoEntity.clazz.getTypeName().equals(nextClassVoEntity.clazz.getTypeName())) {
item.isExcelObjLastField = true;
}
}
}
if (i == cellTitles.size() - 1) {
item.isExcelObjLastField = true;
}
}
}
}
/**
* 根據(jù)列名獲取 ClassVoIExcelEntity
*
* @param cellTitleName
* @param classVoIExcelEntity
* @return
*/
private static ClassVoIExcelEntity getClassEntityByCellTitleName(String cellTitleName, ClassVoIExcelEntity classVoIExcelEntity) {
List<ClassVoFieldIExcel> fields = classVoIExcelEntity.fields;
for (ClassVoFieldIExcel item : fields) {
IExcel iExcel = item.iExcel;
String name = iExcel.name();
if (name.equals(cellTitleName)) {
return classVoIExcelEntity;
}
}
ClassVoIExcelEntity sonClassVo = classVoIExcelEntity.sonClassVo;
if (sonClassVo != null) {
return getClassEntityByCellTitleName(cellTitleName, sonClassVo);
}
return null;
}
/**
* 獲取當前字段所屬excel映射對象
*
* @param cellTitleName
* @param classVoIExcelEntity
* @return
*/
private static ClassVoIExcelEntity getClassVoExcelEntityByExcelCellTitle(String cellTitleName, ClassVoIExcelEntity classVoIExcelEntity) {
ClassVoIExcelEntity rsClassVoIExcelEntity = null;
List<ClassVoFieldIExcel> fields = classVoIExcelEntity.fields;
for (ClassVoFieldIExcel classVoFieldIExcel : fields) {
IExcel iExcel = classVoFieldIExcel.iExcel;
String name = iExcel.name();
if (name.equals(cellTitleName)) {
rsClassVoIExcelEntity = classVoIExcelEntity;
break;
}
}
if (rsClassVoIExcelEntity == null && classVoIExcelEntity.sonClassVo != null) {
rsClassVoIExcelEntity = getClassVoExcelEntityByExcelCellTitle(cellTitleName, classVoIExcelEntity.sonClassVo);
}
return rsClassVoIExcelEntity;
}
/**
* 獲取標題總行數(shù)
* iExcelNames
*
* @param sheet0
* @return
*/
private static int getTitleCount(List<String> iExcelNames, Sheet sheet0) {
for (int i = 0; i < FIND_TITLE_SIZE; i++) {
Row row = sheet0.getRow(i);
if (row != null) {
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (strIsNotBlank(cellValue) && iExcelNames.contains(cellValue)) {
return i;
}
}
}
}
return 0;
}
/**
* 獲取合并行信息
*
* @param cell
* @param sheet
* @return
*/
private static MergedRowInfo getMergedRowInfo(Cell cell, Sheet sheet) {
MergedRowInfo mergedRowInfo = new MergedRowInfo();
for (CellRangeAddress region : sheet.getMergedRegions()) {
if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
mergedRowInfo.isMerge = true;
mergedRowInfo.firstRow = region.getFirstRow();
mergedRowInfo.lastRow = region.getLastRow();
return mergedRowInfo;
}
}
mergedRowInfo.isMerge = false;
return mergedRowInfo;
}
/**
* 檢查當前行不是空的
*
* @param cellTitles
* @param row
* @return
*/
private static boolean checkRowDataIsNotBlank(List<CellTitleValueEntity> cellTitles, Row row) {
AtomicInteger flag = new AtomicInteger();
for (CellTitleValueEntity title : cellTitles) {
Integer cellIndex = title.cellIndex;
Cell cell = row.getCell(cellIndex);
if (cell != null) {
MergedRowInfo mergedRowInfo = getMergedRowInfo(cell, row.getSheet());
if (mergedRowInfo != null && mergedRowInfo.isMerge) {
return true;
}
if (strIsNotBlank(getCellValue(cell))) {
flag.getAndIncrement();
}
}
}
return flag.get() > 0;
}
/**
* 獲取所有小標題
*
* @param sheet
* @param titleCount
* @return
*/
private static List<CellTitleValueEntity> getCellTitles(Sheet sheet, Integer titleCount) {
List<CellTitleValueEntity> cellTitles = new ArrayList<>();
Row cellTitleRow = sheet.getRow(titleCount);
if (cellTitleRow != null) {
int lastCellNum = cellTitleRow.getLastCellNum();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = cellTitleRow.getCell(i);
String cellValue = getCellValue(cell);
if (strIsNotBlank(cellValue)) {
CellTitleValueEntity cellTitleEntity = new CellTitleValueEntity();
cellTitleEntity.cellTitleName = cellValue;
cellTitleEntity.cellIndex = i;
cellTitles.add(cellTitleEntity);
}
}
}
return cellTitles;
}
/**
* 獲取excel表格單元格的值
*
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
String cellvalue = "";
if (cell != null) {
// 判斷當前Cell的Type
switch (cell.getCellType()) {
// 如果當前Cell的Type為NUMERIC
case NUMERIC:
case FORMULA: {
// 判斷當前的cell是否為Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat(IEXCEL_DATE_DEFAULT_FORMAT);
cellvalue = sdf.format(date);
}
// 如果是純數(shù)字
else {
// 取得當前Cell的數(shù)值
cellvalue = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
}
// 如果當前Cell的Type為STRIN
case STRING:
// 取得當前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默認的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
/**
* 設置所有iexcel注解與目標對象的映射字段名和值
*
* @param classVoIExcelEntity classVoIExcelEntity.clazz必須有值
* @param iExcelNames
*/
private static void setAllClassVoIExcelEntity(ClassVoIExcelEntity classVoIExcelEntity, List<String> iExcelNames) {
AtomicBoolean isSonFlag = new AtomicBoolean(false);
Class nowClazz = classVoIExcelEntity.clazz;
List<ClassVoFieldIExcel> classVoFields = new ArrayList<>();
Field[] declaredFields = nowClazz.getDeclaredFields();
Arrays.stream(declaredFields).forEach(field -> {
IExcel iExcel = field.getAnnotation(IExcel.class);
if (iExcel != null && !iExcel.hiddenField()) {
ClassVoFieldIExcel classVoFieldIExcel = new ClassVoFieldIExcel();
if (iExcel.isSon() && !isSonFlag.get()) {
//處理子集
Class sonClass = getGenericClassForList(field);
if (sonClass != null) {
ClassVoIExcelEntity sonClassVoIExcel = new ClassVoIExcelEntity();
sonClassVoIExcel.clazz = sonClass;
sonClassVoIExcel.parentClass = classVoIExcelEntity.clazz;
sonClassVoIExcel.levelIndex = classVoIExcelEntity.levelIndex + 1;
setAllClassVoIExcelEntity(sonClassVoIExcel, iExcelNames);
classVoIExcelEntity.sonClassVo = sonClassVoIExcel;
isSonFlag.set(true);
}
} else {
iExcelNames.add(iExcel.name());
}
classVoFieldIExcel.classVoFieldName = field.getName();
classVoFieldIExcel.iExcel = iExcel;
classVoFields.add(classVoFieldIExcel);
}
});
classVoIExcelEntity.fields = classVoFields;
}
/**
* 獲取List的泛型類型
*
* @param field
* @return
*/
private static Class getGenericClassForList(Field field) {
Class clazz = null;
if (field.getType() == List.class) {
Type genericType = field.getGenericType();
if (genericType != null && genericType instanceof ParameterizedType) {
ParameterizedType pt = (ParameterizedType) genericType;
clazz = (Class<?>) pt.getActualTypeArguments()[0];
}
}
return clazz;
}
/**
* 判斷是否是excel文件
*
* @param fileName
* @return
*/
private static boolean isExcelFile(String fileName) {
if (!strIsNotBlank(fileName)) {
return false;
}
String fileNameSuffix = getFileNameSuffix(fileName);
if (fileNameSuffix.toLowerCase().equals("xlsx") || fileNameSuffix.toLowerCase().equals("xls")) {
return true;
} else {
return false;
}
}
/**
* 檢測excel類型,如果fileName為空，則默認為xlsx
*
* @param fileName
* @return
*/
private static boolean isXlsx(String fileName) {
if (!strIsNotBlank(fileName)) {
return true;
}
String fileNameSuffix = getFileNameSuffix(fileName);
if (fileNameSuffix.toLowerCase().equals("xlsx")) {
return true;
} else {
return false;
}
}
/**
* 獲取文件后綴名
*
* @param fileName
* @return
*/
private static String getFileNameSuffix(String fileName) {
String[] split = fileName.split("\\u002E");
String fileNameSuffix = split[split.length - 1];
return fileNameSuffix;
}
/**
* 檢測字符串不為空
*
* @param str
* @return
*/
private static boolean strIsNotBlank(String str) {
return !(str == null || str.trim().equals(""));
}
/**
* 判斷l(xiāng)ist不為空
*
* @param list
* @return
*/
private static boolean listIsNotEmpty(List list) {
return list != null && list.size() > 0;
}
/**
* 隱藏字段
* @param clazz
* @param columnName
* @param target
* @throws Exception
*/
public static void hideColumn(Class<?> clazz, String columnName, Boolean target) throws Exception {
if (!strIsNotBlank(columnName)) {
throw new NullPointerException("傳入的屬性列名為空");
}
if (target == null) {
target = true;
}
//獲取目標對象的屬性值
Field field = clazz.getDeclaredField(columnName);
//獲取注解反射對象
IExcel excelAnion = field.getAnnotation(IExcel.class);
//獲取代理
InvocationHandler invocationHandler = Proxy.getInvocationHandler(excelAnion);
Field excelField = invocationHandler.getClass().getDeclaredField("memberValues");
excelField.setAccessible(true);
Map memberValues = (Map) excelField.get(invocationHandler);
memberValues.put("hiddenField", target);
}
}
4、測試
a. 測試的實體類：訂單、訂單明細、追溯碼
- OrderModel
package com.ph.excel.vo;
import com.ph.excel.annotation.IExcel;
import java.util.Date;
import java.util.List;
/**
* @ClassName: OrderModel
* @Description: TODO
* @Date: 2023/6/2 14:31
* @author: ph9527
* @version: 1.0
*/
public class OrderModel {
private Long id;
@IExcel(name = "訂單號",isNotBlank = true)
private String orderNo;
@IExcel(name = "訂單時間", sortNo = 2)
private Date orderTime;
@IExcel(name = "購買人", sortNo = 1)
private String buyUser;
@IExcel(isSon = true)
private List<DetailModel> details;
public OrderModel() {
}
public OrderModel(Long id, String orderNo, Date orderTime, String buyUser, Integer buyNum, List<DetailModel> details) {
this.id = id;
this.orderNo = orderNo;
this.orderTime = orderTime;
this.buyUser = buyUser;
this.details = details;
}
@Override
public String toString() {
return "OrderModel{" +
"id=" + id +
", orderNo='" + orderNo + '\'' +
", orderTime=" + orderTime +
", buyUser='" + buyUser + '\'' +
", details=" + details +
'}';
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getOrderNo() {
return orderNo;
}
public void setOrderNo(String orderNo) {
this.orderNo = orderNo;
}
public Date getOrderTime() {
return orderTime;
}
public void setOrderTime(Date orderTime) {
this.orderTime = orderTime;
}
public String getBuyUser() {
return buyUser;
}
public void setBuyUser(String buyUser) {
this.buyUser = buyUser;
}
public List<DetailModel> getDetails() {
return details;
}
public void setDetails(List<DetailModel> details) {
this.details = details;
}
}
- DetailModel：
package com.ph.excel.vo;
import com.ph.excel.annotation.IExcel;
import java.util.Date;
import java.util.List;
/**
* @ClassName: DetailModel
* @Description: TODO
* @Date: 2023/6/2 14:31
* @author: ph9527
* @version: 1.0
*/
public class DetailModel {
private Long id;
private Long orderId;
@IExcel(name = "商品名")
private String goodName;
@IExcel(name = "商品碼", isNotBlank = true, sortNo = 2)
private String goodCode;
@IExcel(name = "購買數(shù)量", sortNo = 1)
private Integer buyNum;
@IExcel(isSon = true)
private List<CodeModel> codes;
@Override
public String toString() {
return "DetailModel{" +
"id=" + id +
", orderId=" + orderId +
", goodName='" + goodName + '\'' +
", goodCode='" + goodCode + '\'' +
", buyNum=" + buyNum +
", codes=" + codes +
'}';
}
public DetailModel() {
}
public DetailModel(Long id, Long orderId, String goodName, String goodCode, Integer buyNum, List<CodeModel> codes) {
this.id = id;
this.orderId = orderId;
this.goodName = goodName;
this.goodCode = goodCode;
this.buyNum = buyNum;
this.codes = codes;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getOrderId() {
return orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
public String getGoodName() {
return goodName;
}
public void setGoodName(String goodName) {
this.goodName = goodName;
}
public String getGoodCode() {
return goodCode;
}
public void setGoodCode(String goodCode) {
this.goodCode = goodCode;
}
public Integer getBuyNum() {
return buyNum;
}
public void setBuyNum(Integer buyNum) {
this.buyNum = buyNum;
}
public List<CodeModel> getCodes() {
return codes;
}
public void setCodes(List<CodeModel> codes) {
this.codes = codes;
}
}
- CodeModel ：
package com.ph.excel.vo;
import com.ph.excel.annotation.IExcel;
/**
* @ClassName: CodeModel
* @Description: TODO
* @Date: 2023/6/2 14:31
* @author: ph9527
* @version: 1.0
*/
public class CodeModel {
private Long id;
private Long detailId;
@IExcel(name = "追溯碼",isNotBlank = true)
private String code;
@Override
public String toString() {
return "CodeModel{" +
"id=" + id +
", detailId=" + detailId +
", code='" + code + '\'' +
'}';
}
public CodeModel() {
}
public CodeModel(Long id, Long detailId, String code) {
this.id = id;
this.detailId = detailId;
this.code = code;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getDetailId() {
return detailId;
}
public void setDetailId(Long detailId) {
this.detailId = detailId;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
b ：測試讀取excel
讀取用到的excel為：
c ：測試讀取的代碼
/**
* 讀取excel測試
*
* @return
*/
@GetMapping("/read")
public JSONObject readExcel() throws FileNotFoundException {
JSONObject rs = new JSONObject();
File file = new File("D:\\PH\\Desktop\\test.xlsx");
FileInputStream inputStream = new FileInputStream(file);
//讀取數(shù)據(jù)
List<OrderModel> orders = IExcelUtils.readExcel(inputStream, file.getName(), OrderModel.class);
//獲取因必填項沒填寫，未讀取的數(shù)據(jù)信息
List<IExcelUtils.BlankRowInfo> errRowInfos = IExcelUtils.getErrRowInfo();
rs.put("讀取的數(shù)據(jù)orders", orders);
rs.put("錯誤的數(shù)據(jù)errRowInfo", errRowInfos);
return rs;
}
d ：讀取excel的結(jié)果：
e：測試導出excel
我導出測試的數(shù)據(jù)直接讀取的exel，下面是讀取的excel圖
f : 導出測試代碼：
/**
* 導出測試
* @param response
* @throws Exception
*/
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception {
//創(chuàng)建測試數(shù)據(jù)
List<OrderModel> data = createExportData();
//導出excel
IExcelUtils.exportExcel("訂單", "訂單信息", response, data, OrderModel.class);
}
g:導出結(jié)果：
因為對訂單和明細排序了，所以"購買人"在excel第一列，"訂單時間"在excel第二列
總結(jié)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了Java四種動態(tài)代理實現(xiàn)方式，對于開始學習java動態(tài)代理或者要復習java動態(tài)代理的朋友來講很有參考價值，有感興趣的朋友可以參考一下2021-04-04
HttpClient實現(xiàn)調(diào)用外部項目接口工具類的示例
下面小編就為大家?guī)硪黄狧ttpClient實現(xiàn)調(diào)用外部項目接口工具類的示例。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-10-10
java對象類型轉(zhuǎn)換和多態(tài)性(實例講解)
下面小編就為大家?guī)硪黄猨ava對象類型轉(zhuǎn)換和多態(tài)性(實例講解)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-10-10
MinIO是一個基于Apache?License?v2.0開源協(xié)議的對象存儲服務,下面這篇文章主要給大家介紹了關(guān)于minio安裝部署及使用的詳細過程,文中通過實例代碼以及圖文介紹的非常詳細,需要的朋友可以參考下2022-09-09
這篇文章主要介紹了Java springboot 整合 Nacos的實例，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-04-04
spring中FactoryBean中的getObject()方法實例解析
這篇文章主要介紹了spring中FactoryBean中的getObject()方法實例解析，分享了相關(guān)代碼示例，小編覺得還是挺不錯的，具有一定借鑒價值，需要的朋友可以參考下2018-02-02
java（jdk）環(huán)境變量配置（XP、win7、win8）圖文教程詳解
對于初學java的同學來說，第一件事不是寫hello world，而是搭建好java開發(fā)環(huán)境，下載jdk，安裝，配置環(huán)境變量。這些操作在xp、win7、win8不同的操作系統(tǒng)里面配置不太一樣，下面通過本文給大家介紹如何在上面不同操作系統(tǒng)下配置2017-03-03
jdk8使用stream實現(xiàn)兩個list集合合并成一個(對象屬性的合并)
本文主要介紹了jdk8使用stream實現(xiàn)兩個list集合合并成一個(對象屬性的合并)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-01-01
這篇文章主要給大家介紹了關(guān)于Java中String類的一些常見方法,文中包括了Java中String類的基本概念、構(gòu)造方式、常用方法以及StringBuilder和StringBuffer的使用,涵蓋了字符串操作的各個方面,包括查找、轉(zhuǎn)換、比較、替換、拆分、截取等,需要的朋友可以參考下2024-11-11
關(guān)于@ComponentScan注解的用法及作用說明
這篇文章主要介紹了關(guān)于@ComponentScan注解的用法及作用說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09