java excel

package myTest.com;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.DimensionsRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RecordFactory;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelMergeUtil {
	public static void merge(List<String> sourceFiles, String destFile)
			throws Exception {
		InputStream[] inputs = new InputStream[sourceFiles.size()];
		for (int i = 0; i < sourceFiles.size(); i++) {
			inputs[i] = new FileInputStream(sourceFiles.get(i));
		}

		OutputStream out = new FileOutputStream(destFile);

		merge(inputs, out);
	}

	public static void main(String[] args) 
	{
		List<String> filepaht=new ArrayList<String>();
		filepaht.add("F:\\test1.xls");
		filepaht.add("F:\\test2.xls");
		filepaht.add("F:\\test3.xls");
		filepaht.add("F:\\test4.xls");
		filepaht.add("F:\\test5.xls");
		try {
			merge(filepaht,"F:\\test_aLL.xls");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void merge(InputStream[] inputs, OutputStream out)
			throws Exception {
		Map map = null;
		try {
			if (inputs == null || inputs.length <= 1) {
				throw new IllegalArgumentException("没有传入输入流数组或只有一个输入流！");
			}
			System.out.println("需要合并的文件数为：" + inputs.length);

			// 第一个文档
			List<Record> rootRecords = getRecords(inputs[0]);
			Workbook workbook = Workbook.createWorkbook(rootRecords);
			List<Sheet> sheets = getSheets(workbook, rootRecords);
			if (sheets == null || sheets.size() == 0) {
				throw new IllegalArgumentException("第一个文档格式错误，必须至少有一个sheet！");
			}

			// 以第一个文档的最后一个sheet为根，以后的数据都追加在这个sheet后面
			Sheet rootSheet = sheets.get(sheets.size() - 1);
			int rootRows = getRows(rootRecords); // 记录第一篇文档的行数，以后的行数在此基础上增加
			rootSheet.setLoc(rootSheet.getDimsLoc());
			map = new HashMap(1000);

			for (int i = 1; i < inputs.length; i++) { // 从第二篇开始遍历
				List<Record> records = getRecords(inputs[i]);
				int rowsOfCurXls = 0;
				// 遍历当前文档的每一个record
				for (Iterator it = records.iterator(); it.hasNext();) {
					Record record = (Record) it.next();
					if (record.getSid() == RowRecord.sid) { // 如果是RowRecord
						RowRecord rowRecord = (RowRecord) record;
						rowRecord.setRowNumber(rootRows
								+ rowRecord.getRowNumber()); // 调整行号
						rootSheet.addRow(rowRecord); // 追加Row
						rowsOfCurXls++; // 记录当前文档的行数
					}
					// SST记录，SST保存xls文件中唯一的String，各个String都是对应着SST记录的索引
					else if (record.getSid() == SSTRecord.sid) {
						SSTRecord sstRecord = (SSTRecord) record;
						for (int j = 0; j < sstRecord.getNumUniqueStrings(); j++) {
							int index = workbook.addSSTString(sstRecord
									.getString(j));
							// 记录原来的索引和现在的索引的对应关系
							map.put(Integer.valueOf(j), Integer.valueOf(index));
						}
					} else if (record.getSid() == LabelSSTRecord.sid) {
						LabelSSTRecord label = (LabelSSTRecord) record;
						// 调整SST索引的对应关系
						label.setSSTIndex(((Integer) map.get(Integer
								.valueOf(label.getSSTIndex()))).intValue());
					}

					// 追加ValueCell
					if (record instanceof CellValueRecordInterface) {
						CellValueRecordInterface cell = (CellValueRecordInterface) record;
						int cellRow = cell.getRow() + rootRows;
						cell.setRow(cellRow);
						rootSheet.addValueRecord(cellRow, cell);
					}
				}
				rootRows += rowsOfCurXls;
			}

			byte[] data = getBytes(workbook, sheets.toArray(new Sheet[0]));

			write(out, data);

			System.out.println("合并完成");
		} finally {
			if (map != null) {
				map.clear();
				map = null;
			}
		}
	}

	static void write(OutputStream out, byte[] data) throws Exception {
		POIFSFileSystem fs = new POIFSFileSystem();
		try {
			fs.createDocument(new ByteArrayInputStream(data), "Workbook");
			fs.writeFilesystem(out);
			out.flush();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取Sheet列表
	 */
	static List<Sheet> getSheets(Workbook workbook, List<Record> records)
			throws Exception {
		int recOffset = workbook.getNumRecords();
		int sheetNum = 0;

		convertLabelRecords(records, recOffset, workbook);

		List<Sheet> sheets = new ArrayList<Sheet>();
		while (recOffset < records.size()) {
			Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset);
			recOffset = sheet.getEofLoc() + 1;
			if (recOffset == 1)
				break;
			sheets.add(sheet);
		}
		return sheets;
	}

	/**
	 * 取得一个sheet中数据的行数
	 */
	static int getRows(List<Record> records) {
		int row = 0;
		for (Iterator it = records.iterator(); it.hasNext();) {
			Record record = (Record) it.next();
			if (record.getSid() == DimensionsRecord.sid) {
				DimensionsRecord dr = (DimensionsRecord) record;
				row = dr.getLastRow();
				break;
			}
		}
		return row;
	}

	/**
	 * 获取Excel文档的记录集
	 */
	static List<Record> getRecords(InputStream input) {
		try {
			POIFSFileSystem poifs = new POIFSFileSystem(input);
			InputStream stream = poifs.getRoot().createDocumentInputStream(
					"Workbook");
			return RecordFactory.createRecords(stream);
		} catch (IOException e) {
			System.out.println("ExcelMergeUtil.getRecords: " + e.toString());
			e.printStackTrace();
		}
		return Collections.EMPTY_LIST;
	}

	static void convertLabelRecords(List<Record> records, int offset,
			Workbook workbook) throws Exception {
		for (int k = offset; k < records.size(); k++) {
			Record rec = (Record) records.get(k);

			if (rec.getSid() == LabelRecord.sid) {
				LabelRecord oldrec = (LabelRecord) rec;

				records.remove(k);
				int stringid = workbook.addSSTString(new UnicodeString(oldrec
						.getValue()));

				LabelSSTRecord newrec = new LabelSSTRecord();
				newrec.setRow(oldrec.getRow());
				newrec.setColumn(oldrec.getColumn());
				newrec.setXFIndex(oldrec.getXFIndex());
				newrec.setSSTIndex(stringid);
				records.add(k, newrec);
			}
		}
	}

	static byte[] getBytes(Workbook workbook, Sheet[] sheets) {
		int nSheets = sheets.length;

		for (int i = 0; i < nSheets; i++) {
			sheets[i].preSerialize();
		}

		int totalsize = workbook.getSize();

		int[] estimatedSheetSizes = new int[nSheets];
		for (int k = 0; k < nSheets; k++) {
			workbook.setSheetBof(k, totalsize);
			int sheetSize = sheets[k].getSize();
			estimatedSheetSizes[k] = sheetSize;
			totalsize += sheetSize;
		}

		byte[] retval = new byte[totalsize];
		int pos = workbook.serialize(0, retval);

		for (int k = 0; k < nSheets; k++) {
			int serializedSize = sheets[k].serialize(pos, retval);
			if (serializedSize != estimatedSheetSizes[k]) {
				throw new IllegalStateException(
						"Actual serialized sheet size (" + serializedSize
								+ ") differs from pre-calculated size ("
								+ estimatedSheetSizes[k] + ") for sheet (" + k
								+ ")");
			}
			pos += serializedSize;
		}
		return retval;
	}

}


///

package myTest.com;

/**
 * 大数据量导出成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 CopyOfTest {
	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\" encoding=\"GBK\" ?>");
			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("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
			sb.append("\n");
			sb.append("<Version>11.9999</Version>");
			sb.append("\n");
			sb.append("</DocumentProperties>");
			sb.append("\n");
			sb.append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
			sb.append("\n");
			sb.append("<WindowHeight>10005</WindowHeight>").append("\n");
			sb.append("<WindowWidth>10005</WindowWidth>");
			sb.append("\n");
			sb.append("<WindowTopX>120</WindowTopX>");
			sb.append("\n");
			 sb.append("<WindowTopY>135</WindowTopY>").append("\n");
			 sb.append("<ActiveSheet>1</ActiveSheet>").append("\n");
			 sb.append("<ProtectStructure>False</ProtectStructure>").append("\n");
			 sb.append("<ProtectWindows>False</ProtectWindows>").append("\n");
			 sb.append("</ExcelWorkbook>").append("\n");
			 sb.append("<Styles>").append("\n");
			 sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">").append("\n");
			 sb.append("<Alignment ss:Vertical=\"Center\"/>").append("\n");
			 sb.append("<Borders/>").append("\n");
			 sb.append("<Font ss:FontName=\"??ì?\" x:CharSet=\"134\" ss:Size=\"12\"/>").append("\n");
			 sb.append("<Interior/>").append("\n");
			 sb.append("<NumberFormat/>").append("\n");
			 sb.append("<Protection/>").append("\n");
			 sb.append("</Style>").append("\n");
			 sb.append("<Style ss:ID=\"s23\">").append("\n");
			 sb.append("<Font ss:FontName=\"??ì?\" x:CharSet=\"134\" ss:Size=\"12\" ss:Color=\"#0000FF\"").append("\n");
			 sb.append("ss:Bold=\"1\"/>").append("\n");
			 sb.append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>").append("\n");
			 sb.append("</Style>").append("\n");
			 sb.append("</Styles>").append("\n");
			 sb.append("\n");
			int sheetcount = 0;
			int recordcount = 2;
			int currentRecord = 0;
			int total = 2;
			int col = 10;

			for (int i = 0; i < total; i++) {
			if (i == 0) {// 一个sheet写满

//					sb.append("\n");
				sb.append("<Worksheet ss:Name=\"Sheet2\">").append("\n");
				sb.append("<Table ss:ExpandedColumnCount=\""+col+"\" ss:ExpandedRowCount=\""+total+"\" x:FullColumns=\"1\"").append("\n");
				sb.append("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">").append("\n");
				sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"73.5\"/>").append("\n");
				sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"119.25\"/>").append("\n");
				sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"89.25\"/>").append("\n");
				sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"141\"/>").append("\n");
			}

				sb.append("<Row ss:AutoFitHeight=\"0\">");
				sb.append("\n");

				for (int j = 0; j < col; j++) 
				{

						sb.append("<Cell ><Data ss:Type=\"String\">"+"aaa打算"+"</Data></Cell>");

					sb.append("\n");
					System.out.println(i);

				}
				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>").append("\n");
			sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">").append("\n");
			sb.append("<Print>").append("\n");
			sb.append("<ValidPrinterInfo/>").append("\n");
			sb.append("<PaperSizeIndex>9</PaperSizeIndex>").append("\n");
			sb.append("<HorizontalResolution>600</HorizontalResolution>").append("\n");
			sb.append("<VerticalResolution>600</VerticalResolution>").append("\n");
			sb.append("</Print>").append("\n");
			sb.append("<Selected/>").append("\n");

			sb.append("<ProtectObjects>False</ProtectObjects>").append("\n");
			sb.append("<ProtectScenarios>False</ProtectScenarios>").append("\n");
			sb.append("</WorksheetOptions>").append("\n");
			sb.append("</Worksheet>").append("\n");
			sb.append("</Workbook>");
			rafs.write(sb.toString().getBytes());
			rafs.flush();
			rafs.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}