版本1.2
新增可以选择sheet工作薄。
工具类:
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.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;
import java.io.File;
import java.io.FileInputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Objects;
/**
* excel文件上传解析工具类
* @author zql
* @createTime 2021-01-16 02:48:19
* @version 1.2
* <pre>新增可以选择sheet工作薄</pre>
*
*/
public class ExcelParseMapper {
/**
* 2003版后缀
*/
private String suffix2003 = ".xls";
/**
* 2007版后缀
*/
private String suffix2007 = ".xlsx";
/**
* 解析指定工作薄方法
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断,1为纵向,反之则为横向
* @param sheetNum 第几个工作薄,从1开始
* @param parseSheetCell 解析工作薄单元格内容的接口
*/
public void parseSpecifiedSheet(File file, int type, int sheetNum, ParseSheetCell parseSheetCell) {
if (Objects.isNull(file)) {
return;
}
if (file.getName().endsWith(suffix2003)) {
parseExcel2003(file, type, sheetNum, parseSheetCell);
}
if (file.getName().endsWith(suffix2007)) {
parseExcel2007(file, type, sheetNum, parseSheetCell);
}
}
/**
* 读取2003Excel
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断,1为纵向,反之则为横向
* @param sheetNum 第几个工作薄,从1开始
* @param parseSheetCell 解析接口
*/
private void parseExcel2003(File file, int type, int sheetNum, ParseSheetCell parseSheetCell) {
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;
// 当type等于1时为纵向
if (type == 1) {
parseLengthwaysExcel2003(sheet, rowNum, parseSheetCell);
} else {
parseTransverseExcel2003(sheet, rowNum, parseSheetCell);
}
wb.close();
} catch (Exception e) {
System.out.println("The error message:" + e.getMessage());
}
}
/**
* 读取2007Excel
*
* @param file excel文件对象
* @param type 用于横向读取或者纵向读取的判断
* @param sheetNum 第几个工作薄,从1开始
* @param parseSheetCell 解析接口
*/
private void parseExcel2007(File file, int type, int sheetNum, ParseSheetCell parseSheetCell) {
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;
// 当type等于1时为纵向
if (type == 1) {
parseLengthwaysExcel2007(sheet, rowNum, parseSheetCell);
} else {
parseTransverseExcel2007(sheet, rowNum, parseSheetCell);
}
wb.close();
} catch (Exception e) {
System.out.println("The error message:" + e.getMessage());
}
}
/**
* 横向解析excel
*
* @param sheet
* @param rowNum
* @param parseSheetCell
*/
private void parseTransverseExcel2003(HSSFSheet sheet, int rowNum, ParseSheetCell parseSheetCell) {
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;
}
parseSheetCell.setCurCell(title[j], i, isComplete, sheetName, cell);
}
}
}
/**
* 纵向解析excel
*
* @param sheet
* @param rowNum
* @param parseSheetCell
*/
private void parseLengthwaysExcel2003(HSSFSheet sheet, int rowNum, ParseSheetCell parseSheetCell) {
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;
}
parseSheetCell.setCurCell(title, i, isComplete, sheetName, cell);
}
}
}
/**
* 横向解析excel
*
* @param sheet
* @param rowNum
* @param parseSheetCell
*/
private void parseTransverseExcel2007(XSSFSheet sheet,int rowNum,ParseSheetCell parseSheetCell){
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;
}
parseSheetCell.setCurCell(title[j], i, isComplete, sheetName, cell);
}
}
}
/**
* 纵向解析excel
*
* @param sheet
* @param rowNum
* @param parseSheetCell
*/
private void parseLengthwaysExcel2007(XSSFSheet sheet, int rowNum, ParseSheetCell parseSheetCell) {
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;
}
parseSheetCell.setCurCell(title, i, isComplete, sheetName, cell);
}
}
}
/**
* 对单元格进行格式化
*
* @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;
}
/**
* 定义一个解析工作薄单元格内容的接口
*/
public interface ParseSheetCell {
/**
* @param title 当前单元格所在列的标题
* @param curRowNum 当前单元格所在的行数
* @param isComplete 是否完成当前行的读取
* @param sheetName 当前读取的工作薄名称
* @param cell 当前单元格对象
*/
void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell);
}
}
测试类:
import org.apache.poi.ss.usermodel.Cell;
import org.junit.Test;
import java.io.File;
/**
*
* @author zql
* @version 1.2
* @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");
File file2 = new File("E:\\excel\\测试数据2.xls");
epm.parseSpecifiedSheet(file, 0, 1, new ExcelParseMapper.ParseSheetCell(){
@Override
public void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell) {
// 如果不使用工具类的getFormartType方法,就自行调用cell的方法,例如,cell.getStringCellValue()
System.out.println(sheetName + "--" + title + "--" + curRowNum + "--" + epm.getFormartType(cell) + "--" + isComplete);
}
});
epm.parseSpecifiedSheet(file2, 1, 2,new ExcelParseMapper.ParseSheetCell(){
@Override
public void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell) {
// 如果不使用工具类的getFormartType方法,就自行调用cell的方法,例如,cell.getStringCellValue()
System.out.println(sheetName + "--" + title + "--" + curRowNum + "--" + epm.getFormartType(cell) + "--" + isComplete);
}
});
}
@Test
public void parse2007() {
File file = new File("E:\\excel\\测试数据.xlsx");
File file2 = new File("E:\\excel\\测试数据2.xlsx");
epm.parseSpecifiedSheet(file, 0, 1, new ExcelParseMapper.ParseSheetCell(){
@Override
public void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell) {
// 如果不使用工具类的getFormartType方法,就自行调用cell的方法,例如,cell.getStringCellValue()
System.out.println(sheetName + "--" + title + "--" + curRowNum + "--" + epm.getFormartType(cell) + "--" + isComplete);
}
});
epm.parseSpecifiedSheet(file2, 1, 2, new ExcelParseMapper.ParseSheetCell(){
@Override
public void setCurCell(String title, int curRowNum, boolean isComplete, String sheetName, Cell cell) {
// 如果不使用工具类的getFormartType方法,就自行调用cell的方法,例如,cell.getStringCellValue()
System.out.println(sheetName + "--" + title + "--" + curRowNum + "--" + epm.getFormartType(cell) + "--" + isComplete);
}
});
}
}
普通项目需要引入的包
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>