下面的代码使用POI3.1,合并11个3000多行的文档用时约6秒
- @SuppressWarnings("unchecked")
- public class XlsMergeUtil {
- private static Logger logger = LoggerFactory.getLogger(XlsMergeUtil.class);
- /**
- * 将多个Xls文件合并为一个,适用于只有一个sheet,并且格式相同的文档
- * @param inputs 输入的Xls文件
- * @param out 输出文件
- */
- public static void merge(InputStream[] inputs, OutputStream out) {
- if (inputs == null || inputs.length <= 1) {
- throw new IllegalArgumentException("没有传入输入流数组,或只有一个输入流.");
- }
- 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 = getRowsOfSheet(rootSheet); //记录第一篇文档的行数,以后的行数在此基础上增加
- rootSheet.setLoc(rootSheet.getDimsLoc());
- Map<Integer, Integer> map = new HashMap(10000);
- for (int i = 1; i < inputs.length; i++) { //从第二篇开始遍历
- List<Record> records = getRecords(inputs[i]);
- int rowsOfCurXls = 0;
- //遍历当前文档的每一个record
- for (Iterator itr = records.iterator(); itr.hasNext();) {
- Record record = (Record) itr.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(map.get(Integer.valueOf(label.getSSTIndex())));
- }
- //追加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);
- }
- static void write(OutputStream out, byte[] data) {
- POIFSFileSystem fs = new POIFSFileSystem();
- // Write out the Workbook stream
- try {
- fs.createDocument(new ByteArrayInputStream(data), "Workbook");
- fs.writeFilesystem(out);
- out.flush();
- } catch (IOException e) {
- e.printStackTrace();
- } finally {
- try {
- out.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- static List<Sheet> getSheets(Workbook workbook, List records) {
- int recOffset = workbook.getNumRecords();
- int sheetNum = 0;
- // convert all LabelRecord records to LabelSSTRecord
- convertLabelRecords(records, recOffset, workbook);
- List<Sheet> sheets = new ArrayList();
- while (recOffset < records.size()) {
- Sheet sh = Sheet.createSheet(records, sheetNum++, recOffset);
- recOffset = sh.getEofLoc() + 1;
- if (recOffset == 1) {
- break;
- }
- sheets.add(sh);
- }
- return sheets;
- }
- static int getRows(List<Record> records) {
- int row = 0;
- for (Iterator itr = records.iterator(); itr.hasNext();) {
- Record record = (Record) itr.next();
- if (record.getSid() == RowRecord.sid) {
- row++;
- }
- }
- return row;
- }
- static int getRowsOfSheet(Sheet sheet) {
- int rows = 0;
- sheet.setLoc(0);
- while(sheet.getNextRow() != null) {
- rows++;
- }
- return rows;
- }
- @SuppressWarnings("deprecation")
- static List<Record> getRecords(InputStream input) {
- try {
- POIFSFileSystem poifs = new POIFSFileSystem(input);
- InputStream stream = poifs.getRoot().createDocumentInputStream("Workbook");
- return org.apache.poi.hssf.record.RecordFactory.createRecords(stream);
- } catch (IOException e) {
- logger.error("IO异常:{}", e.getMessage());
- e.printStackTrace();
- }
- return Collections.EMPTY_LIST;
- }
- static void convertLabelRecords(List records, int offset, Workbook workbook) {
- 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);
- LabelSSTRecord newrec = new LabelSSTRecord();
- int stringid = workbook.addSSTString(new UnicodeString(oldrec.getValue()));
- newrec.setRow(oldrec.getRow());
- newrec.setColumn(oldrec.getColumn());
- newrec.setXFIndex(oldrec.getXFIndex());
- newrec.setSSTIndex(stringid);
- records.add(k, newrec);
- }
- }
- }
- public static byte[] getBytes(Workbook workbook, Sheet[] sheets) {
- // HSSFSheet[] sheets = getSheets();
- int nSheets = sheets.length;
- // before getting the workbook size we must tell the sheets that
- // serialization is about to occur.
- for (int i = 0; i < nSheets; i++) {
- sheets[i].preSerialize();
- }
- int totalsize = workbook.getSize();
- // pre-calculate all the sheet sizes and set BOF indexes
- 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
- + ")");
- Sheet.serializeIndexRecord() does not
- }
- pos += serializedSize;
- }
- return retval;
- }
- public static void main(String[] args) throws Exception {
- final String PATH = "E://projects//java//ws_0//export//data//";
- InputStream[] inputs = new InputStream[10];
- inputs[0] = new java.io.FileInputStream(PATH + "07_10.xls");
- for(int i = 1; i <= 9; i++) {
- inputs[i] = new java.io.FileInputStream(PATH + "07_0" + i + ".xls");
- }
- OutputStream out = new FileOutputStream(PATH + "xx.xls");
- long t1 = System.currentTimeMillis();
- merge(inputs, out);
- System.out.println(System.currentTimeMillis() - t1);//简陋的测试一下时间
- }
- }