使用NPOI或POI 导出Excel大数据(百万级以上)，导致内存溢出的解决方案（NPOI，POI）
参考文章：
 
（1）使用NPOI或POI 导出Excel大数据(百万级以上)，导致内存溢出的解决方案（NPOI，POI）
 
（2）https://www.cnblogs.com/daxiongblog/p/11388729.html
 
备忘一下。

POI之前的版本不支持大数据量处理 如果数据过多则经常报OOM错误，有时候调整JVM大小效果也不是太好。3.8版本的POI新出来了SXSSFWorkbook,可以支持大数据量的操作，只是SXSSFWorkbook只支持.xlsx格式，不支持.xls格式。 

3.8版本的POI对excel的导出操作，一般只使用HSSFWorkbook以及SXSSFWorkbook，HSSFWorkbook用来处理较少的数据量，SXSSFWorkbook用来处理大数据量以及超大数据量的导出。 

 HSSFWorkbook的使用方法和之前的版本的使用方法一致，这里就不在陈述使用方法了 

SXSSFWorkbook的使用例子如下：



    public static void downExcel(String pathUrl) throws Exception{
        File f = new File(pathUrl);
        FileOutputStream out = new FileOutputStream(f);
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("第一页");
        for (int i =0;i<1000;i++){
            Row row = sheet.createRow(i);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue("小时候可牛了"+i);
            Cell cell1 = row.createCell(1);
            cell1.setCellValue("小时候可帅了"+i);
            Cell cell2 = row.createCell(2);
            cell2.setCellValue("小时候可牛了"+i);
            Cell cell4 = row.createCell(3);
            cell4.setCellValue("小时候可牛了"+i);
            Cell cell5 = row.createCell(4);
            cell5.setCellValue("小时候可牛了"+i);
            Cell cell6 = row.createCell(5);
            cell6.setCellValue("小时候可牛了"+i);
            Cell cell7 = row.createCell(6);
            cell7.setCellValue("小时候可牛了"+i);
        }
        workbook.write(out);
        out.flush();
        out.close();
        workbook.close();

    }

参考：http://blog.sina.com.cn/s/blog_68555ee501015xk2.html

今天项目需要导出站点对应的频道展示，导出时使用的poi3.8中的HSSFWorkbook workBook =new HSSFWorkbook()；来创建workBook，但是在循环样式的时候，会报：

java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

