导出——springboot+poi实现指定模板导出功能

  @GetMapping("/writeExcel")
    @ApiOperation(value = "导出企业信息", notes = "导出企业信息", httpMethod = "GET", produces = "application/json")
    public Rest writeExcel(HttpServletResponse response){
        //查询所需要导出的企业信息
        List<EnterpriseExport> enterpriseValueObjects = enterpriseService.selectAll();
        //进行导出
        enterpriseService.Export(enterpriseValueObjects,response);
        return ok();
    }

 /**
     * 查询所有企业信息 用于导出
     */
    List<EnterpriseExport> selectAll();
 #实现类
 @Override
    public List<EnterpriseExport> selectAll() {
        return enterpriseMapper.selectAll();
    }
#mapper
 /**
     * 查询所有企业信息 用于导出
     */
    List<EnterpriseExport> selectAll();
#SQL
    <!--List<EnterpriseValueObject> selectAll();-->
    <select id="selectAll" resultType="com.safesoft.domain.eval.enterprise.entity.EnterpriseExport">
        SELECT DISTINCT
            t0.ID,
            t0.enterprise_name,
            t0.area_type,
            t0.final_score,
            t0.action_times,
            ( SELECT is_target FROM eval_area_govern t1 WHERE t1.delete_flag = '0' AND t1.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) IsTarget,
            ( SELECT is_noticed FROM eval_area_govern t1 WHERE t1.delete_flag = '0' AND t1.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) IsNoticed,
            ( SELECT is_remove FROM eval_area_govern t1 WHERE t1.delete_flag = '0' AND t1.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) IsRemove,
            ( SELECT is_sign FROM eval_area_govern t1 WHERE t1.delete_flag = '0' AND t1.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) IsSign,
            ( SELECT score FROM eval_area_govern t1 WHERE t1.delete_flag = '0' AND t1.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) AreaScore,
            ( SELECT score FROM eval_build_info t2 WHERE t2.delete_flag = '0' AND t2.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) BuildScore,
            ( SELECT score FROM eval_economic_info t3 WHERE t3.delete_flag = '0' AND t3.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) EconomicScore,
            ( SELECT score FROM eval_environmental_info t5 WHERE t5.delete_flag = '0' AND t5.enterprise_id = t0.ID ORDER BY ID DESC LIMIT 1 ) EnvironmentalScore,
            (select score from eval_fire_control_info t6 where t6.delete_flag = '0' and t6.enterprise_id = t0.id ORDER BY id desc limit 1) FireControlScore,
            (select score from eval_illegal_build_info t7 where t7.delete_flag = '0' and t7.enterprise_id = t0.id ORDER BY id desc limit 1) IllegalScore,
            (select score from eval_market_supervision t8 where t8.delete_flag = '0' and t8.enterprise_id = t0.id ORDER BY id desc LIMIT 1) MarketScore,
            (select score from eval_safety_info t9 where t9.delete_flag = '0' and t9.enterprise_id=t0.id ORDER BY id desc limit 1) SafetyScore,
            (select score from eval_social_security t10 where t10.delete_flag = '0' and t10.enterprise_id=t0.id ORDER BY id desc limit 1) SocialScore
        FROM
            eval_enterprise_info t0
        ORDER BY
            final_score DESC
    </select>

2.进行导出

        //进行导出
        enterpriseService.Export(enterpriseValueObjects,response);

   /**
     * 导出的业务逻辑
     */
    void Export(List<EnterpriseExport> enterpriseValueObjects, HttpServletResponse response);

