基于模板excel导出

    本示例中使用了StringTemplate模板技术，进行excel数据导出的操作。

     1.首先导入所需要的jar包，本示例中通过maven引入

     

<dependency>
			<groupId>org.antlr</groupId>
			<artifactId>stringtemplate</artifactId>
			<version>3.2</version>
		</dependency>

  2.示例代码如下：

package yang.zheng.util.excel;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;



class Student{
	private String name;
	
	private int age;
	
	private List<String> hobbys = new ArrayList<String>();

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public List<String> getHobbys() {
		return hobbys;
	}

	public void setHobbys(List<String> hobbys) {
		this.hobbys = hobbys;
	}
}

class Row{
	private String name1;
	
	private String name2;
	
	private String name3;

	public String getName1() {
		return name1;
	}

	public void setName1(String name1) {
		this.name1 = name1;
	}

	public String getName2() {
		return name2;
	}

	public void setName2(String name2) {
		this.name2 = name2;
	}

	public String getName3() {
		return name3;
	}

	public void setName3(String name3) {
		this.name3 = name3;
	}
	
	
}

class Worksheet{
	private String sheet;
	
	private int columnNum;
	
	private int rowNum;
	
	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 class ExcelTemplate{
	
	public static void main(String[] args) throws FileNotFoundException{
		ExcelTemplate template = new ExcelTemplate();
		template.output2();
	}

	/**
	 * @param args
	 * @throws FileNotFoundException 
	 */
	public void template() throws FileNotFoundException {
		StringTemplate st = new StringTemplate("hello,$name$");
		st.setAttribute("name", "china");
		System.out.println(st.toString());
		
		StringTemplate st2 = new StringTemplate("select $columns:{<i>$it$</i>\n}$ from users"); 
		List<String> columns = new ArrayList<String>();
		columns.add("a");
		columns.add("b");
		columns.add("c");
		columns.add("d");
		columns.add("e");
		st2.setAttribute("columns",columns);
		System.out.println(st2.toString());
		
		StringTemplate st3 = new StringTemplate("$students:{" +
				"$it.name$," +
				"$it.age$," +
				"$it.hobbys:{$it$,}$" +
			"}$"); 
		List<Student> students = new ArrayList<Student>();
		Student student = new Student();
		student.setName("hunter");
		student.setAge(24);
		List<String> hobbyList = new ArrayList<String>();
		hobbyList.add("sports");
		hobbyList.add("grils");
		hobbyList.add("money");
		student.setHobbys(hobbyList);
		students.add(student);
		
		student = new Student();
		student.setName("xiaoming");
		student.setAge(25);
		hobbyList = new ArrayList<String>();
		hobbyList.add("movie");
		hobbyList.add("coding");
		student.setHobbys(hobbyList);
		students.add(student);
		
		st3.setAttribute("students", students);
		
		System.out.println(st3.toString());
		
	}
	
	/**
	 * 生成数据量大的时候，该方法会出现内存溢出
	 * @throws FileNotFoundException
	 */
	public void output1() throws FileNotFoundException{
		StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");         
		StringTemplate st4 =  stGroup.getInstanceOf("excelTemplate/test");
		List<Worksheet> worksheets = new ArrayList<Worksheet>();

		File file = new File("D:/output.xls");
		PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
		
		for(int i=0;i<30;i++){
			Worksheet worksheet = new Worksheet();
			worksheet.setSheet("第"+(i+1)+"页");
			List<Row> rows = new ArrayList<Row>();
			for(int j=0;j<6000;j++){
				Row row = new Row();
				row.setName1("zhangzehao");
				row.setName2(""+j);
				row.setName3(i+" "+j);
				rows.add(row);
			}
			worksheet.setRows(rows);
			worksheets.add(worksheet);
		}
		
		st4.setAttribute("worksheets", worksheets);
		writer.write(st4.toString());
		writer.flush();
		writer.close();
		System.out.println("生成excel完成");
	}
	
	/**
	 * 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短
	 * 
	 * 经测试，生成1800万数据，6~10分钟之间，3G大的文件，打开大文件就看内存是否足够大了
	 * 
	 * 数据量小的时候，推荐用jxls的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用
	 * 
	 * @throws FileNotFoundException
	 */
	public void output2() throws FileNotFoundException{
		long startTimne = System.currentTimeMillis();
		
		StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");   
		
		//写入excel文件头部信息
		StringTemplate head =  stGroup.getInstanceOf("excelTemplate/head");
		File file = new File("D:/output.xls");
		PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
		writer.print(head.toString());
		writer.flush();
		
		int sheets = 300;
		//excel单表最大行数是65535
		int maxRowNum = 60000;
		
		//写入excel文件数据信息
		for(int i=0;i<sheets;i++){
			StringTemplate body =  stGroup.getInstanceOf("excelTemplate/body");
			Worksheet worksheet = new Worksheet();
			worksheet.setSheet(" "+(i+1)+" ");
			worksheet.setColumnNum(3);
			worksheet.setRowNum(maxRowNum);
			List<Row> rows = new ArrayList<Row>();
			for(int j=0;j<maxRowNum;j++){
				Row row = new Row();
				row.setName1(""+new Random().nextInt(100000));
				row.setName2(""+j);
				row.setName3(i+""+j);
				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("正在生成excel文件的 sheet"+(i+1));
		}
		
		//写入excel文件尾部
		writer.print("</Workbook>");
		writer.flush();
		writer.close();
		System.out.println("生成excel文件完成");
		long endTime = System.currentTimeMillis();
		System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
	}

}

 3.内容模板（body）

 $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">
 $it.rows:{
   <Row>
    <Cell><Data ss:Type="String">$it.name1$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name2$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name3$</Data></Cell>
   </Row>
 }$
  </Table>
 </Worksheet>
}$

4.表头模板(head)

<?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="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>

4.test模板

<?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="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>