java导出大批量(百万以上)数据的excel文件

java导出大批量(百万以上)数据的excel文件

本文实例为大家分享了java导出百万以上数据的excel文件，供大家参考，具体内容如下

1.传统的导出方式会消耗大量的内存,2003每个sheet页最多65536条数据,2007每个sheet页可以达到100万条数据以上,2007会在生成Workbook时清理数据,所以2007导出量更大;

2.可以导出多个excel文件到某个目录中,然后打包下载;

3.导出excel格式的xml文件,这种方式http://可以分批导出数据,适用于大批量数据的导出,以下简单介绍这种方式:

代码如下:

package com.epay.utils;

/**

* 大数据量导出成EXCEL或XML

* @author qad

* 2017-04-22

*/

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 Test {

public static void main(String[] args) {

StringBuffer sb = new StringBuffer();

try {

DataOutputStream rafs = new DataOutputStream(

new BufferedOutputStream(new FileOutputStream(new File(

"d://test.xls"))));

sb.append("");

sb.append("\n");

sb.append("");

sb.append("\n");

sb.append("

sb.append("\n");

sb.append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");

sb.append("\n");

sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");

sb.append("\n");

sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");

sb.append("\n");

sb.append(" xmlns:html=\"http://w3.org/TR/REC-html40\">");

sb.append("\n");

sb.append(" ");

sb.append("\n");

sb.append(" \n");

sb.append(" \n");

sb.append(" \n");

int sheetcount = 0;

int recordcount = 65535;

int currentRecord = 0;

int total = 100000;

int col = 20;

sb.append("");

sb.append("\n");

sb.append("

+ "\" ss:ExpandedRowCount=\"" + total

+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");

sb.append("\n");

for (int i = 0; i < total; i++) {

if ((currentRecord == recordcount

|| currentRecord > recordcount || currentRecord == 0)

&& i != 0) {// 一个sheet写满

currentRecord = 0;

rafs.write(sb.toString().getBytes());

sb.setLength(0http://);

sb.append("

sb.append("\n");

sb.append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");

sb.append("\n");

sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");

sb.append("\n");

sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");

sb.append("\n");

sb.append(" xmlns:html=\"http://w3.org/TR/REC-html40\">");

sb.append("\n");

sb.append(" ");

sb.append("\n");

sb.append(" \n");

sb.append(" \n");

sb.append(" \n");

int sheetcount = 0;

int recordcount = 65535;

int currentRecord = 0;

int total = 100000;

int col = 20;

sb.append("");

sb.append("\n");

sb.append("

+ "\" ss:ExpandedRowCount=\"" + total

+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");

sb.append("\n");

for (int i = 0; i < total; i++) {

if ((currentRecord == recordcount

|| currentRecord > recordcount || currentRecord == 0)

&& i != 0) {// 一个sheet写满

currentRecord = 0;

rafs.write(sb.toString().getBytes());

sb.setLength(0http://);

sb.append("

sb.append("");

sb.append("\n");

sb.append("False");

sb.append("\n");

sb.append("False");

sb.append("\n");

sb.append("");

sb.append("\n");

sb.append("");

sb.append("

+ "\">");

sb.append("\n");

sb.append("

+ "\" ss:ExpandedRowCount=\"" + recordcount

+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");

sb.append("\n");

}

sb.append("");

for (int j = 0; j < col; j++) {

System.out.println(i);

sb.append("111");

sb.append("\n");

}

sb.append("");

if (i % 5000 == 0) {

rafs.write(sb.toString().getBytes());

rafs.flush();

sb.setLength(0);

}

sb.append("\n");

currentRecord++;

}

rafs.write(sb.toString().getBytes());

sb.setLength(0);

sb.append("

+ "\">");

sb.append("\n");

sb.append("

+ "\" ss:ExpandedRowCount=\"" + recordcount

+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");

sb.append("\n");

}

sb.append("");

for (int j = 0; j < col; j++) {

System.out.println(i);

sb.append("111");

sb.append("\n");

}

sb.append("");

if (i % 5000 == 0) {

rafs.write(sb.toString().getBytes());

rafs.flush();

sb.setLength(0);

}

sb.append("\n");

currentRecord++;

}

rafs.write(sb.toString().getBytes());

sb.setLength(0);

sb.append("

sb.append("");

sb.append("\n");

sb.append("False");

sb.append("\n");

sb.append("False");

sb.append("\n");

sb.append("");

sb.append("\n");

sb.append("");

sb.append("");

sb.append("\n");

rafs.write(sb.toString().getBytes());

rafs.flush();

rafs.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

}

导出xml文件之后直接修改后缀名为.xlsx就可以直接打开,弊端:无法导出.xls格式的excel文件.以上只是一种简单想法,留待以后具体实现.

扩展:既然可以使用xml文件导出excel,那么导出csv文件之后也可以直接改后缀名为.xls,目前只是一种想法,尚未证实可行性.

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。