ExcelKit工具类

1、准备工作

1.1、SheetInfoVo

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;}

1.2、BizException

/*** 业务性异常类*/
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;}
}

2、读取xlsx

依赖包：

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version>
</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;
}

3、读取xls

依赖包：

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version>
</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;
}

4、完整的ExcelKit.java源码

使用的时候切记要把包名导入，并修改导入的 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();}
}