如何更新现有Excel .ods文件？实现将.xls文件数据导入现有.ods文件且不修改其他内容

I totally get your frustration—working with ODS files in Python can feel limited compared to XLSX, but there are solid ways to update existing sheets without messing up the rest of your file. Let's break down two reliable approaches: one using pyexcel-ods3 (since you're already familiar with it) and another with odfpy (a more low-level, robust library for ODF formats).

Using pyexcel-ods3 to Update Existing Sheets #

The key here is to first read all existing data from the ODS file, modify just the sheet you need, then write everything back. The save_data function overwrites the whole file by default, but if you include the original sheets' data along with your updates, you'll preserve the rest of the content.

Here's a complete example:

from pyexcel_ods3 import load_data, save_data
from collections import OrderedDict

# 1. Load the existing ODS file's data
existing_data = load_data("your_existing_file.ods")

# 2. Define your new data for the target sheet
# Replace "Target Sheet" with your actual sheet name
new_sheet_data = [[1, 2, 3], [4, 5, 6], ["new row", "with", "values"]]

# 3. Update the target sheet (overwrites existing content in that sheet)
# If the sheet doesn't exist, this will create it
existing_data["Target Sheet"] = new_sheet_data

# 4. Save the modified data back to the ODS file
save_data("your_existing_file.ods", existing_data)

A few quick notes:

• load_data returns an OrderedDict where keys are sheet names and values are lists of rows (each row is a list of cell values).
• If you want to append rows instead of overwriting the whole sheet, replace the assignment with an extend:

  # Append new rows to the existing sheet content
  existing_data["Target Sheet"].extend(new_sheet_data)
  

Using odfpy (Finer Control Over ODS Files) #

If you need more precision—like modifying specific cells without wiping the entire sheet or preserving existing formatting—odfpy is a great alternative. It directly manipulates the ODF file structure, so it’s less likely to break other sheets or styles.

First, install the library:

pip install odfpy

Then use this code to update your target sheet:

from odf.opendocument import load
from odf.table import Table, TableRow, TableCell
from odf.text import P

def update_ods_sheet(file_path, sheet_name, new_data, clear_existing=True):
    # Load the existing ODS document
    doc = load(file_path)
    
    # Locate the target sheet
    target_sheet = None
    for sheet in doc.getElementsByType(Table):
        if sheet.getAttribute("name") == sheet_name:
            target_sheet = sheet
            break
    
    # Create the sheet if it doesn't exist
    if not target_sheet:
        target_sheet = Table(name=sheet_name)
        doc.spreadsheet.addElement(target_sheet)
    
    # Clear existing rows if specified
    if clear_existing:
        rows = target_sheet.getElementsByType(TableRow)
        for row in rows:
            target_sheet.removeChild(row)
    
    # Add new rows and cells
    for row_data in new_data:
        table_row = TableRow()
        target_sheet.addElement(table_row)
        for cell_value in row_data:
            table_cell = TableCell()
            table_row.addElement(table_cell)
            # ODF requires cell content to be wrapped in a paragraph element
            table_cell.addElement(P(text=str(cell_value)))
    
    # Save the modified document
    doc.save(file_path)

# Example usage
new_data = [[10, 20, 30], [40, 50, 60]]
update_ods_sheet("your_existing_file.ods", "Target Sheet", new_data)

This approach gives you flexibility:

• Choose to clear existing content or append rows.
• Modify individual cells instead of entire rows.
• Preserve other sheets, formatting, and styles in the ODS file.

Both methods work seamlessly with Python 3.9.1—pick the one that fits your use case best!

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