java写出大数据（千万级别）的excel探索（二）

java写出大数据excel的方法探索（二）

上一篇介绍了原理，本偏正式将原理应用于实际。

再来理理清楚xml内容中实际写出的哪些数据是变化的：

以一个worksheet为例

<Worksheet ss:Name="<strong><span style="color:#ff0000;">Sheet1</span></strong>">   
  <Table ss:ExpandedColumnCount="<span style="background-color: rgb(255, 255, 255);"><span style="color:#ff0000;">5</span></span>" ss:ExpandedRowCount="<strong><span style="color:#ff0000;">2</span></strong>" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
   <Row>
    <Cell><Data ss:Type="Number"><strong><span style="color:#cc0000;">1</span></strong></Data></Cell>
    <Cell><Data ss:Type="Number"><strong><span style="color:#ff0000;">1</span></strong></Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number"><span style="color:#ff0000;"><strong>2</strong></span></Data></Cell>
<Cell><Data ss:Type="Number"><span style="color:#ff0000;"><strong>2</strong></span></Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>27</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>

经过测试只有部分内容是需要修改的，如下说明：

ss:Name="<strong><span style="color: rgb(255, 0, 0);">Sheet1</span></strong>" ： sheet名字，多个sheet不能重名

ss:ExpandedColumnCount="<span style="background-color: rgb(255, 255, 255);"><span style="color: rgb(255, 0, 0);">5</span></span>" ： 列数量，这个值可以由我们从写出的数据集合获取

<pre name="code" class="html">ss:ExpandedRowCount="<strong><span style="color: rgb(255, 0, 0);">2</span></strong>" ：行数量，<span style="font-family: Arial, Helvetica, sans-serif;">这个值可以由我们从写出的数据集合获取，这个值就是单个sheet的总量；</span>

<Row>
    <Cell><Data ss:Type="Number"><strong><span style="color: rgb(204, 0, 0);">1</span></strong></Data></Cell>
    <Cell><Data ss:Type="Number"><strong><span style="color: rgb(255, 0, 0);">1</span></strong></Data></Cell>
</Row>

数据，需要写出的数据。

最终结果 1000W条数据写出到了excel  用时如下:

200多秒。

没有再跑第二次了，只是模拟了，不过现在碰到的问题就是excel太大打开费劲，这个问题就让客户自己头疼吧，谁让他提出这样的需求。

代码如下 主要方法：

1、处理业务与启动方法：

/**
 * 系统项目名称
 * org.xhf.excel.utils
 * WriteDataToExcel.java
 * 
 * 2016年5月7日-上午1:41:23
 *  2016XX公司-版权所有
 *
 */
package org.xhf.excel.utils;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.List;

/**
 *
 * WriteDataToExcel
 * 总共10000000条数据，每个excel 3个sheet页 每个页最多1000000条数据，每次从模拟数据库取500条
 * 统计时间：
 * kin kin 2016年5月7日 上午1:41:23
 * 
 * @version 1.0.0
 *
 */
public class WriteBigDataExcel
{
	
	
	
	public static final int defaultMax = 10000000;
	
	public static void main(String[] args)
	{
		long start = System.currentTimeMillis();
		
		exportExcel(3, 1000000, 500);
		
		long end = System.currentTimeMillis();
		
		System.out.println("1000W数据总共时间：" + (end - start));
	}
	
