com.github.rrsunhome.excelsql.util.ExcelUtils Maven / Gradle / Ivy
package com.github.rrsunhome.excelsql.util;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* POI实现excel文件读写(导入/导出)操作工具类
*
* @author qijia.wang
*/
public class ExcelUtils {
/**
* @param is 输入流
* @param sheetIndex sheet index
* @param startRowIndex 开始行
* @return 返回数据
* @throws Exception 异常
*/
public static List> readExcel(InputStream is, int sheetIndex, int startRowIndex) throws Exception {
List> list = new ArrayList>();
Workbook wb = getWorkbook(is);
if (wb != null) {
Sheet sheet = getSheet(wb, sheetIndex);
list = getSheetData(wb, sheet, startRowIndex);
}
return list;
}
/**
* @param is 输入流
* @param sheetName sheet name
* @param startRowIndex 开始行
* @return 返回数据
* @throws Exception 异常
*/
public static List> readExcel(InputStream is, String sheetName, int startRowIndex) throws Exception {
List> list = new ArrayList>();
Workbook wb = getWorkbook(is);
if (wb != null) {
Sheet sheet = getSheet(wb, sheetName);
list = getSheetData(wb, sheet, startRowIndex);
}
return list;
}
/**
* 根据workbook获取该workbook的所有sheet
*
* @param wb Workbook
* @return 返回数据
*/
public static List getAllSheets(Workbook wb) {
int numOfSheets = wb.getNumberOfSheets();
List sheets = new ArrayList();
for (int i = 0; i < numOfSheets; i++) {
sheets.add(wb.getSheetAt(i));
}
return sheets;
}
/**
* 根据excel文件来获取workbook
*
* @param file 文件
* @return workbook
* @throws Exception 异常
*/
public static Workbook getWorkbook(File file) throws Exception {
return getWorkbook(new FileInputStream(file));
}
/**
* @param is 输入流
* @return workbook
* @throws Exception 异常
*/
public static Workbook getWorkbook(InputStream is) throws Exception {
Workbook wb = WorkbookFactory.create(is);
return wb;
}
/**
* 根据excel文件来获取workbook
*
* @param filePath 文件地址
* @return workbook
* @throws Exception 异常
*/
public static Workbook getWorkbook(String filePath) throws Exception {
File file = new File(filePath);
return getWorkbook(file);
}
/**
* 根据workbook和sheet的下标索引值来获取sheet
*
* @param wb Workbook
* @param sheetIndex 下标
* @return sheet
*/
public static Sheet getSheet(Workbook wb, int sheetIndex) {
return wb.getSheetAt(sheetIndex);
}
/**
* 根据workbook和sheet的名称来获取sheet
*
* @param wb Workbook
* @param sheetName sheetName
* @return sheet
*/
public static Sheet getSheet(Workbook wb, String sheetName) {
return wb.getSheet(sheetName);
}
/**
*
* @param wb Workbook
* @param sheet Sheet
* @param startRowIndex startRowIndex
* @return 嵌套数据
* @throws Exception 异常
*/
public static List> getSheetData(Workbook wb, Sheet sheet, int startRowIndex) throws Exception {
List> list = new ArrayList>();
/**
* getLastRowNum方法能够正确返回最后一行的位置；
* getPhysicalNumberOfRows方法能够正确返回物理的行数；
*/
// 获取总行数
int rowNum = sheet.getPhysicalNumberOfRows();
// int rowNum = sheet.getLastRowNum();
// 获取标题行
Row headerRow = sheet.getRow(0);
// 标题总列数
int colNum = headerRow.getPhysicalNumberOfCells();
for (int i = startRowIndex; i < rowNum; i++) {
Row row = sheet.getRow(i);
boolean allRowIsBlank = isBlankRow(wb, row);
if (allRowIsBlank) { // 整行都空，就跳过
continue;
}
List