大数据使用Excel导出

最新推荐文章于 2024-05-13 22:09:10 发布

schy_hqh

最新推荐文章于 2024-05-13 22:09:10 发布

阅读量248

点赞数

分类专栏：第三方jar@POI 文章标签：大数据数据库 java

本文链接：https://blog.csdn.net/schy_hqh/article/details/84459674

版权

第三方jar@POI 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

[b]
需求描述：
1.导出数据量约30-50W
2.提供手动即时生成数据和自动生成数据(直接提供下载)
[/b]
页面
一个“手动导出”按钮，点击后台运行代码生成数据并通过outputstream返回给浏览器。
缺点在于生成数据非常耗时（查询数据库，过滤不合法的数据，汇率转换，查询关联表信息等），最耗时的地方：比如，查询到10W条数据，过滤掉2W条，之后会循环剩下的8W条数据，每次循环都去查询数据库获取某些信息，效率很低。【一次性将数据都查询出来放内存中？分几次查询数据，第一次查询一批，需要时从这批从获取，如果没有再查询一批数据出来，这样能够提高效率呢？】

一个“自动导出”按钮，点击将从数据库中查询出已经由定时任务生成好的数据，直接下载。
涉及到几个知识点：定时任务采用quartZ实现任务的促发。经过数据查询、过滤、计算等操作后，将数据写入Sheet，此时Workbook对象已经算是完成数据填充了。这是将Workbook对象写入到一个流中，再将此流转换为字节数组，再将字节数组转换为字符串，这时就需要注意了，操作字符串难免不涉及编码的问题。最开始没有对字符串进行编码，直接存入数据库，下载的时候直接从数据库中查询出来，转换为流，结果下载的数据使用excel无法打开。最后，通过设置字符串的编码解决了问题。存入数据库时转为UTF-8存入，读取后再使用UTF-8解码，再转为ISO8859-1编码，再转为流，向浏览器输出，这样才解决了excel的读取问题。【为什么不直接存对象呢？由于数据库中某张日志表有一个大文本字段足够存储数据，所以没有使用BLOB来存放大数据，也没有以对象的形式保存Workbook对象。其实在第一次下载后excel无法打开后，就考虑存储对象的方式完成数据存储，但是没成功，取出来的对象和本地对象的sid总是不一致，于是放弃了存对象的实现方式，项目时间紧，只好以最能想到的办法解决问题】

项目中遇到的另一个问题时，项目原来使用的是poi3.1-jar,版本较老了，而且不支持xlsx格式，而当前需求是数据量大，excel必须能容纳足够大的数据，所以引入了poi3.9版本。
这里想说的是，当项目中出现新老jar包的替换时，必须小心！引入poi3.9后发现，虽然能实现大数据的存储(50W)，约20M，但是原来的excel导出全部用的poi3.1实现的，由于3.1中某些方法在3.9中根本就没有了，只好回去修改原来的代码，项目中一共10多个导出，改得我痛苦。。。
而且，一旦确定要进行jar包的升级，选择一个既能支持当前需求，又能最大限度兼容老版本的版本是最合适的，不然，将陡然增加工作量！

以下是POI3.9导出的部分代码和quartz的配置


<bean name="quartzScheduler" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
		<property name="triggers">
			<list>
				<ref bean="cronTrigger_07"/>
			</list>
		</property>
		<property name="configLocation" value="classpath:config/quartz.properties"/>
</bean>

<bean id="cronTrigger_07" class="org.springframework.scheduling.quartz.CronTriggerBean">
		<property name="jobDetail" ref="jobDetail_07"/>
<!--每月1号0点开始每隔5分钟触发一次任务-->
		<property name="cronExpression" value="0 0/5 0 1 * ?"/>
</bean>
<bean id="jobDetail_07" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
		<property name="targetObject" ref="xxxService"/>
		<property name="targetMethod" value="generateExcelDatas"/>
</bean>
<bean id="xxxService" class="xxx.xxx.serviceImpl"
		  autowire="byName" lazy-init="true">
</bean>

创建Workbook对象，要导出xlsx格式，需要使用XSSFWorkbook,而数据量大又不想让内存溢出的话，就用Workbook workbook = new SXSSFWorkbook(1000);指定一定大小的缓冲区，当容量慢时，将清除一些对象出去，始终维持一定的内存暂用空间，不会发生溢出。


//**创建工作薄，工作表*//*
Workbook  workbook = new SXSSFWorkbook(1000);   	
Sheet sheet = createSheet(workbook,businessType);


