@GetMapping("/importExcel")
public importExcel(@RequestParam("file") MultipartFile file){
service.import(file);
}
void import(MultipartFile file);
@Override
public void import(MultipartFile file){
ExcelReader excelReader = null;
try {
if (file.getOriginalFilename() == null || (!file.getOriginalFilename().toLowerCase().endsWith(".xls") && !file.getOriginalFilename().toLowerCase().endsWith(".xlsx"))) {
throw new BadException("");
}
if (file.getOriginalFilename().endsWith(".xlsx")) {
file.getOriginalFilename().replace(".xlsx", ".xls");
}
InputStream inputStream = file.getInputStream();
excelReader = EasyExcel.read(inputStream).build();
importData(excelReader);
} catch (IOException | BadException e) {
throw new BadException("");
} finally {
if (excelReader != null) {
excelReader.finish();
}
}
}
public void importData(ExcelReader excelReader) {
ExcelListener excelListener = new ExcelListener();
ReadSheet readSheet = EasyExcel.readSheet("Sheet1").headRowNumber(2).head(ExcelImportTemp.class).registerReadListener(excelListener).build();
excelReader.read(readSheet);
List<ExcelImportTemp> dataList = excelListener.getDataList();
System.out.println(dataList);
}
@Slf4j
public class ExcelListener extends AnalysisEventListener<ExcelImportTemp> {
private List<ExcelImportTemp> dataList = new ArrayList<ExcelImportTemp>();
@Override
public void invoke(ExcelImportTemp data, AnalysisContext context) {
log.info("读取了一条数据:{}", JSON.toJSONString(data));
dataList.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("读取完毕,共:{}条",dataList.size());
}
public List<ExcelImportTemp> getDataList() {
int sheetNo = 3;
for (ExcelImportTemp data : dataList) {
data.setSheetName("Sheet1");
data.setSheetLineNo(sheetNo);
sheetNo+=1;
}
return dataList;
}
@Data
public class ExcelImportTemp {
@ExcelIgnore
private Integer id;
@ExcelIgnore
private String sheetName;
@ExcelIgnore
private Integer sheetLineNo;
@ExcelProperty(value = {"英文名称"}, index = 0)
private Integer a;
@ExcelProperty(value = {"定额工日", "建筑"}, index = 1)
private Integer b;
}