详情见：
报错解决办法
SXSSFWorkbook workBook =new SXSSFWorkbook();

   @RequestMapping(value="/api/uploadExcelByPoi",method=RequestMethod.GET)
    public void uploadExcelByPoi(@RequestParam(value="chSiteId", required=true) Integer chSiteId,HttpServletRequest request,HttpServletResponse response){
		//查询需要的集合
    	List<FocChannel> list = focChannelsService.uploadExcelByPoi(chSiteId);
    	 //3.创建workbook  
    	SXSSFWorkbook workBook =new SXSSFWorkbook();
        //根据workBook创建sheet  
        Sheet sheet = workBook.createSheet("站点频道展示");
        //根据sheet创建行  
        Row rowHead = sheet.createRow(0);  
        //创建excel的头部标题行,及标题行的样式的设置
        createTitleCell(workBook,rowHead,sheet); 
        //创建主题内容
       buildMainbody(list, workBook, sheet);
       //导出Excel
     	downLoadExcel(request, response, workBook);
       
    }

	private void downLoadExcel(HttpServletRequest request,
			HttpServletResponse response, SXSSFWorkbook workBook) {
		SimpleDateFormat sim=new SimpleDateFormat("yyyyMMddhhmmss");
     	String strDate = sim.format(new Date());
     	//随机数
     	RandomStringUtils randomStringUtils=new RandomStringUtils();
     	//生成指定长度的字母和数字的随机组合字符串
     	String randomStr = randomStringUtils.randomAlphanumeric(5);
   		String xlsName=	strDate+randomStr+"站点频道信息表.xls"	;
   		FileUtil.downloadXLSFile(request, response, workBook, xlsName);
	}
	
	private void buildMainbody(List<FocChannel> list, SXSSFWorkbook workBook,
			Sheet sheet) {
		for (int i = 0; i < list.size(); i++) {
				buildMainBodyAandMainBodyStyle(list, workBook, sheet, i);
		   }
				
	}

	private void buildMainBodyAandMainBodyStyle(List<FocChannel> list,
			SXSSFWorkbook workBook, Sheet sheet, int i) {
		 CreationHelper createHelper = workBook.getCreationHelper();  
	     CellStyle cellStyle = workBook.createCellStyle();  
	     cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));  
		
		Row rowBody = sheet.createRow(i+1);
		//创建列
		FocChannel foc = list.get(i);
		Cell idCell= rowBody.createCell(0);
		idCell.setCellValue(i+1);
		Cell timeCell = rowBody.createCell(1);
		SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd");
		timeCell.setCellValue(sim.format(new Date()));
		Cell inspectCell = rowBody.createCell(2);
		inspectCell.setCellValue("新增");
		Cell websiteCell= rowBody.createCell(3);
		websiteCell.setCellValue("网站");
		Cell webSiteNameCell= rowBody.createCell(4);
		webSiteNameCell.setCellValue(foc.getChCascadeName());
		Cell accountCell = rowBody.createCell(5);
		accountCell.setCellValue("频道");
		Cell urlCell= rowBody.createCell(6);
		urlCell.setCellValue(foc.getChUrl());
		Cell newUrlCell=rowBody.createCell(7);
        newUrlCell.setCellValue("");
        Cell causeCell=rowBody.createCell(8);
        causeCell.setCellValue("");
		CellStyle fontStyle = bulidMainFontStyleHead(workBook);
		idCell.setCellStyle(fontStyle);
		//timeCell.setCellStyle(cellStyle);
		timeCell.setCellStyle(fontStyle);
		inspectCell.setCellStyle(fontStyle);
		websiteCell.setCellStyle(fontStyle);
		webSiteNameCell.setCellStyle(fontStyle);
		accountCell.setCellStyle(fontStyle);
		urlCell.setCellStyle(fontStyle);
		newUrlCell.setCellStyle(fontStyle);
		causeCell.setCellStyle(fontStyle);
	}

	/**
	 * 创建excel的头部标题行
	 * @param rowHead
	 * @param sheet 
	 */
	private void createTitleCell(SXSSFWorkbook workBook,Row rowHead, Sheet sheet) {
		//根据row创建cll  
        Cell idCell = rowHead.createCell(0);  
        idCell.setCellValue("序号");  
        Cell timeCell = rowHead.createCell(1);  
        timeCell.setCellValue("反馈时间");  
        Cell inspectCell = rowHead.createCell(2);  
        inspectCell.setCellValue("新增/核查");  
        Cell websiteCell = rowHead.createCell(3);  
        websiteCell.setCellValue("网站/论坛");  
        Cell webSiteNameCell = rowHead.createCell(4);  
        webSiteNameCell.setCellValue("名称");  
        Cell accountCell = rowHead.createCell(5);  
        accountCell.setCellValue("频道/账号"); 
        Cell urlCell = rowHead.createCell(6);  
        urlCell.setCellValue("频道链接");
        Cell newUrlCell=rowHead.createCell(7);
        newUrlCell.setCellValue("涉及新闻链接");
        Cell causeCell=rowHead.createCell(8);
        causeCell.setCellValue("核查原因");
        CellStyle buildStyleTitle = bulidFontStyleHead(workBook);
        buildStyleTitle.setWrapText(true);//设置自动换行
        idCell.setCellStyle(buildStyleTitle);
        timeCell.setCellStyle(buildStyleTitle);
        inspectCell.setCellStyle(buildStyleTitle);
        websiteCell.setCellStyle(buildStyleTitle);
        accountCell.setCellStyle(buildStyleTitle);
        urlCell.setCellStyle(buildStyleTitle);
        webSiteNameCell.setCellStyle(buildStyleTitle);
        causeCell.setCellStyle(buildStyleTitle);
        newUrlCell.setCellStyle(buildStyleTitle);
      //设置列宽（给时间的单元格的宽度给大点，防止时间显示格式错误！）  
        sheet.setColumnWidth(0, 20*256);  
        sheet.setColumnWidth(1, 20*300);  
        sheet.setColumnWidth(2, 20*256);  
        sheet.setColumnWidth(3, 20*256);  
        sheet.setColumnWidth(4, 20*256);  
        sheet.setColumnWidth(5, 20*256);  
        sheet.setColumnWidth(6, 20*500);
        sheet.setColumnWidth(7, 20*400); 
        sheet.setColumnWidth(8, 20*400); 
        
	}
	
	private CellStyle bulidMainFontStyleHead(SXSSFWorkbook workBook) {
		//设置样式
		CellStyle style = workBook.createCellStyle();
		Font font  = workBook.createFont(); 
		font.setFontHeightInPoints((short) 11);//字号       
		//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗   
		//font.setColor(HSSFColor.RED.index);//设置字体颜色
		font.setFontName("微软雅黑"); // 将“黑体”字体应用到当前单元格上
		style.setFont(font);      
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//内容左右居中    	
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//内容上下居中
		return style;
	}
	
	private CellStyle bulidFontStyleHead(SXSSFWorkbook workBook) {
		//设置样式
		CellStyle style = workBook.createCellStyle();
		Font font  = workBook.createFont(); 
		font.setFontHeightInPoints((short) 11);//字号       
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗   
		//font.setColor(HSSFColor.RED.index);//设置字体颜色
		font.setFontName("黑体"); // 将“黑体”字体应用到当前单元格上
		style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//北京颜色
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
//		style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
		style.setFont(font);      
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//内容左右居中    	
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//内容上下居中
		return style;
	}

	