public Sheet createSheet(Workbook workbook,String businessType) {
		Sheet sheet = workbook.createSheet();
	    if("0".equals(businessType) || "1".equals(businessType)){
	    	if("0".equals(businessType)) {
	    		workbook.setSheetName(0, "XXX清单");  
	    	} else {
	    		workbook.setSheetName(0, "YYY清单");  
	    	}
	    	sheet.setColumnWidth((short) 0, (short) (35.7*50));//
	    	sheet.setColumnWidth((short) 1, (short) (35.7*120));//
	    	sheet.setColumnWidth((short) 2, (short) (35.7*120));//	    	
	    	sheet.setColumnWidth((short) 3, (short) (35.7*120));//	    	
	    	sheet.setColumnWidth((short) 4, (short) (35.7*160));//	    	
	    	sheet.setColumnWidth((short) 5, (short) (35.7*160));//	    	
	    	sheet.setColumnWidth((short) 6, (short) (35.7*180));//	    	
	    	sheet.setColumnWidth((short) 7, (short) (35.7*120));//	    	
	    	sheet.setColumnWidth((short) 8, (short) (35.7*180));//	    	
	    	sheet.setColumnWidth((short) 9, (short) (35.7*90));//	    	
	    	sheet.setColumnWidth((short) 10, (short) (35.7*100));//	    	
	    	sheet.setColumnWidth((short) 11, (short) (35.7*100));//	    	
	    	sheet.setColumnWidth((short) 12, (short) (35.7*100));//	    
	    	} 
	    else if("9".equals(businessType)){
	    	workbook.setSheetName(0, "ZZZ清单");  
	    	sheet.setColumnWidth((short) 0, (short) (35.7*50));//
	    	sheet.setColumnWidth((short) 1, (short) (35.7*280));//
	    	sheet.setColumnWidth((short) 2, (short) (35.7*120));//
	    	sheet.setColumnWidth((short) 3, (short) (35.7*120));//
	    	sheet.setColumnWidth((short) 4, (short) (35.7*100));//
	    	sheet.setColumnWidth((short) 5, (short) (35.7*180));//
	    	sheet.setColumnWidth((short) 6, (short) (35.7*120));//
	    	sheet.setColumnWidth((short) 7, (short) (35.7*180));//
	    	sheet.setColumnWidth((short) 8, (short) (35.7*90));//
	    	sheet.setColumnWidth((short) 9, (short) (35.7*100));//
	    	sheet.setColumnWidth((short) 10, (short) (35.7*100));//
	    	sheet.setColumnWidth((short) 11, (short) (35.7*100));//
	    }
	    return sheet;
	}

[b]表格样式[/b]


Map<String,CellStyle> styleMap = getPaymentDatasStyles(workbook);
public Map<String, CellStyle> getPaymentDatasStyles(Workbook workbook) {

		Map<String,CellStyle> styles = new HashMap<String,CellStyle>();

	    Font fontHead = workbook.createFont();
	    fontHead.setFontName("宋体");      // 字体    
	    fontHead.setFontHeightInPoints((short)16); //字号
	    fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗

		Font fontBody = workbook.createFont();
        fontBody.setFontName("宋体");      // 字体    
        fontBody.setFontHeightInPoints((short)10); //字号

        Font fontMark = workbook.createFont();
        fontMark.setFontName("宋体");      // 字体    
        fontMark.setFontHeightInPoints((short)12); //字号

		 /**首行样式*/
        CellStyle cellStyleHead = workbook.createCellStyle();  
        cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);                       
        cellStyleHead.setFont(fontHead);       

        /**第2行样式*/
        CellStyle cellStyleUnit = workbook.createCellStyle();   
        cellStyleUnit.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleUnit.setBorderBottom(CellStyle.BORDER_MEDIUM);
        cellStyleUnit.setFont(fontBody);

        /**列标题样式*/
        CellStyle cellStyleTitle = workbook.createCellStyle();
        cellStyleTitle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleTitle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleTitle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleTitle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyleTitle.setFont(fontBody);
        cellStyleTitle.setWrapText(true);       

        /**正文样式*/  
        CellStyle cellStyleBody = workbook.createCellStyle();
        cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleBody.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleBody.setFont(fontBody);

        /**正文样式二*/
        CellStyle cellStyleBodyDecimal = workbook.createCellStyle();
        cellStyleBodyDecimal.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleBodyDecimal.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleBodyDecimal.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleBodyDecimal.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleBodyDecimal.setAlignment(CellStyle.ALIGN_RIGHT);
        cellStyleBodyDecimal.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        cellStyleBodyDecimal.setFont(fontBody);

        /**正文样式三*/
        CellStyle cellStyleBodyInt = workbook.createCellStyle();
        cellStyleBodyInt.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleBodyInt.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleBodyInt.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleBodyInt.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleBodyInt.setAlignment(CellStyle.ALIGN_RIGHT);
        cellStyleBodyInt.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        cellStyleBodyInt.setFont(fontBody);

        /**备注*/
        CellStyle cellStyleMark = workbook.createCellStyle();
        cellStyleMark.setFont(fontMark);

        styles.put("HEAD", cellStyleHead);
        styles.put("UNIT", cellStyleUnit);
        styles.put("TITLE", cellStyleTitle);
        styles.put("BODY", cellStyleBody);
        styles.put("BODY_DECIMAL", cellStyleBodyDecimal);
        styles.put("BODY_INT", cellStyleBodyInt);
        styles.put("MARK", cellStyleMark);
		return styles;
	}

