版本1.6
新增使用链表的解析工作薄的接口及使用其的方法。
工具类：
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
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.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.util.LocaleUtil;
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;
/**
* excel文件上传解析工具类
* @author zql
* @createTime 2020-12-06 11:17:30
* @version 1.6
* @modifyLog
* <pre>
* version 1.6
* 1、新增使用链表的解析工作薄的接口及使用其的方法
* </pre>
*
*/
public class ExcelParseMapper {
/**
* 2003版后缀
*/
private String suffix2003 = ".xls";
/**
* 2007版后缀
*/
private String suffix2007 = ".xlsx";
/**
* 解析指定工作薄方法
* @author zql
* @createTime 2020-12-06 15:03:47
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
public void parseSpecifiedSheet(File file, int type, int sheetNum, ParseSheetCellInterface parseSheetCellInterface){
if (Objects.isNull(file)) {
return;
}
if (file.getName().endsWith(suffix2003)) {
this.parseExcel2003(file, type, sheetNum, parseSheetCellInterface);
} else if (file.getName().endsWith(suffix2007)) {
this.parseExcel2007(file, type, sheetNum, parseSheetCellInterface);
}
}
/**
* 读取2003Excel
* @author zql
* @createTime 2020-12-06 15:04:21
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseExcel2003(File file, int type, int sheetNum, ParseSheetCellInterface parseSheetCellInterface) {
try {
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
this.parseLengthwaysExcel2003(sheet, rowNum, parseSheetCellInterface);
} else {
this.parseTransverseExcel2003(sheet, rowNum, parseSheetCellInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2003");
}
}
/**
* 读取2007Excel
* @author zql
* @createTime 2020-12-06 15:05:24
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseExcel2007(File file, int type, int sheetNum, ParseSheetCellInterface parseSheetCellInterface) {
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
this.parseLengthwaysExcel2007(sheet, rowNum, parseSheetCellInterface);
} else {
this.parseTransverseExcel2007(sheet, rowNum, parseSheetCellInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2007");
}
}
/**
* 横向解析2003excel
* @author zql
* @createTime 2020-12-06 15:06:06
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseTransverseExcel2003(HSSFSheet sheet, int rowNum, ParseSheetCellInterface parseSheetCellInterface) {
String sheetName = sheet.getSheetName();
HSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
// 保存标题
for (int i = 0; i < cellTitleNum; i++) {
HSSFCell cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
// 是否完成当前行的读取
boolean isComplete;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
isComplete = false;
// 得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell(j);
if (j == (cellTitleNum - 1)) {
isComplete = true;
}
parseSheetCellInterface.setCurCell(title[j], i, isComplete, sheetName, cell);
}
}
}
/**
* 纵向解析2003excel
* @author zql
* @createTime 2020-12-06 15:06:36
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseLengthwaysExcel2003(HSSFSheet sheet, int rowNum, ParseSheetCellInterface parseSheetCellInterface) {
String sheetName = sheet.getSheetName();
String title = null;
// 是否完成当前行的读取
boolean isComplete;
for (int i = 0; i < rowNum; i++) {
isComplete = false;
// 得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
// 得到标题单元格
HSSFCell cell = row.getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
for (int j = 1, len = row.getLastCellNum(); j < len; j++) {
cell = row.getCell(j);
if (j == (len - 1)) {
isComplete = true;
}
parseSheetCellInterface.setCurCell(title, i, isComplete, sheetName, cell);
}
}
}
/**
* 横向解析2007excel
* @author zql
* @createTime 2020-12-06 15:07:03
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseTransverseExcel2007(XSSFSheet sheet, int rowNum, ParseSheetCellInterface parseSheetCellInterface) {
String sheetName = sheet.getSheetName();
XSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
for (int i = 0; i < cellTitleNum; i++) {
XSSFCell cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
// 是否完成当前行的读取
boolean isComplete;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
// 得到Excel工作表的行
XSSFRow row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
isComplete = false;
// 得到Excel工作表指定行的单元格
XSSFCell cell = row.getCell(j);
if (j == (cellTitleNum - 1)) {
isComplete = true;
}
parseSheetCellInterface.setCurCell(title[j], i, isComplete, sheetName, cell);
}
}
}
/**
* 纵向解析2007excel
* @author zql
* @createTime 2020-12-06 15:07:26
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
private void parseLengthwaysExcel2007(XSSFSheet sheet, int rowNum, ParseSheetCellInterface parseSheetCellInterface) {
String sheetName = sheet.getSheetName();
String title = null;
// 是否完成当前行的读取
boolean isComplete;
for (int i = 0; i < rowNum; i++) {
isComplete = false;
// 得到Excel工作表的行
XSSFRow row = sheet.getRow(i);
// 得到标题单元格
XSSFCell cell = row.getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
for (int j = 1, len = row.getLastCellNum(); j < len; j++) {
cell = row.getCell(j);
if (j == (len - 1)) {
isComplete = true;
}
parseSheetCellInterface.setCurCell(title, i, isComplete, sheetName, cell);
}
}
}
/**
* 对单元格进行格式化
* @author zql
* @createTime 2020-12-06 15:07:54
*
* @param cell
* @return
*/
public Object getFormartType(Cell cell) {
if (Objects.isNull(cell)) {
return "";
}
Object value;
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
DateFormat sdf = new SimpleDateFormat("yyyy-MMM-dd", LocaleUtil.getUserLocale());
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
value = sdf.format(cell.getDateCellValue());
} else if ("@".equals(cell.getCellStyle().getDataFormatString())) {
// 大数值读取时，会读到科学计数法形式，即后面带一个E，所以需要用new DecimalFormat("#")格式化
// #号表示前缀或后缀出现不必要的0时，将其忽略，因此，要想读到几位，就在点号后加几个#号，本例中，#.########将可读到1至8位小数
value = new DecimalFormat("#.########").format(cell.getNumericCellValue());
} else if ("0.00".equals(cell.getCellStyle().getDataFormatString())) {
value = new DecimalFormat("#.########").format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = new DecimalFormat("#.########").format(cell.getNumericCellValue());
} else {
value = Double.toString(cell.getNumericCellValue());
}
break;
case STRING:
value = cell.getRichStringCellValue().toString();
break;
case FORMULA:
// try catch为了防止取到计算公式，而取到计算结果
try {
value = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
value = String.valueOf(cell.getCellFormula());
}
break;
case BLANK:
value = "";
break;
case BOOLEAN:
value = cell.getBooleanCellValue() ? true : false;
break;
case ERROR:
value = ErrorEval.getText(cell.getErrorCellValue());
break;
default:
value = cell.toString();
}
return value;
}
/**
* 解析工作薄单元格内容接口
* @author zql
* @createTime 2020-12-06 11:23:38
* @version 1.0
*
*/
public interface ParseSheetCellInterface {
/**
* 设置当前单元格对象
* @author zql
* @createTime 2020-12-06 11:23:27
*
* @param title 当前单元格所在列的标题
* @param curRowNum 当前单元格所在的行数
* @param isComplete 是否完成当前行的读取
* @param sheetName 当前读取的工作薄名称
* @param cell 当前单元格对象
*/
void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell);
}
/**
* 解析全部数据方法
* @author zql
* @createTime 2020-12-06 15:14:30
*
* @param file excel文件对象
* @param parseAllSheetInterface 解析全部sheet的接口
*/
public void parseAllData(File file, ParseAllSheetInterface parseAllSheetInterface) {
if (Objects.isNull(file)) {
return;
}
if (file.getName().endsWith(suffix2003)) {
this.readAllSheet2003(file, parseAllSheetInterface);
}
if (file.getName().endsWith(suffix2007)) {
this.readAllSheet2007(file, parseAllSheetInterface);
}
}
/**
* 读取2003Excel
* @author zql
* @createTime 2020-12-06 15:14:56
*
* @param file excel文件对象
* @param parseAllSheetInterface 解析全部sheet的接口
*/
private void readAllSheet2003(File file, ParseAllSheetInterface parseAllSheetInterface) {
try {
FileInputStream fis = new FileInputStream(file);
this.parseExcelInputStream2003(fis, parseAllSheetInterface);
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by readAllSheet2003");
}
}
/**
* 读取2007Excel
* @author zql
* @createTime 2020-12-06 15:15:18
*
* @param file excel文件对象
* @param parseAllSheetInterface 解析全部sheet的接口
*/
private void readAllSheet2007(File file, ParseAllSheetInterface parseAllSheetInterface) {
try {
FileInputStream fis = new FileInputStream(file);
this.parseExcelInputStream2007(fis, parseAllSheetInterface);
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + "by readAllSheet2007");
}
}
/**
* 解析当前sheet的方法，此方法配合解析全部数据方法使用
* @author zql
* @createTime 2020-12-06 15:15:42
*
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* @param version 当前的excel版本
* @param sheet 当前的Sheet
* @param rowNum 总行数
* @param parseSheetCellInterface 解析工作薄单元格内容的接口
*/
public void parseCurSheet(int type, int version, Sheet sheet, int rowNum, ParseSheetCellInterface parseSheetCellInterface) {
if (version == 2003) {
HSSFSheet hs = (HSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
this.parseLengthwaysExcel2003(hs, rowNum, parseSheetCellInterface);
} else {
this.parseTransverseExcel2003(hs, rowNum, parseSheetCellInterface);
}
} else if (version == 2007) {
XSSFSheet xs = (XSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
this.parseLengthwaysExcel2007(xs, rowNum, parseSheetCellInterface);
} else {
this.parseTransverseExcel2007(xs, rowNum, parseSheetCellInterface);
}
}
}
/**
* 解析全部sheet的接口
* @author zql
* @createTime 2020-12-06 15:11:22
* @version 1.0
*
*/
public interface ParseAllSheetInterface {
/**
* 解析当前sheet
* @author zql
* @createTime 2020-12-06 15:11:45
*
* @param version 当前的excel版本
* @param sheet 当前Sheet对象
* @param sheetName 当前工作薄名称
* @param rowNum 总行数
*/
void parseCurSheet(int version, Sheet sheet, String sheetName, int rowNum);
}
/**
* 解析全部数据方法
* @author zql
* @createTime 2020-12-06 15:17:09
*
* @param in 字节输入流
* @param version excel版本
* @param parseAllSheetInterface 解析全部sheet的接口
*/
public void parseAllData(InputStream in, int version, ParseAllSheetInterface parseAllSheetInterface) {
if (version == 2003) {
this.parseExcelInputStream2003(in, parseAllSheetInterface);
} else if (version == 2007) {
this.parseExcelInputStream2007(in, parseAllSheetInterface);
}
}
/**
* 解析2003Excel输入流
* @author zql
* @createTime 2020-12-06 15:17:48
*
* @param in 字节输入流
* @param parseAllSheetInterface 解析全部sheet的接口
*/
private void parseExcelInputStream2003(InputStream in, ParseAllSheetInterface parseAllSheetInterface) {
try {
HSSFWorkbook wb = new HSSFWorkbook(in);
in.close();
// 获取工作薄数
int getNumberOfSheets = wb.getNumberOfSheets();
HSSFSheet sheet = null;
int rowNum = 0;
String sheetName = null;
for (int i = 0; i < getNumberOfSheets; i++) {
// 得到工作薄内容
sheet = wb.getSheetAt(i);
// 得到工作薄名
sheetName = sheet.getSheetName();
// 取得最后一行的行号
rowNum = sheet.getLastRowNum() + 1;
parseAllSheetInterface.parseCurSheet(2003, sheet, sheetName, rowNum);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcelInputStream2003");
}
}
/**
* 解析2007Excel输入流
* @author zql
* @createTime 2020-12-06 15:18:12
*
* @param in 字节输入流
* @param parseAllSheetInterface 解析全部sheet的接口
*/
private void parseExcelInputStream2007(InputStream in, ParseAllSheetInterface parseAllSheetInterface) {
try {
XSSFWorkbook wb = new XSSFWorkbook(in);
in.close();
// 获取工作薄数
int getNumberOfSheets = wb.getNumberOfSheets();
XSSFSheet sheet = null;
int rowNum = 0;
String sheetName = null;
for (int i = 0; i < getNumberOfSheets; i++) {
// 得到工作薄内容
sheet = wb.getSheetAt(i);
// 得到工作薄名
sheetName = sheet.getSheetName();
// 取得最后一行的行号
rowNum = sheet.getLastRowNum() + 1;
parseAllSheetInterface.parseCurSheet(2007, sheet, sheetName, rowNum);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcelInputStream2007");
}
}
/**
* 当前行实体类
* @author zql
* @createTime 2020-12-06 15:18:42
* @version 1.0
*
*/
public class CurrentRow {
/**
* 当前工作薄名称
*/
private String sheetName;
/**
* 当前行索引
*/
private int rowIndex;
/**
* 当前行里的所有列
*/
private List<CurrentCell> currentCell;
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public int getRowIndex() {
return rowIndex;
}
public void setRowIndex(int rowIndex) {
this.rowIndex = rowIndex;
}
public List<CurrentCell> getCurrentCell() {
return currentCell;
}
public void setCurrentCell(List<CurrentCell> currentCell) {
this.currentCell = currentCell;
}
}
/**
* 当前单元格实体类
* @author zql
* @createTime 2020-12-06 15:18:55
* @version 1.0
*
*/
public class CurrentCell {
/**
* 当前单元格对应的标题
*/
private String title;
/**
* 当前单元格对应的列索引
*/
private int cellIndex;
/**
* 当前单元格对象
*/
private Cell cellValue;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getCellIndex() {
return cellIndex;
}
public void setCellIndex(int cellIndex) {
this.cellIndex = cellIndex;
}
public Cell getCellValue() {
return cellValue;
}
public void setCellValue(Cell cellValue) {
this.cellValue = cellValue;
}
}
/**
* 解析工作薄的接口
* @author zql
* @createTime 2020-12-06 15:19:09
* @version 1.0
*
* @param <T>
*/
public interface ParseSheetExtractorInterface<T>{
/**
* 解析数据
* @author zql
* @createTime 2020-12-06 15:19:25
*
* @param rowList 当前sheet所有行列表
* @return
*/
T extractData(List<CurrentRow> rowList);
}
/**
* 解析指定工作薄方法
* @author zql
* @createTime 2020-12-06 15:19:53
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
public <T> T parseSpecifiedSheet(File file, int type, int sheetNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
if (Objects.isNull(file)) {
return null;
}
T t = null;
if (file.getName().endsWith(suffix2003)) {
t = this.parseExcel2003(file, type, sheetNum, parseSheetExtractorInterface);
} else if (file.getName().endsWith(suffix2007)) {
t = this.parseExcel2007(file, type, sheetNum, parseSheetExtractorInterface);
}
return t;
}
/**
* 读取2003Excel
* @author zql
* @createTime 2020-12-06 15:25:23
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2003(File file, int type, int sheetNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(sheet, rowNum, parseSheetExtractorInterface);
} else {
t = this.parseTransverseExcel2003(sheet, rowNum, parseSheetExtractorInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2003");
}
return t;
}
/**
* 读取2007Excel
* @author zql
* @createTime 2020-12-06 15:26:22
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2007(File file, int type, int sheetNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(sheet, rowNum, parseSheetExtractorInterface);
} else {
t = this.parseTransverseExcel2007(sheet, rowNum, parseSheetExtractorInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2007");
}
return t;
}
/**
* 横向解析2003excel
* @author zql
* @createTime 2020-12-06 15:27:23
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2003(HSSFSheet sheet, int rowNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
String sheetName = sheet.getSheetName();
HSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
HSSFRow row = null;
HSSFCell cell = null;
// 保存标题
for (int i = 0; i < cellTitleNum; i++) {
cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
List<CurrentRow> rowList = new ArrayList<CurrentRow>();
List<CurrentCell> cellList = null;
CurrentRow curRow = null;
CurrentCell curCell = null;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
curRow = new CurrentRow();
cellList = new ArrayList<CurrentCell>();
// 得到Excel工作表的行
row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
cell = row.getCell(j);
curCell = new CurrentCell();
curCell.setCellIndex(j);
curCell.setCellValue(cell);
curCell.setTitle(title[j]);
cellList.add(curCell);
}
curRow.setRowIndex(i);
curRow.setCurrentCell(cellList);
curRow.setSheetName(sheetName);
rowList.add(curRow);
}
return parseSheetExtractorInterface.extractData(rowList);
}
/**
* 纵向解析2003excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:27:46
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2003(HSSFSheet sheet, int rowNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface){
HSSFRow row = null;
HSSFCell cell = null;
String sheetName = sheet.getSheetName();
String title = null;
String[] titles = new String[rowNum];
List<CurrentRow> rowList = new ArrayList<CurrentRow>();
List<CurrentCell> cellList = null;
CurrentRow curRow = null;
CurrentCell curCell = null;
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
// 因为标题是第一列，所以从第二列开始
// 列变行循环开始
for (int i = 1; i < maxCellNum; i++) {
curRow = new CurrentRow();
cellList = new ArrayList<CurrentCell>();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
curCell = new CurrentCell();
curCell.setCellIndex(j);
curCell.setCellValue(cell);
curCell.setTitle(titles[j]);
cellList.add(curCell);
}
curRow.setRowIndex(i);
curRow.setCurrentCell(cellList);
curRow.setSheetName(sheetName);
rowList.add(curRow);
}
return parseSheetExtractorInterface.extractData(rowList);
}
/**
* 横向解析2007excel
* @author zql
* @createTime 2020-12-06 15:28:07
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2007(XSSFSheet sheet, int rowNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
String sheetName = sheet.getSheetName();
XSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
XSSFRow row = null;
XSSFCell cell = null;
for (int i = 0; i < cellTitleNum; i++) {
cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
List<CurrentRow> rowList = new ArrayList<CurrentRow>();
List<CurrentCell> cellList = null;
CurrentRow curRow = null;
CurrentCell curCell = null;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
cellList = new ArrayList<CurrentCell>();
curRow = new CurrentRow();
// 得到Excel工作表的行
row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
curCell = new CurrentCell();
// 得到Excel工作表指定行的单元格
cell = row.getCell(j);
curCell.setCellIndex(j);
curCell.setCellValue(cell);
curCell.setTitle(title[j]);
cellList.add(curCell);
}
curRow.setRowIndex(i);
curRow.setCurrentCell(cellList);
curRow.setSheetName(sheetName);
rowList.add(curRow);
}
return parseSheetExtractorInterface.extractData(rowList);
}
/**
* 纵向解析2007excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:28:29
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2007(XSSFSheet sheet, int rowNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
String sheetName = sheet.getSheetName();
XSSFRow row = null;
XSSFCell cell = null;
String title = null;
String[] titles = new String[rowNum];
List<CurrentRow> rowList = new ArrayList<CurrentRow>();
List<CurrentCell> cellList = null;
CurrentRow curRow = null;
CurrentCell curCell = null;
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
// 因为标题是第一列，所以从第二列开始
// 列变行循环开始
for (int i = 1; i < maxCellNum; i++) {
curRow = new CurrentRow();
cellList = new ArrayList<CurrentCell>();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
curCell = new CurrentCell();
curCell.setCellIndex(j);
curCell.setCellValue(cell);
curCell.setTitle(titles[j]);
cellList.add(curCell);
}
curRow.setRowIndex(i);
curRow.setCurrentCell(cellList);
curRow.setSheetName(sheetName);
rowList.add(curRow);
}
return parseSheetExtractorInterface.extractData(rowList);
}
/**
* 解析当前sheet的方法，此方法配合解析全部数据方法使用
* @author zql
* @createTime 2020-12-06 15:28:57
*
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param version 当前的excel版本
* @param sheet 当前的Sheet
* @param rowNum 总行数
* @param parseSheetExtractorInterface 解析工作薄的接口
* @return
*/
public <T> T parseCurSheet(int type, int version, Sheet sheet, int rowNum, ParseSheetExtractorInterface<T> parseSheetExtractorInterface) {
T t = null;
if (version == 2003) {
HSSFSheet hs = (HSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(hs, rowNum, parseSheetExtractorInterface);
} else {
t = this.parseTransverseExcel2003(hs, rowNum, parseSheetExtractorInterface);
}
} else if (version == 2007) {
XSSFSheet xs = (XSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(xs, rowNum, parseSheetExtractorInterface);
} else {
t = this.parseTransverseExcel2007(xs, rowNum, parseSheetExtractorInterface);
}
}
return t;
}
/**
* 解析工作薄的接口
* @author zql
* @createTime 2020-12-06 15:30:18
* @version 1.0
*
* @param <T>
*/
public interface ParseSheetIteratorInterface<T> {
/**
* 解析数据
* @author zql
* @createTime 2020-12-06 15:30:27
*
* @param rowList 当前sheet所有行列表
* @return
*/
T extractData(Iterator<Map<String,Cell>> rowList);
}
/**
* 解析指定工作薄方法
* @author zql
* @createTime 2020-12-06 15:32:36
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
public <T> T parseSpecifiedSheet(File file, int type, int sheetNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
if (Objects.isNull(file)) {
return null;
}
T t = null;
if (file.getName().endsWith(suffix2003)) {
t = this.parseExcel2003(file, type, sheetNum, parseSheetIteratorInterface);
} else if (file.getName().endsWith(suffix2007)) {
t = this.parseExcel2007(file, type, sheetNum, parseSheetIteratorInterface);
}
return t;
}
/**
* 读取2003Excel
* @author zql
* @createTime 2020-12-06 15:34:16
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2003(File file, int type, int sheetNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(sheet, rowNum, parseSheetIteratorInterface);
} else {
t = this.parseTransverseExcel2003(sheet, rowNum, parseSheetIteratorInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2003");
}
return t;
}
/**
* 读取2007Excel
* @author zql
* @createTime 2020-12-06 15:35:13
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2007(File file, int type, int sheetNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(sheet, rowNum, parseSheetIteratorInterface);
} else {
t = this.parseTransverseExcel2007(sheet, rowNum, parseSheetIteratorInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2007");
}
return t;
}
/**
* 横向解析2003excel
* @author zql
* @createTime 2020-12-06 15:35:58
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2003(HSSFSheet sheet, int rowNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
HSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
HSSFRow row = null;
HSSFCell cell = null;
// 保存标题
for (int i = 0; i < cellTitleNum; i++) {
cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
List<Map<String, Cell>> rowList = new ArrayList<Map<String, Cell>>();
Map<String, Cell> rowMap = null;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
rowMap = new HashMap<String, Cell>();
// 得到Excel工作表的行
row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
cell = row.getCell(j);
rowMap.put(title[j], cell);
}
rowList.add(rowMap);
}
Iterator<Map<String, Cell>> it = rowList.iterator();
return parseSheetIteratorInterface.extractData(it);
}
/**
* 纵向解析2003excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:36:24
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2003(HSSFSheet sheet, int rowNum,ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
HSSFRow row = null;
HSSFCell cell = null;
String title = null;
String[] titles = new String[rowNum];
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
// 因为标题是第一列，所以从第二列开始
List<Map<String, Cell>> rowList = new ArrayList<Map<String, Cell>>();
Map<String, Cell> rowMap = null;
// 列变行循环开始
for (int i = 1; i < maxCellNum; i++) {
rowMap = new HashMap<String, Cell>();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
rowMap.put(titles[j], cell);
}
rowList.add(rowMap);
}
Iterator<Map<String, Cell>> it = rowList.iterator();
return parseSheetIteratorInterface.extractData(it);
}
/**
* 横向解析2007excel
* @author zql
* @createTime 2020-12-06 15:36:49
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2007(XSSFSheet sheet, int rowNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
XSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
XSSFRow row = null;
XSSFCell cell = null;
for (int i = 0; i < cellTitleNum; i++) {
cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
List<Map<String, Cell>> rowList = new ArrayList<Map<String, Cell>>();
Map<String, Cell> rowMap = null;
// 行循环开始
for (int i = 1; i < rowNum; i++) {
rowMap = new HashMap<String, Cell>();
// 得到Excel工作表的行
row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
cell = row.getCell(j);
rowMap.put(title[j], cell);
}
rowList.add(rowMap);
}
Iterator<Map<String, Cell>> it = rowList.iterator();
return parseSheetIteratorInterface.extractData(it);
}
/**
* 纵向解析2007excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:37:07
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2007(XSSFSheet sheet, int rowNum, ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
XSSFRow row = null;
XSSFCell cell = null;
String title = null;
String[] titles = new String[rowNum];
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
List<Map<String, Cell>> rowList = new ArrayList<Map<String, Cell>>();
Map<String, Cell> rowMap = null;
// 因为标题是第一列，所以从第二列开始
// 列变行循环开始
for (int i = 1; i < maxCellNum; i++) {
rowMap = new HashMap<String, Cell>();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
rowMap.put(titles[j], cell);
}
rowList.add(rowMap);
}
Iterator<Map<String, Cell>> it = rowList.iterator();
return parseSheetIteratorInterface.extractData(it);
}
/**
* 解析当前sheet的方法，此方法配合解析全部数据方法使用
* @author zql
* @createTime 2020-12-06 15:37:28
*
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param version 当前的excel版本
* @param sheet 当前的Sheet
* @param rowNum 总行数
* @param parseSheetIteratorInterface 解析工作薄的接口
* @return
*/
public <T> T parseCurSheet(int type, int version, Sheet sheet, int rowNum,ParseSheetIteratorInterface<T> parseSheetIteratorInterface) {
T t = null;
if (version == 2003) {
HSSFSheet hs = (HSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(hs, rowNum, parseSheetIteratorInterface);
} else {
t = this.parseTransverseExcel2003(hs, rowNum, parseSheetIteratorInterface);
}
} else if (version == 2007) {
XSSFSheet xs = (XSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(xs, rowNum, parseSheetIteratorInterface);
} else {
t = this.parseTransverseExcel2007(xs, rowNum, parseSheetIteratorInterface);
}
}
return t;
}
/**
* 解析工作薄的接口
* @author zql
* @createTime 2020-12-06 15:38:38
* @version 1.0
*
* @param <T>
*/
public interface ParseSheetLinkedInterface<T>{
/**
* 解析数据
* @author zql
* @createTime 2020-12-06 15:38:57
*
* @param rs 当前sheet所有行列表
* @return
*/
T extractData(ResultSetInterface rs);
}
/**
* 解析当前sheet的方法，此方法配合解析全部数据方法使用
* @author zql
* @createTime 2020-12-06 15:41:28
*
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param version 当前的excel版本
* @param sheet 当前的Sheet
* @param rowNum 总行数
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
public <T> T parseCurSheet(int type, int version, Sheet sheet, int rowNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
this.head = null;
this.first = true;
T t = null;
if (version == 2003) {
HSSFSheet hs = (HSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(hs, rowNum, parseSheetLinkedInterface);
} else {
t = this.parseTransverseExcel2003(hs, rowNum, parseSheetLinkedInterface);
}
} else if (version == 2007) {
XSSFSheet xs = (XSSFSheet) sheet;
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(xs, rowNum, parseSheetLinkedInterface);
} else {
t = this.parseTransverseExcel2007(xs, rowNum, parseSheetLinkedInterface);
}
}
return t;
}
/**
* 结果集设置接口
* @author zql
* @createTime 2020-12-06 15:42:54
* @version 1.0
*
*/
public interface ResultSetInterface {
/**
* 下一行
*
* @return
*/
boolean next();
/**
* 得到单元格
*
* @author zql
* @createTime 2020-12-19 15:38:57
*
* @param columnName
* @return
*/
Cell getCell(String columnName);
/**
* 设置单元格
*
* @author zql
* @createTime 2020-12-19 15:38:57
*
* @param columnName
* @param value
*/
void setCell(String columnName, Cell value);
}
/**
* 链表头节点
*/
private ResultSetImp head = null;
/**
* 首次读取
*/
private boolean first = true;
/**
* 结果集实现类
*/
public class ResultSetImp implements ResultSetInterface {
public Map<String,Cell> data;
public ResultSetImp next;
public ResultSetImp() {
this.data = new HashMap<String,Cell>();
}
@Override
public boolean next() {
// 链表的下一个节点不为空且不是第一次调用
if (Objects.isNull(head.next) && !first) {
return false;
}
if (first) {
first = false;
return true;
}
head = head.next;
return true;
}
@Override
public Cell getCell(String columnName) {
return head.data.get(columnName);
}
@Override
public void setCell(String columnName,Cell value) {
this.data.put(columnName, value);
}
}
/**
* 解析指定工作薄方法
* @author zql
* @createTime 2020-12-06 15:44:36
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
public <T> T parseSpecifiedSheet(File file, int type, int sheetNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
if (Objects.isNull(file)) {
return null;
}
T t = null;
if (file.getName().endsWith(suffix2003)) {
t = this.parseExcel2003(file, type, sheetNum, parseSheetLinkedInterface);
} else if (file.getName().endsWith(suffix2007)) {
t = this.parseExcel2007(file, type, sheetNum, parseSheetLinkedInterface);
}
return t;
}
/**
* 读取2003Excel
* @author zql
* @createTime 2020-12-06 15:46:13
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断，1为纵向，反之则为横向
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2003(File file, int type, int sheetNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2003(sheet, rowNum, parseSheetLinkedInterface);
} else {
t = this.parseTransverseExcel2003(sheet, rowNum, parseSheetLinkedInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2003");
}
return t;
}
/**
* 读取2007Excel
* @author zql
* @createTime 2020-12-06 15:46:58
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断
* <pre>
* 如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* </pre>
* @param sheetNum 第几个工作薄，从1开始
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseExcel2007(File file, int type, int sheetNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
T t = null;
try {
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
fis.close();
// 因为sheet索引从1开始，所以要减1
int sheetIndex = sheetNum - 1;
// 对sheet进行判断，防止越界，当sheet小于0时，取第一个sheet，当sheet大于所有sheet总数时，取最后一个
sheetIndex = sheetIndex < 0 ? 0 : sheetIndex;
int getNumberOfSheets = wb.getNumberOfSheets();
sheetIndex = sheetIndex >= getNumberOfSheets ? getNumberOfSheets - 1 : sheetIndex;
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
// 取得最后一行的行号
int rowNum = sheet.getLastRowNum() + 1;
// 取得最后一行的行号
/// int rowNum = sheet.PhysicalNumberOfRows();
// 当type等于1时为纵向
if (type == 1) {
t = this.parseLengthwaysExcel2007(sheet, rowNum, parseSheetLinkedInterface);
} else {
t = this.parseTransverseExcel2007(sheet, rowNum, parseSheetLinkedInterface);
}
wb.close();
} catch (Exception e) {
System.err.println("The error message:" + e.getMessage() + " by parseExcel2007");
}
return t;
}
/**
* 横向解析2003excel
* @author zql
* @createTime 2020-12-06 15:47:39
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2003(HSSFSheet sheet, int rowNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
HSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
// 保存标题
for (int i = 0; i < cellTitleNum; i++) {
HSSFCell cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
// 从最后一行开始存储
// 行循环开始
for (int i = rowNum - 1; i >= 1; i--) {
ResultSetImp newNode = new ResultSetImp();
// 得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell(j);
newNode.setCell(title[j], cell);
}
newNode.next = this.head;
this.head = newNode;
}
return parseSheetLinkedInterface.extractData(this.head);
}
/**
* 纵向解析2003excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:47:59
*
* @param sheet HSSFSheet对象
* @param rowNum 总行数
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2003(HSSFSheet sheet, int rowNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
HSSFRow row = null;
HSSFCell cell = null;
String title = null;
String[] titles = new String[rowNum];
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
// 因为标题是第一列，所以从第二列开始
// 列变行循环开始
for (int i = maxCellNum - 1; i >= 1; i--) {
ResultSetImp newNode = new ResultSetImp();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
newNode.setCell(titles[j], cell);
}
newNode.next = this.head;
this.head = newNode;
}
return parseSheetLinkedInterface.extractData(this.head);
}
/**
* 横向解析2007excel
* @author zql
* @createTime 2020-12-06 15:48:18
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseTransverseExcel2007(XSSFSheet sheet, int rowNum,ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
XSSFRow rowTitle = sheet.getRow(0);
int cellTitleNum = rowTitle.getLastCellNum();
String[] title = new String[cellTitleNum];
XSSFRow row = null;
XSSFCell cell = null;
for (int i = 0; i < cellTitleNum; i++) {
cell = rowTitle.getCell(Short.parseShort(String.valueOf(i)));
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title[i] = cell.getStringCellValue();
} else {
title[i] = "空标题" + i;
}
}
// 行循环开始
for (int i = rowNum - 1; i >= 1; i--) {
ResultSetImp newNode = new ResultSetImp();
// 得到Excel工作表的行
row = sheet.getRow(i);
for (int j = 0; j < cellTitleNum; j++) {
// 得到Excel工作表指定行的单元格
cell = row.getCell(j);
newNode.setCell(title[j], cell);
}
newNode.next = this.head;
this.head = newNode;
}
return parseSheetLinkedInterface.extractData(this.head);
}
/**
* 纵向解析2007excel，如果是纵向，则行列相反，即得到是行对象是原来的列，得到列对象原来的行。
* @author zql
* @createTime 2020-12-06 15:48:43
*
* @param sheet XSSFSheet对象
* @param rowNum 总行数
* @param parseSheetLinkedInterface 解析工作薄的接口
* @return
*/
private <T> T parseLengthwaysExcel2007(XSSFSheet sheet, int rowNum, ParseSheetLinkedInterface<T> parseSheetLinkedInterface) {
XSSFRow row = null;
XSSFCell cell = null;
String title = null;
String[] titles = new String[rowNum];
// 最大列数
int maxCellNum = 0;
// 列数
int cellNum = 0;
// 得到最大的列数
for (int i = 0; i < rowNum; i++) {
cell = sheet.getRow(i).getCell(0);
if (Objects.nonNull(cell)) {
// 把类型先设置为字符串类型
cell.setCellType(CellType.STRING);
title = cell.getStringCellValue();
} else {
title = "空标题" + i;
}
titles[i] = title;
cellNum = sheet.getRow(i).getLastCellNum();
maxCellNum = cellNum < maxCellNum ? maxCellNum : cellNum;
}
// 因为标题是第一列，所以从第二列开始
// 列变行循环开始
for (int i = maxCellNum - 1; i >= 1; i--) {
ResultSetImp newNode = new ResultSetImp();
for (int j = 0; j < rowNum; j++) {
row = sheet.getRow(j);
cell = row.getCell(i);
newNode.setCell(titles[j], cell);
}
newNode.next = this.head;
this.head = newNode;
}
return parseSheetLinkedInterface.extractData(this.head);
}
}
测试类：
import org.apache.poi.ss.usermodel.Sheet;
import org.junit.Test;
import java.io.File;
/**
* 经测试，在1G的jvm内存下，2007版的大概能读取9M左右大小的文件数据，大概可以有20万条数据，过多会造成堆内存溢出
*
* @author zql
* @version 1.6
* @createTime 2020-12-06 16:08:30
*/
public class ExcelParseMapperTest {
private ExcelParseMapper epm = new ExcelParseMapper();
@Test
public void parse2003() {
File file = new File("E:\\excel\\测试数据.xls");
epm.parseAllData(file, new ExcelParseMapper.ParseAllSheetInterface() {
@Override
public void parseCurSheet(int version, Sheet sheet, String sheetName, int rowNum) {
int type = sheetName.equals("纵向") ? 1 : 0;
epm.parseCurSheet(type, version, sheet, rowNum, new ExcelParseMapper.ParseSheetLinkedInterface<String>() {
@Override
public String extractData(ExcelParseMapper.ResultSetInterface rs) {
int i = 1;
while (rs.next()) {
if (type == 1) {
System.out.print((i++) + "---" + epm.getFormartType(rs.getCell("工作内容")));
System.out.print(" | " + epm.getFormartType(rs.getCell("编号")));
System.out.print(" | " + epm.getFormartType(rs.getCell("名称")));
System.out.print(" | " + epm.getFormartType(rs.getCell("部门")));
System.out.print(" | " + epm.getFormartType(rs.getCell("总负责人")));
System.out.print(" | " + epm.getFormartType(rs.getCell("总负责人电话")));
System.out.print(" | " + epm.getFormartType(rs.getCell("地点")));
System.out.print(" | " + epm.getFormartType(rs.getCell("日期")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试负责人")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试负责人电话")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试方案")));
System.out.print(" | " + epm.getFormartType(rs.getCell("组织情况")));
System.out.print(" | " + epm.getFormartType(rs.getCell("气温")));
System.out.print(" | " + epm.getFormartType(rs.getCell("天气")));
System.out.print(" | " + epm.getFormartType(rs.getCell("开始时间")));
System.out.print(" | " + epm.getFormartType(rs.getCell("结束时间")));
System.out.println(" | " + epm.getFormartType(rs.getCell("总用时")));
} else {
System.out.print((i++) + "---" + epm.getFormartType(rs.getCell("编号")));
System.out.print(" | " + epm.getFormartType(rs.getCell("名称")));
System.out.print(" | " + epm.getFormartType(rs.getCell("单位")));
System.out.print(" | " + epm.getFormartType(rs.getCell("消耗")));
System.out.print(" | " + epm.getFormartType(rs.getCell("单价")));
System.out.println(" | " + epm.getFormartType(rs.getCell("重量")));
}
}
return null;
}
});
}
});
}
@Test
public void parse2007() {
File file = new File("E:\\excel\\测试数据.xlsx");
epm.parseAllData(file, new ExcelParseMapper.ParseAllSheetInterface() {
@Override
public void parseCurSheet(int version, Sheet sheet, String sheetName, int rowNum) {
int type = sheetName.equals("纵向") ? 1 : 0;
epm.parseCurSheet(type, version, sheet, rowNum, new ExcelParseMapper.ParseSheetLinkedInterface<String>() {
@Override
public String extractData(ExcelParseMapper.ResultSetInterface rs) {
int i = 1;
while (rs.next()) {
if (type == 1) {
System.out.print((i++) + "---" + epm.getFormartType(rs.getCell("工作内容")));
System.out.print(" | " + epm.getFormartType(rs.getCell("编号")));
System.out.print(" | " + epm.getFormartType(rs.getCell("名称")));
System.out.print(" | " + epm.getFormartType(rs.getCell("部门")));
System.out.print(" | " + epm.getFormartType(rs.getCell("总负责人")));
System.out.print(" | " + epm.getFormartType(rs.getCell("总负责人电话")));
System.out.print(" | " + epm.getFormartType(rs.getCell("地点")));
System.out.print(" | " + epm.getFormartType(rs.getCell("日期")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试负责人")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试负责人电话")));
System.out.print(" | " + epm.getFormartType(rs.getCell("测试方案")));
System.out.print(" | " + epm.getFormartType(rs.getCell("组织情况")));
System.out.print(" | " + epm.getFormartType(rs.getCell("气温")));
System.out.print(" | " + epm.getFormartType(rs.getCell("天气")));
System.out.print(" | " + epm.getFormartType(rs.getCell("开始时间")));
System.out.print(" | " + epm.getFormartType(rs.getCell("结束时间")));
System.out.println(" | " + epm.getFormartType(rs.getCell("总用时")));
}else {
System.out.print((i++) + "---" + epm.getFormartType(rs.getCell("编号")));
System.out.print(" | " + epm.getFormartType(rs.getCell("名称")));
System.out.print(" | " + epm.getFormartType(rs.getCell("单位")));
System.out.print(" | " + epm.getFormartType(rs.getCell("消耗")));
System.out.print(" | " + epm.getFormartType(rs.getCell("单价")));
System.out.println(" | " + epm.getFormartType(rs.getCell("重量")));
}
}
return null;
}
});
}
});
}
}
普通项目需要引入的包
poi-4.0.1.jar
poi-ooxml-4.0.1.jar
poi-ooxml-schemas-4.0.1.jar
commons-codec-1.11.jar
commons-collections4-4.3.jar
commons-math3-3.6.1.jar
xmlbeans-3.0.2.jar
commons-compress-1.18.jar
curvesapi-1.06.jar
maven项目依赖
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
