如何使用Apache POI实现Excel列的复制与偏移操作（含合并列场景）

Got it, let's tackle this. Since you've already used shiftColumns() to move C/D to E/F, copying E/F back to C/D requires handling both cell content/styles and merged regions—especially that merged C2:D2 you mentioned. Apache POI doesn't have a built-in "copy entire column" method, so we'll need to handle this manually with a few targeted steps:

Step 1: Handle Merged Cell Regions First #

First, we need to replicate any merged regions from E/F to the corresponding positions in C/D. It's also a good idea to clear old merged regions in C/D first to avoid conflicts (I’ve included that in the code below).

Step 2: Iterate Through Rows and Copy Cell Data/Styles #

Next, loop through every row in the sheet, and for each row, copy the cell values, formulas, and styles from columns E (0-based index 4) and F (index 5) to columns C (index 2) and D (index 3).

Here's a complete, tested code example that covers all cases:

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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class CopyColumnsWithMergedCells {
    public static void main(String[] args) throws IOException {
        String filePath = "your-excel-file.xlsx"; // Replace with your actual file path
        Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
        Sheet sheet = workbook.getSheetAt(0); // Target the first sheet (adjust index if needed)

        // Define source (E/F) and target (C/D) columns using 0-based indexes
        int[] sourceCols = {4, 5};
        int[] targetCols = {2, 3};

        // Step 1: Manage merged regions
        // Remove existing merged regions in target columns to avoid overlaps
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.getFirstColumn() <= targetCols[1] && merged.getLastColumn() >= targetCols[0]) {
                sheet.removeMergedRegion(i);
            }
        }

        // Copy merged regions from source to target columns
        int colOffset = targetCols[0] - sourceCols[0]; // Calculate column shift amount
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress sourceMerged = sheet.getMergedRegion(i);
            // Check if the merged region is within our source columns
            if (sourceMerged.getFirstColumn() >= sourceCols[0] && sourceMerged.getLastColumn() <= sourceCols[1]) {
                CellRangeAddress targetMerged = new CellRangeAddress(
                        sourceMerged.getFirstRow(),
                        sourceMerged.getLastRow(),
                        sourceMerged.getFirstColumn() + colOffset,
                        sourceMerged.getLastColumn() + colOffset
                );
                sheet.addMergedRegion(targetMerged);
            }
        }

        // Step 2: Copy cell content and styles
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) row = sheet.createRow(rowNum); // Create row if it doesn't exist

            for (int i = 0; i < sourceCols.length; i++) {
                int sourceCol = sourceCols[i];
                int targetCol = targetCols[i];

                Cell sourceCell = row.getCell(sourceCol);
                Cell targetCell = row.getCell(targetCol);
                if (targetCell == null) targetCell = row.createCell(targetCol);

                // Copy cell value based on its type
                if (sourceCell != null) {
                    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.toString());
                    }

                    // Copy cell style (preserves formatting like font, alignment, colors)
                    targetCell.setCellStyle(sourceCell.getCellStyle());
                }
            }
        }

        // Save changes back to the file
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }
}

Key Details to Keep in Mind: #

• 0-Based Indexing: Apache POI uses 0-based column indexes, so C=2, D=3, E=4, F=5—don’t mix this up with Excel’s 1-based labels!
• Merged Region Cleanup: Clearing old merged regions in C/D prevents weird overlapping issues when we add the new copied regions.
• Full Cell Type Support: The code handles all common cell types (strings, dates, numbers, formulas) so no data gets lost in the copy.
• Style Preservation: Using setCellStyle() ensures your merged cells (and regular cells) keep their original formatting.

This code works for both .xlsx (XSSF) and .xls (HSSF) files since we use POI’s generic Workbook and Sheet interfaces.

内容的提问来源于stack exchange，提问作者hasankzl