更新

在使用pandas时，可以使用以下代码：

with pd.ExcelWriter('data.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
    wb = writer.book
    ws = wb.active
    df.to_excel(writer, startrow=ws.min_row-1, startcol=ws.max_column, index=False)

字符串

旧答案

可以使用ws.max_column和ws.max_row：

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook('test.xlsx')
ws = wb.active

型
输出量：

>>> ws.max_row
5

>>> ws.max_column
9

>>> get_column_letter(ws.max_column)
'I'

型
我的excel文件：

的数据

public class ExcelUpdater {

    public static void updateNextEmptyColumn(String filePath, String sheetName, String rowValue, String valueToUpdate) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheet(sheetName);

        int rowIndex = findRowIndexByValue(sheet, rowValue);
        if (rowIndex == -1) {
            // Row value not found, handle the error accordingly
            return;
        }

        Row row = sheet.getRow(rowIndex);
        int nextEmptyColumnIndex = row.getLastCellNum();

        Cell cell = row.createCell(nextEmptyColumnIndex);
        cell.setCellValue(valueToUpdate);

        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
        workbook.write(fileOutputStream);

        fileOutputStream.close();
        workbook.close();
        fileInputStream.close();
    }

    private static int findRowIndexByValue(Sheet sheet, String rowValue) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().equals(rowValue)) {
                    return row.getRowNum();
                }
            }
        }
        return -1; // Row value not found
    }

    public static void main(String[] args) {
        String filePath = "path/to/your/excel/file.xlsx";
        String sheetName = "Sheet1";
        String rowValue = "RowValueToSearch";
        String valueToUpdate = "ValueToUpdate";

        try {
            updateNextEmptyColumn(filePath, sheetName, rowValue, valueToUpdate);
            System.out.println("Value updated successfully!");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Error occurred: " + e.getMessage());
        }
    }
}

字符串

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class ExcelUpdater {

    public static void insertAndKeepVisible(String filePath, String sheetName, String rowValue, String valueToUpdate) throws IOException {
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheet(sheetName);

        int rowIndex = findRowIndexByValue(sheet, rowValue);
        if (rowIndex == -1) {
            // Row value not found, handle the error accordingly
            return;
        }

        Row row = sheet.getRow(rowIndex);

        // Find the last used column index (0-based index)
        int lastUsedColumnIndex = row.getLastCellNum() - 1;

        // Create a new cell in the new column and set the value
        Cell newCell = row.createCell(lastUsedColumnIndex + 1, CellType.STRING);
        newCell.setCellValue(valueToUpdate);

        // Iterate through each row and move existing cells to the right
        for (Row currentRow : sheet) {
            if (currentRow.getRowNum() != rowIndex) {
                // Shift existing cells to the right to accommodate the new column
                Cell previousCell = currentRow.getCell(lastUsedColumnIndex);
                if (previousCell != null) {
                    Cell newShiftedCell = currentRow.createCell(lastUsedColumnIndex + 1, previousCell.getCellType());
                    cloneCellValue(previousCell, newShiftedCell);
                    currentRow.removeCell(previousCell);
                }
            }
        }

        // Autosize the column to fit the content
        sheet.autoSizeColumn(lastUsedColumnIndex + 1);

        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
        workbook.write(fileOutputStream);

        fileOutputStream.close();
        workbook.close();
        fileInputStream.close();
    }

    private static int findRowIndexByValue(Sheet sheet, String rowValue) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().equals(rowValue)) {
                    return row.getRowNum();
                }
            }
        }
        return -1; // Row value not found
    }

    private static void cloneCellValue(Cell srcCell, Cell destCell) {
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                destCell.setCellValue(srcCell.getNumericCellValue());
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                destCell.setCellFormula(srcCell.getCellFormula());
                break;
            case ERROR:
                destCell.setCellValue(srcCell.getErrorCellValue());
                break;
            default:
                break;
        }
    }

    public static void main(String[] args) {
        String filePath = "path/to/your/excel/file.xlsx";
        String sheetName = "Sheet1";
        String rowValue = "RowValueToSearch";
        String valueToUpdate = "ValueToUpdate";

        try {
            insertAndKeepVisible(filePath, sheetName, rowValue, valueToUpdate);
            System.out.println("Value updated and new column inserted successfully!");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Error occurred: " + e.getMessage());
        }
    }
}

字符串

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

public class ExcelUpdateExample {

    public static void main(String[] args) {
        String filePath = "path/to/your/excel_file.xlsx";
        int columnIndexToUpdate = 2; // 0-based index of the column to update (e.g., 2 for column C)

        try {
            FileInputStream fileInputStream = new FileInputStream(new File(filePath));
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // Insert a new column at the specified index
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 0; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    row.shiftCellsRight(columnIndexToUpdate, row.getLastCellNum() - 1, 1);
                    Cell newCell = row.createCell(columnIndexToUpdate);
                    Cell previousCell = row.getCell(columnIndexToUpdate + 1);
                    if (previousCell != null) {
                        newCell.setCellStyle(previousCell.getCellStyle());
                    }
                }
            }

            // Update values in the new column
            int rowIndex = 0; // 0-based index of the row where you want to start updating values

            // Example values to update (replace this with your own data)
            String[] newColumnValues = {"Value1", "Value2", "Value3"};

            for (String value : newColumnValues) {
                Row row = sheet.getRow(rowIndex); // Get the row where you want to update the value
                if (row == null) {
                    row = sheet.createRow(rowIndex); // If the row doesn't exist, create a new one
                }
                Cell cell = row.createCell(columnIndexToUpdate); // Create a new cell in the specified column
                cell.setCellValue(value); // Set the cell value to the desired value

                rowIndex++; // Move to the next row
            }

            // Save the updated workbook
            FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath));
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            workbook.close();

            System.out.println("Excel file updated successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

字符串