excel copy sheet操作

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

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.Map;

public class ExcelSheetExtractor {

    public static InputStream copySheetToNewFile(MultipartFile file, String sheetName, String targetFilePath) throws Exception {
        // 创建目标文件目录
        Files.createDirectories(Paths.get(targetFilePath).getParent());

        // 打开目标文件，如果不存在则创建新的 Workbook
        XSSFWorkbook targetWorkbook;
        File targetFile = new File(targetFilePath);
        if (targetFile.exists()) {
            try (FileInputStream existingFile = new FileInputStream(targetFilePath)) {
                targetWorkbook = new XSSFWorkbook(existingFile);
            }
        } else {
            targetWorkbook = new XSSFWorkbook();
        }

        // 打开上传的 MultipartFile 的 Workbook
        try (XSSFWorkbook sourceWorkbook = new XSSFWorkbook(file.getInputStream())) {
            // 获取上传文件的指定 Sheet，如果不存在则取第一个 Sheet
            XSSFSheet sourceSheet = sourceWorkbook.getSheet(sheetName);
            if (sourceSheet == null) {
                sourceSheet = sourceWorkbook.getSheetAt(0);
            }

            // 检查目标文件中是否存在同名 Sheet，如果存在则删除
            int sheetIndex = targetWorkbook.getSheetIndex(sheetName);
            if (sheetIndex != -1) {
                targetWorkbook.removeSheetAt(sheetIndex);
            }

            // 创建新 Sheet 并复制源 Sheet 的内容
            XSSFSheet targetSheet = targetWorkbook.createSheet(sourceSheet.getSheetName());
            Map<CellStyle, CellStyle> styleMap = new HashMap<>();

            // 复制列宽和列隐藏状态
            for (int i = 0; i < sourceSheet.getRow(0).getLastCellNum(); i++) {
                targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
                targetSheet.setColumnHidden(i, sourceSheet.isColumnHidden(i));
            }

            // 复制行和单元格
            for (int i = 0; i <= sourceSheet.getLastRowNum(); i++) {
                XSSFRow sourceRow = sourceSheet.getRow(i);
                if (sourceRow != null) {
                    XSSFRow targetRow = targetSheet.createRow(i);
                    targetRow.setHeight(sourceRow.getHeight());

                    for (int j = 0; j < sourceRow.getLastCellNum(); j++) {
                        XSSFCell sourceCell = sourceRow.getCell(j);
                        if (sourceCell != null) {
                            XSSFCell targetCell = targetRow.createCell(j);
                            copyCell(sourceCell, targetCell, styleMap, targetWorkbook);
                        }
                    }
                }
            }

            // 复制合并单元格
            for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
                targetSheet.addMergedRegion(mergedRegion);
            }

            // 写入目标 Workbook 到文件
            try (FileOutputStream fileOut = new FileOutputStream(targetFile)) {
                targetWorkbook.write(fileOut);
            }

            // 将目标 Workbook 写入 ByteArrayOutputStream 并返回 InputStream
            try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
                targetWorkbook.write(outputStream);
                return new ByteArrayInputStream(outputStream.toByteArray());
            }
        }
    }


    public static InputStream getSheetInputStream(MultipartFile file, String sheetName) throws Exception {
        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
             XSSFWorkbook newWorkbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                sheet = workbook.getSheetAt(0);
            }

            Sheet newSheet = newWorkbook.createSheet(sheet.getSheetName());
            Map<CellStyle, CellStyle> styleMap = new HashMap<>();

            // 复制列宽
//            for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
//                newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
//                newSheet.setColumnHidden(i, sheet.isColumnHidden(i));
//            }
            for (int i = 0; i < 100; i++) {
                newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
                newSheet.setColumnHidden(i, sheet.isColumnHidden(i));
            }
            // 复制行和单元格
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row srcRow = sheet.getRow(i);
                if (srcRow != null) {
                    Row destRow = newSheet.createRow(i);
                    destRow.setHeight(srcRow.getHeight());

                    for (int j = 0; j < srcRow.getLastCellNum(); j++) {
                        Cell oldCell = srcRow.getCell(j);
                        if (oldCell != null) {
                            Cell newCell = destRow.createCell(j);
                            copyCell(oldCell, newCell, styleMap, newWorkbook);
                        }
                    }
                }
            }

            // 复制合并单元格
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                newSheet.addMergedRegion(mergedRegion);
            }

            // 复制表格视图设置
            copySheetSettings(sheet, newSheet);

            ByteArrayOutputStream out = new ByteArrayOutputStream();
            newWorkbook.write(out);
            return new ByteArrayInputStream(out.toByteArray());
        }
    }
    private static void copyCell(Cell sourceCell, Cell targetCell, Map<CellStyle, CellStyle> styleMap, Workbook tempWorkbook) {
        CellStyle sourceStyle = sourceCell.getCellStyle();
        CellStyle targetStyle = styleMap.get(sourceStyle);

        if (targetStyle == null) {
            targetStyle = tempWorkbook.createCellStyle();
            targetStyle.cloneStyleFrom(sourceStyle);
            styleMap.put(sourceStyle, targetStyle);
        }

        targetCell.setCellStyle(targetStyle);

        switch (sourceCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(sourceCell)) {
                    targetCell.setCellValue(sourceCell.getDateCellValue());
                } else {
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            case BLANK:
                targetCell.setBlank();
                break;
            default:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
        }
    }

    private static void copySheetSettings(Sheet sourceSheet, Sheet targetSheet) {
        targetSheet.setDisplayGridlines(sourceSheet.isDisplayGridlines());
        targetSheet.setDisplayFormulas(sourceSheet.isDisplayFormulas());
        targetSheet.setDisplayRowColHeadings(sourceSheet.isDisplayRowColHeadings());

        // 复制其他设置
        targetSheet.setDefaultColumnWidth(sourceSheet.getDefaultColumnWidth());
        targetSheet.setDefaultRowHeight(sourceSheet.getDefaultRowHeight());
        targetSheet.setAutobreaks(sourceSheet.getAutobreaks());
        targetSheet.setVerticallyCenter(sourceSheet.getVerticallyCenter());
        targetSheet.setHorizontallyCenter(sourceSheet.getHorizontallyCenter());
        targetSheet.setPrintGridlines(sourceSheet.isPrintGridlines());
        targetSheet.setRightToLeft(sourceSheet.isRightToLeft());
        targetSheet.setPrintRowAndColumnHeadings(sourceSheet.isPrintRowAndColumnHeadings());

        // 复制缩放比例
        if (sourceSheet instanceof XSSFSheet && targetSheet instanceof XSSFSheet) {
            ((XSSFSheet) targetSheet).setZoom(100);
        }
    }

}