poi多excel合并sheet

猪牛彤

已于 2022-09-06 16:33:48 修改

阅读量555

点赞数

分类专栏： poi 文章标签： java

于 2022-09-06 16:29:41 首次发布

本文链接：https://blog.csdn.net/fsgsgdh/article/details/126727967

版权

poi 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

该代码示例演示了如何使用Apache POI库将多个包含多个工作表的Excel文件合并到一个新的Excel文件中。程序遍历指定目录下的所有Excel文件，读取每个文件的工作表，并复制到目标文件中，同时处理单元格样式、公式和合并区域，最后将所有内容写入新的Excel文件。

摘要由CSDN通过智能技术生成

将多个生成好的excel中的sheet页取出合并成一个excel

下面展示一些 内联代码片。

public static void main(String[] args) throws Exception{

        // TODO Auto-generated method stub
        // 将所有类型的尽调excel文件合并成一个excel文件
        String Path = "G:\\111";
        File file = new File(Path);
        File[] tempList = file.listFiles();
        String TmpList [] = new String [2];
        System.out.println("该目录下对象个数：" + tempList.length);
        for (int i = 0; i < tempList.length; i++) {
            if (tempList[i].isFile()) {
                TmpList[i] = tempList[i].toString();
                System.out.println("文件:"+TmpList[i]+" 待处理");
            }
        }
//        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        InputStream in1 = new FileInputStream(TmpList[0]);
        XSSFWorkbook fromExcel1 = new XSSFWorkbook(in1);
//        for (String fromExcelName : TmpList) {    // 遍历每个源excel文件，TmpList为源文件的名称集合
        for (int i = 1; i < TmpList.length; i++) {
            InputStream in = new FileInputStream(TmpList[i]);
            XSSFWorkbook fromExcel = new XSSFWorkbook(in);
            int length = fromExcel.getNumberOfSheets();
            if(length<=1){       //长度为1时
                XSSFSheet oldSheet = fromExcel.getSheetAt(0);
                XSSFSheet newSheet = fromExcel1.createSheet(oldSheet.getSheetName());
                copySheet(fromExcel1, oldSheet, newSheet);
            }else{
//                for (int i = 0; i < length; i++) {// 遍历每个sheet
                    XSSFSheet oldSheet = fromExcel.getSheetAt(i);
                    XSSFSheet newSheet = fromExcel1.createSheet(oldSheet.getSheetName()+"wqe");
                    newSheet.setForceFormulaRecalculation(true);
                    fromExcel1.setSheetOrder(oldSheet.getSheetName()+"wqe", 1);//开启自动重算
                    copySheet(fromExcel1, oldSheet, newSheet);


//                }
            }
        }
        String allFileName = Path+ "\\1.xlsx";    //定义新生成的xlx表格文件
        FileOutputStream fileOut = new FileOutputStream(allFileName);
        fromExcel1.write(fileOut);
        fileOut.flush();
        fileOut.close();
//		// 删除各个源文件
//		for (String fromExcelName : TmpList) {// 遍历每个源excel文件
//			File Existfile = new File(fromExcelName);
//			if (Existfile.exists()) {
//				Existfile.delete();
//			}
//		}
        System.out.println("运行结束!");



    }


    /**
     * 合并单元格
     * @param fromSheet
     * @param toSheet
     */
    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }
    public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
        toStyle.cloneStyleFrom(fromStyle);// 此一行代码搞定
    }

    /**
     * 复制单元格
     * @param wb
     * @param fromCell
     * @param toCell
     */
    public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //  toCell.setEncoding(fromCell.getStringCelllValue());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        int fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
            if (XSSFDateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {
            // nothing21
        } else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else { // nothing29
        }

    }

    /**
     * 行复制功能
     * @param wb
     * @param oldRow
     * @param toRow
     */
    public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }

    /**
     * Sheet复制
     * @param wb
     * @param fromSheet
     * @param toSheet
     */
    public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        // 设置列宽
        XSSFRow row = fromSheet.getRow(fromSheet.getFirstRowNum());
        if(row != null){
            int length = row.getLastCellNum();
            for (int i = 0; i <= length; i++) {
                toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
            }
        }

        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }