java POI 下载Excel自定义模板 导入Excel 导出Excel

一、应用场景

1.点击导入按钮，弹出导入框

2.点击下载模板，用户在模板中填入数据

3.导入模板数据

4.经过一系列处理后，根据模板导出数据

二、下载Excel模板

     public void downloadTemplate(HttpServletResponse response) {
        String newFileName = null;
        try {
            // URLEncoder.encode可以防止中文乱码
            newFileName = URLEncoder.encode("模板名称", "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        // 当客户端请求的资源是一个可下载的资源（这里的“可下载”是指浏览器会弹出下载框或者下载界面）时，对这个可下载资源的描述（例如下载框中的文件名称）就是来源于该头域。
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + newFileName + ".xlsx");
        // 服务器告诉浏览器它发送的数据属于什么文件类型，也就是响应数据的MIME类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 关闭缓存（HTTP/1.1）
        response.setHeader("Cache-Control", "no-store");
        // 关闭缓存（HTTP/1.0）
        response.setHeader("Pragma", "no-cache");
        // 缓存有效时间
        response.setDateHeader("Expires", 0);
        InputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            // 自定义模板路径 当时位置为 resources/download.xlsx
            inputStream = new ClassPathResource("/download.xlsx").getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IoUtil.close(inputStream);
            IoUtil.close(outputStream);
        }
    }

三、导入Excel

public String importData(MultipartFile file) throws Exception {
        // 新增的对象(示例)
        InsertData insertData = new InsertData();
        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        // 获取文件输入流
        InputStream inputStream = file.getInputStream();
        // 读取整个excel
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        // 获取第一个sheet页
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        try {
            // 从第四行开始循环获取每一行数据
            for (int i = 3; i < sheetAt.getPhysicalNumberOfRows() - 1; i++) {
                Row row = sheetAt.getRow(i);
                // 从第四行开始循环获取每一列数据  只获取第一列到第九列的数据，后面的数据导出时添加
                for (int j = 0; j < 9; j++) {
                    /**
                     * 业务处理代码......
                     * 新增数据(示例) 获取第一列和第二列的数据
                     * */
                    if("".equals(getCellValue(row.getCell(0))) {
                        failureMsg.insert(0, "很抱歉，导入失败，姓名不能为空！");
                        throw new ServiceException(failureMsg.toString());
                    }else {
                        insertData.setName(getCellValue(row.getCell(0)));
                    }
                    insertData.setAge(getCellValue(row.getCell(1)));
                    this.insert(insertData);
                    successNum++;
                    successMsg.append("<br/>" + " 导入成功");
                }
            }
        } catch (Exception e) {
            failureNum++;
        }
        if (failureNum > 0) {
            throw new ServiceException(failureMsg.toString());
        } else {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        return successMsg.toString();
    }

private String getCellValue(Cell cell){
        String cellValue = "";
        switch (cell.getCellType()){
            case _NONE: // 未知类型，用于表示初始化前的状态或缺少具体类型。仅供内部使用。
                break;
            case NUMERIC: // 数字类型，用于Excel日期格式化
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    DateFormat df = new SimpleDateFormat("EEE MMM dd HH:mm:ss 'CST' yyyy", Locale.US);
                    DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    try {
                        cellValue = sdf.format(df.parse(d.toString()));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                } else {
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case STRING: // 字符串类型
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA: // 公式类型
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BLANK: // 空白的单元格
                break;
            case BOOLEAN: // 布尔类型
                break;
            case ERROR: // 错误类型
                break;
            default:
        }
        return cellValue;
    }

四、导出Excel

public void exportCodeExcel(HttpServletResponse response) {
        try {
            // 拿到需要导出的数据
            List<TestData> testDataList = testDataMapper.getList();
            InputStream inputStream = new ClassPathResource("/download.xlsx").getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            // 动态列 修改表头名 、修改模板数据等操作
            // 填充excel数据
            exportExcelData(workbook, testDataList);
            // 输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            // excel工作空间写入流
            workbook.write(byteArrayOutputStream);
            // 写到本地
            String excelName = "导出";
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" +
                    new String(excelName.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.flush();
            output.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

private void exportExcelData(XSSFWorkbook workbook, List<TestData> testDataList) {
        // 填充第一个sheet页内容
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        /**
         * XSSFSheet.shiftRows() 是 Apache POI 库中 XSSF（基于XML的Excel文件格式）工作表的                    方法之一。它用于将工作表中的行进行移动。
         * shiftRows() 方法具有以下参数：
         *  startRow：开始移动的行索引。
         *  endRow：结束移动的行索引。
         *  n：要移动的行数。正数表示向下移动，负数表示向上移动。
         *  copyRowHeight：是否复制移动的行的行高。
         *  resetOriginalRowHeight：是否将原始行的行高设置为默认值
         * */
        sheetAt.shiftRows(3 + 1, sheetAt.getLastRowNum(), testDataList.size(), true, false);
        int rowNum = 3;
        for (TestData dt : testDataList) {
            // 从第四行开始添加每一列的数据
            Row row = sheetAt.createRow(rowNum);
            row.createCell(0).setCellValue(dt.getName());
            row.createCell(1).setCellValue(dt.getAge());
            row.createCell(2).setCellValue(dt.getIdCard());
            row.createCell(3).setCellValue(dt.getSex());
            row.createCell(4).setCellValue(dt.getPhone());
            rowNum++;
        }
}