javaspringBoot---POI---excel批量导出

ctrl层

@ResponseBody
@RequestMapping("/exportCertificateInfoExcel")
public void exportCertificateInfoExcel(LwCertificateInfo lwCertificateInfo, HttpServletResponse response, String exportFlag) throws IOException, SQLException {
    File file = lwCertificateInfoService.exportCertificateInfoExcel(lwCertificateInfo, exportFlag);
    ResponseUtils.response(file, file.getName(), response);
}

service层

public interface LwCertificateInfoService extends BaseService<LwCertificateInfo> {
File exportCertificateInfoExcel(LwCertificateInfo lwCertificateInfo, String exportFlag) throws IOException, SQLException;
}

servicempl层

/**
     * 批量查询后导出培训证书
     * @return Integer 插入的记录
     */
    @Override
    public File exportCertificateInfoExcel(LwCertificateInfo LwCertificateInfo, String exportFlag) throws IOException, SQLException {
        if (LwCertificateInfo.getLaborIdcode().equals("")) {
            LwCertificateInfo.setLaborIdcode(null);
        }
        if (LwCertificateInfo.getEnterpriseName().equals("")) {
            LwCertificateInfo.setEnterpriseName(null);
        }
        if (LwCertificateInfo.getLaborName().equals("")) {
            LwCertificateInfo.setLaborName(null);
        }
        List<LwCertificateInfo> lwCertificateInfoList = selectList(LwCertificateInfo);
        String desktop = "D:\\证书信息";
        File filemkdir = new File(desktop);
        if (!filemkdir.exists()) {
            filemkdir.mkdirs();
        }
        String filePath = desktop + "\\template.xls";
        System.out.println("文件路径" + filePath);
        File file = new File(filePath);
        Long timeStamp = System.currentTimeMillis();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("培训证书及人员信息");
            FileOutputStream out = new FileOutputStream(filePath);
            //设置样式
            HSSFCellStyle blackStyle = workbook.createCellStyle();
            //水平居中
            blackStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //垂直居中
            blackStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
            //设置字体
            HSSFFont font = workbook.createFont();
            font.setFontName("华文行楷");
            //设置字号
            font.setFontHeightInPoints((short) 24);
            blackStyle.setFont(font);
            //创建第一行,并制作标题信息
            // 创建行,从0开始
            HSSFRow row0 = sheet.createRow(0);
            //行高设置为20
            row0.setHeightInPoints(20);
            //合并
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 7);
            sheet.addMergedRegion(region);
            // 创建行的单元格,也是从0开始
            HSSFCell cell1 = row0.createCell(0);
            // 设置单元格内容
            cell1.setCellValue("信息");
            cell1.setCellStyle(blackStyle);
            row0.setHeightInPoints(30);
            //设置列宽
            sheet.setColumnWidth(0, 10 * 256);
            sheet.setColumnWidth(1, 31 * 256);
            sheet.setColumnWidth(2, 25 * 256);
            sheet.setColumnWidth(3, 10 * 256);
            sheet.setColumnWidth(4, 25 * 256);
            sheet.setColumnWidth(5, 15 * 256);
            sheet.setColumnWidth(6, 15 * 256);
            sheet.setColumnWidth(7, 25 * 256);
            // 创建行,从0开始  设置单元格内容,重载
            HSSFRow row1 = sheet.createRow(1);
            row1.createCell(0).setCellValue("姓名");
            row1.createCell(1).setCellValue("身份证");
            row1.createCell(2).setCellValue("单位");
            row1.createCell(3).setCellValue("岗位");
            row1.createCell(4).setCellValue("编号");
            row1.createCell(5).setCellValue("开始日期");
            row1.createCell(6).setCellValue("结束日期");
            row1.createCell(7).setCellValue("发证单位");
            row1.setHeightInPoints(20);
            int lwListSize = lwCertificateInfoList.size();
            for (int i = 0; i < lwListSize; i++) {
                HSSFRow row_name = sheet.createRow(i + 2);
                LwCertificateInfo lwCertificateInfo = lwCertificateInfoList.get(i);
                //设置日期格式
                CreationHelper helper = workbook.getCreationHelper();
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                HSSFDataFormat format = workbook.createDataFormat();
//                cellStyle.setDataFormat(format.getFormat("yyyy年m月d日"));
//                cellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));
                cellStyle.setDataFormat(format.getFormat("yyyy年MM月dd日"));
                row_name.createCell(0).setCellValue(lwCertificateInfo.getLaborName());
                row_name.createCell(1).setCellValue(lwCertificateInfo.getLaborIdcode());
                row_name.createCell(2).setCellValue(lwCertificateInfo.getEnterpriseName());
                row_name.createCell(3).setCellValue(lwCertificateInfo.getPostName());
                row_name.createCell(4).setCellValue(lwCertificateInfo.getCategoryCode());
                row_name.createCell(5).setCellValue(lwCertificateInfo.getCertificateBeginDate());
                row_name.createCell(6).setCellValue(lwCertificateInfo.getCertificateEndDate());
//                HSSFCell cell5 = row_name.createCell(5);
//                HSSFCell cell6 = row_name.createCell(6);
//                cell6.setCellStyle(cellStyle);
//                cell5.setCellStyle(cellStyle);
                row_name.createCell(7).setCellValue(lwCertificateInfo.getSendCategoryUnit());
                //设置单元格高度
                row_name.setHeightInPoints(20);
            }
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return file;
    }