2021-07-01-apache的POI框架创建EXCEL

POI创建EXCEL

1 POI依赖

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

创建Excel

 /**
     * 导出(本月/本周/本季度/本年度)的项目统计表格
     *
     * @author Sxing
     * @since 2021年8月20日15:21:50
     */
    public void downloadJyzxSectionExcel(HttpServletRequest request, HttpServletResponse response, Short cycle) throws Exception{
        List<GpSystemNoticeGetBO> list =  gpSystemNoticeService.findDateSection(cycle).getData();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet  =  workbook.createSheet("项目统计表");

        //设置单元格宽度（第一个参数代表列的位置，从0开始；第二个参数代表需要设置的宽度）
        sheet.setColumnWidth(0, 12 * 256);
        sheet.setColumnWidth(1, 13 * 256);
        sheet.setColumnWidth(2, 11 * 256);
        sheet.setColumnWidth(3, 10 * 256);
        sheet.setColumnWidth(4, 11 * 256);
        sheet.setColumnWidth(5, 11 * 256);
        sheet.setColumnWidth(6, 11 * 256);
        sheet.setColumnWidth(7, 15 * 256);
        sheet.setColumnWidth(8, 23 * 256);
        sheet.setColumnWidth(9, 15 * 256);
        sheet.setColumnWidth(10, 11 * 256);
        sheet.setColumnWidth(11, 11 * 256);
        sheet.setColumnWidth(12, 10 * 256);
        sheet.setColumnWidth(13, 10 * 256);
        sheet.setColumnWidth(14, 15 * 256);
        sheet.setColumnWidth(15, 12 * 256);
        sheet.setColumnWidth(16, 16 * 256);

        //----------------标题样式---------------------
        HSSFCellStyle titleStyle = workbook.createCellStyle();        //标题样式
        titleStyle.setAlignment(HorizontalAlignment.CENTER); //设置单元格的水平对齐类型<居中>
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型<居中>

        titleStyle.setBorderBottom(BorderStyle.MEDIUM); //底部边框
        titleStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//底部边框颜色

        titleStyle.setBorderLeft(BorderStyle.MEDIUM); //左边框
        titleStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//左边框颜色

        titleStyle.setBorderTop(BorderStyle.MEDIUM); //上
        titleStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        titleStyle.setBorderRight(BorderStyle.MEDIUM);//右
        titleStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());


        HSSFFont font = workbook.createFont();
        font.setItalic(false);                     // 设置字体为斜体字
        font.setColor(Font.COLOR_NORMAL);            // 将字体设置为“黑色”
        font.setFontHeightInPoints((short)20);    // 将字体大小设置为18px
        font.setFontName("Helvetica Neue");         //字体样式
        font.setBold(false);                         // 加粗
        //font.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
        //font.setStrikeout(true);                  // 是否添加删除线
        titleStyle.setFont(font);
        //----------------表头样式----------------------------------------------------
        HSSFCellStyle titleStyle1 = workbook.createCellStyle();        //标题样式
        titleStyle1.setAlignment(HorizontalAlignment.CENTER);//设置单元格的水平对齐类型<居中>
        titleStyle1.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型<居中>

        titleStyle1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //设置背景色
        titleStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置为1时，填充单元格颜色

        titleStyle1.setBorderBottom(BorderStyle.MEDIUM); //底部边框
        titleStyle1.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//底部边框颜色

        titleStyle1.setBorderLeft(BorderStyle.MEDIUM); //左边框
        titleStyle1.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//左边框颜色

        titleStyle1.setBorderTop(BorderStyle.MEDIUM); //上
        titleStyle1.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        titleStyle1.setBorderRight(BorderStyle.MEDIUM);//右
        titleStyle1.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        HSSFFont  font1 = workbook.createFont();
        font1.setItalic(false);                     // 设置字体为斜体字
        font1.setColor(Font.COLOR_NORMAL);            // 将字体设置为“黑色”
        font1.setFontHeightInPoints((short)10);    // 将字体大小设置为18px
        font1.setFontName("Helvetica Neue");
        font1.setBold(true);// 加粗
        //font.setUnderline(Font.U_DOUBLE);         // 添加（Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线）
        //font.setStrikeout(true);                  // 是否添加删除线
        titleStyle1.setFont(font1);
        //----------------单元格样式----------------------------------
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //自动换行*重要*
        cellStyle.setWrapText(true);
        //表格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置单元格的水平对齐类型<居中>
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型<居中>
        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //底部边框
        cellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//底部边框颜色

        cellStyle.setBorderLeft(BorderStyle.MEDIUM); //左边框
        cellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());//左边框颜色

        cellStyle.setBorderTop(BorderStyle.MEDIUM); //上
        cellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        cellStyle.setBorderRight(BorderStyle.MEDIUM);//右
        cellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        Font cellFont = workbook.createFont();
        cellFont.setColor(Font.COLOR_NORMAL);            // 将字体设置为“黑色”
        cellFont.setFontHeightInPoints((short)10);    // 将字体大小设置为18px
        cellFont.setFontName("宋体");             // 字体应用到当前单元格上
        cellStyle.setFont(cellFont);
        //----------------开始创建Excel----------------------------------
        //获取第一行
        HSSFRow row1 = sheet.createRow(0);
        row1.setHeightInPoints(36);//目的是想把行高设置成20px
        HSSFCell cell = row1.createCell(0);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0,16);
        sheet.addMergedRegion(region);
        cell.setCellValue("项目统计表");
        cell.setCellStyle(titleStyle);

        //第2行
        HSSFRow row2 = sheet.createRow(1);
        row2.setHeightInPoints(25);//目的是想把行高设置成20px
        //第1列
        HSSFCell row2Cell1 = row2.createCell(0);
        row2Cell1.setCellValue("运营商");
        row2Cell1.setCellStyle(titleStyle1);
        //第2列
        HSSFCell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue("代理机构");
        row2Cell2.setCellStyle(titleStyle1);
        //第3列
        HSSFCell row2Cell3 = row2.createCell(2);
        row2Cell3.setCellValue("项目编号");
        row2Cell3.setCellStyle(titleStyle1);
        //第4列
        HSSFCell row2Cell4 = row2.createCell(3);
        row2Cell4.setCellValue("时间");
        row2Cell4.setCellStyle(titleStyle1);
        //第5列
        HSSFCell row2Cell5 = row2.createCell(4);
        row2Cell5.setCellValue("名称");
        row2Cell5.setCellStyle(titleStyle1);
        //第6列
        HSSFCell row2Cell6 = row2.createCell(5);
        row2Cell6.setCellValue("标的额");
        row2Cell6.setCellStyle(titleStyle1);
        //第7列
        HSSFCell row2Cell7 = row2.createCell(6);
        row2Cell7.setCellValue("区域");
        row2Cell7.setCellStyle(titleStyle1);
        //第8列
        HSSFCell row2Cell8 = row2.createCell(7);
        row2Cell8.setCellValue("投标人名称");
        row2Cell8.setCellStyle(titleStyle1);
        //第9列
        HSSFCell row2Cell9 = row2.createCell(8);
        row2Cell9.setCellValue("组织机构代码证");
        row2Cell9.setCellStyle(titleStyle1);
        //第10列
        HSSFCell row2Cell10 = row2.createCell(9);
        row2Cell10.setCellValue("付费时间");
        row2Cell10.setCellStyle(titleStyle1);
        //第11列
        HSSFCell row2Cell11 = row2.createCell(10);
        row2Cell11.setCellValue("付费方式");
        row2Cell11.setCellStyle(titleStyle1);
        //第12列
        HSSFCell row2Cell12 = row2.createCell(11);
        row2Cell12.setCellValue("付费账号名");
        row2Cell12.setCellStyle(titleStyle1);
        //第13列
        HSSFCell row2Cell13 = row2.createCell(12);
        row2Cell13.setCellValue("付费金额");
        row2Cell13.setCellStyle(titleStyle1);
        //第14列
        HSSFCell row2Cell14 = row2.createCell(13);
        row2Cell14.setCellValue("联系人");
        row2Cell14.setCellStyle(titleStyle1);
        //第15列
        HSSFCell row2Cell15 = row2.createCell(14);
        row2Cell15.setCellValue("电话");
        row2Cell15.setCellStyle(titleStyle1);
        //第16列
        HSSFCell row2Cell16 = row2.createCell(15);
        row2Cell16.setCellValue("合计");
        row2Cell16.setCellStyle(titleStyle1);
        //第17列
        HSSFCell row2Cell17 = row2.createCell(16);
        row2Cell17.setCellValue("备注");
        row2Cell17.setCellStyle(titleStyle1);

        //
        int rownum = 2;
        for (GpSystemNoticeGetBO gpnoticeBo : list){
            List<BbPayInfoQueryBO> rtList = bbPayInfoServiceImpl.getPlatformFeeInfo(gpnoticeBo.getGsId(),
                BbPayTypeEnum.PLATFORM_FEE.getValue()).getData();
            int regionNum = rownum;
            //缴费金额合计
            BigDecimal payAmountCount = new BigDecimal("0.00");

            for(BbPayInfoQueryBO payInfo : rtList){
                payAmountCount = payAmountCount.add(payInfo.getPayAmount());
            }

            for (int i = 0; i<rtList.size(); i++){
//                if(i==0){
//                    payAmountCount = rtList.get(i).getPayAmount();
//                }else {
//                    payAmountCount = payAmountCount.add(rtList.get(i).getPayAmount());
//                }
                //获取组织机构的统一社会信用代码
                ApCompanyGetBO companyBO = apCompanyServiceImpl.getById(rtList.get(i).getPersonCoId()).getData();

                //通过行政区划ID获取完整名称 获取区域名称
                String regionName = apZoningServiceImpl.getFullName(gpnoticeBo.getRegionId()).getData();

                //缴费方式 微信/支付宝
                String payType = "";
                if (rtList.get(i).getPayType() == BbPaymentEnum.ALI_PAY.getValue() ){
                    payType = "支付宝";
                }else if(rtList.get(i).getPayType() == BbPaymentEnum.WX_PAY.getValue()){
                    payType = "微信";
                }

                //创建行
                HSSFRow rowi = sheet.createRow(rownum+i);
                rowi.setHeightInPoints(30);//目的是想把行高设置成30px

                //第1列
                HSSFCell rowiCell1 = rowi.createCell(0);
                rowiCell1.setCellValue("");//运营商
                rowiCell1.setCellStyle(cellStyle);

                //第2列
                HSSFCell rowiCell2 = rowi.createCell(1);
                rowiCell2.setCellValue(gpnoticeBo.getAgentName());//代理机构
                rowiCell2.setCellStyle(cellStyle);

                //第3列
                HSSFCell rowiCell3 = rowi.createCell(2);
                rowiCell3.setCellValue(gpnoticeBo.getPublicGtpCode());//项目编号
                rowiCell3.setCellStyle(cellStyle);

                HSSFCell rowiCell4 = rowi.createCell(3);
                rowiCell4.setCellValue("");//时间
                rowiCell4.setCellStyle(cellStyle);

                HSSFCell rowiCell5 = rowi.createCell(4);
                rowiCell5.setCellValue(gpnoticeBo.getGsName());//名称
                rowiCell5.setCellStyle(cellStyle);

                HSSFCell rowiCell6 = rowi.createCell(5);
                rowiCell6.setCellValue(gpnoticeBo.getEstimatedPrice().toString());//标的额
                rowiCell6.setCellStyle(cellStyle);

                HSSFCell rowiCell7 = rowi.createCell(6);
                rowiCell7.setCellValue(regionName); //区域
                rowiCell7.setCellStyle(cellStyle);

                HSSFCell rowiCell8 = rowi.createCell(7);
                rowiCell8.setCellValue(rtList.get(i).getPersonCoName());//投标人名称
                rowiCell8.setCellStyle(cellStyle);

                HSSFCell rowiCell9 = rowi.createCell(8);
                rowiCell9.setCellValue(companyBO.getCreditCode());//组织机构代码证(// 统一社会信用代码)
                rowiCell9.setCellStyle(cellStyle);

                HSSFCell rowiCell10 = rowi.createCell(9);
                rowiCell10.setCellValue(rtList.get(i).getPayTime());//付费时间
                rowiCell10.setCellStyle(cellStyle);

                HSSFCell rowiCell11 = rowi.createCell(10);
                rowiCell11.setCellValue(payType);//付费方式
                rowiCell11.setCellStyle(cellStyle);

                HSSFCell rowiCell12 = rowi.createCell(11);
                rowiCell12.setCellValue("");//付费账号名
                rowiCell12.setCellStyle(cellStyle);

                HSSFCell rowiCell13 = rowi.createCell(12);
                rowiCell13.setCellValue(rtList.get(i).getPayAmount().toString()); //付费金额
                rowiCell13.setCellStyle(cellStyle);

                HSSFCell rowiCell14 = rowi.createCell(13);
                rowiCell14.setCellValue(rtList.get(i).getPersonName()); //联系人
                rowiCell14.setCellStyle(cellStyle);

                HSSFCell rowiCell15 = rowi.createCell(14);
                rowiCell15.setCellValue(rtList.get(i).getPersonPhone());//电话
                rowiCell15.setCellStyle(cellStyle);

                HSSFCell rowiCell16 = rowi.createCell(15);
                rowiCell16.setCellValue(payAmountCount.toString());//合计
                rowiCell16.setCellStyle(cellStyle);

                HSSFCell rowiCell17 = rowi.createCell(16);
                rowiCell17.setCellValue("");//备注
                rowiCell17.setCellStyle(cellStyle);

                regionNum = regionNum++;
            }
            if(CollectionUtils.isNotEmpty(rtList) && rtList.size() > 1){
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,0,0));  //先合并第一行第1列和第二行第1列
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,1,1));  //合并第一行第2列和第二行第2列
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,2,2));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,3,3));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,4,4));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,5,5));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,6,6));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,15,15));
                sheet.addMergedRegion(new CellRangeAddress(regionNum,regionNum+rtList.size()-1,16,16));
            }
             rownum = rownum+rtList.size();
        }
        try {
             // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition",
                "attachment; filename=projectCount.xls");    		//filename =  文件名 会出现中文不显示
            response.setContentType("application/msexcel");
            workbook.write(output);
            output.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

效果图