pom.xml依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
完整代码
/**
* @version 1.0.0
* @ClassName ExcelUtil.java
* @Description excel工具类
*/
public final class ExcelUtil {
private ExcelUtil() {
}
/**
* 2003以下版本的excel
*/
public static final String EXCEL_2003L = ".xls";
/**
* 2007以上版本的excel
*/
public static final String EXCEL_2007U = ".xlsx";
public static final String DATA_GENERAL_FORMAT = "General";
public static final ThreadLocal<DecimalFormat> DECIMAL_FORMAT_LOCAL = ThreadLocal.withInitial(() -> new DecimalFormat("0"));
public static final ThreadLocal<DecimalFormat> DECIMAL_FORMAT_LOCAL2 = ThreadLocal.withInitial(() -> new DecimalFormat("0.00"));
/**
* 获取IO流中的数据,组装成List<Object[]>对象
*
* @param sheet 第几张工作簿(以0开始)
* @param startRow 工作簿中第行开始(以0开始)
* @return
* @throws Exception
*/
public static List<Object[]> getExcelData(Sheet sheet, int startRow) throws Exception {
List<Object[]> list = new ArrayList();
//遍历当前sheet中的所有行
List<Object> tampList = null;
Row row = null;
Cell cell = null;
for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
//遍历所有的列
tampList = new ArrayList();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
tampList.add(getCellValue(cell));
}
list.add(tampList.toArray());
}
return list;
}
/**
* 获取指定某一行的数据
*
* @param sheet
* @param rowIndex 工作簿中第行开始(以0开始)
* @return
*/
public static List<Object> getRowData(Sheet sheet, int rowIndex) {
List<Object> list = new ArrayList();
Row row = sheet.getRow(rowIndex);
if (row == null) {
return list;
}
Cell cell = null;
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
cell = row.getCell(i);
list.add(getCellValue(cell));
}
return list;
}
/**
* 获取excel数据, 每一行数据就是一个Map, key为字段, value为数据值
*
* @return
*/
public static List<Map<String, Object>> getExcelDataMapList(Sheet sheet, int startDataRowIndex, int headerRowIndex) throws Exception {
//获取列头
List<Object> headerCols = getRowData(sheet, headerRowIndex);
//开始解析实际数据
Row row = null;
Cell cell = null;
List<Map<String, Object>> list = new ArrayList();
//遍历当前sheet中的所有行
for (int i = startDataRowIndex; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
Map<String, Object> rowMap = new HashMap(16);
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
rowMap.put(String.valueOf(headerCols.get(j)), getCellValue(cell));
}
if (rowMap.isEmpty()) {
continue;
}
list.add(rowMap);
}
return list;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
*
* @param file
* @return
* @throws Exception
*/
public static Workbook getWorkbook(File file) throws Exception {
return getWorkbook(new FileInputStream(file), file.getName());
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
*
* @param file
* @return
* @throws Exception
*/
public static Workbook getWorkbook(MultipartFile file) throws Exception {
return getWorkbook(file.getInputStream(), file.getOriginalFilename());
}
/**
* 获取sheet
*
* @param workbook
* @param index 第几张工作簿(以0开始)
* @return
*/
public static Sheet getExcelSheet(Workbook workbook, int index) {
Sheet sheet = workbook.getSheetAt(index);
if (sheet == null) {
throw new BizException("Excel工作薄为空!", CodeMsg.FILE_ANALYSIS_ERROR.getCode());
}
return sheet;
}
/**
* 获取Sheet的最大行数
*
* @return
*/
public static int getSheetMaxRowNum(Sheet sheet) {
return sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
}
public static Workbook getWorkbook(InputStream in, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (EXCEL_2003L.equals(fileType)) {
//2003以下
wb = new HSSFWorkbook(in);
} else if (EXCEL_2007U.equals(fileType)) {
//2007以上
wb = new XSSFWorkbook(in);
} else {
throw new BizException(CodeMsg.FILE_ANALYSIS_ERROR);
}
if (null == wb) {
throw new BizException("Excel为空!", CodeMsg.FILE_ANALYSIS_ERROR.getCode());
}
return wb;
}
/**
* 描述:对表格中数值进行格式化
*
* @param cell
* @return
*/
public static Object getCellValue(Cell cell) {
//用String接收所有返回的值
String value = null;
switch (cell.getCellType()) {
case STRING:
//String类型的数据
value = cell.getStringCellValue();
break;
case NUMERIC:
//数值类型(取值用cell.getNumericCellValue() 或cell.getDateCellValue())
if (DATA_GENERAL_FORMAT.equals(cell.getCellStyle().getDataFormatString())) {
value = DECIMAL_FORMAT_LOCAL.get().format(cell.getNumericCellValue());
} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
value = DateTimeUtil.DATE_TIME_LOCAL.get().format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
value = DECIMAL_FORMAT_LOCAL2.get().format(cell.getNumericCellValue());
}
break;
case BOOLEAN:
//Boolean类型
value = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
//表达式类型
value = String.valueOf(cell.getCellFormula());
break;
case ERROR:
value = String.valueOf(cell.getErrorCellValue());
break;
default:
value = "";
break;
}
if (StringUtils.isBlank(value)) {
value = "";
}
if (cell == null) {
return "";
}
return value;
}
}