Java实现excel大数据量导入_Java教程

本文实例为大家分享了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);

									 }

									}