POI EXCEL 文件导出

来源：互联网发布：yum brand 编辑：程序博客网时间：2024/04/27 16:14
poi excel 文件如果量过大的话，会导致java内存溢出错误，我的代码如下：
   /**     * @param workbook     * @param fileType     */    public void getAndFillSheet(Workbook workbook, Integer fileType, Map<String, XSSFCellStyle> styleMap, Map map, boolean Multi) {        if (workbook != null && fileType != null && styleMap != null && map != null) {            switch (fileType) {                case 1: {                    //生成月份报表//                List<BiReportProductWeeklySalesBean> model4 = getProductWeeklySales();                    Sheet sheet = workbook.createSheet(ISheetInfo.SHOP.SHOPMONTHLY.headers1[0]);                    if (Multi) {                        sheet = biRepExcelFileCreator.createExcelFile(getMultiAllMonthlyData(map), getMMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.MSHOP.MSHOPMONTHLY.headers1, ISheetInfo.MSHOP.MSHOPMONTHLY.headers2, ISheetInfo.MSHOP.MSHOPMONTHLY.headers3);                    } else {                        sheet = biRepExcelFileCreator.createExcelFile(getShopMonthly(map), getMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.SHOP.SHOPMONTHLY.headers1, ISheetInfo.SHOP.SHOPMONTHLY.headers2, ISheetInfo.SHOP.SHOPMONTHLY.headers3);                    }                }                break;                case 2: {                    //生成周份表                    Sheet sheet = workbook.createSheet(ISheetInfo.SHOP.SHOPWEEKLY.headers1[0]);                    if (Multi) {                        sheet = biRepExcelFileCreator.createExcelFile(getMultiAllWeeklyData(map), getMMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.MSHOP.MSHOPWEEKLY.headers1, ISheetInfo.MSHOP.MSHOPWEEKLY.headers2, ISheetInfo.MSHOP.MSHOPWEEKLY.headers3);                    } else {                        sheet = biRepExcelFileCreator.createExcelFile(getShopWeeklySales(map), getMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.SHOP.SHOPWEEKLY.headers1, ISheetInfo.SHOP.SHOPWEEKLY.headers2, ISheetInfo.SHOP.SHOPWEEKLY.headers3);                    }                }                break;                case 3: {//                List<BiReportShopDailySalesReportBean> model = biReportSalesShopDaoExt.selectListByDate(map);                    //生成日表报表,getData(para) important! dealSaleShopModel(null)                    Sheet sheet = workbook.createSheet(ISheetInfo.SHOP.SHOPDAILY.headers1[0]);                    if (Multi) {                        sheet = biRepExcelFileCreator.createExcelFile((getMultiAllDailyData(map)), getMMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.MSHOP.MSHOPDAILY.headers1, ISheetInfo.MSHOP.MSHOPDAILY.headers2, ISheetInfo.MSHOP.MSHOPDAILY.headers3);                    } else {                        sheet = biRepExcelFileCreator.createExcelFile((dealSaleShopModel(getData(map), map)), getMergeInfo(fileType), sheet, styleMap,                                ISheetInfo.SHOP.SHOPDAILY.headers1, ISheetInfo.SHOP.SHOPDAILY.headers2, ISheetInfo.SHOP.SHOPDAILY.headers3);                    }                }                break;                case 4: {                    List<BiReportProductMonthlySalesBean> model = getProductMonthly(map);//商品月报                    Sheet sheet = workbook.createSheet(ISheetInfo.MODEL.MODELMONTHLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(getModelMonthly(map), getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.MODEL.MODELMONTHLY.headers1, ISheetInfo.MODEL.MODELMONTHLY.headers2, ISheetInfo.MODEL.MODELMONTHLY.headers3);                }                break;                case 5: {                    List<BiReportProductWeeklySalesBean> model4 = getProductWeeklySales(map);//商品周报                    Sheet sheet = workbook.createSheet(ISheetInfo.MODEL.MODELWEEKLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(getModelWeeklySales(map), getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.MODEL.MODELWEEKLY.headers1, ISheetInfo.MODEL.MODELWEEKLY.headers2, ISheetInfo.MODEL.MODELWEEKLY.headers3);                }                break;                case 6: {//                List<BiReportProductDailySalesBean> model = biReportSalesProductDaoExt.selectListByDate(map);//商品日报                    Sheet sheet = workbook.createSheet(ISheetInfo.MODEL.MODELDAILY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(getModelDaily(map), getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.MODEL.MODELDAILY.headers1, ISheetInfo.MODEL.MODELDAILY.headers2, ISheetInfo.MODEL.MODELDAILY.headers3);                }                break;                case 7: {                    List<BiReportProductMonthlySalesBean> model = getProductMonthly(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.PRODUCT.PRODUCTMONTHLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(model, getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.PRODUCT.PRODUCTMONTHLY.headers1, ISheetInfo.PRODUCT.PRODUCTMONTHLY.headers2, ISheetInfo.PRODUCT.PRODUCTMONTHLY.headers3);                }                break;                case 8: {                    List<BiReportProductWeeklySalesBean> model4 = getProductWeeklySales(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.PRODUCT.PRODUCTWEEKLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(model4, getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.PRODUCT.PRODUCTWEEKLY.headers1, ISheetInfo.PRODUCT.PRODUCTWEEKLY.headers2, ISheetInfo.PRODUCT.PRODUCTWEEKLY.headers3);                }                break;                case 9: {//                List<BiReportProductDailySalesBean> model = biReportSalesProductDaoExt.selectListByDate(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.PRODUCT.PRODUCTDAILY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(getProductDaily(map), getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.PRODUCT.PRODUCTDAILY.headers1, ISheetInfo.PRODUCT.PRODUCTDAILY.headers2, ISheetInfo.PRODUCT.PRODUCTDAILY.headers3);                }                break;                case 10: {                    List<BiReportSKUMonthlySalesBean> model = getSkuMonthly(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.SKU.SKUMONTHLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(model, getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.SKU.SKUMONTHLY.headers1, ISheetInfo.SKU.SKUMONTHLY.headers2, ISheetInfo.SKU.SKUMONTHLY.headers3);                }                break;                case 11: {                    List<BiReportSKUWeeklySalesBean> model = getSkuWeeklySales(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.SKU.SKUWEEKLY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(model, getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.SKU.SKUWEEKLY.headers1, ISheetInfo.SKU.SKUWEEKLY.headers2, ISheetInfo.SKU.SKUWEEKLY.headers3);                }                break;                case 12: {//                List<BiReportSKUDailySalesBean> model = biReportSalesSKUDaoExt.selectListByDate(map);                    Sheet sheet = workbook.createSheet(ISheetInfo.SKU.SKUDAILY.headers1[0]);                    sheet = biRepExcelFileCreator.createExcelFile(getSkuDaily(map), getMergeInfo(fileType), sheet, styleMap,                            ISheetInfo.SKU.SKUDAILY.headers1, ISheetInfo.SKU.SKUDAILY.headers2, ISheetInfo.SKU.SKUDAILY.headers3);                }                break;                default: {                }                break;            }        } else {            $info(PARAERROR);        }    }
由于一次性生成的poi文件过大，会导致程序查询到数据后将数据放到workbook对象时会出现内存溢出错误！
解决方案：限制一次性生成的文件的量，由于生成workbook（poi）对象存储在内存中，要判断excel数据量的同时，及时从内存中将数据输出！
0 0