java 如何使用poi读取本地excel的内容呢?
引用:
maven 在pom.xml中引入;
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
gradle 在build.gradle:
compile 'org.apache.poi:poi:3.15',
compile 'org.apache.poi:poi-ooxml:3.15'
读取excel代码:
public class ReadExcelUtils {
private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
private static final String EXCEL_XLS = ".xls";
private static final String EXCEL_XLSX = ".xlsx";
/**
* 读取excel数据
*
* @throws Exception
*/
public static List<List<String>> readExcelInfo(String fileName) throws Exception {
/*
* workbook:工作簿,就是整个Excel文档
* sheet:工作表
* row:行
* cell:单元格
*/
checkExcelValid(fileName);
Workbook workbook = getWorkBook(fileName);
List<List<String>> dataList = null;
if (workbook != null) {
int sheetNum = workbook.getNumberOfSheets();
//创建二维数组保存所有读取到的行列数据,外层存行数据,内层存单元格数据
dataList = new ArrayList<>();
//遍历工作簿中的sheet,第一层循环所有sheet表
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (int index = 0; index < sheetNum; index++) {
Sheet sheet = workbook.getSheetAt(index);
if (sheet == null) {
continue;
}
//如果当前行没有数据跳出循环,第二层循环单sheet表中所有行
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
//根据文件头可以控制从哪一行读取,在下面if中进行控制
if (row == null) {
continue;
}
//遍历每一行的每一列,第三层循环行中所有单元格
List<String> cellList = new ArrayList<>();
for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
Cell cell = row.getCell(cellIndex);
cellList.add(getCellValue(cell, evaluator));
}
dataList.add(cellList);
}
}
}
return dataList;
}
/**
* 获取单元格的数据,暂时不支持公式
*/
private static String getCellValue(Cell cell, FormulaEvaluator evaluator) {
if (cell == null || cell.toString().trim().equals("")) {
return null;
}
CellType cellType = cell.getCellTypeEnum();
String cellValue;
if (cellType == CellType.STRING) {
cellValue = cell.getStringCellValue().trim();
cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
return cellValue;
}
if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) { //判断日期类型
long time = cell.getDateCellValue().getTime();
LocalDateTime localDateTime = LocalDateTime.ofEpochSecond(time / 1000, (int) (time % 1000),
ZoneOffset.ofHours(8));
cellValue = localDateTime.format(DATE_TIME_FORMATTER);
} else { //否
cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
}
return cellValue;
}
if (cellType == CellType.BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
return cellValue;
}
if (cellType == CellType.FORMULA) {
CellValue evaluate = evaluator.evaluate(cell);
cellValue = evaluate.getStringValue();
return cellValue;
}
return null;
}
/**
* 判断excel的版本,并根据文件流数据获取workbook
*
* @throws IOException
*/
private static Workbook getWorkBook(String fileName) throws Exception {
Workbook workbook = null;
if (fileName.endsWith(EXCEL_XLS)) {
workbook = new HSSFWorkbook(new FileInputStream(fileName));
} else if (fileName.endsWith(EXCEL_XLSX)) {
workbook = new XSSFWorkbook(new FileInputStream(fileName));
}
return workbook;
}
/**
* 校验文件是否为excel
*
* @throws Exception
*/
private static void checkExcelValid(String fileName) throws Exception {
if (!fileName.endsWith(EXCEL_XLS) && !fileName.endsWith(EXCEL_XLSX)) {
throw new Exception("文件不是Excel");
}
}
}
表格里面的内容可能要多种格式,需要对常见的数据类型进行处理
测试:
public class ReadExcelTest {
public static void main(String[] args) throws Exception {
String filePath="E:/statics.xls";
List<List<String>> lists = ReadExcelUtils.readExcelInfo(filePath);
lists.forEach(e -> System.out.println(e.toString()));
}
}
结果:
[网元类型, 指标名称, 标题, 是否展示, 下采样方式, 指标单位, 数据类型, 标签, 指标描述, chartType, strategy, rate]
[linux, cpu_time_active, CPU 处于活动状态的时间统计, 否, avg, 秒, counter, cpu, instance_name, CPU 处于活动状态的时间统计,Active = User + System + Nice + Iowait + Irq + Softirq + Steal, 2, 3, 1]
[linux, cpu_time_guest, CPU 处于 Guest 状态的时间统计, 否, avg, 秒, counter, cpu, instance_name, CPU 处于 Guest 状态的时间统计, 2, 3, 0]
总结:
使用poi操作excel比较方便,要处理好表格里面的不同的数据内容。
了解了读取,那如何导出带复杂的表头的内容?