[b]将数据往sheet中写[/b]


putDatas2Xlsx(sheet,styleMap,list,type,locations,allExchRate);
public void putDatas2Xlsx(Sheet sheet, Map<String, CellStyle> styleMap,List<ScmsDocFeeDetailVo> detailList,String businessType,
			Map<String,String> locations,Map<String,BigDecimal> allExchRate) throws Exception {


		final String agentCodeForGD = "aaa";

		//**********************列标题***************************//*
		String headName = "";
        String [] headList = null;
        headName = "XXX清单（03）";
	    if("0".equals(businessType)){
	    	headList = new String[]{};
	    }else 
	    if("1".equals(businessType)){
	    	headName = "XXX清单（02）";
	    	headList = new String[]{};
	    } else 
	    if("9".equals(businessType)){
	    	headName = "XXX清单（01）";
	    	headList = new String[]{};
	    }

        ScmsDocFeeDetailVo vo = null;
        String risk = "";
        String handler1CodeName = "";

	    Row row = null;          
        Cell cell = null; 

        CellStyle cellStyleHead = styleMap.get("HEAD");
        CellStyle cellStyleUnit = styleMap.get("UNIT");
        CellStyle cellStyleTitle = styleMap.get("TITLE");
        CellStyle cellStyleBody = styleMap.get("BODY");
        CellStyle cellStyleBodyDecimal = styleMap.get("BODY_DECIMAL");
        CellStyle cellStyleBodyInt = styleMap.get("BODY_INT");
        CellStyle cellStyleMark = styleMap.get("MARK");

        //创建第一行 标题
	    int rowNum = 0;
        row = sheet.createRow(rowNum++);//创建行
        cell = row.createCell(0);
        cell.setCellValue(headName);
        cell.setCellStyle(cellStyleHead); 
        //合并单元格
        if("0".equals(businessType) || "1".equals(businessType)) {
        	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
        } else {
        	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
        }

        //创建第二行 单位
        row = sheet.createRow(rowNum++);
        row.setHeight((short)400); 
        for(int i=0;i<headList.length;i++){
        	cell = row.createCell(i);
        	cell.setCellStyle(cellStyleUnit);
        	if(("0".equals(businessType) && i==10) || ("1".equals(businessType) && i==10) ||("9".equals(businessType) && i==9)){
        		cell.setCellValue("单位：元");
        	}
        }
        if("0".equals(businessType)) {
        	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
        } else if("1".equals(businessType)) {
        	sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));
        }

        //第三行 列标题   
        row = sheet.createRow(rowNum++);
        for(int i=0;i<headList.length;i++){
        	cell = row.createCell(i);   
            cell.setCellStyle(cellStyleTitle);
            cell.setCellValue(headList[i]);
        }
        row.setHeightInPoints((short)33);//行高 

         //** 打印主体信息列表------------------------------------------*//*
		int detailCount = detailList.size();
    	for(int i = 0;i<detailCount;i++){
    		vo = detailList.get(i);
			if("0".equals(businessType) || "1".equals(businessType)) {
								handler1CodeName =codeService.translateCode("UserCode", vo.getHandler1Code(), "", "");
				vo.setInsuredName(handler1CodeName);
								vo.setAgentCode(agentCodeForGD);
			}

			/**汇率转换*/
			if(vo.getCurrency()!=null && !"".equals(vo.getCurrency().trim())) {
				BigDecimal exchRate = allExchRate.get(vo.getCurrency().trim());
				vo.setCostFee(vo.getCostFee().multiply(exchRate).setScale(2, RoundingMode.HALF_UP));
			} 


			if(vo.getPaidPremium()==null || vo.getPaidPremium().equals("") || vo.getPaidPremium().compareTo(BigDecimal.ZERO)==0) {
				vo.setCostRate(BigDecimal.ZERO);
			} else {
				vo.setCostRate(vo.getCostFee().divide(vo.getPaidPremium(), 2, RoundingMode.HALF_UP));
			}
    			//开始循环生成EXCEL行数据   
                short colNum = 0;//初始化CELL下标   
                row = sheet.createRow(rowNum++);//创建行   
                row.setHeight((short)400);

            	cell = row.createCell(colNum++);   
            	cell.setCellType(Cell.CELL_TYPE_NUMERIC);
	            cell.setCellStyle(cellStyleBodyInt);   
	            cell.setCellValue((i+1));   

    		    	//清单 
    			if("0".equals(businessType) || "1".equals(businessType)){

    		    	cell = row.createCell(colNum++);   
    		    	cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);
    	            String preComCode = vo.getComCode().substring(0, 4);
    	            String addressName = locations.get(preComCode);
    	            if(addressName!=null) {
    	            	cell.setCellValue(addressName);  
    	            } else {
    	            	cell.setCellValue(preComCode);  
    	            }

    	            cell = row.createCell(colNum++);   
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            cell.setCellValue(vo.getComCode());  

    	            cell = row.createCell(colNum++);   
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            handler1CodeName=codeService.translateCode("UserCode", vo.getHandler1Code(), "PUB", "");
    	            cell.setCellValue(handler1CodeName);  

    	            cell = row.createCell(colNum++);  
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            cell.setCellValue(agentCodeForGD);  

    	            cell = row.createCell(colNum++); 
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            cell.setCellValue(vo.getSellerNo()); 

    	            cell = row.createCell(colNum++);  
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            cell.setCellValue(vo.getCertiNo());  

    	            cell = row.createCell(colNum++);   
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);  
    	            if(vo.getStartDate()!=null && !vo.getStartDate().equals("")) {
    	            	cell.setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(vo.getStartDate()));  
    	            } else {
    	            	cell.setCellValue("");  
    	            }

    	            cell = row.createCell(colNum++); 
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody); 
    	            risk=codeService.translateCode("RiskCode", vo.getRiskCode(), "", "");
    	            cell.setCellValue(risk);  

    	            cell = row.createCell(colNum++);  
    	            cell.setCellType(Cell.CELL_TYPE_STRING);
    	            cell.setCellStyle(cellStyleBody);   
    	            cell.setCellValue(vo.getRiskCode());  

    	            cell = row.createCell(colNum++);   
    	            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    	            cell.setCellStyle(cellStyleBodyDecimal);   
    	            cell.setCellValue(vo.getPaidPremium().doubleValue());  

    	            cell = row.createCell(colNum++);   
    	            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    	            cell.setCellStyle(cellStyleBodyDecimal);   
    	            cell.setCellValue(vo.getCostFee().doubleValue());  

    	            cell = row.createCell(colNum++);  
    	            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    	            cell.setCellStyle(cellStyleBodyDecimal);   
    	            cell.setCellValue(vo.getCostRate().doubleValue());  
    		    }else
    		    	//手续费清单
    		    if("9".equals(businessType)){

        		    cell = row.createCell(colNum++);
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
        		    cell.setCellValue(vo.getAgentName());  

        		    cell = row.createCell(colNum++); 
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
        		    cell.setCellValue(vo.getAgentCode());  

        		    cell = row.createCell(colNum++); 
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
    	            String preComCode = vo.getComCode().substring(0, 4);
    	            String addressName = locations.get(preComCode);
    	            if(addressName!=null) {
    	            	cell.setCellValue(addressName);  
    	            } else {
    	            	cell.setCellValue(preComCode);  
    	            }

        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);
        		    cell.setCellValue(vo.getComCode());  

        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
        		    cell.setCellValue(vo.getCertiNo());  

        		    cell = row.createCell(colNum++); 
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
        		    if(vo.getStartDate()!=null) {
        		    	cell.setCellValue((new SimpleDateFormat("yyyy-MM-dd")).format(vo.getStartDate()));  
        		    } else{
        		    	cell.setCellValue("");
        		    }
        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody); 
        		    //调用服务转换代码为姓名【在循环中每次调用服务进行查询，导致性能下降很多，这里必须进行优化处理。可以一次性查询所有需要转换的数据到内存中！！！】
        		    risk=codeService.translateCode("RiskCode", vo.getRiskCode(), "", "");
        		    cell.setCellValue(risk);  

        		    cell = row.createCell(colNum++);  
        		    cell.setCellType(Cell.CELL_TYPE_STRING);
        		    cell.setCellStyle(cellStyleBody);   
        		    cell.setCellValue(vo.getRiskCode());  

        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        		    cell.setCellStyle(cellStyleBodyDecimal);   
        		    cell.setCellValue(vo.getPaidPremium().doubleValue()); 

        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        		    cell.setCellStyle(cellStyleBodyDecimal); 
        		    cell.setCellValue(vo.getCostFee().doubleValue()); 

        		    cell = row.createCell(colNum++);   
        		    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        		    cell.setCellStyle(cellStyleBodyDecimal);   
        		    cell.setCellValue(vo.getCostRate().doubleValue());  

    		    }
    		}
    	//备注
        row = sheet.createRow(rowNum++);//创建行   
        row.setHeight((short)400);
    	cell = row.createCell(0);
    	cell.setCellStyle(cellStyleMark);
        cell.setCellValue("备注：");   //序号

	}

