使用java实现excel基础表格导出

使用java实现excel基础表格导出

    /**
     * 数据导出
     */
    @PostMapping("/export")
    @Log(name = "承保管理_花木险种承保管理",type = "数据导出")
    public void export(@RequestBody Map<String, Object> params, HttpServletResponse response){
        flowersProductService.export(params,response);
    }

void export(Map<String, Object> params, HttpServletResponse response);

 @Override
    public void export(Map<String, Object> params, HttpServletResponse response) {
        SysUser user = LoginUserContextHolder.getUser();

        String[] titles = {"镇区", "保单号", "险类", "产品", "投保人", "联系电话", "被保险人","起保日期", "终保日期",
                "总保额", "含税总保费", "总净保费", "地市补贴保费", "县级补贴保费", "农户自交保费", "投保户数",  "总保险数量", "林木坐落地点",
                "出单月份", "人保保费", "太保保费", "国寿财保费", "平安保费", "大地保费", "中央财政","省级补贴保费"};


        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

        Workbook workbook = new SXSSFWorkbook();

        //表头样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);//设置字体大小
        cellStyle.setFont(font);//选择需要用到的字体格式
        cellStyle.setWrapText(true);//设置自动换行

        //表格数据样式
        CellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 9);//设置字体大小
        cellStyle2.setFont(font2);//选择需要用到的字体格式

        Page<FlowersProduct> page = new Page<>(1, -1);

        try {
            // 创建sheet页
            SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("花木年累计承保明细");
            sheet.setColumnWidth(1, 4500);
            sheet.setColumnWidth(2, 4500);
            sheet.createFreezePane(0, 1);//冻结表头
            // 创建单元格、第一行表头
            SXSSFRow row = sheet.createRow(0);

            for (int x = 0; x < titles.length; x++) {
                SXSSFCell fixationCell = row.createCell(x);
                fixationCell.setCellStyle(cellStyle);
                fixationCell.setCellValue(new XSSFRichTextString(titles[x]));
            }
            //用户类型：1.保险机构  2.镇街  3.市级
            if(user.getUserType()==1){
                params.put("organCode", user.getOrganCode());
            }else if (user.getUserType()==2){
                params.put("qhdm", user.getOrganCode());
            }
            List<FlowersProduct> entitys = flowersProductMapper.getList2(page, params);
            if (CollectionUtils.isNotEmpty(entitys)) {
                for (int k = 0; k < entitys.size(); k++) {
                    SXSSFRow rows = sheet.createRow(k + 1);
                    FlowersProduct data = entitys.get(k);
                    List<String> values = new ArrayList<String>();
                    values.add(data.getZhen()==null?"":data.getZhen());
                    values.add(data.getBdh()==null?"":data.getBdh());
                    values.add(data.getInsurancetypeName()==null?"":data.getInsurancetypeName());
                    values.add(data.getCp()==null?"":data.getCp());
                    values.add(data.getTbr()==null?"":data.getTbr());
                    values.add(data.getSjh()==null?"":data.getSjh());
                    values.add(data.getBbxr()==null?"":data.getBbxr());
                    if (data.getBxqq()!=null){
                        values.add(formatter.format(data.getBxqq()));
                    }else {
                        values.add("");
                    }
                    if (data.getBxzq()!=null){
                        values.add(formatter.format(data.getBxzq()));
                    }else {
                        values.add("");
                    }
                    values.add(String.valueOf(data.getBe()==null?0:data.getBe()));
                    values.add(String.valueOf(data.getHszbf()==null?0:data.getHszbf()));
                    values.add(String.valueOf(data.getZjbf()==null?0:data.getZjbf()));
                    values.add(String.valueOf(data.getDzbtbf()==null?0:data.getDzbtbf()));
                    values.add(String.valueOf(data.getXjbtbf()==null?0:data.getXjbtbf()));
                    values.add(String.valueOf(data.getNhzjbf()==null?0:data.getNhzjbf()));
                    values.add(data.getTbhs()==null?"":data.getTbhs());
                    values.add(data.getZbxsl()==null?"":data.getZbxsl());
                    values.add(data.getLmzldd()==null?"":data.getLmzldd());
                    values.add(data.getCdyf()==null?"":data.getCdyf());
                    values.add(String.valueOf(data.getRbbf()==null?0:data.getRbbf()));
                    values.add(String.valueOf(data.getTbbf()==null?0:data.getTbbf()));
                    values.add(String.valueOf(data.getGscbf()==null?0:data.getGscbf()));
                    values.add(String.valueOf(data.getPabf()==null?0:data.getPabf()));
                    values.add(String.valueOf(data.getDdbf()==null?0:data.getDdbf()));
                    values.add(String.valueOf(data.getZyczbtje()==null?0:data.getZyczbtje()));
                    values.add(String.valueOf(data.getSjbtbf()==null?0:data.getSjbtbf()));
                    for (int l = 0; l < titles.length; l++) {
                        SXSSFCell fixationCell = rows.createCell(l);
                        fixationCell.setCellStyle(cellStyle2);
                        CellUtil.createCell(rows, l, values.get(l));
                    }
                }
            }
            if (null != workbook) {
                workbook.write(response.getOutputStream());
            }
        } catch (Exception e) {
        } finally {
            if (null != workbook) {
                try {
                    workbook.close();
                } catch (IOException e) {
                }
            }
        }
    }