精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (1)
|
|
---|---|
作者 | 正文 |
发表时间:2008-09-10
下面的代码使用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);//简陋的测试一下时间 } } 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2008-09-10
讲讲我的解决方法,
一般导出的Excel用来做报表或者统计用的,也不要求图片或其它对象,就是数据, 我的做法是写文本格式的Excel文件,而不是用POI等生成二进制的文件, 第一种格式,CSV,最简单的,格式最差,最基本的行列,不能合并,不能设置着色, 第二种,HTML格式的,如:"<TABLE>....</TABLE>"这样的文本,后辍名改为XLS就可以了,可以设置跨行列的合并,可以着色,图片没试过,估计是可以的,还可以设置单元格对齐,单元格的格式等, 写文本的时候,根本不用担心 OOM的问题,我最大写过 500多M的一个excel文件,不过这已经没有意义了,excel一个Sheet最大6万多行,多了也显示不出来. |
|
返回顶楼 | |
发表时间:2008-09-10
leasass 写道 讲讲我的解决方法,
一般导出的Excel用来做报表或者统计用的,也不要求图片或其它对象,就是数据, 我的做法是写文本格式的Excel文件,而不是用POI等生成二进制的文件, 第一种格式,CSV,最简单的,格式最差,最基本的行列,不能合并,不能设置着色, 第二种,HTML格式的,如:"<TABLE>....</TABLE>"这样的文本,后辍名改为XLS就可以了,可以设置跨行列的合并,可以着色,图片没试过,估计是可以的,还可以设置单元格对齐,单元格的格式等, 写文本的时候,根本不用担心 OOM的问题,我最大写过 500多M的一个excel文件,不过这已经没有意义了,excel一个Sheet最大6万多行,多了也显示不出来. 你的办法我也用过,的确没有溢出的问题,不过客户不同意的。我们现在的这个的客户很各色,以前其他的客户就可以用你的办法搞定。比如客户要求一次导出20w行数据,这就要求导出的文件必须带sheet,上面的代码也不支持sheet,下面的就可以了: @SuppressWarnings("unchecked") public class XlsMergeUtil { private static Logger logger = LoggerFactory.getLogger(XlsMergeUtil.class); /** * 将多个Xls文件合并为一个,适用于只有一个sheet,并且格式相同的文档 * @param inputs 输入的Xls文件,第一个XLS文件必须给出足够sheet空间 * 例如,总共200000行数据,第一个文件至少3个空白sheet * @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(0); int rootRows = getRowsOfSheet(rootSheet); //记录第一篇文档的行数,以后的行数在此基础上增加 rootSheet.setLoc(rootSheet.getDimsLoc()); Map<Integer, Integer> map = new HashMap(10000); int sheetIndex = 0; for (int i = 1; i < inputs.length; i++) { //从第二篇开始遍历 List<Record> records = getRecords(inputs[i]); //达到最大行数限制,换一个sheet if(getRows(records) + rootRows >= RowRecord.MAX_ROW_NUMBER) { if((++sheetIndex) > (sheets.size() - 1)) { logger.warn("第一个文档给出的sheets小于需要的数量,部分数据未能合并."); break; } rootSheet = sheets.get(sheetIndex); rootRows = getRowsOfSheet(rootSheet); rootSheet.setLoc(rootSheet.getDimsLoc()); logger.debug("切换Sheet{}", sheetIndex); } 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; } logger.debug("分配内存{}bytes", totalsize); 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]) { // Wrong offset values have been passed in the call to setSheetBof() above. // For books with more than one sheet, this discrepancy would cause excel // to report errors and loose data while reading the workbook throw new IllegalStateException("Actual serialized sheet size (" + serializedSize + ") differs from pre-calculated size (" + estimatedSheetSizes[k] + ") for sheet (" + k + ")"); // TODO - add similar sanity check to ensure that Sheet.serializeIndexRecord() does not // write mis-aligned offsets either } 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[25]; inputs[0] = new java.io.FileInputStream(PATH + "07_10.xls"); for(int i = 1; i < 25 ; i++) { inputs[i] = new java.io.FileInputStream(PATH + "07_01.xls"); } OutputStream out = new FileOutputStream(PATH + "xx.xls"); long t1 = System.currentTimeMillis(); merge(inputs, out); System.out.println(System.currentTimeMillis() - t1); } } |
|
返回顶楼 | |
发表时间:2008-09-10
用完那些row、cell之后手动设为null有一点帮助
|
|
返回顶楼 | |
发表时间:2008-09-10
才3000多行? cell的数据很大么?要这么搞?
不然又是闲得慌的. |
|
返回顶楼 | |
发表时间:2008-09-10
翻一下我早些时候的贴应该能找到,实际上你改一下java启动参数内存设置就KO了,一次读6k的不到一秒钟
|
|
返回顶楼 | |
发表时间:2008-09-11
超级潜水员 写道 才3000多行? cell的数据很大么?要这么搞?
不然又是闲得慌的. 把25~40个3261行的合并成一个文件 |
|
返回顶楼 | |
发表时间:2008-09-11
Joo 写道 翻一下我早些时候的贴应该能找到,实际上你改一下java启动参数内存设置就KO了,一次读6k的不到一秒钟
6k?还叫数据? 我现在做的这个项目,变态客户要求把52w行数据导出到一个Excel,传统的使用HSSF或JXL方式读写,你给我调调参数试试。 |
|
返回顶楼 | |
发表时间:2008-09-11
jongh 写道 用完那些row、cell之后手动设为null有一点帮助
cell有上千万...,我这里没有用到Cell对象,都是二进制的record 而且在getBytes之前设置为null会什么也导不出去。 |
|
返回顶楼 | |
发表时间:2008-09-11
能不能导一部分先保存一下文件,然后再追加
|
|
返回顶楼 | |