全部栏目

首页 旅游资讯 线路攻略 景点大全 国内游 境外游 美食特产
您的当前位置：首页正文

Java导出大批量数据excel（百万级）（转载）

2021-06-18 来源：汇智旅游网
Java导出⼤批量数据excel（百万级）（转载）

⽬前java框架中能够⽣成excel⽂件的的确不少，但是，能够⽣成⼤数据量的excel框架，我倒是没发现，⼀般数据量⼤了都会出现内存溢出，所以，⽣成⼤数据量的excel⽂件要返璞归真，⽤java的基础技术,IO流来实现。

如果想⽤IO流来⽣成excel⽂件，必须要知道excel的⽂件格式内容，相当于⽣成html⽂件⼀样，⽤字符串拼接html标签保存到⽂本⽂件就可以⽣成⼀个html⽂件了。同理，excel⽂件也是可以的。怎么知道excel的⽂件格式呢？其实很简单，随便新建⼀个excel⽂件，双击打开，然后点击“⽂件”-》“另存为”，保存的类型为“xml表格”，保存之后⽤⽂本格式打开，就可以看到excel的字符串格式⼀览⽆遗了。把下⾯的xml字符串复制到⽂本⽂件，然后保存为xls格式，就是⼀个excel⽂件。

xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"xmlns:html=\"http://www.w3.org/TR/REC-html40\">

1996-12-17T01:32:42Z

2000-11-18T06:53:49Z 11.9999

4530 8505 480 120

False False

zhangzehao zhangzehao

zhangzehao

3

5 3

False

False

False

False

False

False

　　如果要⽣成千万级别以上的excel，除了这个关键点之外，还要控制IO流，如果有1000万记录，要迭代1000万次组装xml字符串，这样肯定占⽤相当⼤的内存，肯定内存溢出，所以，必须把组装的xml字符串分批⽤IO流刷新到硬盘⾥，如果是在web应⽤中，可以刷新到

response中，web应⽤会⾃动把临时流保存到客户端的临时⽂件中，然后再⼀次性复制到你保存的路径。⾔归正传，分批刷新的话，可以迭代⼀批数据就flush进硬盘，同时把list，⼤对象赋值为空，显式调⽤垃圾回收器，表明要回收内存。这样的话，不管⽣成多⼤的数据量都不会出现内存溢出的，我曾经试过导出1亿的excel⽂件，都不会出现内存溢出，只是⽤了35分钟。

当然，如果要把实现做的优雅⼀些，在组装xml字符串的时候，可以结合模板技术来实现，我个⼈喜好stringtemplate这个轻量级的框架，我给出的DEMO也是采⽤了模板技术⽣成的，当然velocity和freemarker都是可以，stringbuilder也⾏，呵呵。

我为⼈⽐较懒，本意不是为了写个帖⼦的，只是想多赚点下载⾖:lol1 ，这和赚钱⼀样谁不想？谁知道就写了那么多。同时鄙⼈知识寡陋，希望可以抛砖引⽟。

综上：使⽤技术为 stringTemplate pom.xml：

antlr antlr 2.7.7

org.antlr

stringtemplate 3.2.1

template对象：

class Row{

private List result; public List getResult() { return result; }

public void setResult(List result) { this.result = result; }}

class Worksheet{

private String sheet;

private int columnNum;

private int rowNum; private List title;

private List rows; public String getSheet() { return sheet; }

public void setSheet(String sheet) { this.sheet = sheet; }

public List getRows() { return rows; }

public void setRows(List rows) { this.rows = rows; }

public int getColumnNum() { return columnNum; }

public void setColumnNum(int columnNum) { this.columnNum = columnNum; }

public int getRowNum() { return rowNum; }

public void setRowNum(int rowNum) { this.rowNum = rowNum; }

public List getTitle() { return title; }

public void setTitle(List title) { this.title = title; }}

推荐：

[ 最近项⽬中⽤到⼤数据量导出功能.不能确定到底有多⼤,最少⼗来万条记录. 100M的excel⽂件,excel2003已经不能够打开了,只能⽤2007版.不知道后期数据量更⼤模版⽂件（通⽤）：excel 头模板

xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">

1996-12-17T01:32:42Z

2013-08-02T09:21:24Z 11.9999

4530 8505 480 120

False False

body模板：

$worksheet:{

$it.title:{

$it$ }$

$it.rows:{ $it.result:{

$it$ }$

}$

}$

实际处理类：传⼊list对象，利⽤反射获取对象属性名及属性值

long startTimne = System.currentTimeMillis();

StringTemplateGroup stGroup = new StringTemplateGroup(\"stringTemplate\");

//写⼊excel⽂件头部信息

StringTemplate head = stGroup.getInstanceOf(\"head\"); File file = new File(\"D:/output2.xls\");

PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file))); writer.print(head.toString()); writer.flush();

int totalRowNum = listWinningRecordDTOList.size(); int maxRowNum = 60000;

int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1); //excel单表最⼤⾏数是65535

List record = listWinningRecordDTOList; List title = new ArrayList();

List getMethods = new ArrayList(); Class clazz = record.get(0).getClass();

