一次大数据量导出优化--借助xml导出xls、xlsx文件

最近遇到一个问题，线上生产环境某个功能导出数据到excel文件非常缓慢，几万数据导十多分钟都导不出来，导出慢的原因一是主表A数据量太大，接近2亿，另外里面部分数据来自于另外一张表B，B表也是几千万的数据量，数据库层面能做的优化已经做了，视图、索引这些工具都上了（没有分表是一开始项目设计阶段就没考虑，后面也没有专人维护，是另外一段故事了，这里不展开描述），但是依旧很慢，那就只能改导出代码了。

项目原来使用的是jxl来导出，生成的是xls格式Excel文件，这是旧版本的Excel文件，缺点有两点：一是单sheet页数据量小，只有6万多，二是文件太大，同等数据量下，xlsx格式的比xls格式的文件小4倍。一番搜索后，发现了POI自3.8版本后新加了一个SXSSFWorkbook类，可以处理大数据量的导出，并且内存占用不高，下面是一个小demo，写入10万行数据花费11065ms，文件大小14M不到，可以说很高效了。

package exceltest;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SXSSFWorkbookTest {

	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		Excel2007AboveOperate();
		long end = System.currentTimeMillis();
		System.out.println("花费:"+(end-start));//10万数据花费:11065
	}
	
	public static void Excel2007AboveOperate() {
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
		for (int i = 0; i < 10; i++) {
        	sxssfWorkbook.createSheet();
        	sxssfWorkbook.setSheetName(i, "第"+i+"页");
        	Sheet first = sxssfWorkbook.getSheetAt(i);
            for (int j = 0; j < 10000; j++) {
                Row row = first.createRow(j);
                for (int k = 0; k < 11; k++) {
                    if(j == 0) {
                        // 首行
                        row.createCell(k).setCellValue("column" + k);
                    } else {
                        // 数据
                        if (k == 0) {
                            CellUtil.createCell(row, k, String.valueOf(j));
                        } else
                            CellUtil.createCell(row, k, String.valueOf(Math.random()));
                    }
                }
            }
        }
        FileOutputStream out;
		try {
			out = new FileOutputStream("F:\workbook666.xlsx");
			sxssfWorkbook.write(out);
	        out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

以为故事到这里就结束了，然而事情并没有那么简单！生产环境有个包集成了POI的代码，也可以用SXSSFWorkbook这个nb的类，但是会报错java.lang.RuntimeException: Provider for class javax.xml.stream.XMLEventFactory cannot be created，查找一番下来，有说缺少依赖包的，有说jdk版本低云云，单独引用最新的POI包，依旧是报错，怀疑是集成包里的poi代码缺少了一些东西，至于是什么无从考量，只能另辟蹊径来解决这个导出问题了，只要是依赖POI包的方法都不行，只有找到不依赖POI包却依然能导出excel文件方法才行！

又是一番寻找，看到了一篇博客，讲的是xls文件的本质其实是一个xml文件，可以通过手动拼接的方式来生成一个符合xls格式的xml文件，原博客文章暂时没找到，如果原作者看到可以留言提醒下。直接上demo代码。

package exceltest;

import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class Xml2ExcelTest {

	public static void main(String[] args) {
		test2();
	}
	
	
	public static void test2() {
		StringBuffer sb = new StringBuffer();
		File file = new File("F://testxml2xls666.xls");
        try {
			DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append(" n");
			sb.append("  n");
			sb.append("   n");
			sb.append("   n");
			sb.append("   n");
			sb.append("   n");
			sb.append("   n");
			sb.append("   n");
			sb.append("  n");
			sb.append(" n");
            
            int maxRow = 10;
            int maxCol = 10;
            for (int i = 0; i < 5; i++) {
            	sb.append("n");  
                sb.append("n");
                for (int j = 0; j < maxRow; j++) {
                	sb.append("n");
                	sb.append("").append("还好").append(i).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("").append(String.valueOf(Math.random())).append("n");  
                    sb.append("n");
                }  
                sb.append("n");
                sb.append("n");  
                sb.append("Falsen");  
                sb.append("Falsen");  
                sb.append("n");
                sb.append("n");
                rafs.write(sb.toString().getBytes());
                rafs.flush();
                sb = null;
                sb = new StringBuffer();
            }
			sb.append("n");  
			rafs.write(sb.toString().getBytes());  
            rafs.flush();
            rafs.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
	}

}

demo可行，拿正式数据做测试，10万数据可以正常导出，速度比之前通过jxl导出快，但是有个缺点很明显，就是文件太大了，如果上生产，那文件大小估计得按G来算，这个方法也无法胜任啊！但是却提供了一种思路，既然xls文件能通过xml文件间接得到，那么xlsx文件是否也可行？想到就干！

又是一番查找，找到了一半的答案，喜忧参半吧！喜的是这个思路行得通，xlsx文件其实是一个压缩文件，可以将后缀改为zip解压，里面包含了很多的xml文件，可以用多线程逐个击破，同时解决写入和效率问题，大家可以在自己电脑上试下，你会发现新大陆的。忧的是里面每个单元格值不是明文，是键值对，需要准备一个很大的数据结构来存储。于是将本地新建的xlsx文件和通过代码生成的xlsx文件做个了比较，代码生成的xml文件里是直接放的明文，难道是我本地Office版本太高了？可能是吧，问题暂且放一边，先把demo跑通过才是正事。

先看下解压出来的文件结构：

_rels/.rels

docProps/app.xml
docProps/core.xml

xl/_rels/workbook.xml.rels
xl/worksheets/sheet1.xml
......
xl/worksheets/sheetn.xml
xl/sharedStrings.xml
xl/styles.xml
xl/workbook.xml

[Content_Types].xml

_rels/.rels文件内容是固定的，可以直接写。

docProps/app.xml文件内容是固定的，可以直接写。


	Apache POI

docProps/core.xml文件里面需要注意下创建时间，这个时间格式需要特殊处理下。


	
	2021-12-16T12:10:02Z
	Apache POI

xl/sharedStrings.xml文件内容是固定的，可以直接写。

xl/styles.xml样式文件，需要自定义样式的可以提前通过代码生成。

xl/workbook.xmlsheet页的信息文件，每个sheet页都有一个id，是一一对应的关系。name是表名，可以自定义，sheetId从1开始，r:id从rId3开始。

xl/_rels/workbook.xml.relssheet的依赖文件信息，rId1和rId2都是固定的，从rId3开始对应创建的sheet页文件路径。

[Content_Types].xml汇总文件，里面包含了xlsx文件依赖的相关xml文件信息

xl/worksheets/sheet1.xmlsheet页数据文件，其中第一个sheet页是默认选中打开的sheet页，会加上tabSelected="true"属性，只能在其中一个xml文件里设置，不能给多个sheet页xml文件设置。行号从1开始，列号从大写的英文字母加数字(也是从1开始)组成。cols标签里的col标签是设置列宽，列号通过min="1" max="1"这两个标签定义，也是从1开始。合并单元格使用标签，跟在标签后面，比如想要合并A1到D1，可以这样写。











column0
column1
column2
column3
column4
column5
column6
column7
column8
column9
column10
......

以上就是一个xlsx文件里包含的xml文件，使用IO流将相关的信息写入到xml文件中去，最后压缩成xlsx文件，就可以得到一个xlsx格式的excel文件了。

但是其中有个点需要注意，压缩方法不能使用java.util.zip里的，用java.util.zip得到的压缩文件打开会提示文件损坏，得换成apache的compress方法，因为这是poi相关包源码用到的压缩方法。至于为什么用java.util.zip生成的文件打不开，这个原因暂时未知。

下面是用写xml文件生成xlsx文件的代码demo。

package exceltest;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.function.Supplier;

import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;
import org.apache.commons.compress.utils.IOUtils;


public class XML2XlsxFileDemo {

	public static void main(String[] args) {
		testM();

		try {
			File file = new File("F:\test996.xlsx");
			FileOutputStream outputStreamExcel = new FileOutputStream(file);
			ZipArchiveOutputStream zos = new ZipArchiveOutputStream(outputStreamExcel);
			compressDirectoryToZipfile("F:\xlsxtest222", "F:\xlsxtest222", zos);
			zos.flush();
			zos.finish();
			zos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void compressDirectoryToZipfile(String rootDir, String sourceDir, ZipArchiveOutputStream out) throws IOException {
	    try {
	    	File[] files = new File(sourceDir).listFiles();
		    assert files != null;
		    for (File file : files) {
		        if (file.isDirectory()) {
		            compressDirectoryToZipfile(rootDir, sourceDir + File.separator + file.getName(), out);
		        } else {
		            ZipArchiveEntry entry = new ZipArchiveEntry(file.getAbsolutePath().substring(rootDir.length() + 1));
		            out.putArchiveEntry(entry);
		            try (InputStream in = new BufferedInputStream(new FileInputStream(sourceDir + File.separator + file.getName()))) {
		            	IOUtils.copy(in, out);
		            }
		            out.closeArchiveEntry();
		        }
		    }
	    } catch(Exception e) {
	    	e.printStackTrace();
	    }
	}
	
	public static void testM() {
		String filefolder = "F:\xlsxtest222";
		try {
			File fo = new File(filefolder + File.separator + "_rels");
			if (!fo.exists()) {
				fo.mkdirs();
			}
			fo = new File(filefolder + File.separator + "docProps");
			if (!fo.exists()) {
				fo.mkdirs();
			}
			fo = new File(filefolder + File.separator + "xl" + File.separator + "_rels");
			if (!fo.exists()) {
				fo.mkdirs();
			}
			fo = new File(filefolder + File.separator + "xl" + File.separator + "worksheets");
			if (!fo.exists()) {
				fo.mkdirs();
			}
			
			File _rels_f = new File(filefolder + File.separator + "_rels" + File.separator + ".rels");
			if (!_rels_f.exists()) {
				_rels_f.createNewFile();
			}
			DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(_rels_f)));
			StringBuilder sb = new StringBuilder();
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			File docProps_appxml_f = new File(filefolder + File.separator + "docProps" + File.separator + "app.xml");
			if (!docProps_appxml_f.exists()) {
				docProps_appxml_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(docProps_appxml_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("Apache POIn");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			File docProps_corexml_f = new File(filefolder + File.separator + "docProps" + File.separator + "core.xml");
			if (!docProps_corexml_f.exists()) {
				docProps_corexml_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(docProps_corexml_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("n");
			//格式化创建时间
			SimpleDateFormat datestr = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
			String date = datestr.format(new Date());
			sb.append("").append(date).append("n");
			sb.append("Apache POIn");
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			File xl_sharedStrings_f = new File(filefolder + File.separator + "xl" + File.separator + "sharedStrings.xml");
			if (!xl_sharedStrings_f.exists()) {
				xl_sharedStrings_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_sharedStrings_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			File xl_styles_f = new File(filefolder + File.separator + "xl" + File.separator + "styles.xml");
			if (!xl_styles_f.exists()) {
				xl_styles_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_styles_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			File xl_workbook_f = new File(filefolder + File.separator + "xl" + File.separator + "workbook.xml");
			if (!xl_workbook_f.exists()) {
				xl_workbook_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl_workbook_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			sb.append("n");
			for (int i=0; i<10;i++) {
				sb.append("n");
			}
			sb.append("n");
			sb.append("n");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			
			ExecutorService completableFutureExecutor = Executors.newCachedThreadPool();
			List> futures = new ArrayList<>();
	        for (int i = 0; i < 10; i++) {
	        	final int fi = i;
	        	CompletableFuture completableFuture = CompletableFuture.supplyAsync(new Supplier() {
	    			@Override
	    			public String get() {
	    				try {
	    					return createNewSheet(fi);
	    				} catch (Exception e) {
	    					e.printStackTrace();
	    					return null;
	    				}
	    			}
	    		}, completableFutureExecutor);
	        	
	            futures.add(completableFuture);
	        }
	        //等待全部完成
	        CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
	        
	        File xl__rels_workbookxml_f = new File(filefolder + File.separator + "xl" + File.separator +"_rels"+ File.separator + "workbook.xml.rels");
			if (!xl__rels_workbookxml_f.exists()) {
				xl__rels_workbookxml_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(xl__rels_workbookxml_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("n"); 
			sb.append("n"); 
			sb.append("n");
			for (int j = 0; j < futures.size(); j++) {
				String val = futures.get(j).get();
				sb.append("n");
			}
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			
			File Content_Types_f = new File(filefolder + File.separator + "[Content_Types].xml");
			if (!Content_Types_f.exists()) {
				Content_Types_f.createNewFile();
			}
			rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(Content_Types_f)));
			sb = new StringBuilder();
			sb.append("n");
			sb.append("n"); 
			sb.append("n");
			sb.append("n"); 
			sb.append("n"); 
			sb.append("n");
			sb.append("n"); 
			sb.append("n"); 
			sb.append("n");
			for (int j = 0; j < futures.size(); j++) {
				String val = futures.get(j).get();
				sb.append("n");
			}
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			
			completableFutureExecutor.shutdown();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static String createNewSheet(int i) {
		String filename = "sheet" + (i+1) + ".xml";
		try {
			String filefolder = "F:\xlsxtest222";
			File _rels_f = new File(filefolder + File.separator + "xl" + File.separator + "worksheets" + File.separator + filename);
			if (!_rels_f.exists()) {
				_rels_f.createNewFile();
			}
			DataOutputStream rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(_rels_f)));
			StringBuilder sb = new StringBuilder();
			sb.append("n");
			if (i == 0) {
				sb.append("n");
			} else {
				sb.append("n");
			}
			for (int j = 0; j < 100; j++) {
				if (j == 0) {
					sb.append("n");
					sb.append("column0");
					sb.append("column1");
					sb.append("column2");
					sb.append("column3");
					sb.append("column4");
					sb.append("column5");
					sb.append("column6");
					sb.append("column7");
					sb.append("column8");
					sb.append("column9");
					sb.append("column10");
					sb.append("n");
				} else {
					sb.append("n");
					sb.append("").append(j).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("").append(String.valueOf(Math.random())).append("");
					sb.append("n");
				}
			}
			sb.append("");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
			return filename;
		} catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
}

需要使用的依赖包commons-compressmaven坐标如下：


  org.apache.commons
  commons-compress
  1.20

以上就是全部内容，转载请注明出处。

一次大数据量导出优化--借助xml导出xls、xlsx文件

大数据系统相关栏目本月热门文章