Java实现excel大数据量导入

本文实例为大家分享了Java实现excel大数据量导入的具体代码，供大家参考，具体内容如下

情景分析：

通常我们通过poi读取excel文件时，若在用户模式下，由于数据量较大、Sheet较多，很容易出现内存溢出的情况

用户模式读取excel的典型代码如下：

FileInputStream file = new FileInputStream("c:\\test.xlsx");
Workbook wb=new XSSFWorkbook(file);

而03版（xls）excel文件每个sheet最大长度为 65536行，07版（xlsx）excel文件每个sheet最大长度为 1048576行，因此我们主要解决07版（xlsx）excel大数据量导入出现内存溢出的问题

由于读取cvs文件效率高且占用内存少，所以我们采用xlsx转换为cvs的方式来解决

读取xlsx格式数据：

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();
 }
 }
 }

}

说明：

代码中的加载excel的核心方法：

OPCPackage pkg = OPCPackage.open(path);//文件路径
OPCPackage pkg = OPCPackage.openOrCreate(file);//文件
OPCPackage pkg = OPCPackage.open(InputStream);//文件流

文件流方式对内存依赖极大，所以实际应用时，如果只能获取文件流的话，可以先将文件通过流拷贝到本地再解析代码中的

若上传文件大小超过限制，可在配置文件中设置，SpringBoot2项目application.properties中设置：

spring.servlet.multipart.maxFileSize=50MB
spring.servlet.multipart.maxRequestSize=100MB

实际业务数据入库的话也可采用异步任务@Async的方式来提高入库效率：

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

读取xls格式数据：

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;
  }
 }
}

读取xlsx格式数据（自动获取表头长度方式）：

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);
 }
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。