Excel表格数据导入
导入maven依赖
地址：https://mvnrepository.com/artifact/com.xuxueli/xxl-excel/1.1.1
<!-- https://mvnrepository.com/artifact/com.xuxueli/xxl-excel --> <dependency> <groupId>com.xuxueli</groupId> <artifactId>xxl-excel</artifactId> <version>1.1.1</version> </dependency>
接下来看看，表格的结构
先贴上，工具类
import com.xuxueli.poi.excel.annotation.ExcelSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.stereotype.Component; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; @Component public class MyExcelUtil { /** * 导出Excel文件到磁盘 */ public static void exportToFile(Class clazz, List<?> dataList, HttpServletResponse response){ // workbook XSSFWorkbook workbook = MyExcelExportUtil.exportWorkbook(dataList); OutputStream out = null; try { response.setContentType("APPLICATION/OCTET-STREAM"); // set headers for the response String headerKey = "Content-Disposition"; ExcelSheet excelSheet = (ExcelSheet)clazz.getAnnotation(ExcelSheet.class); String filename = excelSheet.name(); String fileNameWithExt = filename + ".xlsx"; String headerValue = null; headerValue = String.format("attachment; filename="%s"", URLEncoder.encode(fileNameWithExt, "utf-8")); response.setHeader(headerKey, headerValue); out = response.getOutputStream(); // workbook 2 FileOutputStream workbook.write(out); // flush } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { try { if (out!=null) { out.close(); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } } /** * 导入 */ public static List importFromFile(Class clazz, MultipartFile file){ InputStream inputStream =null; try{ inputStream = file.getInputStream(); List objects = MyExcelImportUtil.importExcel(clazz, inputStream); return objects; }catch (Exception e){ e.printStackTrace(); throw new RuntimeException(e); }finally { try { if (inputStream!=null) { inputStream.close(); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } } } }
import com.xuxueli.poi.excel.ExcelExportUtil; import com.xuxueli.poi.excel.annotation.ExcelField; import com.xuxueli.poi.excel.annotation.ExcelSheet; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.util.StringUtils; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Modifier; import java.util.ArrayList; import java.util.Date; import java.util.List; public class MyExcelExportUtil extends ExcelExportUtil { public static XSSFWorkbook exportWorkbook(List<?> dataList) { if (dataList != null && dataList.size() != 0) { Class sheetClass = dataList.get(0).getClass(); ExcelSheet excelSheet = (ExcelSheet) sheetClass.getAnnotation(ExcelSheet.class); String sheetName = dataList.get(0).getClass().getSimpleName(); HSSFColor.HSSFColorPredefined headColor = null; if (excelSheet != null) { if (excelSheet.name() != null && excelSheet.name().trim().length() > 0) { sheetName = excelSheet.name().trim(); } headColor = excelSheet.headColor(); } ArrayList fields = new ArrayList(); if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) { Field[] workbook = sheetClass.getDeclaredFields(); int sheet = workbook.length; for (int headStyle = 0; headStyle < sheet; ++headStyle) { Field headRow = workbook[headStyle]; ExcelField rowData = (ExcelField) headRow.getAnnotation(ExcelField.class); if (rowData == null) { continue; } String name = rowData.name().trim(); if (StringUtils.isEmpty(name)) { continue; } if (!Modifier.isStatic(headRow.getModifiers())) { fields.add(headRow); } } } if (fields != null && fields.size() != 0) { XSSFWorkbook var19 = new XSSFWorkbook(); Sheet var20 = var19.createSheet(sheetName); CellStyle var21 = null; if (headColor != null) { var21 = var19.createCellStyle(); var21.setFillForegroundColor(headColor.getIndex()); var21.setFillPattern(FillPatternType.SOLID_FOREGROUND); var21.setFillBackgroundColor(headColor.getIndex()); } Row var22 = var20.createRow(0); int dataIndex; for (dataIndex = 0; dataIndex < fields.size(); ++dataIndex) { Field rowIndex = (Field) fields.get(dataIndex); ExcelField rowData = (ExcelField) rowIndex.getAnnotation(ExcelField.class); if (rowData == null) { continue; } String name = rowData.name().trim(); if (StringUtils.isEmpty(name)) { continue; } String rowX = rowData != null && rowData.name() != null && rowData.name().trim().length() > 0 ? rowData.name() : rowIndex.getName(); Cell i = var22.createCell(dataIndex, 1); if (var21 != null) { i.setCellStyle(var21); } i.setCellValue(String.valueOf(rowX)); } for (dataIndex = 0; dataIndex < dataList.size(); ++dataIndex) { int var23 = dataIndex + 1; Object var24 = dataList.get(dataIndex); Row var25 = var20.createRow(var23); for (int var26 = 0; var26 < fields.size(); ++var26) { Field field = (Field) fields.get(var26); ExcelField rowData = (ExcelField) field.getAnnotation(ExcelField.class); if (rowData == null) { continue; } String name = rowData.name().trim(); if (StringUtils.isEmpty(name)) { continue; } try { field.setAccessible(true); Object e = field.get(var24); Cell cellX = var25.createCell(var26, 1); if (e == null) { cellX.setCellValue(""); } else { Class<?> aClass = e.getClass(); Date date = new Date(); if (aClass == date.getClass()) { cellX.setCellValue(DateUtil.formatTimestamp((Date) e)); } else if(aClass.isEnum()){ try { Method getDesc = aClass.getMethod("getDesc"); Object invoke = getDesc.invoke(e); cellX.setCellValue(String.valueOf(invoke)); } catch (Exception e1) { cellX.setCellValue(""); } } else { cellX.setCellValue(String.valueOf(e)); } } } catch (IllegalAccessException var18) { throw new RuntimeException(var18); } } } return var19; } else { throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty."); } } else { throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data can not be empty."); } } }
import com.xuxueli.poi.excel.ExcelImportUtil; import com.xuxueli.poi.excel.annotation.ExcelField; import com.xuxueli.poi.excel.annotation.ExcelSheet; import com.xuxueli.poi.excel.util.FieldReflectionUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.springframework.util.StringUtils; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Modifier; import java.util.ArrayList; import java.util.Iterator; import java.util.List; public class MyExcelImportUtil extends ExcelImportUtil { public static List<Object> importExcel(Class<?> sheetClass, Workbook workbook) { try { ExcelSheet excelSheet = (ExcelSheet) sheetClass.getAnnotation(ExcelSheet.class); String sheetName = excelSheet != null && excelSheet.name() != null && excelSheet.name().trim().length() > 0 ? excelSheet.name().trim() : sheetClass.getSimpleName(); List<Field> fields = new ArrayList(); int rowIndex; if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) { Field[] var5 = sheetClass.getDeclaredFields(); int var6 = var5.length; for (rowIndex = 0; rowIndex < var6; ++rowIndex) { Field field = var5[rowIndex]; if (!Modifier.isStatic(field.getModifiers())) { fields.add(field); } } } if (fields != null && fields.size() != 0) { Sheet sheet = workbook.getSheet(sheetName); Iterator<Row> sheetIterator = sheet.rowIterator(); rowIndex = 0; ArrayList dataList; ArrayList headList = new ArrayList(); for1: for (dataList = new ArrayList(); sheetIterator.hasNext(); ++rowIndex) { Row rowX = (Row) sheetIterator.next(); if (rowIndex == 0) { Iterator<Cell> cellIterator = rowX.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //cell.setCellType(CellType.STRING); String stringCellValue = cell.getStringCellValue(); if (!StringUtils.isEmpty(stringCellValue)) { headList.add(stringCellValue); } } } if (rowIndex > 0) { Object rowObj = sheetClass.newInstance(); for2: for (int i = 0; i < fields.size(); ++i) { Field field = (Field) fields.get(i); ExcelField excelField = field.getAnnotation(ExcelField.class); if (excelField == null) { continue; } String name = excelField.name().trim(); if (StringUtils.isEmpty(name)) { continue; } int index = headList.indexOf(name); if (index < 0) { continue; } Cell cell = rowX.getCell(index); if (cell != null) { try { cell.setCellType(CellType.STRING); String fieldValueStr = cell.getStringCellValue(); if (i == 0 && StringUtils.isEmpty(fieldValueStr)) { break for1; }//当前行第一列为空跳出循环for1 Object fieldValue = FieldReflectionUtil.parseValue(field, fieldValueStr); field.setAccessible(true); field.set(rowObj, fieldValue); } catch (Exception ex) { field.setAccessible(true); field.set(rowObj, null); } } else { if (i == 0) { break for1; }//当前行第一列为空跳出循环for1 } } dataList.add(rowObj); } } return dataList; } else { throw new RuntimeException(">>>>>>>>>>> xxl-excel error, data field can not be empty."); } } catch (Exception var15) { throw new RuntimeException(var15); } } public static List<Object> importExcel(Class<?> sheetClass, InputStream inputStream) { try { Workbook workbook = WorkbookFactory.create(inputStream); List<Object> dataList = importExcel(sheetClass, workbook); return dataList; } catch (IOException var4) { throw new RuntimeException(var4); } catch (InvalidFormatException var5) { throw new RuntimeException(var5); } } }
定义实体类接收表格数据，注意列名要对应
@Data @ExcelSheet(name = "Sheet1") public class CompetitionUserEntity implements Serializable { @ExcelField(name = "电话") private String loginId; @ExcelField(name = "姓名") private String academicName; @ExcelField(name = "年级") private String grade; @ExcelField(name = "学校") private String school; @ExcelField(name = "省份") private String belongProvince; @ExcelField(name = "城市") private String belongCity; @ExcelField(name = "区县") private String district; }
调用：
Postman 调用：
service代码：
返回的list 自己断点看看数据，就知道是否成功
public Result importData(MultipartFile file) { List<CompetitionUserEntity> list = MyExcelUtil.importFromFile(CompetitionUserEntity.class, file);return null； }