JAVA 用POI对excel中xls文件读较大数据速度方法

参考自：https://www.52pojie.cn/forum.php?mod=viewthread&tid=664686&extra=page%3D7%26filter%3Dtypeid%26typeid%3D192
配置maven依赖

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.15</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.15</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml-schemas</artifactId>
		<version>3.17</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
	<dependency>
		<groupId>commons-lang</groupId>
		<artifactId>commons-lang</artifactId>
		<version>2.6</version>
	</dependency>

用usermodel模式读取

package redexcel.POI;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
/**
 * 可读可写的Xls/Xlsx
 * */
public class XlsReadWriteMethod {

  private String filePath;
  
   XlsReadWriteMethod(String filePath) {
       this.filePath = filePath;
   }
   public static void main(String[] args) {
       try {
           Long start = System.currentTimeMillis();
           String filePath = "D:\\1.xlsx";
           XlsReadWriteMethod xlsReadWriteMethod = new XlsReadWriteMethod(filePath);
           Iterator iter = xlsReadWriteMethod.RowIterator(0);
           Long end = System.currentTimeMillis();
           System.out.println("get data time = " + (end - start));
       } catch (Exception e) {
           e.printStackTrace();
       }
   }


   /**
     * 迭代FileBean的每一行
     * */
public Iterator<String[]> RowIterator(int sheetAt) throws Exception {
       Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
       Sheet sheet = workbook.getSheetAt(sheetAt); //获取工作表索引

       List<String[]> list = new LinkedList<String[]>();
       for (int i = 0; i < sheet.getLastRowNum(); i++) {
           Row row = sheet.getRow(i);
           if(null == row)  continue; // 这一行是空行：没东西

           int firstCellNum = row.getFirstCellNum();
           int lastCellNum = row.getLastCellNum();
           String[] oneRowValue = new String[lastCellNum - firstCellNum];
           int countNoneCol = 0;

           for (int j = firstCellNum; j < lastCellNum; j++) {
               Cell cell = row.getCell(j);
               int thisCol = j - firstCellNum;
               if(null == cell) {
                   oneRowValue[thisCol] = "";
                   continue;
               }
               switch (cell.getCellType()) // 在API中未被弃用
               {
                   // 由于cell.getcelltype（），getcode（）已弃用，不提供替换方法，因此只使用直接方法
                   case -1 : //无
                       oneRowValue[thisCol] = "";
                       break;
                   case 0 : // 数字日期
                       short dataFormat = cell.getCellStyle().getDataFormat();
          
                       if(HSSFDateUtil.isCellDateFormatted(cell) || HSSFDateUtil.isCellDateFormatted(cell)
                               || 31 == dataFormat || 58 == dataFormat || 32 == dataFormat || 176 == dataFormat) {
                           try {
                               oneRowValue[thisCol] = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd hh:MM:ss");
                           }catch ( Exception e) {
                               try {
                                   cell.setCellType(CellType.STRING);
                               }catch (Exception ex) {
                                   //出问题就不转
                               }
                               oneRowValue[thisCol] = String.valueOf(cell.getStringCellValue());
                           }
                       } else {
                           try {
                               cell.setCellType(CellType.STRING);
                           }catch (Exception ex) {
                               //出问题就不转
                           }
                           oneRowValue[thisCol] = String.valueOf(cell.getStringCellValue());
                       }
                       break;
                   case 1 : // 字符串
                       oneRowValue[thisCol] = cell.getStringCellValue();
                       break;
                   case 2 : // 公式
                       try {
                           oneRowValue[thisCol] = String.valueOf(cell.getNumericCellValue());
                       } catch (IllegalStateException e) {
                           try {
                               oneRowValue[thisCol] = String.valueOf(cell.getRichStringCellValue());
                           } catch (Exception ex){
                               oneRowValue[thisCol] = "";
                           }
                       }
                       break;
                   case 3 : // 空白
                       oneRowValue[thisCol] = "";
                       break;
                   case 4 : // 布尔值
                       oneRowValue[thisCol] = String.valueOf(cell.getBooleanCellValue());
                       break;
                   case 5 : // 错误
                       oneRowValue[thisCol] = String.valueOf(cell.getErrorCellValue());
                       break;
                   default :
                       oneRowValue[thisCol] = "";
                       break;
               } 
               if("".equals(oneRowValue[thisCol])) countNoneCol++;

           } 

           if(countNoneCol == (lastCellNum - firstCellNum)) continue;//这一行是空行，里面的东西都无效
           list.add(oneRowValue);
       } 

       return list.iterator();
   }

}