Excel导入功能参考

1、导入的接口
@PostMapping(value = "/importDict",consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
@ApiOperation(value = "字典导入")
public void importDict(HttpServletResponse response,@RequestParam("file") MultipartFile file){
    try {
        dictService.importDict(response,file);
    }catch (Exception e){
        CommonResult.fail("导出失败！"+e);
    }
}

 2、导入的服务层方法

@Override
public void importDict(HttpServletResponse response, MultipartFile file) {
    List<AcceptDictExcel> list = ExcelUtils.excelToList(file, AcceptDictExcel.class);
    try {
        for (AcceptDictExcel acceptDictExcel : list) {
            String dictNo = acceptDictExcel.getDictNo();
            QueryWrapper<AcceptDict> 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;
            }
            AcceptDict dict = new AcceptDict();
            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, "安全验收数据字典-导入结果.xlsx", new ByteArrayInputStream(os.toByteArray()));
}

3、ExcelUtils工具类中的excelToList和download方法；

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;
    }
}

public static void download(HttpServletResponse response, String fileName, InputStream inputStream) {
    try {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
        ServletOutputStream out = response.getOutputStream();
        int b = false;
        byte[] buffer = new byte[65536];

        int b;
        while((b = inputStream.read(buffer)) != -1) {
            out.write(buffer, 0, b);
        }

        inputStream.close();
        if (out != null) {
            out.flush();
            out.close();
        }
    } catch (Exception var6) {
        var6.printStackTrace();
    }
}

private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) {
    String filePath = fileName + excelTypeEnum.getValue();

    try {
        fileName = URLEncoder.encode(filePath, StandardCharsets.UTF_8.name());
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        return response.getOutputStream();
    } catch (IOException var5) {
        throw new RuntimeException();
    }
}