SpringBoot整合POI实现Excel导入(百万级数据处理方式)
目录
- 1、pom
- 1、基本excel导入
- 2、大量数据excel导入
1、pom
引入pom包：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>4.0.0</version> </dependency>1、基本excel导入
导入模板样式如下：
解析器封装：
为了做到统一的excel解析处理，进行通用的解析器封装，ExcelHandler，放了满足一边解析数据一边存库的需要，通过Consumer接口实现回调操作，保证解析到一定数据量后就可以进行后续操作。
代码如下：
import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;import java.lang.reflect.Constructor;import java.util.ArrayList;import java.util.List;import java.util.function.Consumer;/** * @Author: LiHuaZhi * @Date: 2022/1/16 14:58 * @Description: **/public class ExcelHandler<T> { /** * 批量处理梳理 */ public Integer batchNum; /** * 开始解析的行号，第一行时，startRow为0 */ public Integer startRow; /** * 封装的entity对象 */ public Class<?> entity; public Constructor<?> constructor; /** * 传入解析数据的service对象 */ public Consumer<List<T>> uploadService; /** * 接收解析对象值 */ public List<T> list = new ArrayList<>(); public ExcelHandler(Consumer<List<T>> uploadService, Class<?> entity, Integer startRow, Integer batchNum) { this.uploadService = uploadService; this.entity = entity; this.startRow = startRow; this.batchNum = batchNum == null ? 1000 : batchNum; } /** * @param inputStream 数据流 * @throws Exception */ public void handlerData(InputStream inputStream) throws Exception { // 创建构造器 this.constructor = entity.getDeclaredConstructor(new Class[]{List.class}); // 创建表格对象 XSSFWorkbook workbook = new XSSFWorkbook(inputStream); //有多少个sheet int sheets = workbook.getNumberOfSheets(); for (int i = 0; i < sheets; i++) { Sheet sheet = workbook.getSheetAt(i); //获取多少行 int rows = sheet.getPhysicalNumberOfRows(); //注意：第0为表头，第1行开始解析数据 for (int currentRow = 0; currentRow < rows; currentRow++) { parseCellData(sheet, currentRow); } } // 所有数据解析完成后 saveData(list); } /** * 一行解析完成后 */ private void endRow(List<Object> values, int currentRow) throws Exception { if (currentRow < startRow) { // 加载表头数据 System.out.println("解析头部数据：" + values); } else { System.out.println("解析表格数据：" + values); T data = (T) constructor.newInstance(values); list.add(data); } if (list.size() >= batchNum) { // 回调接口，处理数据 saveData(list); } } /** * 获取表格数据 * * @param sheet * @param currentRow * @return */ private void parseCellData(Sheet sheet, int currentRow) throws Exception { //获得第行号 Row row = sheet.getRow(currentRow); // 解析正文数据 List<Object> values = new ArrayList<>(); for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { Cell cell = row.getCell(cellNum); Object value = getCellValue(cell); values.add(value); } endRow(values, currentRow); } /** * 解析数据类型 * * @param cell * @return */ private Object getCellValue(Cell cell) { //1.获取到单元格的属性类型 CellType cellType = cell.getCellType(); //2.根据单元格数据类型获取数据 Object value = null; switch (cellType) { case STRING: value = cell.getStringCellValue(); break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { //日期格式 value = cell.getDateCellValue(); } else { //数字,poi解析的数值都是double类型 value = cell.getNumericCellValue(); } break; case FORMULA: //公式 value = cell.getCellFormula(); break; default: break; } return value; } /** * 回调保存数据 * * @param dataList */ private void saveData(List<T> dataList) { if (dataList.size() > 0) { uploadService.accept(dataList); dataList.clear(); } }}实体类：
为了方便数据的统一解析，在解析时不对实体具体字段进行赋值操作，而是在实体类中根据字段类型进行相应的转换并且赋值；
@Datapublic class User { private String name; private Integer agx; private String gender; private String address; private Double height; // 进行解析后的类型转换 public User(List<Object> values) { this.name = values.get(0) == null ? "" : values.get(0).toString(); this.agx = values.get(1) == null ? 0 : new Double(values.get(1).toString()).intValue(); this.gender = values.get(2) == null ? "" : values.get(0).toString(); this.address = values.get(3) == null ? "" : values.get(0).toString(); this.height = values.get(4) == null ? 0 : Double.parseDouble(values.get(4).toString()); }}Controller：
@PostMapping("upload") @ResponseBody public String upload(@RequestParam("file") MultipartFile file) throws Exception { try { if (file == null) { return "文件为空"; } InputStream inputStream = file.getInputStream(); // 注册事件处理器 Consumer<List<User>> consumer = uploadData -> importData2(uploadData); // 传入参数进行解析 new ExcelHandler(consumer, User.class, 1, null).handlerData(inputStream); } catch (IOException e) { e.printStackTrace(); return "解析失败"; } return "操作成功"; }// 接收Handler回传的数据 public void importData2(List<User> list) { System.out.println("解析数量：" + list.size()); }2、大量数据excel导入
针对于数据过多时，可以使用采用大量数据excel导入方式，比如上万级别的数据，实测中导入百万数据大约耗时20秒;
导入模板样式如下，并且数据量为百万：
解析器封装：
为了做到统一的excel解析处理，进行通用的解析器封装，SheetHandler以及XssfSheetHandler，放了满足一边解析数据一边存库的需要，通过Consumer接口实现回调操作，保证解析到一定数据量后就可以进行后续操作。
代码如下：
- SheetHandler
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.usermodel.XSSFComment;import java.lang.reflect.Constructor;import java.util.ArrayList;import java.util.List;import java.util.function.Consumer;/** * 自定义的事件处理器 * 处理每一行数据读取 * 实现接口 * * @author LiHuaZhi */public class SheetHandler<T> implements XSSFSheetXMLHandler.SheetContentsHandler { /** * 批量处理梳理 */ public Integer batchNum; /** * 开始解析的行号，第一行时，startRow为0 */ public Integer startRow; /** * 封装的entity对象 */ public Class<?> entity; /** * 当前解析的行号 */ private Integer currentRow; /** * 传入解析数据的service对象 */ public Consumer<List<T>> uploadService; /** * 临时解析对象的构造器 */ Constructor<?> constructor; /** * 接收解析对象值 */ public List<T> list = new ArrayList<>(); /** * 解析头部数据 */ public List<String> headList = new ArrayList<>(); /** * 解析单元格 */ public List<String> valueList = new ArrayList<>(); /** * 解析的列号,默认0为第一列 */ private Integer cellNum = 0; public SheetHandler(Integer batchNum, Integer startRow, Consumer<List<T>> uploadService, Class<?> entity) throws Exception { constructor = entity.getDeclaredConstructor(new Class[]{List.class}); this.batchNum = batchNum == null ? 1000 : batchNum; this.startRow = startRow; this.uploadService = uploadService; this.entity = entity; } /** * 当开始解析某一行的时候触发 * i:行索引 */ @Override public void startRow(int row) { currentRow = row; } /** * 当结束解析某一行的时候触发 * i:行索引 */ @Override public void endRow(int row) { try { cellNum = 0; if (headList.size() > 0) { System.out.println("解析头部数据：" + headList); } if (valueList.size() > 0) { System.out.println("解析表格数据：" + valueList); T data = (T) constructor.newInstance(valueList); list.add(data); } if (list.size() >= batchNum) { // 回调接口，处理数据 saveData(list); } } catch (Exception e) { e.printStackTrace(); } finally { headList.clear(); valueList.clear(); } } /** * 对行中的每一个表格进行处理 * cellReference: 单元格名称 * value：数据 * xssfComment：批注 */ @Override public void cell(String cellReference, String value, XSSFComment xssfComment) { try { if (currentRow < startRow) { // 加载表头数据 headList.add(value); } else { // 获取表格数据 valueList.add(value); } cellNum++; } catch (Exception e) { e.printStackTrace(); } } /** * 解析完sheet，多个sheet会回调多次 */ @Override public void endSheet() { System.out.println("解析完成"); saveData(list); } private void saveData(List<T> dataList) { if (dataList.size() > 0) { uploadService.accept(dataList); dataList.clear(); } }}- XssfSheetHandler
import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.model.SharedStringsTable;import org.apache.poi.xssf.model.StylesTable;import org.xml.sax.InputSource;import org.xml.sax.XMLReader;import org.xml.sax.helpers.XMLReaderFactory;import java.io.InputStream;import java.util.function.Consumer;/** * @Author: LiHuaZhi * @Date: 2022/1/16 14:57 * @Description: **/public class XssfSheetHandler { /** * @param inputStream 数据流 * @param consumer 自定义回调 * @param entity 解析数据实体 * @param batchNum 批处理数量 * @param startRow excel解析正文行号 * @throws Exception */ public static void handlerData(InputStream inputStream, Consumer consumer, Class<?> entity, Integer batchNum, Integer startRow) throws Exception { //1.根据excel报表获取OPCPackage OPCPackage opcPackage = OPCPackage.open(inputStream); //2.创建XSSFReader XSSFReader reader = new XSSFReader(opcPackage); //3.获取SharedStringTable对象 SharedStringsTable table = reader.getSharedStringsTable(); //4.获取styleTable对象 StylesTable stylesTable = reader.getStylesTable(); //5.创建Sax的xmlReader对象 XMLReader xmlReader = XMLReaderFactory.createXMLReader(); XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable, table, new SheetHandler(batchNum, startRow, consumer, entity), false); xmlReader.setContentHandler(xmlHandler); //7.逐行读取 XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData(); while (sheetIterator.hasNext()) { //每一个sheet的流数据 InputStream stream = sheetIterator.next(); InputSource is = new InputSource(stream); xmlReader.parse(is); } }}实体类：
为了方便数据的统一解析，在解析时不对实体具体字段进行赋值操作，而是在实体类中根据字段类型进行相应的转换并且赋值；
@Datapublic class User { private String name; private Integer agx; private String gender; private String address; private Double height; // 进行解析后的类型转换 public User(List<Object> values) { this.name = values.get(0) == null ? "" : values.get(0).toString(); this.agx = values.get(1) == null ? 0 : new Double(values.get(1).toString()).intValue(); this.gender = values.get(2) == null ? "" : values.get(0).toString(); this.address = values.get(3) == null ? "" : values.get(0).toString(); this.height = values.get(4) == null ? 0 : Double.parseDouble(values.get(4).toString()); }}Controller：
/** * 数据导入 - 百万数据导入 - 大约20S，平均50000条/秒，当数据千条后使用会有更好的效果 * * @param file * @return */ @PostMapping("upload/bw") @ResponseBody public String uploadBaiWan(@RequestParam("file") MultipartFile file) throws Exception { InputStream inputStream = file.getInputStream(); // 注册事件处理器 Consumer<List<User>> consumer = uploadData -> importData(uploadData); XssfSheetHandler.handlerData(inputStream, consumer, User.class, null, 1); return "操作成功"; } // 接收Handler回传的数据 public void importData(List<User> list) { System.out.println("解析数量：" + list.size()); }
