Java大批量导出数据,格式可以为xml或excel.

        最近项目中用到大数据量导出功能.不能确定到底有多大,最少十来万条记录.
        100M的excel文件,excel2003已经不能够打开了,只能用2007版.不知道后期数据量更大的时候怎么办.废话不多说,上代码.Copy别人的.以备以后使用.
       

package com.epay.utils;

/**
 * 大数据量导出成EXCEL或XML
 * @author J.W
 * 2011-06-08
 */
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.xml"))));
			sb.append("<?xml version=\"1.0\"?>");
			sb.append("\n");
			sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
			sb.append("\n");
			sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
			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://www.w3.org/TR/REC-html40\">");
			sb.append("\n");
			sb.append(" <Styles>\n");
			sb.append("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
			sb.append("   <Alignment ss:Vertical=\"Center\"/>\n");
			sb.append("   <Borders/>\n");
			sb.append("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\n");
			sb.append("   <Interior/>\n");
			sb.append("   <NumberFormat/>\n");
			sb.append("   <Protection/>\n");
			sb.append("  </Style>\n");
			sb.append(" </Styles>\n");
			int sheetcount = 0;
			int recordcount = 20;
			int currentRecord = 0;
			int total = 100;
			int col = 20;
			sb.append("<Worksheet ss:Name=\"Sheet0\">");
			sb.append("\n");
			sb.append("<Table ss:ExpandedColumnCount=\"" + col
					+ "\" 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(0);
					sb.append("</Table>");
					sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
					sb.append("\n");
					sb.append("<ProtectObjects>False</ProtectObjects>");
					sb.append("\n");
					sb.append("<ProtectScenarios>False</ProtectScenarios>");
					sb.append("\n");
					sb.append("</WorksheetOptions>");
					sb.append("\n");
					sb.append("</Worksheet>");
					sb.append("<Worksheet ss:Name=\"Sheet" + i / recordcount
							+ "\">");
					sb.append("\n");
					sb.append("<Table ss:ExpandedColumnCount=\"" + col
							+ "\" ss:ExpandedRowCount=\"" + recordcount
							+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
					sb.append("\n");
				}
				sb.append("<Row>");
				for (int j = 0; j < col; j++) {
					System.out.println(i);
					sb.append("<Cell><Data ss:Type=\"String\">111</Data></Cell>");
					sb.append("\n");
				}
				sb.append("</Row>");
				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("</Table>");
			sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
			sb.append("\n");
			sb.append("<ProtectObjects>False</ProtectObjects>");
			sb.append("\n");
			sb.append("<ProtectScenarios>False</ProtectScenarios>");
			sb.append("\n");
			sb.append("</WorksheetOptions>");
			sb.append("\n");
			sb.append("</Worksheet>");
			sb.append("</Workbook>");
			sb.append("\n");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

       
        文件格式可以自定义,改成XML或者excel都可以, 代码复制到eclipse可以直接运行.还需要再研究研究poi导出.完了以后做个比较,顺便记录下来.

评论

1 楼 rbw204 2013-09-03  

单元格内容是中文的时候，打开excel报错，打不开，请问遇到过吗，怎么解决？