poi导出大量excel以及表格属性设置

1、列宽

https://blog.csdn.net/duqian42707/article/details/51491312

sheet.setColumnWidth(i,24*256+184);
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);

2、样式

DataFormat dataFormat=wb.createDataFormat();
CellStyle timeSyle=wb.createCellStyle();
timeSyle.setDataFormat(dataFormat.getFormat("yyyy/m/d h:mm:ss"));
setCellValue(double)

3、大于6万多行时,使用SXSSFWorkbook

比如

  @Override
    public SXSSFWorkbook exportProjectAllList(FindProjectManageAllListReq req) throws ServicesException, ParseException {

        List<ProjectManageAllList> list = findProjectManageAllListForExcel(req);
//        List<FindLoanSuccessListResp> list = baseDao.findAllIsPageByCustom(event);

        // 第一步,创建一个webbook,对应一个Excel文件
        XSSFWorkbook wb1 = new XSSFWorkbook();
        SXSSFWorkbook wb = new SXSSFWorkbook(wb1,100);
        // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
        SXSSFSheet sheet = wb.createSheet("运营统计报表");

        // 第三步,在sheet中添加表头第0,注意老版本poiExcel的行数列数有限制short
        SXSSFRow row = sheet.createRow(0);

        // 第四步,创建单元格,并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
        style.setWrapText(false);

        String[] head={"序号 No","项目编号   Order ID","流水号 flow no","用户名   User Name","姓名   Name",//5
                "手机号   phone","申请金额   Apply amount","放款金额   Lending Amount","借款期限   tenor","放款日期   Lending Date",
                "应还款日   Repay Date","应还本息   Repay Amount","已还金额 payed amount",  "结清时间 settle date","资金名称 fund name",
                "逾期天数 overdue day","逾期罚息 overdue fee","本期应还总金额 current Total"//18
        };
        int indexHead=0;

        short colNum=0;
        SXSSFCell cell=null;
        style.setWrapText(false);

        DataFormat dataFormat=wb.createDataFormat();
        CellStyle timeSyle=wb.createCellStyle();
        timeSyle.setDataFormat(dataFormat.getFormat("yyyy/m/d h:mm:ss"));

        DataFormat dataFormat2=wb.createDataFormat();
        CellStyle dateSyle=wb.createCellStyle();
        dateSyle.setDataFormat(dataFormat2.getFormat("yyyy/m/d"));

        for(int i=0;i<head.length;i++){
            cell = row.createCell(colNum++);
            cell.setCellValue(new XSSFRichTextString(head[indexHead++]));
            cell.setCellStyle(style);
//            sheet.setColumnWidth(i,24*256+184);
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);
        }

        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd");

        for (int i = 0; i < list.size(); i++)
        {
            colNum=0;
            row = sheet.createRow(i + 1);
            ProjectManageAllList bus = list.get(i);

            row.createCell(colNum++).setCellValue((double)i + 1);
            row.createCell(colNum++).setCellValue(bus.getNumber());
            row.createCell(colNum++).setCellValue(bus.getFlowNo());
            row.createCell(colNum++).setCellValue(bus.getLoginname());
            row.createCell(colNum++).setCellValue(bus.getRealname());

            row.createCell(colNum++).setCellValue(bus.getPhone());
//
            row.createCell(colNum++).setCellValue(bus.getResalePriceAsk()==null?0:Double.valueOf(bus.getResalePriceAsk()));
            row.createCell(colNum++).setCellValue(bus.getFirstPriceLoan()==null?0:Double.valueOf(bus.getFirstPriceLoan()));
            row.createCell(colNum++).setCellValue(bus.getDeadline());

            cell=row.createCell(colNum++);
            cell.setCellStyle(timeSyle);
            cell.setCellValue(bus.getLoanTime()==null?null:dateFormat.parse(bus.getLoanTime()));

            cell=row.createCell(colNum++);
            cell.setCellStyle(dateSyle);
            cell.setCellValue(bus.getRepaymentDate()==null?null:dateFormat2.parse(bus.getRepaymentDate()));

            row.createCell(colNum++).setCellValue(bus.getCurrentBalance()==null?0:Double.valueOf(bus.getCurrentBalance()));
            row.createCell(colNum++).setCellValue(bus.getAlreadyBalance()==null?0:Double.valueOf(bus.getAlreadyBalance()));

            cell=row.createCell(colNum++);
            cell.setCellStyle(timeSyle);
            cell.setCellValue(bus.getRealityDate()==null?null:dateFormat.parse(bus.getRealityDate()));

            row.createCell(colNum++).setCellValue(bus.getFundsName());

            row.createCell(colNum++).setCellValue(bus.getOverdueDay()==null?0:Integer.valueOf(bus.getOverdueDay()));
            row.createCell(colNum++).setCellValue(bus.getCurrentOverdue()==null?0:Double.valueOf(bus.getCurrentOverdue()));
            row.createCell(colNum++).setCellValue(bus.getCurrentTotal()==null?0:Double.valueOf(bus.getCurrentTotal()));

        }
        return wb;

    }

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页