@Override
    public void Export(List<EnterpriseExport> enterpriseValueObjects, HttpServletResponse response) {
        XSSFWorkbook wb =null;
        try {
            // excel模板路径
            Resource resource = new ClassPathResource("企业综合评价平台数据0203.xlsx");
            FileInputStream fis = new FileInputStream(resource.getFile());
            wb = new XSSFWorkbook(fis);
            // 读取了模板内所有sheet内容
            XSSFSheet sheet = wb.getSheetAt(0);
            // 在相应的单元格进行赋值
            int rowIndex = 2;
            int j = 1;
            for (EnterpriseExport enterpriseValueObject : enterpriseValueObjects) {
                XSSFRow row = sheet.getRow(rowIndex);
                if (null == row) {
                    row = sheet.createRow(rowIndex);
                }
                XSSFCell cell0 = row.getCell(0);
                if (null == cell0) {
                    cell0 = row.createCell(0);
                }
                cell0.setCellValue(j);// 标识

                XSSFCell cell1 = row.getCell(1);
                if (null == cell1) {
                    cell1 = row.createCell(1);
                }
                cell1.setCellValue(enterpriseValueObject.getEnterpriseName());

                XSSFCell cell2 = row.getCell(2);
                if (null == cell2) {
                    cell2 = row.createCell(2);
                }
                if (enterpriseValueObject.getAreaType() == null){
                    cell2.setCellValue(" ");
                }else {
                    cell2.setCellValue(Optional.ofNullable(enterpriseValueObject.getAreaType()==1?"区内":"区外").orElse(" 1"));//
                }


                XSSFCell cell3 = row.getCell(3);
                if (null == cell3) {
                    cell3 = row.createCell(3);
                }
                cell3.setCellValue(Optional.ofNullable(enterpriseValueObject.getFinalScore()).orElse(" "));

                XSSFCell cell4 = row.getCell(4);
                if (null == cell4) {
                    cell4 = row.createCell(4);
                }
                cell4.setCellValue(Optional.ofNullable(enterpriseValueObject.getActionTimes()).orElse(0));//

                XSSFCell cell5 = row.getCell(5);
                if (null == cell5) {
                    cell5 = row.createCell(5);
                }
                log.info("index:{}",rowIndex);
                if ("1".equals(enterpriseValueObject.getIsTarget())){
                    enterpriseValueObject.setIsTarget("是");
                }else {
                    enterpriseValueObject.setIsTarget(" ");
                }
                cell5.setCellValue(Optional.ofNullable(enterpriseValueObject.getIsTarget()).orElse(" "));   //是否为全域整治对象



                XSSFCell cell6 = row.getCell(6);
                if (null == cell6) {
                    cell6 = row.createCell(6);
                }
                //整治进度 1-告知 2-签约 3-拆除
                if ("1".equals(enterpriseValueObject.getIsNoticed())){
                    if ("1".equals(enterpriseValueObject.getIsSign())){
                        if ("1".equals(enterpriseValueObject.getIsRemove())){
                            cell6.setCellValue("拆除");
                        }else {
                            cell6.setCellValue("签约");
                        }
                    }else {
                        cell6.setCellValue("告知");
                    }
                }else {
                    cell6.setCellValue(" ");//整治进度
                }

                XSSFCell cell7 = row.getCell(7);
                if (null == cell7) {
                    cell7 = row.createCell(7);
                }
                cell7.setCellValue(Optional.ofNullable(enterpriseValueObject.getAreaScore()).orElse(" "));//全域整治信息分数

                XSSFCell cell8 = row.getCell(8);
                if (null == cell8) {
                    cell8 = row.createCell(8);
                }
                cell8.setCellValue(Optional.ofNullable(enterpriseValueObject.getBuildScore()).orElse(" "));//

                XSSFCell cell9 = row.getCell(9);
                if (null == cell9) {
                    cell9 = row.createCell(9);
                }
                cell9.setCellValue(Optional.ofNullable(enterpriseValueObject.getEconomicScore()).orElse(" "));//

                XSSFCell cell10 = row.getCell(10);
                if (null == cell10) {
                    cell10 = row.createCell(10);
                }
                cell10.setCellValue(Optional.ofNullable(enterpriseValueObject.getEnvironmentalScore()).orElse(" "));//

                XSSFCell cell11 = row.getCell(11);
                if (null == cell11) {
                    cell11 = row.createCell(11);
                }
                cell11.setCellValue(Optional.ofNullable(enterpriseValueObject.getFireControlScore()).orElse(" "));//

                XSSFCell cell12 = row.getCell(12);
                if (null == cell12) {
                    cell12 = row.createCell(12);
                }
                cell12.setCellValue(Optional.ofNullable(enterpriseValueObject.getIllegalScore()).orElse(" "));//

                XSSFCell cell13 = row.getCell(13);
                if (null == cell13) {
                    cell13 = row.createCell(13);
                }
                cell13.setCellValue(Optional.ofNullable(enterpriseValueObject.getMarketScore()).orElse(" "));//

                XSSFCell cell14 = row.getCell(14);
                if (null == cell14) {
                    cell14 = row.createCell(14);
                }
                cell14.setCellValue(Optional.ofNullable(enterpriseValueObject.getSafetyScore()).orElse(" "));//
                XSSFCell cell15 = row.getCell(15);
                if (null == cell15) {
                    cell15 = row.createCell(15);
                }
                cell15.setCellValue(Optional.ofNullable(enterpriseValueObject.getSocialScore()).orElse(" "));//
                rowIndex++;
                j++;
            }

            //7.下载
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            String fileName = "企业综合评价平台数据" + new Date().getTime() + ".xlsx";
            new DownloadUtils().download(os, response, fileName);
        } catch (Exception e) {
            log.error("导出excel出现异常"+e);
        }
    }

3.用于导出的工具类

/**
 * 用于 Excel下载的工具类
 */
public class DownloadUtils {
    /**
     *
     * @param byteArrayOutputStream 输出字节流
     * @param response
     * @param returnName 输出到客户端的文件名
     * @throws IOException
     */
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        //response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));            //保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("Content-Disposition","attachment;filename="+returnName);
        response.setContentLength(byteArrayOutputStream.size());
        response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
        ServletOutputStream outputStream = response.getOutputStream();    //取得输出流
        byteArrayOutputStream.writeTo(outputStream);                    //写到输出流
        outputStream.flush();                                            //刷数据
        byteArrayOutputStream.close();                                    //关闭
    }
}