Turn from http://www.itboth.com/d/MjI3Ef/excel-java
reference material: http://bbs.51cto.com/thread-1074293-1-1.html http://bbs.51cto.com/viewthread.php?tid=1074293&extra=&page=1
At present, there are indeed many Excel files that can be generated in the java framework. However, I did not find that the excel framework that can generate a large amount of data will have memory overflow when the amount of data is large. Therefore, the generation of Excel files with a large amount of data should return to nature and be realized by using the basic technology of java and IO stream.
If you want to use IO stream to generate excel file, you must know the format and content of Excel file, which is equivalent to generating html file. You can generate an html file by splicing html tags with strings and saving them to a text file. Similarly, Excel files are also OK. How do you know the file format of excel? In fact, it's very simple. Just create a new EXCEL file, double-click to open it, and then click "file" - "save as". The saved type is "xml table". After saving, open it in text format, and you can see the string format of excel at a glance.
Copy the following xml string to a text file and save it in xls format, which is an excel file.
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Created>1996-12-17T01:32:42Z</Created>
<LastSaved>2000-11-18T06:53:49Z</LastSaved>
<Version>11.9999</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>4530</WindowHeight>
<WindowWidth>8505</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>120</WindowTopY>
<AcceptLabelsInFormulas/>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Song typeface" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:AutoFitWidth="0" ss:Width="73.5"/>
<Row>
<Cell><Data ss:Type="String">zhangzehao</Data></Cell>
<Cell><Data ss:Type="String">zhangzehao</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">zhangzehao</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>3</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
In addition to this key point, if you want to generate excel with more than ten million levels, you also need to control the IO flow. If there are 10 million records, you need to iterate 10 million times to assemble the xml string, which must occupy a considerable amount of memory and memory overflow. Therefore, you must refresh the assembled xml character string to the hard disk with the IO flow in batches. If it is in a web application, you can refresh it to the response, The web application will automatically save the temporary stream to the temporary file of the client, and then copy it to your saved path at one time. To get back to business, if you refresh in batches, you can flush a batch of data into the hard disk iteratively, assign the list and large objects to null, and explicitly call the garbage collector to reclaim memory. In this way, no matter how much data is generated, there will be no memory overflow. I once tried to export 100 million Excel files, and there will be no memory overflow. It only took 35 minutes.
Of course, if you want to make the implementation elegant, you can combine template technology when assembling xml strings. I personally like the lightweight framework stringtemplate. The DEMO I give is also generated by template technology. Of course, velocity and freemaker are OK, and stringbuilder is OK, ha ha.
I'm lazy. I didn't mean to write a post. I just want to earn more download beans: lol1, it's the same as making money. Who doesn't want to? Who knows, I wrote so much. At the same time, I have little knowledge and hope to attract jade.
To sum up: the technology used is stringTemplate
pom.xml:
<dependency>
<groupId>antlr</groupId>
<artifactId>antlr</artifactId>
<version>2.7.7</version>
</dependency>
<dependency>
<groupId>org.antlr</groupId>
<artifactId>stringtemplate</artifactId>
<version>3.2.1</version>
</dependency>
template object:
class Row{
private List<String> result;
public List<String> getResult() {
return result;
}
public void setResult(List<String> result) {
this.result = result;
}
}
class Worksheet{
private String sheet;
private int columnNum;
private int rowNum;
private List<String> title;
private List<Row> rows;
public String getSheet() {
return sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public List<Row> getRows() {
return rows;
}
public void setRows(List<Row> 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<String> getTitle() {
return title;
}
public void setTitle(List<String> title) {
this.title = title;
}
}
Recommendation: export data in large quantities in Java. The format can be xml or excel
[the export function of large amount of data has been used in the recent project. It is not sure how large it is. There are at least 100000 records. The 100M excel file, Excel 2003, can no longer be opened and can only be used in the 2007 version. I don't know that the amount of data in the later stage is larger
Template file (general):
excel header template
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Created>1996-12-17T01:32:42Z</Created> <LastSaved>2013-08-02T09:21:24Z</LastSaved> <Version>11.9999</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <RemovePersonalInformation/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>4530</WindowHeight> <WindowWidth>8505</WindowWidth> <WindowTopX>480</WindowTopX> <WindowTopY>120</WindowTopY> <AcceptLabelsInFormulas/> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Song typeface" x:CharSet="134" ss:Size="12"/> <Interior/> <NumberFormat/> <Protection/> </Style> </Styles>
body template:
$worksheet:{
<Worksheet ss:Name="$it.sheet$">
<Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Row>
$it.title:{
<Cell><Data ss:Type="String">$it$</Data></Cell>
}$
</Row>
$it.rows:{
<Row>
$it.result:{
<Cell><Data ss:Type="String">$it$</Data></Cell>
}$
</Row>
}$
</Table>
</Worksheet>
}$
Actual processing class: pass in the list object and use reflection to obtain the object attribute name and attribute value
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");
//Solve possible Chinese garbled code
stGroup.setFileCharEncoding("UTF-8");
//Write excel file header information
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);
//The maximum number of rows in excel sheet is 65535
List record = target;
List<String> title = new ArrayList<String>();
List<Method> getMethods = new ArrayList<Method>();
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");
//Write excel file data information
for(int i=0;i<sheets;i++){
StringTemplate body = stGroup.getInstanceOf("st/body");
Worksheet worksheet = new Worksheet();
worksheet.setTitle(title);
worksheet.setSheet(" "+(i+1)+" ");
worksheet.setColumnNum(columnLength);
worksheet.setRowNum(maxRowNum+1);
List<Row> rows = new ArrayList<Row>();
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<String> result = new ArrayList<String>(columnLength);
for(int n=0;n<columnLength;n++){
Object 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", worksheet);
writer.print(body.toString());
writer.flush();
rows.clear();
rows = null;
worksheet = null;
body = null;
Runtime.getRuntime().gc();
System.out.println("Building excel Document sheet"+(i+1));
}
//Write to the end of excel file
writer.print("</Workbook>");
writer.flush();
writer.close();
System.out.println("generate excel File complete");
long endTime = System.currentTimeMillis();
System.out.println("Time use="+((endTime-startTime)/1000)+"second");
}
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<Sample> result = new ArrayList<Sample>();
for(int i=0;i<100;i++){
result.add(new Sample("Zoom in on both sides"+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());
//Solve possible Chinese garbled code
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();
}
}