java 读取 excel 表格内容

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、添加依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
二、工具类
根据文件后缀判断 2003 || 2007 || 2010 格式。

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtils {

private static Workbook wb;
private static Sheet sheet;
private static Row row;

private static final String EXCEL_XLS = "xls";
private static final String EXCEL_XLSX = "xlsx";

/**
* 读取表头
*
* @param inputStream inputStream
* @param suffix file suffix
* @return map <index,value>
*/
public static Map<Integer, String> readExcelTitle(InputStream inputStream, String suffix) {
getWorkbook(inputStream, suffix);
sheet = wb.getSheetAt(0);
row = sheet.getRow(0);
// 标题总列数
int colNum = row.getPhysicalNumberOfCells();
Map<Integer, String> map = new HashMap<>();
for (int i = 0; i < colNum; i++) {
map.put(i, row.getCell(i).getStringCellValue());
}
return map;
}

/**
* 读取excel内容
*
* @param inputStream 文件
* @return Map<行, Map < 下标, Object>>
*/
public static Map<Integer, Map<Integer, String>> readExcelContent(InputStream inputStream, String suffix) {
getWorkbook(inputStream, suffix);
Map<Integer, Map<Integer, String>> content = new HashMap<>();
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
Map<Integer, String> cellValue = new HashMap<>();
while (j < colNum) {
String obj = getCellFormatValue(row.getCell(j));
cellValue.put(j, obj);
j++;
}
content.put(i, cellValue);
}
return content;
}

private static String getCellFormatValue(Cell cell) {
String cellValue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case Cell.CELL_TYPE_NUMERIC:
case Cell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell)) {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss");
Instant instant = cell.getDateCellValue().toInstant();
ZoneId zoneId = ZoneId.systemDefault();
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, zoneId);
cellValue = dateTimeFormatter.format(localDateTime);
} else {
// 如果是纯数字
// 取得当前Cell的数值
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRING
case Cell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellValue = cell.getRichStringCellValue().getString();
break;
default:
// 默认的Cell值
cellValue = "";
}
}
return cellValue;
}

private static void getWorkbook(InputStream inputStream, String suffix) {

try {
//2003
if (EXCEL_XLS.equals(suffix)) {
wb = new HSSFWorkbook(inputStream);
//2007/2010
} else if (EXCEL_XLSX.equals(suffix)) {
wb = new XSSFWorkbook(inputStream);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}