Excel导出

 /**
     * Excel标题
     */
    private static final String[] title = new String[]{"姓名","身份证号码","字号",
            "联系电话","经营范围名称","经营范围编码","集群地址","原住所地址","用户邮箱","经营网络地址","组织形式编码",
            "组织形式编码名称","行业表述编码","行业表述名称","行政区划编码","行政区划名称"
    };
    public static final List titleHead= Collections.unmodifiableList(Arrays.asList(title));

 

  public void excelExport(HttpServletResponse response) {
        try {
            List<IndividualBusinessConstractPo> constractPoList = new ArrayList<>();
            List<IndividualBusinessVo> exportList = mapper.findExport();
            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("sheet1");
            //设置标题样式
            for (int i = 0; i < titleHead.size(); i++) {
                sheet.setColumnWidth(i,5000);
            }
            Row rowTitle = sheet.createRow(0);
            for (int i = 0; i < titleHead.size(); i++) {
                String title = (String) titleHead.get(i);
                Cell rowCellTitle = rowTitle.createCell(i);
                rowCellTitle.setCellValue(title);
                //设置字体颜色
                //设置单元格样式
                CellStyle cellStyle = wb.createCellStyle();
                Font font = wb.createFont();
                font.setFontName("黑体");
                font.setFontHeightInPoints((short) 12);//设置字体大小
                font.setBold(true);
                cellStyle.setFont(font);
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
                rowCellTitle.setCellStyle(cellStyle);
                Comment comment = sheet.createDrawingPatriarch()
                        .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)rowCellTitle.getColumnIndex(), rowCellTitle.getRowIndex(), (short) rowCellTitle.getColumnIndex(), rowCellTitle.getRowIndex()));
                rowCellTitle.setCellComment(comment);
            }
            for (int i = 1; i <= exportList.size(); i++) {
                IndividualBusinessVo businessVo = exportList.get(i - 1);
                IndividualBusinessPo business = businessVo.getIndividualBusiness();
                IndividualBusinessConstractPo constract = businessVo.getIndividualBusinessConstract();
                EnterpriseConstractConfigPo constractConfig = businessVo.getEnterpriseConstractConfig();
                Row row = sheet.createRow(i);
                //姓名
                if (business.getName() != null){
                    Cell cell0 = row.createCell(0);
                    cell0.setCellValue(business.getName());
                }
                //身份证号码
                if (business.getIdcard() != null){
                    //解密
                    String idcard = RSAUtils.RSADecode(business.getIdcard());
                    row.createCell(1).setCellValue(idcard);
                }
                //字号
                if (constract.getIndividualNumber() != null){
                    row.createCell(2).setCellValue(constract.getIndividualNumber());
                }
                //联系电话
                if (business.getPhone() != null){
                    row.createCell(3).setCellValue(business.getPhone());
                }
                //经营范围名称
                if (constractConfig.getBusinessScopeName() != null){
                    row.createCell(4).setCellValue(constractConfig.getBusinessScopeName());
                }
                //经营范围编码
                if (constractConfig.getBusinessScopeCode() != null){
                    row.createCell(5).setCellValue(constractConfig.getBusinessScopeCode());
                }
                //集群地址
                if (constract.getRegAddr() != null){
                    row.createCell(6).setCellValue(constract.getRegAddr());
                }
                //原住所地址
                if (business.getAddress() != null){
                    row.createCell(7).setCellValue(business.getAddress());
                }
                //用户邮箱
                if (business.getEmail() != null){
                    row.createCell(8).setCellValue(business.getEmail());
                }
                //经营网络地址
                if (constractConfig.getAddrWeb() != null){
                    row.createCell(9).setCellValue(constractConfig.getAddrWeb());
                }
                //组织形式编码
                if (constractConfig.getOrgCode() != null){
                    row.createCell(10).setCellValue(constractConfig.getOrgCode());
                }
                //组织形式编码名称
                if (constractConfig.getOrgName() != null){
                    row.createCell(11).setCellValue(constractConfig.getOrgName());
                }
                //行业表述编码
                if (constractConfig.getIndustryCode() != null){
                    row.createCell(12).setCellValue(constractConfig.getIndustryCode());
                }
                //行业表述名称
                if (constractConfig.getIndustryName() != null){
                    row.createCell(13).setCellValue(constractConfig.getIndustryName());
                }
                //行政区划编码
                if (constractConfig.getAreaCode() != null){
                    row.createCell(14).setCellValue(constractConfig.getAreaCode());
                }
                //行政区划名称
                if (constractConfig.getAreaName() != null){
                    row.createCell(15).setCellValue(constractConfig.getAreaName());
                }
                constract.setExpFlag("N");
                constractPoList.add(constract);
            }
            //更新导出标识
            if (!CollectionUtils.isEmpty(constractPoList)){
                constractMapper.updateList(constractPoList);
            }
            OutputStream output = response.getOutputStream();
            response.reset();

            String fileName = "export.xls";
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));

            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}