Excel文件导入导出

一、使用POI导出Excel

后端通过response发送：

public void exportExcel(String[] fieldArray, ArrayList<LinkedHashMap<String, Object>> dataList, HttpServletResponse response) {
    	//HSSF对应旧的xls格式，XSSF对应新的xlsx格式，SXSSF是在XSSF的基础上，支持导出大批量的excel数据。
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");

        //标题行
        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(19);
        for(int i = 0; i < fieldArray.length; i ++){
            titleRow.createCell(i, CellType.STRING).setCellValue(fieldArray[i].substring(3));
        }

        //数据行
        for (int dataRowIndex = 0; dataRowIndex < dataList.size(); dataRowIndex++) {
            Row sheetRow = sheet.createRow(dataRowIndex + 1);
            LinkedHashMap dataRow = dataList.get(dataRowIndex);
            for (int fieldIndex = 0; fieldIndex < fieldArray.length; fieldIndex++) {
                String dataRowValue = dataRow.get(fieldArray[fieldIndex]).toString();
                String cellValue = dataRowValue == null ? "" : dataRowValue;
                sheetRow.createCell(fieldIndex, CellType.STRING).setCellValue(cellValue);
            }
        }

        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset-urf-8");
            response.setHeader("content-type","application/octet-stream");
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode("销售收入预测-汇总表.xlsx", "UTF-8"));
            //必须使用"Access-Control-Expose-Headers"头将"Content-Disposition"头暴露出来, 前端才能通过"Content-Disposition"头拿到文件名称
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

前端接收:

//使用Axios
axios.get('/preCostController/exportExcel',
    {
        params: {
            key_1: value_1,
            key_2: value_2
        },
    	//响应类型必须设置为'blob'二进制对象
        responseType: 'blob'
    })
    .then((response) => {
        var a = document.createElement("a");
        var blob = new Blob([response.data], {type: "application/vnd.ms-excel"});
        a.href = URL.createObjectURL(blob);
        var contentDisposition = response.headers['content-disposition'].toString();
    	//拿到文件名
        var index = contentDisposition.indexOf('filename=') + 9;
        a.download = decodeURI(contentDisposition.substring(index));
        // a.click();   下面这个写法兼容火狐
        a.dispatchEvent(new MouseEvent('click', {bubbles: true, cancelable: true, view: window}));
        window.URL.revokeObjectURL(blob);
    })
    .catch(function (error) {
        console.error(error);
    });

//使用url
var link = document.createElement('a');
link.style.display = 'none';
link.href = "/url?&param1=value1&param2=value2";
document.body.appendChild(link);
link.click();

二、使用POI导入Excel

public ResultEntity fileUpload(@RequestParam MultipartFile file) throws IOException {
        System.out.println(file.getOriginalFilename());
        if(file.getOriginalFilename() == null){
            //...
        }

        String fileName = file.getOriginalFilename();
        boolean isSuccess;
    
    	//使用 XSSFWorkbook 接收 xlsx 或 xlsm 后缀名的文件
    	XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
    
    	//使用 HSSFWorkbook 接收 xls 后缀名的文件
    	//HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
    
    	Sheet sheet = workbook.getSheetAt(0);

        for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j < columnSize; j++) {
                Cell cell = row.getCell(j);
                CellType cellType = cell.getCellType();
                if(cellType.toString().equals("NUMERIC")){
                    double cellValue = cell.getNumericCellValue();
                    System.out.print(cellValue + " ");
                }else {
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " ");
                }
            }
            System.out.println();
        }

    }

三、下载resource目录下Excel文件到浏览器

public void templateDownload(HttpServletResponse response) throws IOException {
        String fileName = "根据产品料号分类-导入模板.xlsx";
        ClassPathResource resource = new ClassPathResource("model/" + fileName);
        InputStream inputStream = resource.getInputStream();

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-type","application/octet-stream");
        response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        //必须使用 "Access-Control-Expose-Headers" 头将 "Content-Disposition" 头对前端暴露出来, 前端才能获取到文件名
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

        ServletOutputStream outputStream = response.getOutputStream();
        IoUtil.copy(inputStream, outputStream);	//Hutool
        response.flushBuffer();
        outputStream.close();
        log.info("{} 导出成功", fileName);
    }