java合并多个excel文件内容到一个新的excel文件中

1、引入依赖

 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>4.1.2</version>
</dependency>

 

2、工具类

/**
     * 合并多个excel文件内容到一个新的excel文件中
     * @param fileLists   文件集合
     * @param path    目标文件保存路径
     * @param fileName   目标文件名
     */
    public void mergeExcel(List<File> fileLists, String path, String fileName){
       FileOutputStream fos = null;
       InputStream fis = null;
       File excelFile = null;//生成新的excel文件
       HSSFWorkbook hssfWorkbook = null;
       XSSFWorkbook xssfWorkbook = null;
       HSSFWorkbook hworkbook = new HSSFWorkbook();
       XSSFWorkbook xworkboot = new XSSFWorkbook();
       //遍历需要合并的文件
        for (int i = 0;i<fileLists.size();i++){
            try {
                fis = new FileInputStream(fileLists.get(i).getAbsoluteFile());
                if(".xls".equals(fileLists.get(i).getName().endsWith(".xls"))){
                    hssfWorkbook = new HSSFWorkbook(fis);
                    int sheetNum = hssfWorkbook.getNumberOfSheets();
                    if(sheetNum<=1){
                        Sheet newExcelSheet = null;
                        Sheet tempSheet = hssfWorkbook.getSheetAt(0);
                        int sheetCount = hworkbook.getNumberOfSheets();
                        if(sheetCount>=1){
                            int num = 0;
                            for (int j = 0;j<sheetCount;j++){
                                if(hworkbook.getSheetAt(j).getSheetName().equals(tempSheet.getSheetName())){
                                    newExcelSheet = hssfWorkbook.getSheetAt(j);
                                    num = num + 1;
                                    break;
                                }else {
                                    continue;
                                }
                            }
                            if(num == 0){
                                newExcelSheet = hssfWorkbook.createSheet(tempSheet.getSheetName());
                            }
                            //复制sheet内容
                            copyExcelSheet(hssfWorkbook,tempSheet,newExcelSheet,i);
                        }else{
                            newExcelSheet = hworkbook.createSheet(tempSheet.getSheetName());
                            copyExcelSheet(hssfWorkbook,tempSheet,newExcelSheet,i);
                        }
                    }else{
                        Sheet newExcelSheet = null;
                       int sheetCount =  hssfWorkbook.getNumberOfSheets();
                       for (int j = 0;j<sheetNum;j++){
                          Sheet tmpSheet =  hssfWorkbook.getSheetAt(j);
                          if(sheetCount>=1){
                              for (int k = 0;k<sheetCount;k++){
                                  if(tmpSheet.getSheetName().equals(hworkbook.getSheetAt(k).getSheetName())){
                                      newExcelSheet = hworkbook.getSheetAt(k);
                                  }else{
                                      newExcelSheet = hworkbook.createSheet(tmpSheet.getSheetName());
                                  }
                                 //复制sheet内容
                                  copyExcelSheet(hworkbook,tmpSheet,newExcelSheet,i);
                              }
                          }else{
                              newExcelSheet = hworkbook.createSheet(tmpSheet.getSheetName());
                              copyExcelSheet(hworkbook,tmpSheet,newExcelSheet,i);
                          }
                       }
                    }
                }else if(".xlsx".equals(fileLists.get(i).getName().endsWith(".xlsx"))){
                    xssfWorkbook = new XSSFWorkbook(fis);
                    int sheetNum = xssfWorkbook.getNumberOfSheets();
                    if(sheetNum<=1){
                        XSSFSheet newExcelSheet = null;
                       XSSFSheet tempSheet =  xssfWorkbook.getSheetAt(0);
                       int sheetCount =  xworkboot.getNumberOfSheets();
                       if (sheetCount>=1){
                           int num = 0;
                           for (int j = 0;j<sheetCount;j++){
                               if(xworkboot.getSheetAt(j).getSheetName().equals(tempSheet.getSheetName())){
                                   newExcelSheet = xworkboot.getSheetAt(j);
                                   num = num + 1;
                                   break;
                               }else{
                                   continue;
                               }
                           }
                           if(num == 0){
                               newExcelSheet = xworkboot.createSheet(tempSheet.getSheetName());
                           }
                           //复制sheet
                           copyXSSFExcelSheet(xssfWorkbook,tempSheet,newExcelSheet,i);
                       }
                    }
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }catch (IOException e) {
                e.printStackTrace();
            }
        }




    }

    private void copyXSSFExcelSheet(XSSFWorkbook xssfWorkbook, XSSFSheet tempSheet, XSSFSheet newExcelSheet, int i) {

        int rowNum = xssfWorkbook.getSheet(newExcelSheet.getSheetName()).getLastRowNum();
        if(i>=1){
            rowNum = rownum + 3;
        }
        if(tempSheet.getLastRowNum()>0){
            int len = tempSheet.getRow(tempSheet.getFirstRowNum()).getLastCellNum();
            for (int j = 0;j<len;j++){
                newExcelSheet.setColumnWidth(j,tempSheet.getColumnWidth(j));
            }
            //复制每行内容
            Iterator<Row> it = tempSheet.iterator();
            while (it.hasNext()){
               XSSFRow tmpRow = (XSSFRow) it.next();
               if(rowNum>0){
                  XSSFRow newExcelRow =  newExcelSheet.createRow(rownum++);
                  copyXssfExcelRow(xssfWorkbook,tmpRow,newExcelRow);
               }
            }
        }
    }

    private void copyXssfExcelRow(XSSFWorkbook xssfWorkbook, XSSFRow tmpRow, XSSFRow newExcelRow) {
        //设置行高
        newExcelRow.setHeight(tmpRow.getHeight());
        //获取所有列
        Iterator<Cell> it = tmpRow.cellIterator();
        while (it.hasNext()){
            XSSFCell tmpCell = (XSSFCell) it.next();
           XSSFCell newExcelCell =  newExcelRow.createCell(tmpCell.getColumnIndex());
           //复制单元格
            copyXssfExcelCell(xssfWorkbook,tmpCell,newExcelCell);
        }
    }

    private void copyXssfExcelCell(XSSFWorkbook xssfWorkbook, XSSFCell tmpCell, XSSFCell newExcelCell) {
        XSSFCellStyle newExcelStyle = xssfWorkbook.createCellStyle();
        newExcelStyle.cloneStyleFrom(tmpCell.getCellStyle());
        newExcelCell.setCellStyle(newExcelStyle);
        if(tmpCell.getCellComment()!=null){
            newExcelCell.setCellComment(tmpCell.getCellComment());
        }
        //不同数据类型处理
        switch (tmpCell.getCellType()){
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newExcelCell.setCellValue(tmpCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newExcelCell.setCellValue(tmpCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newExcelCell.setCellValue(tmpCell.getCachedFormulaResultType());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newExcelCell.setCellValue(tmpCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newExcelCell.setCellValue(tmpCell.getStringCellValue());
                break;
        }
    }

    private void copyExcelSheet(HSSFWorkbook hssfWorkbook, Sheet tempSheet, Sheet newExcelSheet, int i) {

        //获取sheet中的最后一行
        int rowNum = hssfWorkbook.getSheet(newExcelSheet.getSheetName()).getLastRowNum();
        if(i>=1){
            rowNum = rownum + 3;
        }
        if(tempSheet.getLastRowNum()>0){
           int len =  tempSheet.getRow(tempSheet.getFirstRowNum()).getLastCellNum();
           for (int j = 0;j<len;j++){
               newExcelSheet.setColumnWidth(j,tempSheet.getColumnWidth(j));
           }
           //复制每行内容
           Iterator<Row> it = tempSheet.iterator();
           while (it.hasNext()){
              Row tmpRow =  it.next();
              if(rowNum>0){
                  //创建新行
                 Row newExcelRow =  newExcelSheet.createRow(rownum);
                 copyExcelRow(hssfWorkbook,tmpRow,newExcelRow);
              }else{
                  Row newExcelRow = newExcelSheet.createRow(tmpRow.getRowNum() + 1);
                  copyExcelRow(hssfWorkbook,tmpRow,newExcelRow);
              }
           }
        }
    }

    private void copyExcelRow(HSSFWorkbook hssfWorkbook, Row tmpRow, Row newExcelRow) {
        //设置行高
        newExcelRow.setHeight(tmpRow.getHeight());
        //获取所有列
        Iterator<Cell> it = tmpRow.cellIterator();
        while (it.hasNext()){
           Cell tmpCell =  it.next();
           //创建单元格
           Cell newExcelCell =  newExcelRow.createCell(tmpCell.getColumnIndex());
           //复制单元格
            copyExcelCell(hssfWorkbook,tmpCell,newExcelCell);
        }
    }

    private void copyExcelCell(HSSFWorkbook hssfWorkbook, Cell tmpCell, Cell newExcelCell) {
        HSSFCellStyle newExcelStyle = hssfWorkbook.createCellStyle();
        //复制单元格样式
        newExcelStyle.cloneStyleFrom(tmpCell.getCellStyle());
        newExcelCell.setCellStyle(newExcelStyle);
        if(tmpCell.getCellComment()!= null){
            newExcelCell.setCellComment(tmpCell.getCellComment());
        }
        //不同数据类型处理
        switch (tmpCell.getCellType()){
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newExcelCell.setCellValue(tmpCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newExcelCell.setCellValue(tmpCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newExcelCell.setCellValue(tmpCell.getCachedFormulaResultType());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newExcelCell.setCellValue(tmpCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newExcelCell.setCellValue(tmpCell.getStringCellValue());
                break;
        }
    }