java 合并文件夹中所有execl的所有sheet到一个sheet中

最新推荐文章于 2024-04-24 17:55:39 发布

Debug_Name

最新推荐文章于 2024-04-24 17:55:39 发布

阅读量466

点赞数

分类专栏：日常生活文章标签： java 开发语言

本文链接：https://blog.youkuaiyun.com/qq_38534887/article/details/130383694

版权

日常生活专栏收录该内容

2 篇文章

订阅专栏

依赖

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

package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test{
    public static void main(String[] args) throws IOException {
        // 遍历文件夹e:/test中的所有xlsx文件
        File folder = new File("C:\\Users\\XXX\\Desktop\\XXX);
        File[] files = folder.listFiles((dir, name) -> name.endsWith(".xlsx"));
        for (File file : files) {
            // 读取xlsx文件
            FileInputStream inputStream = new FileInputStream(file);
            Workbook workbook = new XSSFWorkbook(inputStream);
            List<Sheet> sheets = new ArrayList<>();
            int sheetCount = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                sheets.add(workbook.getSheetAt(i));
            }
            // 合并xlsx文件中的所有sheet到新的sheet
            Sheet newSheet = workbook.createSheet("Merged");
            int rowIndex = 0;
            for (Sheet sheet : sheets) {
                int lastRowNum = sheet.getLastRowNum();
                for (int j = 0; j <= lastRowNum; j++) {
                    newSheet.createRow(rowIndex++);
                    newSheet.getRow(rowIndex - 1).setRowStyle(sheet.getRow(j).getRowStyle());
                    int lastCellNum = sheet.getRow(j).getLastCellNum();
                    for (int k = 0; k < lastCellNum; k++) {
                        if (sheet.getRow(j).getCell(k).getCellType() == CellType.NUMERIC) {
                            newSheet.getRow(rowIndex - 1).createCell(k).setCellValue(sheet.getRow(j).getCell(k).getNumericCellValue());
                        } else {
                            newSheet.getRow(rowIndex - 1).createCell(k).setCellValue(sheet.getRow(j).getCell(k).getStringCellValue());
                        }
                    }
                }
            }
            // 删除原来旧的sheet
            for (int i = sheetCount - 1; i >= 0; i--) {
                workbook.removeSheetAt(i);
            }
            // 写入新的xlsx文件
            FileOutputStream outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            workbook.close();
            inputStream.close();
            outputStream.close();
           
        }
    }
}