POI解析多excel多sheet文件（单文件百万级以下）生成指定文件入Hive
临下班前有个需求，有个同事有一份excel数据需要导入到hive中，到手后发现需要导入的excel文件有5个，且每个excel有60个sheet，每个sheet文件是顶行的，由于文件是xls格式的，单excel文件数据量大概在390万左右，且sheet表有的有标题，有的是空行，且有的sheet要解析有的不要。
直接用poi解析xls格式形式进行解析，结果在new HSSFWorkbook(inputStream)这一步对输入文件流进行装载的时候发生内存问题（java.lang.OutOfMemoryError:Javaheapspace (堆内存溢出) java.lang.OutOfMemoryError:GCoverheadlimitexceeded (当垃圾回收器释放空间占用较多时间时抛出)）无法进行下部解析，尝试转为xlsx格式，同样发生类似的问题。
在网上查看大数据量解析excel博文，发现excel2007以上版有OPCPackage包能进行解析，理是根据行号范围批量将内容加入到内存中非一次性加入，这样就解决了内存不足的问题。但是，尝试了几篇网上的例子均不能原运行成功，因为那哥们这件事挺急需要第二天给智博会演示相关数据，也就没在这种方法上继续进行尝试，转而寻求更快捷方式，将单个文件的sheet数降为10个(数据量在六十几万)，这样再搭配多线程就完美导出文件了，再上传到hdfs上验证结束。
所需依赖包:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
解析文件：
package com.ali.scheduler.util;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static void main(String[] args) throws IOException {
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(3);
final List<File> files = getFileList("D:/阿里-拆分表/");
Date date = new Date();
System.out.println("startdate-->"+sdf.format(date));
long startTime = date.getTime();
for (int i = 0; i < files.size(); i++) {
final int index = i;
fixedThreadPool.execute(new Runnable() {
public void run() {
try {
parseExcel(files.get(index).getAbsolutePath());
} catch (Exception e) {
System.err.println("["+files.get(index)+"]文件处理异常!\n"+e.getMessage());
}
}
});
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
fixedThreadPool.shutdown();
while (true) {//等待所有任务都执行结束
if (fixedThreadPool.isTerminated()) {//所有的子线程都结束了
System.out.println("共耗时:"+(System.currentTimeMillis()-startTime)/1000.0+"s");
break;
}
}
// System.out.println(getFileList("D:/阿里/"));
}
public static void parseExcel(String filePath) throws Exception{
// String filePath = "D:/阿里/test.xls";
boolean isExcel2003 = filePath.toLowerCase().endsWith("xls")?true:false;
int sheetNum = 0;//工作区间
List<Object[]> datas = new ArrayList<Object[]>();//用来存数据
Date date = new Date();
System.out.println(filePath+"startdate-->"+sdf.format(date));
String fName=new File(filePath).getName();
fName = fName.substring(0,fName.lastIndexOf("."));
if(isExcel2003){
datas = readXLS(filePath, sheetNum,date,fName);
}else{
datas = readXLSX(filePath, sheetNum,date,fName);
}
// System.out.println(datas);
try {
exportFile(datas,new File("D:/pinganfile/result/"+fName));
} catch (Exception e) {
e.printStackTrace();
}
}
public static List<File> getFileList(String strPath) {
File dir = new File(strPath);
File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
List<File> filelist = new ArrayList<>();
if (files != null) {
for (int i = 0; i < files.length; i++) {
// String fileName = files[i].getName();
if (files[i].isDirectory()) { // 判断是文件还是文件夹
getFileList(files[i].getAbsolutePath()); // 获取文件绝对路径
} else{
// String strFileName = files[i].getAbsolutePath();
filelist.add(files[i]);
}
}
}
return filelist;
}
private static int exportFile(List<Object[]> datas, File file) throws Exception {
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "utf-8"));// 附加
// 添加数据
int index = 0;
StringBuffer sb = new StringBuffer();
for (int i = 0; i < datas.size(); i++) {
Object[] data =datas.get(i);
for(int j=0;j<data.length;j++){
sb.append(data[j]+Constant.COLUMN_DELIMITER);//\177
}
bw.write(sb.toString());
sb.setLength(0);
bw.newLine();
if (index % 50 == 0) {
bw.flush();
}
}
bw.close();
return index;
}
private static List<Object[]> readXLS(String filePath, int sheetNum,Date date,String fName) throws IOException {
FileInputStream inputStream = new FileInputStream(filePath);
System.out.println(fName+"输入流封装用时:"+((System.currentTimeMillis()-date.getTime())/1000.0)+"s");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
System.out.println(fName+"输入流装载WorkBook用时:"+((System.currentTimeMillis()-date.getTime())/1000.0)+"s");
List<Object[]> datas = new ArrayList<Object[]>();//用来存数据
/*** step1: 获取Excel的工作区间总数*/
int sheetNo = workbook.getNumberOfSheets();//取得工作区间的个数
System.out.println(fName+"共有sheet数:"+sheetNo);
for (int i = 0; i < sheetNo; i++) {
// if (i != sheetNum) {//判断是否为需要取得工作区间
// continue;
// }
/*** step2：取得所需工作区间(下标从0开始) */
HSSFSheet sheet = workbook.getSheetAt(i);
if (sheet == null || sheet.getSheetName().toUpperCase().equals("SQL")) {
return datas;
}
/*** step3：getPhysicalNumberOfRows获取总共有多少行数据因为中间空行的话，则读取出来的数据不准确 */
// int hasRowNum = sheet.getPhysicalNumberOfRows();
/** 获取的是最后一行的编号（编号从0开始）。*/
int hasRowNum = sheet.getLastRowNum()+1;
if(hasRowNum == 0){//sheet中所有行都没有内容
System.out.println("["+fName+"]"+sheet.getSheetName()+"共有"+(hasRowNum)+"条数据需要处理");
return datas;
}else{
System.out.println("["+fName+"]"+sheet.getSheetName()+"共有"+(hasRowNum-1)+"条数据需要处理");
}
//已经处理了的行数
int procssedNum = 0;
//默认从第二行读取(第一行表头或空行不读)
int jj=1;
//指定文件名从第三行读取
if(fName.endsWith("_1") && i==0){
jj=2;
}
for (int j = jj;j<hasRowNum ; j++) {
/** step4: 获取每一行 */
HSSFRow row = sheet.getRow(j);
/** step5 : 去除空行 */
if (row != null) {
/** step6: 获取每一行的长度 */
int length = row.getLastCellNum();
if (length > 0) {
Object[] data = new Object[length];//定义一个集合，装每一行的数值
for (int m = 0; m < length; m++) {
/** step7: 获取每一行的每一列的值 */
if(row.getCell(m).getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
data[m] = Double.valueOf(row.getCell(m).getNumericCellValue()).intValue();
}else{
data[m] = row.getCell(m);
}
}
/** step8: 存数据 */
datas.add(data);
}
procssedNum++;
if(procssedNum%5000==0){
System.out.println("["+fName+"]"+sheet.getSheetName()+"已处理 "+procssedNum+" 条数据!");
}
}
}
}
System.out.println("读取"+fName+"WorkBook内容用时:"+((System.currentTimeMillis()-date.getTime())/1000.0)+"s");
/** step9: 关闭输入流 */
inputStream.close();
/** step10: 返回数据 */
return datas;
}
private static List<Object[]> readXLSX(String filePath, int sheetNum,Date date,String fName) throws IOException {
FileInputStream inputStream = new FileInputStream(new File(filePath));
System.out.println(fName+"输入流封装用时:"+((System.currentTimeMillis()-date.getTime())/1000.0)+"s");
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
System.out.println(fName+"输入流装载WorkBook用时:"+((System.currentTimeMillis()-date.getTime())/1000.0)+"s");
List<Object[]> datas = new ArrayList<Object[]>();//定义一个list用来存数据
/*** step1: 获取Excel的工作区间的数量*/
int sheetNo = workbook.getNumberOfSheets();
for(int i=0;i<sheetNo;i++){
// if(i != sheetNum){
// continue;
// }
/** step2: 获取某一工作区间 */
XSSFSheet sheet = workbook.getSheetAt(i);
if(sheet == null || sheet.getSheetName().toUpperCase().equals("SQL")){
return datas;
}
/*** step3：getPhysicalNumberOfRows获取总共有多少行数据因为中间空行的话，则读取出来的数据不准确 */
// int hasRowNum = sheet.getPhysicalNumberOfRows();
/** 获取的是最后一行的编号（编号从0开始）。*/
int hasRowNum = sheet.getLastRowNum()+1;
if(hasRowNum == 0){//sheet中所有行都没有内容
System.out.println("["+fName+"]"+sheet.getSheetName()+"共有"+(hasRowNum)+"条数据需要处理");
return datas;
}else{
System.out.println("["+fName+"]"+sheet.getSheetName()+"共有"+(hasRowNum-1)+"条数据需要处理");
}
//已经处理了的行数
int procssedNum = 0;
//默认从第二行读取(第一行表头或空行不读)
int jj=1;
//指定文件名从第三行读取
if(fName.endsWith("_1") && i==0){
jj=2;
}
/** step4: 取每一行的数据 */
for(int j=jj;j<hasRowNum;j++){
XSSFRow row = sheet.getRow(j);
/** step5: 去空行 */
if(row == null){
continue;
}
/** step6: 取每一行的长度 */
int length = row.getLastCellNum();
Object[] data = new Object[length];//定义一个数组用来存数据
/** step7: 取每一列的数据 */
for(int k=0; k<length; k++){
XSSFCell cell = row.getCell(k);
if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
data[k] = Double.valueOf(cell.getNumericCellValue()).intValue();
}else{
data[k] = cell;
}
}
/** step8: 存数据 */
datas.add(data);
procssedNum++;
if(procssedNum%5000==0){
System.out.println("["+fName+"]"+sheet.getSheetName()+"已处理 "+procssedNum+" 条数据!");
}
}
/** step9: 关闭输入流 */
inputStream.close();
}
System.out.println("["+fName+"]"+"共耗时:"+(System.currentTimeMillis()-date.getTime())/1000.0+"s");
/** step10: 返回数据 */
return datas;
}
}
---------------------
作者：csdn-延
来源：CSDN
原文：https://blog.csdn.net/m0_37125796/article/details/81111059
