1. 根据上传 excel 文件读取
所需jar 包:
compile group: 'org.apache.poi', name: 'poi', version: '3.13'//word excel 解析
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.13'//word excel 解析
compile group: 'org.apache.poi', name: 'poi-scratchpad', version: '3.13'//word excel 解析
示例代码
/**
* 从上传文件中获取预览数据
*/
public List<List<String>> readFromUploadExcel(MultipartFile file, boolean hasTitle) {
InputStream inputStream;
List<List<String>> lists = new ArrayList<>();
try {
inputStream = file.getInputStream();
lists = this.readExcel(inputStream, hasTitle, 20);
} catch (IOException e) {
e.printStackTrace();
}
return lists;
}
private List<List<String>> readExcel(InputStream inputStream, boolean hasTitle, int maxLine) {
List<List<String>> lists = new ArrayList<>();
try {
Workbook workbook = WorkbookFactory.create(inputStream);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum(); //总行数 ,第一行标题未计入
Row row = sheet.getRow(0);
int cols = row.getLastCellNum(); //总列数
int startRow = hasTitle ? 1 : 0;
int readLines = rows;
if (maxLine > 0 && maxLine < rows) {
readLines = maxLine;
}
for (int i = startRow; i <= readLines; i++) { //遍历每一行 0 为标题
row = sheet.getRow(i);
List<String> list = new ArrayList<>();
for (int j = 1; j <= cols - 1; j++) { //遍历每一列
Cell cell = row.getCell(j);
list.add(getCellValue(cell, evaluator));
}
lists.add(list);
}
} catch (IOException | InvalidFormatException e) {
e.printStackTrace();
}
return lists;
}
//考虑到 数据类型以及用到的函数解析
private String getCellValue(Cell cell, FormulaEvaluator evaluator) {
if (cell == null) {
return "";
}
String cellValue = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue=cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue=String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue=getCellValue(evaluator.evaluate(cell));
break;
default:
break;
}
return cellValue;
}