Java实现excel大数据量导入

import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.PrintStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.xml.parsers.ParserConfigurationException; import javax.xml.parsers.SAXParser; import javax.xml.parsers.SAXParserFactory; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.openxml4j.opc.PackageAccess; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; public class XLSX2CSV { enum xssfDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, } /** * 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api */ class MyXSSFSheetHandler extends DefaultHandler { private StylesTable stylesTable; private ReadOnlySharedStringsTable sharedStringsTable; private final PrintStream output; private final int minColumnCount; private boolean vIsOpen; private xssfDataType nextDataType; private short formatIndex; private String formatString; private final DataFormatter formatter; private int thisColumn = -1; private int lastColumnNumber = -1; private StringBuffer value; private String[] record; private List<String[]> rows = new ArrayList<String[]>(); private boolean isCellNull = false; public MyXSSFSheetHandler(StylesTable styles,ReadOnlySharedStringsTable strings, int cols, PrintStream target) { this.stylesTable = styles; this.sharedStringsTable = strings; this.minColumnCount = cols; this.output = target; this.value = new StringBuffer(); this.nextDataType = xssfDataType.NUMBER; this.formatter = new DataFormatter(); record = new String[this.minColumnCount]; rows.clear();// 每次读取都清空行集合 } public void startElement(String uri, String localName, String name,Attributes attributes) throws SAXException { if ("inlineStr".equals(name) || "v".equals(name)) { vIsOpen = true; // Clear contents cache value.setLength(0); } // c => cell else if ("c".equals(name)) { // Get the cell reference String r = attributes.getValue("r"); int firstDigit = -1; for (int c = 0; c < r.length(); ++c) { if (Character.isDigit(r.charAt(c))) { firstDigit = c; break; } } thisColumn = nameToColumn(r.substring(0, firstDigit)); // Set up defaults. this.nextDataType = xssfDataType.NUMBER; this.formatIndex = -1; this.formatString = null; String cellType = attributes.getValue("t"); String cellStyleStr = attributes.getValue("s"); if ("b".equals(cellType)) nextDataType = xssfDataType.BOOL; else if ("e".equals(cellType)) nextDataType = xssfDataType.ERROR; else if ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINESTR; else if ("s".equals(cellType)) nextDataType = xssfDataType.SSTINDEX; else if ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA; else if (cellStyleStr != null) { // It's a number, but almost certainly one // with a special style or format int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); this.formatIndex = style.getDataFormat(); this.formatString = style.getDataFormatString(); if (this.formatString == null) this.formatString = BuiltinFormats .getBuiltinFormat(this.formatIndex); } } } public void endElement(String uri, String localName, String name)throws SAXException { String thisStr = null; // v => contents of a cell if ("v".equals(name)) { // Process the value contents as required. // Do now, as characters() may be called more than once switch (nextDataType) { case BOOL: char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break; case ERROR: thisStr = "\"ERROR:" + value.toString() + '"'; break; case FORMULA: // A formula could result in a string value, // so always add double-quote characters. thisStr = '"' + value.toString() + '"'; break; case INLINESTR: // TODO: have seen an example of this, so it's untested. XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); thisStr = '"' + rtsi.toString() + '"'; break; case SSTINDEX: String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)); thisStr = rtss.toString(); } catch (NumberFormatException ex) { output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); } break; case NUMBER: String n = value.toString(); // 判断是否是日期格式 if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) { Double d = Double.parseDouble(n); Date date=HSSFDateUtil.getJavaDate(d); thisStr=formateDateToString(date); } else if (this.formatString != null) thisStr = formatter.formatRawCellContents( Double.parseDouble(n), this.formatIndex, this.formatString); else thisStr = n; break; default: thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; break; } // Output after we've seen the string contents // Emit commas for any fields that were missing on this row if (lastColumnNumber == -1) { lastColumnNumber = 0; } //判断单元格的值是否为空 if (thisStr == null || "".equals(isCellNull)) { isCellNull = true;// 设置单元格是否为空值 } record[thisColumn] = thisStr; // Update column if (thisColumn > -1) lastColumnNumber = thisColumn; } else if ("row".equals(name)) { // Print out any missing commas if needed if (minColumns > 0) { // Columns are 0 based if (lastColumnNumber == -1) { lastColumnNumber = 0; } if (isCellNull == false && record[0] != null && record[1] != null)// 判断是否空行 { rows.add(record.clone()); isCellNull = false; for (int i = 0; i < record.length; i++) { record[i] = null; } } } lastColumnNumber = -1; } } public List<String[]> getRows() { return rows; } public void setRows(List<String[]> rows) { this.rows = rows; } /** * Captures characters only if a suitable element is open. Originally * was just "v"; extended for inlineStr also. */ public void characters(char[] ch, int start, int length)throws SAXException { if (vIsOpen) value.append(ch, start, length); } /** * Converts an Excel column name like "C" to a zero-based index. * @param name * @return Index corresponding to the specified name */ private int nameToColumn(String name) { int column = -1; for (int i = 0; i < name.length(); ++i) { int c = name.charAt(i); column = (column + 1) * 26 + c - 'A'; } return column; } private String formateDateToString(Date date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期 return sdf.format(date); } } private OPCPackage xlsxPackage; private int minColumns; private PrintStream output; /** * Creates a new XLSX -> CSV converter * * @param pkg * The XLSX package to process * @param output * The PrintStream to output the CSV to * @param minColumns * The minimum number of columns to output, or -1 for no minimum */ public XLSX2CSV(OPCPackage pkg, PrintStream output,int minColumns) { this.xlsxPackage = pkg; this.output = output; this.minColumns = minColumns; } /** * Parses and shows the content of one sheet using the specified styles and * shared-strings tables. * @param styles * @param strings * @param sheetInputStream */ public List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException { InputSource sheetSource = new InputSource(sheetInputStream); SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings,this.minColumns, this.output); sheetParser.setContentHandler(handler); sheetParser.parse(sheetSource); return handler.getRows(); } /** * 初始化这个处理程序 * @throws IOException * @throws OpenXML4JException * @throws ParserConfigurationException * @throws SAXException */ public List<String[]> process() throws IOException, OpenXML4JException,ParserConfigurationException, SAXException { ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage); XSSFReader xssfReader = new XSSFReader(this.xlsxPackage); List<String[]> list = null; StylesTable styles = xssfReader.getStylesTable(); XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); int index = 0; while (iter.hasNext()) { InputStream stream = iter.next(); index++; //默认取第一个工作薄 if(index == 1){ list = processSheet(styles, strings, stream); stream.close(); } } return list; } /** * 读取Excel * * @param path * 文件路径 * @param sheetName * sheet名称 * @param minColumns * 列总数 * @return * @throws SAXException * @throws ParserConfigurationException * @throws OpenXML4JException * @throws IOException */ private static List<String[]> readerExcel(String path,int minColumns) throws IOException, OpenXML4JException,ParserConfigurationException, SAXException { OPCPackage p = OPCPackage.open(path, PackageAccess.READ); XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; } /** * 读取Excel * * @param file * File * @param sheetName * sheet名称 * @param minColumns * 列总数 * @return * @throws SAXException * @throws ParserConfigurationException * @throws OpenXML4JException * @throws IOException */ private static List<String[]> readerExcelInputStream(File file,int minColumns) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { OPCPackage p = OPCPackage.openOrCreate(file); XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns); List<String[]> list = xlsx2csv.process(); p.close(); return list; } //获取表头及数据内容,可在业务层调用 public static Map<String,Object> getTableDate(InputStream in) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException{ File out = new File("C:\\date.xlsx");//默认在C盘生成date.xlsx文件 OutputStream output = null; try { output = new FileOutputStream(out); byte[] buf = new byte[1024]; int bytesRead; while ((bytesRead = in.read(buf)) > 0) { output.write(buf, 0, bytesRead); } } finally { in.close(); output.close(); } Map<String,Object> map = new HashMap<String,Object>(); List<String> headresult = new ArrayList<String>();//表头集合 List<List<String>> dataresult = new ArrayList<List<String>>();//表数据集合 List<String[]> list = readerExcelInputStream(out,30);//默认读取30列 for(int i=0;i<list.size();i++){ if(i == 0){ for(String cell : list.get(i)){ if(cell != null){ headresult.add(cell); } } }else{ List<String> dataList = new ArrayList<String>(); for(String cell : list.get(i)){ dataList.add(cell == null ? "" : cell); } dataresult.add(dataList); } } map.put("headresult", headresult); map.put("dataresult", dataresult); return map; } public static void main(String[] args) throws Exception { File file = new File("C:/Users/Administrator/Desktop/测试.xlsx"); System.out.println("开始读取..."); List<String[]> list = XLSX2CSV.readerExcelInputStream(file, 30);//默认读取30列 System.out.println("数据量："+list.size()); for(int i=0;i<list.size();i++){ if(i > 0){ for (String cell : list.get(i)) { System.out.print(cell + ","); } System.out.println(); } } } }

import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.scheduling.annotation.Async; import org.springframework.stereotype.Service; import cn.com.app.dao.JkconfigDao; @Service public class JkrzService { @Autowired private JkconfigDao jkconfigDao; @Async("myTaskAsyncPool") public void transJkrz(Map<String, Object> m) { jkconfigDao.insert(m); } }

import java.util.concurrent.Executor; import java.util.concurrent.ThreadPoolExecutor; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.scheduling.annotation.EnableAsync; import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor; @Configuration @EnableAsync public class AsyncTaskConfig { @Autowired private TaskThreadPoolConfig config; // ThredPoolTaskExcutor的处理流程 // 当池子大小小于corePoolSize，就新建线程，并处理请求 // 当池子大小等于corePoolSize，把请求放入workQueue中，池子里的空闲线程就去workQueue中取任务并处理 // 当workQueue放不下任务时，就新建线程入池，并处理请求，如果池子大小撑到了maximumPoolSize，就用RejectedExecutionHandler来做拒绝处理 // 当池子的线程数大于corePoolSize时，多余的线程会等待keepAliveTime长时间，如果无请求可处理就自行销毁 // 当threadNamePrefix设置为true，则核心线程也会超时关闭 @Bean public Executor myTaskAsyncPool() { ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor(); executor.setCorePoolSize(config.getCorePoolSize()); executor.setMaxPoolSize(config.getMaxPoolSize()); executor.setQueueCapacity(config.getQueueCapacity()); executor.setKeepAliveSeconds(config.getKeepAliveSeconds()); executor.setAllowCoreThreadTimeOut(true); executor.setThreadNamePrefix("MyExecutor-"); // rejection-policy：当pool已经达到max size的时候，如何处理新任务 // CALLER_RUNS：不在新线程中执行任务，而是由调用者所在的线程来执行 executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy()); executor.initialize(); return executor; } }

#核心线程数,当线程数小于核心线程数时,即使有线程空闲,线程池也会优先创建新线程,设置allowCoreThreadTimeout=true（默认false）时,核心线程会超时退出 spring.task.pool.corePoolSize=20 #最大线程数,当线程数大于等于corePoolSize,且任务队列已满时,线程池会创建新线程来处理任务 spring.task.pool.maxPoolSize=60 #线程空闲时间,当线程空闲时间达到keepAliveSeconds(秒)时,线程会退出,直到线程数量等于corePoolSize,如果allowCoreThreadTimeout=true,则会直到线程数量等于0 spring.task.pool.keepAliveSeconds=1 #任务队列容量,当核心线程数达到最大时,新任务会放在队列中排队等待执行 spring.task.pool.queueCapacity=400

import org.apache.poi.hssf.eventusermodel.*; import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.*; import org.apache.poi.hssf.usermodel.HSSFDataFormatter; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * @desc 用于解决.xls2003版本大数据量问题 **/ public class ExcelXlsReader implements HSSFListener { //存储所有数据 private List<List<String>> dataList = new ArrayList<List<String>>(); private int minColums = -1; private POIFSFileSystem fs; /** * 总行数 */ private int totalRows=0; /** * 上一行row的序号 */ private int lastRowNumber; /** * 上一单元格的序号 */ private int lastColumnNumber; /** * 是否输出formula，还是它对应的值 */ private boolean outputFormulaValues = true; /** * 用于转换formulas */ private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener; //excel2003工作簿 private HSSFWorkbook stubWorkbook; private SSTRecord sstRecord; private FormatTrackingHSSFListener formatListener; private final HSSFDataFormatter formatter = new HSSFDataFormatter(); /** * 文件的绝对路径 */ private String filePath = ""; //表索引 private int sheetIndex = 0; private BoundSheetRecord[] orderedBSRs; @SuppressWarnings("unchecked") private ArrayList boundSheetRecords = new ArrayList(); private int nextRow; private int nextColumn; private boolean outputNextStringRecord; //当前行 private int curRow = 0; //存储一行记录所有单元格的容器 private List<String> cellList = new ArrayList<String>(); /** * 判断整行是否为空行的标记 */ private boolean flag = false; @SuppressWarnings("unused") private String sheetName; /** * 遍历excel下所有的sheet * * @param fileName * @throws Exception */ public int process(String fileName) throws Exception { filePath = fileName; this.fs = new POIFSFileSystem(new FileInputStream(fileName)); MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this); formatListener = new FormatTrackingHSSFListener(listener); HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); if (outputFormulaValues) { request.addListenerForAllRecords(formatListener); } else { workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener); request.addListenerForAllRecords(workbookBuildingListener); } factory.processWorkbookEvents(request, fs); return totalRows; //返回该excel文件的总行数，不包括首列和空行 } public List<List<String>> process(InputStream in) throws Exception { //filePath = fileName; this.fs = new POIFSFileSystem(in); MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this); formatListener = new FormatTrackingHSSFListener(listener); HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); if (outputFormulaValues) { request.addListenerForAllRecords(formatListener); } else { workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener); request.addListenerForAllRecords(workbookBuildingListener); } factory.processWorkbookEvents(request, fs); return dataList; //返回该excel文件的总行数，不包括首列和空行 } /** * HSSFListener 监听方法，处理Record * 处理每个单元格 * @param record */ @SuppressWarnings("unchecked") public void processRecord(Record record) { int thisRow = -1; int thisColumn = -1; String thisStr = null; String value = null; switch (record.getSid()) { case BoundSheetRecord.sid: boundSheetRecords.add(record); break; case BOFRecord.sid: //开始处理每个sheet BOFRecord br = (BOFRecord) record; if (br.getType() == BOFRecord.TYPE_WORKSHEET) { //如果有需要，则建立子工作簿 if (workbookBuildingListener != null && stubWorkbook == null) { stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook(); } if (orderedBSRs == null) { orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords); } sheetName = orderedBSRs[sheetIndex].getSheetname(); sheetIndex++; } break; case SSTRecord.sid: sstRecord = (SSTRecord) record; break; case BlankRecord.sid: //单元格为空白 BlankRecord brec = (BlankRecord) record; thisRow = brec.getRow(); thisColumn = brec.getColumn(); thisStr = ""; cellList.add(thisColumn, thisStr); break; case BoolErrRecord.sid: //单元格为布尔类型 BoolErrRecord berec = (BoolErrRecord) record; thisRow = berec.getRow(); thisColumn = berec.getColumn(); thisStr = berec.getBooleanValue() + ""; cellList.add(thisColumn, thisStr); checkRowIsNull(thisStr); //如果里面某个单元格含有值，则标识该行不为空行 break; case FormulaRecord.sid://单元格为公式类型 FormulaRecord frec = (FormulaRecord) record; thisRow = frec.getRow(); thisColumn = frec.getColumn(); if (outputFormulaValues) { if (Double.isNaN(frec.getValue())) { outputNextStringRecord = true; nextRow = frec.getRow(); nextColumn = frec.getColumn(); } else { thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"'; } } else { thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"'; } cellList.add(thisColumn, thisStr); checkRowIsNull(thisStr); //如果里面某个单元格含有值，则标识该行不为空行 break; case StringRecord.sid: //单元格中公式的字符串 if (outputNextStringRecord) { StringRecord srec = (StringRecord) record; thisStr = srec.getString(); thisRow = nextRow; thisColumn = nextColumn; outputNextStringRecord = false; } break; case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; curRow = thisRow = lrec.getRow(); thisColumn = lrec.getColumn(); value = lrec.getValue().trim(); value = value.equals("") ? "" : value; cellList.add(thisColumn, value); checkRowIsNull(value); //如果里面某个单元格含有值，则标识该行不为空行 break; case LabelSSTRecord.sid: //单元格为字符串类型 LabelSSTRecord lsrec = (LabelSSTRecord) record; curRow = thisRow = lsrec.getRow(); thisColumn = lsrec.getColumn(); if (sstRecord == null) { cellList.add(thisColumn, ""); } else { value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim(); value = value.equals("") ? "" : value; cellList.add(thisColumn, value); checkRowIsNull(value); //如果里面某个单元格含有值，则标识该行不为空行 } break; case NumberRecord.sid: //单元格为数字类型 NumberRecord numrec = (NumberRecord) record; curRow = thisRow = numrec.getRow(); thisColumn = numrec.getColumn(); //第一种方式 //value = formatListener.formatNumberDateCell(numrec).trim();//这个被写死，采用的m/d/yy h:mm格式，不符合要求 //第二种方式，参照formatNumberDateCell里面的实现方法编写 Double valueDouble=((NumberRecord)numrec).getValue(); String formatString=formatListener.getFormatString(numrec); if (formatString.contains("m/d/yy")){ formatString="yyyy-MM-dd hh:mm:ss"; } int formatIndex=formatListener.getFormatIndex(numrec); value=formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim(); value = value.equals("") ? "" : value; //向容器加入列值 cellList.add(thisColumn, value); checkRowIsNull(value); //如果里面某个单元格含有值，则标识该行不为空行 break; default: break; } //遇到新行的操作 if (thisRow != -1 && thisRow != lastRowNumber) { lastColumnNumber = -1; } //空值的操作 if (record instanceof MissingCellDummyRecord) { MissingCellDummyRecord mc = (MissingCellDummyRecord) record; curRow = thisRow = mc.getRow(); thisColumn = mc.getColumn(); cellList.add(thisColumn, ""); } //更新行和列的值 if (thisRow > -1) lastRowNumber = thisRow; if (thisColumn > -1) lastColumnNumber = thisColumn; //行结束时的操作 if (record instanceof LastCellOfRowDummyRecord) { if (minColums > 0) { //列值重新置空 if (lastColumnNumber == -1) { lastColumnNumber = 0; } } lastColumnNumber = -1; if (flag&&curRow!=0) { //该行不为空行且该行不是第一行，发送（第一行为列名，不需要） //ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow + 1, cellList); //每行结束时，调用sendRows()方法 totalRows++; //添加到数据集合中 dataList.add(cellList); } //清空容器 cellList = new ArrayList<String>(); //cellList.clear(); flag=false; } } /** * 如果里面某个单元格含有值，则标识该行不为空行 * @param value */ public void checkRowIsNull(String value){ if (value != null && !"".equals(value)) { flag = true; } } }

import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; 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.util.ArrayList; import java.util.List; /** * @desc POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式 * 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题， * 该模式是POI官方推荐的读取大数据的模式， * 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出 * <p> * 用于解决.xlsx2007版本大数据量问题 **/ public class ExcelXlsxReader extends DefaultHandler { //存储所有数据 private List<List<String>> dataList = new ArrayList<List<String>>(); /** * 单元格中的数据可能的数据类型 */ enum CellDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL } /** * 共享字符串表 */ private SharedStringsTable sst; /** * 上一次的索引值 */ private String lastIndex; /** * 文件的绝对路径 */ private String filePath = ""; /** * 工作表索引 */ private int sheetIndex = 0; /** * sheet名 */ private String sheetName = ""; /** * 总行数 */ private int totalRows=0; /** * 一行内cell集合 */ private List<String> cellList = new ArrayList<String>(); /** * 判断整行是否为空行的标记 */ private boolean flag = false; /** * 当前行 */ private int curRow = 1; /** * 当前列 */ private int curCol = 0; /** * T元素标识 */ private boolean isTElement; /** * 判断上一单元格是否为文本空单元格 */ private boolean startElementFlag = true; private boolean endElementFlag = false; private boolean charactersFlag = false; /** * 异常信息，如果为空则表示没有异常 */ private String exceptionMessage; /** * 单元格数据类型，默认为字符串类型 */ private CellDataType nextDataType = CellDataType.SSTINDEX; private final DataFormatter formatter = new DataFormatter(); /** * 单元格日期格式的索引 */ private short formatIndex; /** * 日期格式字符串 */ private String formatString; //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等 private String prePreRef = "A", preRef = null, ref = null; //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格 private String maxRef = null; /** * 单元格 */ private StylesTable stylesTable; /** * 遍历工作簿中所有的电子表格 * 并缓存在mySheetList中 * * @param filename * @throws Exception */ public int process(String filename) throws Exception { filePath = filename; OPCPackage pkg = OPCPackage.open(filename); XSSFReader xssfReader = new XSSFReader(pkg); stylesTable = xssfReader.getStylesTable(); SharedStringsTable sst = xssfReader.getSharedStringsTable(); XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); this.sst = sst; parser.setContentHandler(this); XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); while (sheets.hasNext()) { //遍历sheet curRow = 1; //标记初始行为第一行 sheetIndex++; InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错 sheetName = sheets.getSheetName(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行 sheet.close(); } return totalRows; //返回该excel文件的总行数，不包括首列和空行 } public List<List<String>> process(InputStream in) throws Exception { //filePath = filename; OPCPackage pkg = OPCPackage.open(in); XSSFReader xssfReader = new XSSFReader(pkg); stylesTable = xssfReader.getStylesTable(); SharedStringsTable sst = xssfReader.getSharedStringsTable(); //XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); XMLReader parser = XMLReaderFactory.createXMLReader(); this.sst = sst; parser.setContentHandler(this); XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); while (sheets.hasNext()) { //遍历sheet curRow = 1; //标记初始行为第一行 sheetIndex++; InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错 sheetName = sheets.getSheetName(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行 sheet.close(); } return dataList; } /** * 第一个执行 * * @param uri * @param localName * @param name * @param attributes * @throws SAXException */ @Override public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { //c => 单元格 if ("c".equals(name)) { //前一个单元格的位置 if (preRef == null) { preRef = attributes.getValue("r"); } else { //中部文本空单元格标识 ‘endElementFlag' 判断前一次是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串跳过把空字符串的位置赋予preRef if (endElementFlag){ preRef = ref; } } //当前单元格的位置 ref = attributes.getValue("r"); //首部文本空单元格标识 ‘startElementFlag' 判断前一次，即首部是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串, 且已知当前格，即第二格带“B”标志，则ref赋予preRef if (!startElementFlag && !flag){ //上一个单元格为文本空单元格，执行下面的，使ref=preRef；flag为true表明该单元格之前有数据值，即该单元格不是首部空单元格，则跳过 // 这里只有上一个单元格为文本空单元格，且之前的几个单元格都没有值才会执行 preRef = ref; } //设定单元格类型 this.setNextDataType(attributes); endElementFlag = false; charactersFlag = false; startElementFlag = false; } //当元素为t时 if ("t".equals(name)) { isTElement = true; } else { isTElement = false; } //置空 lastIndex = ""; } /** * 第二个执行 * 得到单元格对应的索引值或是内容值 * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值 * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值 * @param ch * @param start * @param length * @throws SAXException */ @Override public void characters(char[] ch, int start, int length) throws SAXException { startElementFlag = true; charactersFlag = true; lastIndex += new String(ch, start, length); } /** * 第三个执行 * * @param uri * @param localName * @param name * @throws SAXException */ @Override public void endElement(String uri, String localName, String name) throws SAXException { //t元素也包含字符串 if (isTElement) { //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符 String value = lastIndex.trim(); cellList.add(curCol, value); endElementFlag = true; curCol++; isTElement = false; //如果里面某个单元格含有值，则标识该行不为空行 if (value != null && !"".equals(value)) { flag = true; } } else if ("v".equals(name)) { //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引 String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值 //补全单元格之间的空单元格 if (!ref.equals(preRef)) { int len = countNullCell(ref, preRef); for (int i = 0; i < len; i++) { cellList.add(curCol, ""); curCol++; } } else if (ref.equals(preRef) && !ref.startsWith("A")){ //ref等于preRef，且以B或者C...开头，表明首部为空格 int len = countNullCell(ref, "A"); for (int i = 0; i <= len; i++) { cellList.add(curCol, ""); curCol++; } } cellList.add(curCol, value); curCol++; endElementFlag = true; //如果里面某个单元格含有值，则标识该行不为空行 if (value != null && !"".equals(value)) { flag = true; } } else { //如果标签名称为row，这说明已到行尾，调用optRows()方法 if ("row".equals(name)) { //默认第一行为表头，以该行单元格数目为最大数目 if (curRow == 1) { maxRef = ref; } //补全一行尾部可能缺失的单元格 if (maxRef != null) { int len = -1; //前一单元格，true则不是文本空字符串，false则是文本空字符串 if (charactersFlag){ len = countNullCell(maxRef, ref); }else { len = countNullCell(maxRef, preRef); } for (int i = 0; i <= len; i++) { cellList.add(curCol, ""); curCol++; } } if (flag&&curRow!=1){ //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要） //ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList); totalRows++; //添加到数据集合中 dataList.add(cellList); } //清空容器 cellList = new ArrayList<String>(); //cellList.clear(); curRow++; curCol = 0; preRef = null; prePreRef = null; ref = null; flag=false; } } } /** * 处理数据类型 * * @param attributes */ public void setNextDataType(Attributes attributes) { nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字 formatIndex = -1; formatString = null; String cellType = attributes.getValue("t"); //单元格类型 String cellStyleStr = attributes.getValue("s"); // String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1 if ("b".equals(cellType)) { //处理布尔值 nextDataType = CellDataType.BOOL; } else if ("e".equals(cellType)) { //处理错误 nextDataType = CellDataType.ERROR; } else if ("inlineStr".equals(cellType)) { nextDataType = CellDataType.INLINESTR; } else if ("s".equals(cellType)) { //处理字符串 nextDataType = CellDataType.SSTINDEX; } else if ("str".equals(cellType)) { nextDataType = CellDataType.FORMULA; } if (cellStyleStr != null) { //处理日期 int styleIndex = Integer.parseInt(cellStyleStr); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); formatIndex = style.getDataFormat(); formatString = style.getDataFormatString(); if (formatString.contains("m/d/yyyy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d") ) { nextDataType = CellDataType.DATE; formatString = "yyyy-MM-dd hh:mm:ss"; } if (formatString == null) { nextDataType = CellDataType.NULL; formatString = BuiltinFormats.getBuiltinFormat(formatIndex); } } } /** * 对解析出来的数据进行类型处理 * @param value 单元格的值， * value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值， * SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值 * @param thisStr 一个空字符串 * @return */ @SuppressWarnings("deprecation") public String getDataValue(String value, String thisStr) { switch (nextDataType) { // 这几个的顺序不能随便交换，交换了很可能会导致数据错误 case BOOL: //布尔值 char first = value.charAt(0); thisStr = first == '0' ? "FALSE" : "TRUE"; break; case ERROR: //错误 thisStr = "\"ERROR:" + value.toString() + '"'; break; case FORMULA: //公式 thisStr = '"' + value.toString() + '"'; break; case INLINESTR: XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); thisStr = rtsi.toString(); rtsi = null; break; case SSTINDEX: //字符串 String sstIndex = value.toString(); try { int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值 thisStr = rtss.toString(); //System.out.println(thisStr); //有些字符串是文本格式的，但内容却是日期 rtss = null; } catch (NumberFormatException ex) { thisStr = value.toString(); } break; case NUMBER: //数字 if (formatString != null) { thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim(); } else { thisStr = value; } thisStr = thisStr.replace("_", "").trim(); break; case DATE: //日期 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString); // 对日期字符串作特殊处理，去掉T thisStr = thisStr.replace("T", " "); break; default: thisStr = " "; break; } return thisStr; } public int countNullCell(String ref, String preRef) { //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD String xfd = ref.replaceAll("\\d+", ""); String xfd_1 = preRef.replaceAll("\\d+", ""); xfd = fillChar(xfd, 3, '@', true); xfd_1 = fillChar(xfd_1, 3, '@', true); char[] letter = xfd.toCharArray(); char[] letter_1 = xfd_1.toCharArray(); int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]); return res - 1; } public String fillChar(String str, int len, char let, boolean isPre) { int len_1 = str.length(); if (len_1 < len) { if (isPre) { for (int i = 0; i < (len - len_1); i++) { str = let + str; } } else { for (int i = 0; i < (len - len_1); i++) { str = str + let; } } } return str; } /** * @return the exceptionMessage */ public String getExceptionMessage() { return exceptionMessage; } }

import java.io.InputStream; import java.util.List; public class ExcelReaderUtil { //excel2003扩展名 public static final String EXCEL03_EXTENSION = ".xls"; //excel2007扩展名 public static final String EXCEL07_EXTENSION = ".xlsx"; //读取xls格式 public static List<List<String>> readExcelXls(InputStream in) throws Exception { ExcelXlsReader excelXls=new ExcelXlsReader(); List<List<String>> dataList =excelXls.process(in); return dataList; } //读取xlsx格式 public static List<List<String>> readExcelXlsx(InputStream in) throws Exception { ExcelXlsxReader excelXls=new ExcelXlsxReader(); List<List<String>> dataList =excelXls.process(in); return dataList; } public static void readExcel(String fileName) throws Exception { int totalRows =0; if (fileName.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件 ExcelXlsReader excelXls=new ExcelXlsReader(); totalRows =excelXls.process(fileName); } else if (fileName.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件 ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader(); totalRows = excelXlsxReader.process(fileName); } else { throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。"); } System.out.println("发送的总行数：" + totalRows); } public static void main(String[] args) throws Exception { String path="C:\\test.xlsx"; ExcelReaderUtil.readExcel(path); } }

相关阅读