使用poi读取excel数据示例
分两种情况：
一种读取指定单元格的值
另一种是读取整行的值
依赖包：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
代码示例：
public String getCellFromExcel(String path, String row, String col) throws Exception {
return getCellFromExcel(path, 0, Integer.valueOf(row), Integer.valueOf(col));
}
public String getRowFromExcel(String path, String row) throws Exception {
return getRowFromExcel(path, 0, Integer.valueOf(row));
}
public String getRowFromExcel(String path, int sheet, int row) throws Exception {
File xlsx = new File(path);
Workbook workbook = WorkbookFactory.create(xlsx);
Sheet sheet1 = workbook.getSheetAt(sheet);
Row row1 = sheet1.getRow(row);
List<String> list = new ArrayList<>();
int rowNum = row1.getLastCellNum();
for (int i = 0; i < rowNum; i++) {
list.add(getCellValueByCell(row1.getCell(i)));
}
logger.info("文件名：{}，sheet：{}，row：{}，取值：{}", path, sheet, row, list.toString());
return list.toString();
}
public String getCellFromExcel(String path, int sheet, int row, int col) throws Exception {
File xlsx = new File(path);
Workbook workbook = WorkbookFactory.create(xlsx);
Sheet sheet1 = workbook.getSheetAt(sheet);
Row row1 = sheet1.getRow(row);
String cell = getCellValueByCell(row1.getCell(col));
logger.info("文件名：{}，sheet：{}，row：{}，col：{}，取值：{}", path, sheet, row, col, cell);
return cell;
}
//获取单元格各类型值，返回字符串类型
private static String getCellValueByCell(Cell cell) {
//判断是否为null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
int cellType = cell.getCellType();
// 以下是判断数据的类型
switch (cellType) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (0 == cell.getCellType()) {//判断单元格的类型是否则NUMERIC类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
Date date = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat(
"yyyy-MM-dd HH:mm");
cellValue = formater.format(date);
} else {
cellValue = cell.getNumericCellValue() + "";
}
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}