	/**
	 * 
	 * exportExcel(这里用一句话描述这个方法的作用)
	 * (这里描述这个方法适用条件 – 可选)
	 * @param sheetCount 每个文件多少sheet页
	 * @param lineCount 每个sheet页最大多少行数据
	 * @param iteratorNum  这个只是模拟批次查询而已，根据实际情况设置
	 *void
	 * @exception
	 * @since  1.0.0
	 */
	public static void exportExcel(int sheetCount, int lineCount, int iteratorNum)
	{
		int fileNum = 0;
		

<span style="white-space:pre">		sheetCount--;</span>

		BufferedWriter bw = null;

		OutputStreamWriter osw = null;

		FileOutputStream fos = null;
		
		String file = InitTemplate.path + "file"+fileNum+".xls";
		
		int cursor = 0;
		int sheetNum = 0;
		int rows = 0;
		try{
			//第一个文件直接写入
			fos = new FileOutputStream(file);
			osw = new OutputStreamWriter(fos, "UTF-8");
			bw = new BufferedWriter(osw);
			bw.write(InitTemplate.fileHeader);
			bw.flush();
			boolean isNewHeader = true;
			//游标模拟数据库最大数据，分页查询
			while(cursor < defaultMax)
			{
				System.out.println(cursor);
				//一个sheet页
				List<List<String>> lines = null;
				lines = DataBase.getData(iteratorNum);
				int columnCount = lines.get(0).size();
				cursor+=lines.size();
				if(isNewHeader)//第一次迭代
				{
					String sheetName = "Sheet" + sheetNum;
					String sheetHeader = getSheetHeader(sheetName, columnCount, lineCount);
					bw.write(sheetHeader);
					bw.flush();
					isNewHeader = false;
				}
				rows+=lines.size();
				if(rows > lineCount)
				{
					//写出sheet尾
					bw.write(InitTemplate.sheetFooter);
					bw.flush();
					sheetNum++;
					//写出下一个sheet，新的sheetName，如果sheet数量超过规定的数量，则新建一个新的文件，将本次查到的数据写入到下一个文件的sheet0内
					if(sheetNum > sheetCount)
					{
						//写出文件尾
						bw.write(InitTemplate.fileFooter);
						bw.flush();
						
						//写入到一个新的文件中
						fileNum++;
						file = InitTemplate.path + "file"+fileNum+".xls";
						fos = new FileOutputStream(file);
						osw = new OutputStreamWriter(fos, "UTF-8");
						bw = new BufferedWriter(osw);
						bw.write(InitTemplate.fileHeader);
						bw.flush();
						sheetNum = 0;//sheet清零
					}
					rows = lines.size();
					String sheetName = "Sheet" + sheetNum;
					String sheetHeader = getSheetHeader(sheetName, columnCount, lineCount);
					bw.write(sheetHeader);
					bw.flush();
				}
				writeSheetContent(bw, lines);
			}
			if(rows > 0)
			{
				bw.write(InitTemplate.sheetFooter);
				bw.flush();
				bw.write(InitTemplate.fileFooter);
				bw.flush();
			}
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally{
			if(bw!=null)
			{
				try
				{
					bw.close();
				}
				catch (IOException e)
				{
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * writeSheetContent(这里用一句话描述这个方法的作用)
	 * (这里描述这个方法适用条件 – 可选)
	 * @param bw
	 * @param lines
	 * @return
	 * @throws IOException
	 *List<List<String>>
	 * @exception
	 * @since  1.0.0
	*/
	private static void writeSheetContent(BufferedWriter bw, List<List<String>> lines) throws IOException
	{
		for (int i = 0; i < lines.size(); i++)
		{
			StringBuffer sb = new StringBuffer(100);
			sb.append("<Row>\r\n");

			for (int j = 0; j < lines.get(i).size(); j++)
			{
				sb.append("<Cell><Data ss:Type=\"Number\">");
				sb.append(lines.get(i).get(j));
				sb.append("</Data></Cell>\r\n");
			}
			sb.append("</Row>\r\n");
			bw.write(sb.toString());
			bw.flush();
			sb = null;
		}
		lines=null;
		Runtime.getRuntime().gc();
	}

	private static String getSheetHeader(String sheetName, int columnCount, int lineCount)
	{
		StringBuffer sh = new StringBuffer(100);
		sh.append("<Worksheet ss:Name=\""+sheetName+"\">\r\n");
		sh.append("<Table ss:ExpandedColumnCount=\""+columnCount+"\" ");
		sh.append("ss:ExpandedRowCount=\""+lineCount+"\" ");
		sh.append("x:FullColumns=\"1\"\r\nx:FullRows=\"1\" ");
		sh.append("ss:DefaultColumnWidth=\"54\" ");
		sh.append("ss:DefaultRowHeight=\"13.5\">\r\n");
		return sh.toString();
	}
}

2、加载生成文件的固定部分：

/**
 * 系统项目名称
 * org.xhf.excel.utils
 * Init.java
 * 
 * 2016年5月7日-上午1:20:25
 *  2016XX公司-版权所有
 *
 */
package org.xhf.excel.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * Init
 * 
 * kin
 * kin
 * 2016年5月7日 上午1:20:25
 * 
 * @version 1.0.0
 *
 */
public class InitTemplate
{
	
	public static final String path = System.getProperty("user.dir") + File.separator + "config/";
	
	//xml中这一行以上的部分<Worksheet ss:Name="Sheet1">
	public static String fileHeader;
	
	//每个sheet最后一个</Row>标签的到下一个<Worksheet....>之间的内容
	public static String sheetFooter;
	
	//xml结尾
	public static final String fileFooter = "</Workbook>";
	
	/**
	 * 初始化将模版读取到缓存中
	 */
	static
	{
		File headerFile = new File(path + "template/head");
		fileHeader = getString(headerFile);
		
		Pattern p = Pattern.compile("ss:FontName=\"(.*)\" x:");
		Matcher m = p.matcher(fileHeader); 
        while(m.find()){
        	fileHeader = fileHeader.replaceAll(m.group(1), "宋体");
         }
		File sheetFooterFile = new File(path + "template/sheetFooter");
		sheetFooter = getString(sheetFooterFile);
	}
	
	private static String getString(File file)
	{
		StringBuffer sb = new StringBuffer(100);
		InputStreamReader fr = null;
		BufferedReader br = null;
		FileInputStream fis = null;
		try
		{
			fis = new FileInputStream(file);
			fr = new InputStreamReader(fis, "UTF-8");
			br = new BufferedReader(fr);
			String str = "";
			while((str = br.readLine())!= null)
			{
				sb.append(str).append("\r\n");
			}
			return sb.toString();
		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			close(fis, br, fr);
		}
		return "";
	}
	
	private static void close(FileInputStream fis, BufferedReader br, InputStreamReader fr)
	{
		//省略
	}
	public static void close(BufferedWriter bw, OutputStreamWriter osw, FileOutputStream fos)
	{
		//省略
	}


	public static void close(FileInputStream fis, BufferedInputStream bis, FileOutputStream fos,
			BufferedOutputStream bos)
	{
		//省略了
	}
	
}

附上固定部分的内容：

header：

<?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>2006-09-16T00:00:00Z</Created>
  <LastSaved>2006-09-16T00:00:00Z</LastSaved>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8010</WindowHeight>
  <WindowWidth>14805</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>105</WindowTopY>
  <ActiveSheet>2</ActiveSheet>
  <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="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>

sheetfooter：

 </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>27</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>

3、很简单的模拟数据库数据

/**
 * 系统项目名称
 * org.xhf.excel.utils
 * DataBase.java
 * 
 * 2016年5月7日-上午1:47:41
 *  2016XX公司-版权所有
 *
 */
package org.xhf.excel.utils;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * DataBase
 * 
 * kin
 * kin
 * 2016年5月7日 上午1:47:41
 * 
 * @version 1.0.0
 *
 */
public class DataBase
{
	/**
	 * 模拟数据库数据 一次 size*10的数量
	 * @param size
	 * @return
	 *List<List<String>>
	 * @exception
	 * @since  1.0.0
	 */
	public static List<List<String>> getData(int size)
	{
		List<List<String>> body = new ArrayList<List<String>>();
		
		for(int i = 0; i< size ;i++)
		{
			List<String> line = new ArrayList<String>();
			List<String> line1 = new ArrayList<String>();
			List<String> line2 = new ArrayList<String>();
			List<String> line3 = new ArrayList<String>();
			List<String> line4 = new ArrayList<String>();
			List<String> line5 = new ArrayList<String>();
			List<String> line6 = new ArrayList<String>();
			List<String> line7 = new ArrayList<String>();
			List<String> line8 = new ArrayList<String>();
			List<String> line9 = new ArrayList<String>();
			for(int j=0;j<10;j++)
			{
				line.add(i+j + i*j + "");
				line1.add(i+j + i*j + "");
				line2.add(i+j + i*j + "");
				line3.add(i+j + i*j + "");
				line4.add(i+j + i*j + "");
				line5.add(i+j + i*j + "");
				line6.add(i+j + i*j + "");
				line7.add(i+j + i*j + "");
				line8.add(i+j + i*j + "");
				line9.add(i+j + i*j + "");
			}
			body.add(line);
			body.add(line1);
			body.add(line2);
			body.add(line3);
			body.add(line4);
			body.add(line5);
			body.add(line6);
			body.add(line7);
			body.add(line8);
			body.add(line9);
		}
		return body;
		
	}
	
}