Field[] fields = clazz.getDeclaredFields(); if(fields != null && fields.length > 0){ for(Field field : fields){

if(!\"serialVersionUID\".equals(field.getName())) { title.add(field.getName());

getMethods.add(clazz.getDeclaredMethod(\"get\" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1))); } } }

// BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);

// PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();// for(PropertyDescriptor propertyDescriptor : proDescrtptors){// title.add(propertyDescriptor.getName());

// getMethods.add(propertyDescriptor.getReadMethod());// }

int columnLength = title.size();

SimpleDateFormat sdf = new SimpleDateFormat(\"yyyy-MM-dd HH:mm:ss\"); //写⼊excel⽂件数据信息 for(int i=0;iStringTemplate body = stGroup.getInstanceOf(\"body\"); Worksheet worksheet = new Worksheet(); worksheet.setTitle(title);

worksheet.setSheet(\" \"+(i+1)+\" \");

worksheet.setColumnNum(columnLength); worksheet.setRowNum(maxRowNum+1); List rows = new ArrayList(); int startIndex = i*maxRowNum;

int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1); for(int j=startIndex;j<=endIndex;j++){ Row row = new Row();

List result = new ArrayList(columnLength); for(int n=0;nObject value = getMethods.get(n).invoke(record.get(j)); if(value == null){ result.add(\"\"); }else{

if(value instanceof Date){

result.add(sdf.format((Date)value)); }else{

result.add(value.toString()); } }

}

row.setResult(result); rows.add(row); }

worksheet.setRows(rows);

body.setAttribute(\"worksheet\ writer.print(body.toString()); writer.flush(); rows.clear(); rows = null;

worksheet = null; body = null;

Runtime.getRuntime().gc();

System.out.println(\"正在⽣成excel⽂件的 sheet\"+(i+1)); }

//写⼊excel⽂件尾部

writer.print(\"\"); writer.flush(); writer.close();

System.out.println(\"⽣成excel⽂件完成\"); long endTime = System.currentTimeMillis();

System.out.println(\"⽤时=\"+((endTime-startTimne)/1000)+\"秒\");

整理后的公⽤类：

import org.antlr.stringtemplate.StringTemplate;

import org.antlr.stringtemplate.StringTemplateGroup;import java.io.*;

import java.lang.reflect.Field;

import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;

/**

* Created by Administrator on 2016/2/25. */

public class ExcelStUtil {

public static void export(OutputStream outputStream,List target) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException { long startTime = System.currentTimeMillis();

StringTemplateGroup stGroup = new StringTemplateGroup(\"stringTemplate\"); //解决可能发⽣的中⽂乱码

stGroup.setFileCharEncoding(\"UTF-8\"); //写⼊excel⽂件头部信息

StringTemplate head = stGroup.getInstanceOf(\"st/head\");

PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream)); writer.print(head.toString()); writer.flush();

int totalRowNum = target.size(); int maxRowNum = 60000;

int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1); //excel单表最⼤⾏数是65535

List record = target;

List title = new ArrayList();

List getMethods = new ArrayList(); Class clazz = record.get(0).getClass();

Field[] fields = clazz.getDeclaredFields(); if(fields != null && fields.length > 0){ for(Field field : fields){

if(!\"serialVersionUID\".equals(field.getName())) { title.add(field.getName());

getMethods.add(clazz.getDeclaredMethod(\"get\" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1))); } } }

// BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);

// PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();// for(PropertyDescriptor propertyDescriptor : proDescrtptors){// title.add(propertyDescriptor.getName());

// getMethods.add(propertyDescriptor.getReadMethod());// }

int columnLength = title.size();

SimpleDateFormat sdf = new SimpleDateFormat(\"yyyy-MM-dd HH:mm:ss\"); //写⼊excel⽂件数据信息 for(int i=0;iStringTemplate body = stGroup.getInstanceOf(\"st/body\"); Worksheet worksheet = new Worksheet(); worksheet.setTitle(title);

worksheet.setSheet(\" \"+(i+1)+\" \");

worksheet.setColumnNum(columnLength); worksheet.setRowNum(maxRowNum+1); List rows = new ArrayList(); int startIndex = i*maxRowNum;

int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1); for(int j=startIndex;j<=endIndex;j++){

Row row = new Row();

List result = new ArrayList(columnLength); for(int n=0;nObject value = getMethods.get(n).invoke(record.get(j)); if(value == null){ result.add(\"\"); }else{

if(value instanceof Date){

result.add(sdf.format((Date)value)); }else{

result.add(value.toString()); } }

}

row.setResult(result); rows.add(row); }

worksheet.setRows(rows);

body.setAttribute(\"worksheet\ writer.print(body.toString()); writer.flush(); rows.clear(); rows = null;

worksheet = null; body = null;

Runtime.getRuntime().gc();

System.out.println(\"正在⽣成excel⽂件的 sheet\"+(i+1)); }

//写⼊excel⽂件尾部

writer.print(\"\"); writer.flush(); writer.close();

System.out.println(\"⽣成excel⽂件完成\"); long endTime = System.currentTimeMillis();

System.out.println(\"⽤时=\"+((endTime-startTime)/1000)+\"秒\"); }

public static void main(String[] args) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException { System.out.println(Thread.currentThread().getContextClassLoader().getResource(\"\").getPath()); System.out.println(ExcelStUtil.class.getResource(\"\").getPath());

System.out.println(ExcelStUtil.class.getClassLoader().getResource(\"\").getPath()); List result = new ArrayList(); for(int i=0;i<100;i++){

result.add(new Sample(\"放⼤双⽅的\"+String.valueOf(i),String.valueOf(i))); }

//OutputStream outputStream = new FileOutputStream(\"D:/output2.xls\");

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); ExcelStUtil.export(byteArrayOutputStream,result);

//ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray()); //解决可能发⽣的中⽂乱码

ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toString().getBytes(\"UTF-8\")); File file = new File(\"D:/output2.xls\");

OutputStream output = new FileOutputStream(file);

BufferedOutputStream bufferedOutput = new BufferedOutputStream(output); //bufferedOutput.write(byteArrayOutputStream.toByteArray());

bufferedOutput.write(byteArrayOutputStream.toString().getBytes(\"UTF-8\")); bufferedOutput.flush(); bufferedOutput.close(); }}

推荐：

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文