JZTXT

Excel导入功能参考

发布时间 2023-03-28 12:21:05作者: 勇敢-的心

@PostMapping(value = "/importDemo",consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@ApiOperation(value = "Demo导入")
    public void importDemo(HttpServletResponse response,@RequestParam("file") MultipartFile file){
    try {
        demoService.importDemo(response,file);
        }catch (Exception e){
    CommonResult.fail("导出失败！"+e);
    }
}

 

public void importDemo(HttpServletResponse response, MultipartFile file) {
List<AcceptDictExcel> list = ExcelUtils.excelToList(file, AcceptDictExcel.class);
try {
for (AcceptDictExcel acceptDictExcel : list) {
String dictNo = acceptDictExcel.getDictNo();
QueryWrapper<AcceptDictDemo> wrapper = new QueryWrapper<>();
wrapper.eq("dict_no",dictNo);
int count = dictMapper.selectCount(wrapper);
if (StringUtils.isEmpty(acceptDictExcel.getDictNo())){
acceptDictExcel.setResult("数据编码不能为空！");
continue;
}
if (StringUtils.isEmpty(acceptDictExcel.getDictName())){
acceptDictExcel.setResult("数据名称不能为空！");
continue;
}
if (count>0){
acceptDictExcel.setResult("数据编码已存在！");
continue;
}
AcceptDictDemo dict = new AcceptDictDemo();
dict.setDictNo(acceptDictExcel.getDictNo());
dict.setDictName(acceptDictExcel.getDictName());
dictMapper.insert(dict);
acceptDictExcel.setResult("成功");
}
}catch (Exception e){
System.out.println(e);
}
XSSFWorkbook workbook = new XSSFWorkbook();

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);

Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);


XSSFSheet sheet = workbook.createSheet("Sheet1");
Row row0 = sheet.createRow(0);
Cell cell0 = row0.createCell(0);
cell0.setCellValue("数据编码");
cell0.setCellStyle(cellStyle);

Cell cell1 = row0.createCell(1);
cell1.setCellValue("数据名称");

Cell cell2 = row0.createCell(2);
cell2.setCellValue("导入结果");

for (AcceptDictExcel dictExcel : list) {
XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
XSSFCell cell = row.createCell(0);
cell.setCellValue(dictExcel.getDictNo());
cell = row.createCell(1);
cell.setCellValue(dictExcel.getDictName());
cell = row.createCell(2);
cell.setCellValue(dictExcel.getResult());

}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
ExcelUtils.download(response, "Demo-导入结果.xlsx", new ByteArrayInputStream(os.toByteArray()));
}

 

public static <T> List<T> excelToList(MultipartFile excelFile, Class<T> clazz) {
    List<T> dataList = null;

    try {
        InputStream inputStream = excelFile.getInputStream();
        dataList = ((ExcelReaderBuilder)EasyExcel.read(inputStream).head(clazz)).sheet().doReadSync();
        return dataList;
    } catch (Exception var4) {
        var4.printStackTrace();
        return null;
    }
}