poi导出excel的格式设置

PoiExpXls.java

public void onExpExcel() {
        String[] excelHeader4 = {"代号", "名称", "金额", "代号", "名称", "金额"};
        String[] excelHeader3 = {"支出项目", "收入项目"};
        List<T623.Bean> dataList6 = new ArrayList<>();
        List<T623.Bean> dataList5 = new ArrayList<>();
        BigDecimal totexp = null;//支出合计
        BigDecimal totrev = null;//收入合计
        BigDecimal bene = null;//结益
        BigDecimal loss = null;//结损
        for (T623.Bean datas : dataList) {
            if ("2".equals(datas.getPLTYPE())) {
                if ("3".equals(datas.getPLCLAS())) {
                    totexp = datas.getPLAMNT();//支出合计
                } else if ("4".equals(datas.getPLCLAS())) {
                    bene = datas.getPLAMNT();//结益
                } else {
                    dataList6.add(datas);
                }
            } else if ("1".equals(datas.getPLTYPE())) {
                if ("3".equals(datas.getPLCLAS())) {
                    totrev = datas.getPLAMNT();//收入合计
                } else if ("4".equals(datas.getPLCLAS())) {
                    loss = datas.getPLAMNT();//结损
                } else {
                    dataList5.add(datas);
                }
            }
        }
        try {
            FacesContext ctx = FacesContext.getCurrentInstance();
            HttpServletResponse response = (HttpServletResponse) ctx.getExternalContext().getResponse();
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("acctab");
            HSSFRow row = null;

            HSSFFont font = wb.createFont();
            font.setFontName("Arial");                          //字体
            font.setFontHeightInPoints((short) 10);             //字体大小
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);      //粗体

            HSSFCellStyle styleHeader = wb.createCellStyle();    //header设置
            styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
            styleHeader.setFont(font);
            styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //左边框
            styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);   //右边框
            styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);    //上
            styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下

            HSSFCellStyle styleLeft = wb.createCellStyle();
            styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);  //居左
            styleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //左边框
            styleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);   //右边框
            styleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);    //上边框
            styleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下

            HSSFCellStyle styleCenter = wb.createCellStyle();
            styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);  //居中
            styleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //左边框
            styleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);   //右边框
            styleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);    //上边框
            styleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下

            HSSFCellStyle styleRight = wb.createCellStyle();
            styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);    //居右
            styleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);   //左边框
            styleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);   //右边框
            styleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);    //上边框
            styleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下

            //第一行
            row = sheet.createRow((int) 0);
            for (int i = 0; i < 1; i++) {
                row.setHeight((short) 400);     //行高
                HSSFFont font0 = wb.createFont();
                font0.setFontName("Arial");                          //字体
                font0.setFontHeightInPoints((short) 12);             //字体大小
                font0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);      //粗体
                HSSFCellStyle styleHeader0 = wb.createCellStyle();    //header设置
                styleHeader0.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
                styleHeader0.setFont(font0);
                //styleHeader0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 5));
                HSSFCell cell = row.createCell(i);
                cell.setCellValue("损益表");
                cell.setCellStyle(styleHeader0);
            }

            //第二行
            SimpleDateFormat smt = new SimpleDateFormat("yyyy年MM月dd日");
            row = sheet.createRow((int) 1);
            for (int i = 0; i < 6; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(styleHeader);
                if (i == 0) {
                    cell.setCellValue("货币：");
                } else if (i == 1) {
                    if ("001".equals(curcde)) {
                        cell.setCellValue("001-人民币");
                    } else if ("014".equals(curcde)) {
                        cell.setCellValue("014-各外币折美元");
                    } else {
                        cell.setCellValue("999-各货币折人民币");
                    }
                } else if (i == 2) {
                    cell.setCellValue(smt.format(new Date()));
                } else if (i == 5) {
                    cell.setCellValue("单位：元");
                }
            }

            //第三行
            row = sheet.createRow((int) 2);
            for (int i = 0; i < excelHeader3.length; i++) {
                if (i == 0) {
                    Region region2row = new Region(2, (short) 0, 2, (short) 2);
                    //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                    sheet.addMergedRegion(region2row);
                    HSSFCell cell = row.createCell(i);
                    cell.setCellValue(excelHeader3[i]);
                    cell.setCellStyle(styleHeader);
                } else if (i == 1) {
                    Region region2row = new Region(2, (short) 3, 2, (short) 5);
                    //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
                    sheet.addMergedRegion(region2row);
                    HSSFCell cell = row.createCell(3);
                    cell.setCellValue(excelHeader3[i]);
                    cell.setCellStyle(styleHeader);
                }
            }

            row = sheet.createRow((int) 3);
            for (int i = 0; i < excelHeader4.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader4[i]);
                cell.setCellStyle(styleHeader);
                sheet.autoSizeColumn(i);
            }

            for (int i = 0; i < dataList6.size(); i++) {
                row = sheet.createRow(i + 4);
                T623.Bean bean = dataList6.get(i);
                if ("2".equals(bean.getPLCLAS())) {
                    row.createCell(0).setCellValue(bean.getPLCODE());
                    sheet.getRow(i + 4).getCell(0).setCellStyle(styleCenter);
                    sheet.setColumnWidth(0, 3000);  //设置列宽
                } else {
                    row.createCell(0).setCellValue(bean.getPLCODE());
                    sheet.getRow(i + 4).getCell(0).setCellStyle(styleRight);
                    sheet.setColumnWidth(0, 3000);
                }
                row.createCell(1).setCellValue(bean.getPLNAME());
                sheet.getRow(i + 4).getCell(1).setCellStyle(styleLeft);
                sheet.setColumnWidth(1, 8000);

                row.createCell(2).setCellValue(bean.getPLAMNT().toString());
                sheet.getRow(i + 4).getCell(2).setCellStyle(styleRight);
                sheet.setColumnWidth(2, 6000);
            }

            for (int i = 0; i < dataList5.size(); i++) {
                if (dataList5.size() > dataList6.size()) {
                    if (i < dataList6.size()) {
                        row = sheet.getRow(i + 4);
                    } else {
                        row = sheet.createRow(i + 4);
                    }
                    T623.Bean bean = dataList5.get(i);
                    if ("2".equals(bean.getPLCLAS())) {
                        row.createCell(3).setCellValue(bean.getPLCODE());
                        sheet.getRow(i + 4).getCell(3).setCellStyle(styleCenter);
                        sheet.setColumnWidth(3, 3000);
                    } else {
                        row.createCell(3).setCellValue(bean.getPLCODE());
                        sheet.getRow(i + 4).getCell(3).setCellStyle(styleRight);
                        sheet.setColumnWidth(3, 3000);
                    }
                    row.createCell(4).setCellValue(bean.getPLNAME());
                    sheet.getRow(i + 4).getCell(4).setCellStyle(styleLeft);
                    sheet.setColumnWidth(4, 8000);

                    row.createCell(5).setCellValue(bean.getPLAMNT().toString());
                    sheet.getRow(i + 4).getCell(5).setCellStyle(styleRight);
                    sheet.setColumnWidth(5, 6000);
                } else {
                    row = sheet.getRow(i + 4);
                    T623.Bean bean = dataList5.get(i);
                    if ("2".equals(bean.getPLCLAS())) {
                        row.createCell(3).setCellValue(bean.getPLCODE());
                        sheet.getRow(i + 4).getCell(3).setCellStyle(styleLeft);
                        sheet.setColumnWidth(3, 3000);
                    } else {
                        row.createCell(3).setCellValue(bean.getPLCODE());
                        sheet.getRow(i + 4).getCell(3).setCellStyle(styleRight);
                        sheet.setColumnWidth(3, 3000);
                    }
                    row.createCell(4).setCellValue(bean.getPLNAME());
                    sheet.getRow(i + 4).getCell(4).setCellStyle(styleLeft);
                    sheet.setColumnWidth(4, 8000);

                    row.createCell(5).setCellValue(bean.getPLAMNT().toString());
                    sheet.getRow(i + 4).getCell(5).setCellStyle(styleRight);
                    sheet.setColumnWidth(5, 6000);
                }
            }

            if (dataList5.size() > dataList6.size()) {

                row = sheet.createRow(dataList5.size() + 4);
                row.createCell(1).setCellValue("支出合计:");
                row.getCell(1).setCellStyle(styleLeft);
                row.createCell(2).setCellValue(totexp.toString());
                row.getCell(2).setCellStyle(styleRight);
                row.createCell(4).setCellValue("收入合计:");
                row.getCell(4).setCellStyle(styleLeft);
                row.createCell(5).setCellValue(totrev.toString());
                row.getCell(5).setCellStyle(styleRight);

                row = sheet.createRow(dataList5.size() + 5);
                row.createCell(1).setCellValue("结益:");
                row.getCell(1).setCellStyle(styleLeft);
                row.createCell(2).setCellValue(bene.toString());
                row.getCell(2).setCellStyle(styleRight);
                row.createCell(4).setCellValue("结损:");
                row.getCell(4).setCellStyle(styleLeft);
                row.createCell(5).setCellValue(loss.toString());
                row.getCell(5).setCellStyle(styleRight);

            }
            OutputStream ouputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode("SBS损益表.xls", "UTF-8"));
            wb.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }