import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SheetInfoVo implements Serializable {
/**
* 读取的sheet页
*/
private int sheetIndex;
/**
* 忽略的行数
*/
private int ignoreRow;
/**
* 忽略的列数
*/
private int ignoreColumn;
/**
* 指定行（如果不指定，那么动态获取）
*/
private int lastRowNum;
/**
* 指定列（如果不指定，那么动态获取）
*/
private int lastCellNum;
}
/**
* 业务性异常类
*/
public class BizException extends RuntimeException{
/** 数据校验异常 */
public static final String ERRORCODE_INVALID_DATA = "01";
/** 当前用户没有操作权限 */
public static final String ERRORCODE_UNAUTHORIZED = "02";
/** 数据当前状态不允许该操作 */
public static final String ERRORCODE_INVALID_STATUS = "03";
/** 其他业务异常 */
public static final String ERRORCODE_OTHER = "99";
private final String errorCode;
public BizException(String errorCode, String message) {
super(message);
this.errorCode = errorCode;
}
public BizException(String errorCode, String message, Throwable cause) {
super(message, cause);
this.errorCode = errorCode;
}
public String getErrorCode() {
return errorCode;
}
}
依赖包：
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>4.1.2version>
dependency>
读取方法：
/**
* 读取xlsx文件的第一个Sheet页（07）
* @param inputStream
* @return
*/
public static List<List<String>> readOneXlsxSheet(final FileInputStream inputStream, final SheetInfoVo sheetInfo) throws BizException {
List<List<String>> resultList = new LinkedList<>();
SheetInfoVo vo = initSheetInfo(sheetInfo);
int sheetIndex = vo.getSheetIndex();
int ignoreRow = vo.getIgnoreRow();
int ignoreColumn = vo.getIgnoreColumn();
int lastRowNum = vo.getLastRowNum();
int lastColumnNum = vo.getLastCellNum();
try {
//poi-ooxml包
XSSFWorkbook workbook= new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(sheetIndex);
//如果有指定读取到第几行，那么按指定的来
lastRowNum = lastRowNum == 0 ? sheet.getLastRowNum() : lastRowNum;
for (int rowIndex = ignoreRow; rowIndex <= lastRowNum; rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
int lastCellNum = lastColumnNum == 0 ? row.getLastCellNum() : lastColumnNum;
List<String> result = new LinkedList<>();
for (int columnIndex = ignoreColumn; columnIndex < lastCellNum; columnIndex++) {
Cell cell = row.getCell(columnIndex);
String value = getCellValue(cell);
result.add(value);
}
resultList.add(result);
}
}catch (IllegalArgumentException e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "读取的sheet页异常或读取的单元格异常，请联系IT处理");
}catch (Exception e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "文件读取异常，请联系IT处理");
}
return resultList;
}
依赖包：
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poiartifactId>
<version>4.1.2version>
dependency>
读取文件方法：
/**
* 读取xls文件的第一个Sheet页（03）
* @param inputStream
* @return
*/
public static List<List<String>> readOneXlsSheet(FileInputStream inputStream, SheetInfoVo sheetInfo) throws BizException{
List<List<String>> resultList = new LinkedList<>();
SheetInfoVo vo = initSheetInfo(sheetInfo);
int sheetIndex = vo.getSheetIndex();
int ignoreRow = vo.getIgnoreRow();
int ignoreColumn = vo.getIgnoreColumn();
int lastRowNum = vo.getLastRowNum();
int lastColumnNum = vo.getLastCellNum();
try {
//poi-ooxml包
Workbook workbook= new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(sheetIndex);
//如果有指定读取到第几行，那么按指定的来
lastRowNum = lastRowNum == 0 ? sheet.getLastRowNum() : lastRowNum;
for (int rowIndex = ignoreRow; rowIndex <= lastRowNum; rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
int lastCellNum = lastColumnNum == 0 ? row.getLastCellNum() : lastColumnNum;
List<String> result = new LinkedList<>();
for (int columnIndex = ignoreColumn; columnIndex < lastCellNum; columnIndex++) {
Cell cell = row.getCell(columnIndex);
String value = getCellValue(cell);
result.add(value);
}
resultList.add(result);
}
}catch (IllegalArgumentException e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "读取的sheet页异常或读取的单元格异常，请联系IT处理");
}catch (Exception e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "文件读取异常，请联系IT处理");
}
return resultList;
}
使用的时候切记要把包名导入，并修改导入的 BizException 和 SheetInfoVo 类路径。
使用的时候切记要把包名导入，并修改导入的 BizException 和 SheetInfoVo 类路径。
使用的时候切记要把包名导入，并修改导入的 BizException 和 SheetInfoVo 类路径。
package com.example.kit;
import com.example.common.BizException;
import com.example.vo.SheetInfoVo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import java.util.regex.Pattern;
public class ExcelKit {
/**
* 读取Excel文件，默认读取第一个Sheet页，不跳过所有行与列
* @param file
* @return
* @throws Exception
*/
public static List<List<String>> readOneSheet(File file) throws Exception {
return readOneSheet(file, null);
}
/**
* 读取Excel文件，通过sheetInfo对象，指定读取的sheet页、跳过行列标题数，甚至指定读取的行数列数数
* @param file 读取的文件
* @param sheetInfo 读取的形式
* @return 读取到的二维 String 列表
* @throws IOException
* @throws BizException
*/
public static List<List<String>> readOneSheet(File file, SheetInfoVo sheetInfo) throws IOException, BizException {
if (file == null) {
throw new FileNotFoundException("读取文件不能为空！");
}
FileInputStream inputStream = new FileInputStream(file);
String fileName = file.getName();
Workbook workbook = null;
if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
}else if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
}else {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "上传文件类型有误，请上传.xls或.xlsx的文件");
}
return readOneExcelSheet(workbook, sheetInfo);
}
public static List<List<String>> readOneExcelSheet(final Workbook workbook, final SheetInfoVo sheetInfo) throws BizException {
List<List<String>> resultList = new LinkedList<>();
SheetInfoVo vo = initSheetInfo(sheetInfo);
int sheetIndex = vo.getSheetIndex();
int ignoreRow = vo.getIgnoreRow();
int ignoreColumn = vo.getIgnoreColumn();
int lastRowNum = vo.getLastRowNum();
int lastColumnNum = vo.getLastCellNum();
try {
Sheet sheet = workbook.getSheetAt(sheetIndex);
//如果有指定读取到第几行，那么按指定的来
lastRowNum = lastRowNum == 0 ? sheet.getLastRowNum() : lastRowNum;
for (int rowIndex = ignoreRow; rowIndex <= lastRowNum; rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
int lastCellNum = lastColumnNum == 0 ? row.getLastCellNum() : lastColumnNum;
List<String> result = new LinkedList<>();
for (int columnIndex = ignoreColumn; columnIndex < lastCellNum; columnIndex++) {
Cell cell = row.getCell(columnIndex);
String value = getCellValue(cell);
result.add(value);
}
resultList.add(result);
}
}catch (IllegalArgumentException e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "读取的sheet页异常或读取的单元格异常，请联系IT处理");
}catch (Exception e) {
throw new BizException(BizException.ERRORCODE_INVALID_STATUS, "文件读取异常，请联系IT处理");
}
return resultList;
}
/**
* 初始化 SheetInfo
* @param sheetInfo
* @return
*/
private static SheetInfoVo initSheetInfo(final SheetInfoVo sheetInfo) {
SheetInfoVo vo = new SheetInfoVo(0, 0, 0, 0, 0);
if (sheetInfo == null) {
vo.setSheetIndex(0);
vo.setIgnoreRow(0);
vo.setIgnoreColumn(0);
vo.setLastRowNum(0);
vo.setLastCellNum(0);
}else {
int sheetIndex = sheetInfo.getSheetIndex();
int ignoreRow = sheetInfo.getIgnoreRow();
int ignoreColumn = sheetInfo.getIgnoreColumn();
int lastRowNum = sheetInfo.getLastRowNum();
int lastCellNum = sheetInfo.getLastCellNum();
if (isNotNullAndBigThenZero(sheetIndex)) {
vo.setSheetIndex(sheetIndex);
}
if (isNotNullAndBigThenZero(ignoreRow)) {
vo.setIgnoreRow(ignoreRow);
}
if (isNotNullAndBigThenZero(ignoreColumn)) {
vo.setIgnoreColumn(ignoreColumn);
}
if (isNotNullAndBigThenZero(lastRowNum)) {
vo.setLastRowNum(lastRowNum);
}
if (isNotNullAndBigThenZero(lastCellNum)) {
vo.setLastCellNum(lastCellNum);
}
}
return vo;
}
private static boolean isNotNullAndBigThenZero(Integer value) {
if (value != null && value > 0) {
return true;
}
return false;
}
/**
* 获取单元格数据
* @param cell
* @return
*/
private static String getCellValue(Cell cell) {
String value = "";
if (cell == null) {
return value;
}
switch (cell.getCellType()) {
case STRING:
value = cell.getRichStringCellValue().getString();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = getRealStringValueOfDouble(cell.getNumericCellValue());
}
break;
case FORMULA:
cell.setCellType(CellType.STRING);
// 导入时如果为公式生成的数据则无值
if (!cell.getRichStringCellValue().getString().equals("")) {
value = cell.getRichStringCellValue().getString();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y" : "N");
break;
default:
value = "";
break;
}
return value;
}
// 处理科学计数法与普通计数法的字符串显示，尽最大努力保持精度
private static String getRealStringValueOfDouble(Double value) {
String doubleStr = value.toString();
//是否使用科学计数法
boolean isScientificNotation = doubleStr.contains("E");
if (isScientificNotation) {
//很小的小数
if (doubleStr.contains("E-")) {
doubleStr = handleSoSmallNumber(doubleStr);
}else { //很大的数
doubleStr = handleSoBigNumber(doubleStr);
}
} else {
java.util.regex.Pattern p = Pattern.compile(".0$");
java.util.regex.Matcher m = p.matcher(doubleStr);
if (m.find()) {
doubleStr = doubleStr.replace(".0", "");
}
}
return doubleStr;
}
/**
* 处理科学计数法很小的数，类似0.00000000000000123456
* @param value
* @return
*/
private static String handleSoSmallNumber(String value) {
StringBuffer sb = new StringBuffer();
int indexOfE = value.indexOf("E-");
//小数的尾巴
String tail = value.substring(0, indexOfE).replace(".", "");
//指数
int pow = Integer.parseInt(value.substring(indexOfE + 2));
//补零
sb.append("0.");
while (--pow > 0) {
sb.append("0");
}
sb.append(tail);
return sb.toString();
}
/**
* 处理科学计数法很大的数，类似12345678910.123456
* @param value
* @return
*/
private static String handleSoBigNumber(String value) {
StringBuffer sb = new StringBuffer();
int indexOfE = value.indexOf("E");
int pow = Integer.parseInt(value.substring(indexOfE + 1));
int valueLength = value.substring(0, indexOfE).length();
if (pow > valueLength - 2) {
String head = value.substring(0, indexOfE).replace(".", "");
sb.append(head);
int zeroNum = pow - head.length() + 1;
while (zeroNum-- > 0) {
sb.append("0");
}
}else {
String head = value.substring(0, pow + 2).replace(".", "");
String tail = value.substring(pow + 2, indexOfE);
sb.append(head).append(".").append(tail);
}
return sb.toString();
}
}