文章詳情頁

使用Springboot+poi上傳并處理百萬級數據EXCEL

瀏覽：156日期：2022-06-16 14:23:09

1 Excel上傳

針對Excel的上傳，采用的是比較常規的方法，其實和文件上傳是相同的。具體源碼如下：

@PostMapping(value = '', consumes = 'multipart/*', headers = 'content-type=multipart/form-data') public Map<String, Object> addBlacklist( @RequestParam('file') MultipartFile multipartFile, HttpServletRequest request ) { //判斷上傳內容是否符合要求 String fileName = multipartFile.getOriginalFilename(); if (!fileName.matches('^.+.(?i)(xls)$') && !fileName.matches('^.+.(?i)(xlsx)$')) { return returnError(0,'上傳的文件格式不正確'); } String file = saveFile(multipartFile, request); int result = 0; try { result = blacklistServcice.addBlackLists(file); } catch (Exception e) { e.printStackTrace(); } return returnData(result); } private String saveFile(MultipartFile multipartFile, HttpServletRequest request) { String path; String fileName = multipartFile.getOriginalFilename(); // 判斷文件類型 String realPath = request.getSession().getServletContext().getRealPath('/'); String trueFileName = fileName; // 設置存放Excel文件的路徑 path = realPath + trueFileName; File file = new File(path); if (file.exists() && file.isFile()) { file.delete(); } try { multipartFile.transferTo(new File(path)); } catch (IOException e) { e.printStackTrace(); } return path; }

上面的源碼我們可以看見有一個saveFile方法，這個方法是將文件存在服務器本地，這樣方便后續文件內容的讀取，用不著一次讀取所有的內容從而導致消耗大量的內存。當然這里大家如果有更好的方法希望能留言告知哈。

2 Excel處理工具源碼

import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.model.SharedStringsTable;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.xml.sax.InputSource;import org.xml.sax.SAXException;import org.xml.sax.XMLReader;import org.xml.sax.helpers.DefaultHandler;import org.xml.sax.helpers.XMLReaderFactory; import java.io.InputStream;import java.sql.SQLException;import java.util.*; /** * XSSF and SAX (Event API) */public abstract class XxlsAbstract extends DefaultHandler { private SharedStringsTable sst; private String lastContents; private int sheetIndex = -1; private List<String> rowlist = new ArrayList<>(); public List<Map<String, Object>> dataMap = new LinkedList<>(); //即將進行批量插入的數據 public int willSaveAmount; //將要插入的數據量 public int totalSavedAmount; //總共插入了多少數據 private int curRow = 0; //當前行 private int curCol = 0; //當前列索引 private int preCol = 0; //上一列列索引 private int titleRow = 0; //標題行，一般情況下為0 public int rowsize = 0; //列數 //excel記錄行操作方法，以sheet索引，行索引和行元素列表為參數，對sheet的一行元素進行操作，元素為String類型 public abstract void optRows(int sheetIndex, int curRow, List<String> rowlist) throws SQLException; //只遍歷一個sheet，其中sheetId為要遍歷的sheet索引，從1開始，1-3 /** * @param filename * @param sheetId sheetId為要遍歷的sheet索引，從1開始，1-3 * @throws Exception */ public void processOneSheet(String filename, int sheetId) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // rId2 found by processing the Workbook // 根據 rId# 或 rSheet# 查找sheet InputStream sheet2 = r.getSheet('rId' + sheetId); sheetIndex++; InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close(); } public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader(); this.sst = sst; parser.setContentHandler(this); return parser; } public void endElement(String uri, String localName, String name) { // 根據SST的索引值的到單元格的真正要存儲的字符串 try { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)) .toString(); } catch (Exception e) { } // v => 單元格的值，如果單元格是字符串則v標簽的值為該字符串在SST中的索引 // 將單元格內容加入rowlist中，在這之前先去掉字符串前后的空白符 if (name.equals('v')) { String value = lastContents.trim(); value = value.equals('') ? ' ' : value; int cols = curCol - preCol; if (cols > 1) {for (int i = 0; i < cols - 1; i++) { rowlist.add(preCol, '');} } preCol = curCol; rowlist.add(curCol - 1, value); } else { //如果標簽名稱為 row ，這說明已到行尾，調用 optRows() 方法 if (name.equals('row')) {int tmpCols = rowlist.size();if (curRow > this.titleRow && tmpCols < this.rowsize) { for (int i = 0; i < this.rowsize - tmpCols; i++) { rowlist.add(rowlist.size(), ''); }}try { optRows(sheetIndex, curRow, rowlist);} catch (SQLException e) { e.printStackTrace();}if (curRow == this.titleRow) { this.rowsize = rowlist.size();}rowlist.clear();curRow++;curCol = 0;preCol = 0; } } }}

3 解析成功后的數據處理

首先我們將源碼展示出來，然后再具體說明

public int addBlackLists(String file) throws ExecutionException, InterruptedException { ArrayList<Future<Integer>> resultList = new ArrayList<>(); XxlsAbstract xxlsAbstract = new XxlsAbstract() { //針對數據的具體處理 @Override public void optRows(int sheetIndex, int curRow, List<String> rowlist) { /** * 判斷即將插入的數據是否已經到達8000，如果到達8000， * 進行數據插入 */if (this.willSaveAmount == 5000) { //插入數據 List<Map<String, Object>> list = new LinkedList<>(this.dataMap); Callable<Integer> callable = () -> { int count = blacklistMasterDao.addBlackLists(list); blacklistRecordMasterDao.addBlackListRecords(list); return count; }; this.willSaveAmount = 0; this.dataMap = new LinkedList<>(); Future<Integer> future = executor.submit(callable); resultList.add(future);} //匯總數據Map<String, Object> map = new HashMap<>();map.put('uid', rowlist.get(0));map.put('createTime', rowlist.get(1));map.put('regGame', rowlist.get(2)); map.put('banGame', rowlist.get(2));this.dataMap.add(map);this.willSaveAmount++;this.totalSavedAmount++; } }; try { xxlsAbstract.processOneSheet(file, 1); } catch (Exception e) { e.printStackTrace(); } //針對沒有存入的數據進行處理 if(xxlsAbstract.willSaveAmount != 0){ List<Map<String, Object>> list = new LinkedList<>(xxlsAbstract.dataMap); Callable<Integer> callable = () -> {int count = blacklistMasterDao.addBlackLists(list);blacklistRecordMasterDao.addBlackListRecords(list);return count; }; Future<Integer> future = executor.submit(callable); resultList.add(future); } executor.shutdown(); int total = 0; for (Future<Integer> future : resultList) { while (true) {if (future.isDone() && !future.isCancelled()) { int sum = future.get(); total += sum; break;} else { Thread.sleep(100);} } } return total; }

針對上面的源碼，我們可以發現，我們需要將讀取到的EXCEL數據插入到數據庫中，這里為了減小數據庫的IO和提高插入的效率，我們采用5000一批的批量插入（注意：如果數據量過大會導致組成的SQL語句無法執行）。

這里需要獲取到一個最終執行成功的插入結果，并且插入執行很慢。所有采用了Java多線程的Future模式，采用異步的方式最終來獲取J執行結果。

通過上面的實現，樓主測試得到最終一百萬條數據需要四分鐘左右的時間就可以搞定。如果大家有更好的方法，歡迎留言。

補充知識：Java API SXSSFWorkbook導出Excel大批量數據（百萬級）解決導出超時

之前使用簡單的HSSFWorkbook，導出的數據不能超過