[b]保存数据到数据库[/b]


	OutputStream os = null;
    	ByteArrayOutputStream baos = null;
         try{   
        	os = new ByteArrayOutputStream();
            workbook.write(os);
            baos = (ByteArrayOutputStream) os;
            //编码转换-非常必须一定要这样做！
            String encodeStr = baos.toString("ISO-8859-1");
            encodeStr = java.net.URLEncoder.encode(encodeStr, "UTF-8");
            //现在可以直接存储encodeStr 字符串到数据库中了！
//.......

[b]从数据库中取数据并返回给浏览器下载[/b]


public void exportXlsxFromDB()throws Exception {
		/**基于页面输入信息查询数据*/
		String comCode = ServletActionContext.getRequest().getParameter("comCode");
		String paymentYear = ServletActionContext.getRequest().getParameter("paymentYear");
		String paymentMonth = ServletActionContext.getRequest().getParameter("paymentMonth");
		String paymentDate = paymentYear+"-"+paymentMonth;
		String riskType = ServletActionContext.getRequest().getParameter("risk");
		String businessType = ServletActionContext.getRequest().getParameter("businessType");

		long start = System.currentTimeMillis();
		logger.info("开始导出机构"+comCode+"及其下级"+paymentDate+"月的实收数据，导出险类:"+riskType+",业务类型："+businessType);
		//查询数据
		Table table = tableService.getExcelDataFromDB(paymentDate, comCode, riskType, businessType);
		if(table==null) {
			//数据库无数据
			throw new BusinessException(paymentDate+"数据尚未定时生成，请手动生成本次数据。【提示：生成数据时，如果数据量较大，将比较耗时，请耐心等待...】");
		}
		String sheetName = interfaceLog.getResponsetype();
		String excelDatas = interfaceLog.getResponsexml();

		//还原数据---非常关键&&重要加必须
		excelDatas = java.net.URLDecoder.decode(excelDatas, "UTF-8");
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		baos.write(excelDatas.getBytes("ISO-8859-1"));

    	//设置返回内容类型
    	this.getResponse().reset();
    	this.getResponse().setContentType("application/x-msdownload");   
    	this.getResponse().setHeader("Content-Disposition","attachment; filename="+new String(sheetName.getBytes("gb2312"),"ISO-8859-1")+paymentDate+".xlsx");   
        OutputStream outStream = null;   
        try{   
        	outStream = this.getResponse().getOutputStream();
    		baos.writeTo(outStream);
    		outStream.flush();
        }catch(Exception e){   
        	log.debug(e.getMessage());
        	e.printStackTrace();
        	throw new ScmsBusinessException("导出异常，请联系管理员！",e);
        }finally{   
        	if(outStream!=null) {
        		outStream.close();   
        	}
        	if(baos!=null) {
        		baos.close();
        	}
        } 
	}

schy_hqh

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
大数据使用Excel导出

[b]需求描述：1.导出数据量约30-50W2.提供手动即时生成数据和自动生成数据(直接提供下载)[/b]页面一个“手动导出”按钮，点击后台运行代码生成数据并通过outputstream返回给浏览器。缺点在于生成数据非常耗时（查询数据库，过滤不合法的数据，汇率转换，查询关联表信息等），最耗时的地方：比如，查询到10W条数据，过滤掉2W条，之后会循环剩下的8W条数据，每次...
复制链接

扫一扫