Excel文件通过POI导入
1.加入相关依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
2.读取Excel文件
public List<String[]> parseExcel(InputStream inputStream, String suffix, int startRow) throws IOException {
Workbook workbook = null;
if ("xls".equals(suffix)) {
workbook = new HSSFWorkbook(inputStream);
} else if ("xlsx".equals(suffix)) {
workbook = new XSSFWorkbook(inputStream);
} else {
return null;
}
Sheet sheet = workbook.getSheetAt(0);
if (sheet == null) {
return null;
}
int lastRowNum = sheet.getLastRowNum();
if (startRow >= lastRowNum) {
return null;
}
List<String[]> result = new ArrayList<>();
Row row = null;
Cell cell = null;
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
row = sheet.getRow(rowNum);
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
if (lastCellNum != 0) {
String[] rowArray = new String[lastCellNum];
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
cell = row.getCell(cellNum);
if (cell == null) {
rowArray[cellNum] = null;
} else {
rowArray[cellNum] = parCell(cell);
}
}
result.add(rowArray);
}
}
return result;
}
3.解析单元个数据
private String parCell(Cell cell) {
String cellStr = null;
switch(cell.getCellTypeEnum()){
case STRING:
cellStr=cell.getRichStringCellValue().toString();
break;
case BLANK:
cellStr=null;
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat simpleDateFormat=null;
if (cell.getCellType()== HSSFDataFormat.getBuiltinFormat("h:mm")){
simpleDateFormat=new SimpleDateFormat("HH:mm");
}else {
simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
}
Date temp=cell.getDateCellValue();
cellStr=simpleDateFormat.format(temp);
}else {
double numericCellValue = cell.getNumericCellValue();
DecimalFormat decimalFormat = new DecimalFormat();
String dataFormatString = cell.getCellStyle().getDataFormatString();
if (dataFormatString.equals("General")){
decimalFormat.applyPattern("#");
}
cellStr=decimalFormat.format(numericCellValue);
}
break;
case BOOLEAN:
cellStr=String.valueOf(cell.getBooleanCellValue());
break;
default:
cellStr=null;
}
return cellStr;
}