点击上方蓝色字关注我们！
前言
最近实务上连续遇到两个类似的案例，合并为一个其实就是超大数据量的表格合并加透视的问题，比如从业务系统内导出一个月的数据就有30万行以上，Excel文档的大小在50M左右，需要合并透视整年或数年的数据。
手工粘贴再透视的话，最主要的问题就是Excel不支持100万行以上的数据，所以你连享受Excel文档卡死的资格都没有。
碰到这种问题用SQL当然好，数据量完全不在话下，各种汇总、查询的速度都非常快，不过对于看到英文字母就头晕的同学来说就不太友好了，虽然学习起来并不难，但依旧有一定门槛，常用的查询代码一般也要摸3天才能摸熟。话说回来，虽然我学了这玩意儿，但是在实务中真正用到的次数却屈指可数，导致我每次使用的时候都要回头把之前学的东西再捡一遍，也许是自己涉及这个数据量级的项目不多吧。
简介
其实MS对于Excel数据量的限制也早有了解决方案，那就是Power Query和Power Pivot。
在Excel2010时，MS推出了Power Query的插件(可以在官网下载)，使得Excel处理数据的能力大幅提升，在Excel2016中直接内置了Power Query，放在数据选项卡中：
Power Query可以理解为一个查询数据的软件，这个名字可以类比微软自家的其他软件，比如我们熟知的PPT叫"PowerPoint"。
Power Pivot可以理解为透视表软件，它比Excel内置的透视表强大了很多，一般被用来建立数据模型，对于我们来说就把他当做超级透视表就行了。
其实Power Pivot就是微软把自家数据库SQL Server的分析服务引擎拆出来装到了Excel中。
顺便提一句：MrExcel.com创始人Bill这样说:Power Pivot是Excel在20年的改进中最棒的改进。
所以说，你学这个就够了，懂伐？
在Excel的COM加载项中，把Power Pivot调出来：
上面大概介绍了一下PQ和PP，可能你会觉得有点绕，很复杂的样子，其实这个东西用起来真的很简单，在试用了几次Power Pivot后，我觉得对于财审人员日常的工作来说，基本上可以不用去学SQL了，因为用PQ用起来不需要学什么代码，最多就几个函数稍微记一下，其他的都靠点，并且上载进Excel后，数据透视什么就跟平时普通的透视操作一样，大幅降低了学习成本。
如果你手里有Office Excel2016的话，下载案例花几分钟跟着我的步骤走一遍试试吧，以后碰到几百万行的表也不怕了。
本文的案例，后台回复【PQ】获取下载地址。
案例
需求
下面我以一个40-50万数据的例子，来简单的演示一下怎样用PQ：
这里有两个月的数据，我通过复制，把数据拉到47万行和57万行，单个文档的大小在25-30M。
需求：将其合并，再一起透视。
先直接看动图吧，真的很简单，之前手工几小时都搞不定的东西，用这个只需要2分钟就能搞定。
1.载入数据并调整数据类型：
2.上载至Excel并数据透视：
01
使用power query载入数据
1.在数据选项卡中，依次点击【从文件】-【从文件夹】
2.选好了文件夹路径后，可以看到要导入的文档都进来了，确认没问题后，点击右下角的【编辑】↓
3.进入编辑界面后，我们看到文档的信息都被导入进来了↓
4.表格中的数据都以二进制的形式储存在第一列"content"中，所以我们需要用一个函数把他们提取出来：
5.依次点击选项卡【添加列】-【自定义列】
输入函数"=Excel.Workbook([content],true)"，注意大小写，大家直接复制就行。
这个函数的两个参数：
1.[content]就是要解析的列名。
2.如果每个文档的第一行是标题行，就写true，他会把所有的文档标题行放在第一行并去重。
一般来说，我们直接复制这个函数就行了。
如果是csv的文件，函数为：Csv.Document([Content],[Delimiter=",", Encoding=936])
6.输入好函数后，点击确定，可以看到我们的自定义列，点击右上角的展开按钮。
7.在新出来的列中，找到Data列，继续点击右上角的展开按钮，我们需要的数据就全部合并且加载进来了。
8.加载完成后，会自动选中展开的数据，点击鼠标右键-删除其他列，留下我们需要的列即可。
02
转换数据类型，上载至Excel
1.加载进来后，数据类型一般会自动变成文本，如果我们不进行数据类型的转换的话，在后面的透视过程中是不能求和的，因此，在这里把小数、日期都直接转换好↓
2.转换数据类型后，将PQ编辑器中的数据上载至Excel中：
如果数据量大的话，尽量不要直接点关闭并上载，否则所有数据直接灌入Excel就爆炸了，这个按钮我建议只有汇总多个相同格式的工作簿且单个工作簿的数据不多的情况下使用。
在弹出的选项框中，选中【仅创建连接】并勾选【将此数据添加到数据模型】
这样的话，所有的数据就没有直接添加进Excel中，Excel仅作为呈现数据的界面，查询等操作都在PQ的引擎中进行。
加载完成后，在Excel的右侧会显示加载状态。
03
数据透视
前面已经把所有的文档通过PQ上载进Excel中了，到了数据透视这一步就是所有人都熟悉的：插入透视表-选择字段并进行透视了。
这里唯一要说的就是勾上【使用此工作簿的数据模型】
之后的操作就完全和透视表完全一样了。
04
刷新数据源
之前我们导入了7、8月的表，如果我们又获取了9-12月表，那么直接把文档放在同一文件夹下，在Excel的右侧点击刷新，就能把新增的表按上述步骤自动过进来，再也不用手动进行设置了。
这篇文章我用最简单的案例把PQ全部流程都走了一遍，在实际情况中大家应该会碰到一些别的坑，比如有些数据我们是不需要的，那么在PQ编辑器中上载至Excel之前，筛选需要的内容即可，上载的内容为筛选后的内容。
这个功能熟练之后，以后同一个格式的汇总，无论是多工作表还是多工作簿的都不在话下了，更多的技巧可以去知乎或者相关的论坛看看。
end
如果文章对你有帮助，点一下右下角的[在看]吧，谢谢你的支持。