Selenium+Java实现Excel动态求和写入遇仅最后行生效问题求助

Hey there! Let's work through your problem step by step—sounds like you're hitting two main issues: only the last row sticking when writing to Excel with POI, and needing JavaScript code for Selenium to handle auto-saving Excel to show dynamic values.

First Fix: Make Sure Your POI Code Saves All Rows Correctly #

The most common reason only the last row gets written is either overwriting the file each time you add a row, or not properly flushing/closing the output stream after writing all data. Here's a corrected version of your code snippet that ensures all rows are saved:

package addegexcelwrite1valuesfl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelSumWriter {
    public static void main(String[] args) {
        File excelFile = new File("your-sum-file.xlsx");
        Workbook workbook = null;

        try {
            // Load existing workbook or create a new one
            if (excelFile.exists()) {
                workbook = new XSSFWorkbook(new FileInputStream(excelFile));
            } else {
                workbook = new XSSFWorkbook();
            }

            // Get or create the target sheet
            Sheet sheet = workbook.getSheet("SumResults");
            if (sheet == null) {
                sheet = workbook.createSheet("SumResults");
            }

            // Example: Write multiple sum rows
            for (int rowNum = 0; rowNum < 5; rowNum++) {
                int num1 = rowNum * 2;
                int num2 = rowNum * 3;
                int sum = num1 + num2;

                // Get existing row or create a new one
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    row = sheet.createRow(rowNum);
                }

                // Write values to cells
                row.createCell(0).setCellValue(num1);
                row.createCell(1).setCellValue(num2);
                row.createCell(2).setCellValue(sum);
            }

            // Critical: Write all data to file and close streams properly
            try (FileOutputStream fos = new FileOutputStream(excelFile)) {
                workbook.write(fos);
                fos.flush(); // Force all data to be written to disk
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Clean up workbook resources
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Key Notes:

• We load the existing workbook (instead of creating a new one every time) so previous rows aren't overwritten.
• We use try-with-resources for the FileOutputStream—this automatically closes the stream after writing, ensuring all data is persisted.
• We only write to the file once, after all rows are added, instead of per-row.

Second Part: Selenium JavaScript Code for Excel Auto-Save #

The exact JS code depends on whether you're working with Office Online (web-based Excel) or a local Excel file opened in a browser (like Edge/IE):

1. For Office Online (Web Excel)

Use this JS to trigger a manual save or toggle auto-save:

// Trigger manual save (clicks the save button)
document.querySelector('[aria-label="Save"]').click();

// Toggle AutoSave on/off
document.querySelector('[aria-label="Toggle AutoSave"]').click();

Run it in Selenium with:

JavascriptExecutor js = (JavascriptExecutor) driver;
// Trigger save
js.executeScript("document.querySelector('[aria-label=\"Save\"]').click();");
// Enable auto-save
js.executeScript("document.querySelector('[aria-label=\"Toggle AutoSave\"]').click();");

2. For Local Excel Files Opened in Edge/IE

If you're using Selenium to control a browser that opened a local Excel file, use this JS to trigger Excel's built-in save command:

// Execute Excel's "Save" command
window.external.CommandBars.ExecuteMso("FileSave");

Run it in Selenium with:

JavascriptExecutor js = (JavascriptExecutor) driver;
js.executeScript("window.external.CommandBars.ExecuteMso('FileSave');");

Quick Troubleshooting Tip #

If you're still seeing only the last row, double-check that you're not reinitializing the workbook or output stream inside your loop—this would overwrite the file each iteration.

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