求教Apache POI 合并多个Excel的效率问题

fjt_2378623 2017-09-27 12:22:22

POI新人，现在有多个Excel文件各自包含多个sheet工作表，要将这些Excel文件合并。
目前合并操作是通过各sheet工作表逐行复制到新excel文件对应的同名sheet表中，代码如下：
比如两个excel分别包含4个工作表，每个工作表只是20来行的，这样合并时间也要15秒左右

。何况我们场景里还包括更多个Excel需要合并。
PS：POI版本是3.16，Excel版本是2010，即使用的是XSSFSheet类。
查看POI的API文档里面并没有找到整个sheet一次性复制的相关方法，是我看的不仔细还是我的方法仍需要优化？

求大神赐教啊！



import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;



import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFColor;

import org.apache.poi.xssf.usermodel.XSSFDataFormat;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;



 public static boolean mergeFiles(List<String> fileList, String targetFile){

        //初始化目标文件工作簿

        XSSFWorkbook targetwb = new  XSSFWorkbook();

        

        //初始化原文件工作簿集合

        List<XSSFWorkbook> wblist = new ArrayList<XSSFWorkbook>();

        

        try {

            FileOutputStream fileOut = new FileOutputStream(targetFile);

            

            if(null != fileList && 0 < fileList.size()){

                //遍历原文件集合，生成原文件工作簿集合

                for(String file : fileList){

                    wblist.add(new XSSFWorkbook(new FileInputStream(file)));

                }

                

                //遍历原文件工作簿集合，进行复制操作

                for(XSSFWorkbook xwb : wblist){

                    targetwb = copyExcelRows(xwb, targetwb);

                }

            }

            else{

                createExcelFile(targetFile);

            }

            

            

            //输出目标文件到指定路径

            targetwb.write(fileOut);

            

            fileOut.flush();

            fileOut.close();

            targetwb.close();

            System.out.println("生成完成!");

            

        } catch (FileNotFoundException e) {

            e.printStackTrace();

            System.out.println("生成失败， 未找到模板文件!");

            

            return false;

        } catch (IOException e) {

            e.printStackTrace();

            System.out.println("生成失败， 目标文件写入失败!");

            

            return false;

        }

        

        return true;

    }



public static XSSFWorkbook copyExcelRows(XSSFWorkbook wb, XSSFWorkbook pTargetWb ) {

        int pStartRow = 0;                       //开始行

        int pEndRow =0;                          //结束行

        int pPosition = 0;                       //位置

        String pSourceSheetName="";

        String pTargetSheetName ="";

        XSSFRow sourceRow = null;

        XSSFRow targetRow = null;

        XSSFCell sourceCell = null;

        XSSFCell targetCell = null;

        XSSFSheet sourceSheet = null;

        XSSFSheet targetSheet = null;

        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 14);

        int cType;

        int i;

        short j;

        int targetRowFrom;

        int targetRowTo;

        if ((pStartRow == -1) || (pEndRow == -1)) {

            return null;

        }

        int sheetNum = wb.getNumberOfSheets();

        for(int s = 0;s < sheetNum;s++){

            pSourceSheetName = wb.getSheetName(s);

            pTargetSheetName = pSourceSheetName;

            int pTargetSheetNumber = pTargetWb.getNumberOfSheets()+1;

            if(pTargetWb.getSheet(pTargetSheetName) != null){

                pTargetSheetName = pTargetSheetName + "(" + pTargetSheetNumber + ")";

            }

            pTargetWb.createSheet(pTargetSheetName);

            sourceSheet = wb.getSheet(pSourceSheetName);

            targetSheet = pTargetWb.getSheet(pTargetSheetName);

            pEndRow = sourceSheet.getPhysicalNumberOfRows();

            // 拷贝合并的单元格

            for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {

                region = sourceSheet.getMergedRegion(i);

                if ((region.getFirstRow() >= pStartRow)

                        && (region.getLastRow() <= pEndRow)) {

                    targetRowFrom = region.getFirstRow() - pStartRow + pPosition;

                    targetRowTo = region.getLastRow() - pStartRow + pPosition;

                    region.setFirstRow(targetRowFrom);

                    region.setLastRow(targetRowTo);

                    targetSheet.addMergedRegion(region);

                }

            }

            // 设置列宽

            for (i = pStartRow; i <= pEndRow; i++) {

                sourceRow = sourceSheet.getRow(i);

                if (sourceRow != null) {

                    for (j = sourceRow.getLastCellNum(); j > sourceRow

                            .getFirstCellNum(); j--) {

                        targetSheet

                                .setColumnWidth(j, sourceSheet.getColumnWidth(j));

                        targetSheet.setColumnHidden(j, false);

                    }

                    break;

                }

            }

            // 拷贝行并填充数据

            for (; i <= pEndRow; i++) {

                sourceRow = sourceSheet.getRow(i);

                if (sourceRow == null) {

                    continue;

                }

                targetRow = targetSheet.createRow(i - pStartRow + pPosition);

                targetRow.setHeight(sourceRow.getHeight());

                for (j = sourceRow.getFirstCellNum(); j < sourceRow

                        .getPhysicalNumberOfCells(); j++) {

                    sourceCell = sourceRow.getCell(j);

                    if (sourceCell == null) {

                        continue;

                    }

                    targetCell = targetRow.createCell(j);

                    String sCellstyle = getCellStyle(sourceCell,wb);

//                    System.out.println(sCellstyle);

                    targetCell.setCellStyle(createCellStyle(pTargetWb,sCellstyle));

                    cType = sourceCell.getCellType();

                    targetCell.setCellType(cType);

                    switch (cType) {

                    case XSSFCell.CELL_TYPE_BOOLEAN:

                        targetCell.setCellValue(sourceCell.getBooleanCellValue());

                        break;

                    case XSSFCell.CELL_TYPE_ERROR:

                        targetCell

                                .setCellErrorValue(sourceCell.getErrorCellValue());

                        break;

                    case XSSFCell.CELL_TYPE_FORMULA:

                        // parseFormula这个函数的用途在后面说明

                        targetCell.setCellFormula(parseFormula(sourceCell

                                .getCellFormula()));

                        break;

                    case XSSFCell.CELL_TYPE_NUMERIC:

                        targetCell.setCellValue(sourceCell.getNumericCellValue());

                        break;

                    case XSSFCell.CELL_TYPE_STRING:

                        targetCell

                                .setCellValue(sourceCell.getRichStringCellValue());

                        break;

                    }

                }

            }

        }

        

        return pTargetWb;

    }