用Pandas生成的XLSX文件无法用Gnumeric打开及多表Gnumeric文件生成咨询

Why Gnumeric can't open your Pandas-generated XLSX? #

The issue boils down to format compatibility between the XlsxWriter engine and Gnumeric. XlsxWriter implements some modern XLSX features that Gnumeric (especially older versions) doesn’t fully support—even though Excel handles them without a problem.

Quick fixes to try: #

• Switch to openpyxl as the Excel engine: Openpyxl generates more universally compatible XLSX files that Gnumeric tends to handle seamlessly. Adjust your code like this:

  import pandas as pd
  df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
  # Replace xlsxwriter with openpyxl
  writer = pd.ExcelWriter('pandas_simple.xlsx', engine='openpyxl')
  df.to_excel(writer, sheet_name='Sheet1')
  writer.close()  # Use close() instead of save() for newer Pandas versions
  

• Update Gnumeric: If you’re running an older release, updating to the latest version might add better support for modern XLSX standards.

Alternative ways to generate multi-sheet files compatible with Gnumeric (without Pandas ExcelWriter) #

If you want to avoid Pandas’ ExcelWriter entirely, here are reliable, straightforward options:

1. Use openpyxl directly #

Openpyxl is a pure-Python library for working with XLSX files, and it creates files that play nicely with Gnumeric. Here’s how to build a multi-sheet file:

from openpyxl import Workbook

# Initialize workbook
wb = Workbook()

# Set up first sheet and add data
ws1 = wb.active
ws1.title = "Sheet1"
ws1.append(["Data"])
for num in [10, 20, 30, 20, 15, 30, 45]:
    ws1.append([num])

# Add a second sheet with different data
ws2 = wb.create_sheet(title="Sheet2")
ws2.append(["Another Dataset"])
ws2.append([5, 10, 15])

# Save the final file
wb.save("multi_sheet_openpyxl.xlsx")

2. Use xlwt for legacy XLS format #

If you don’t strictly need XLSX, xlwt generates older XLS files that are widely compatible with Gnumeric. Note that XLS has a 65,536-row limit, but it’s a solid choice for smaller datasets:

import xlwt

# Create workbook and sheets
wb = xlwt.Workbook()
ws1 = wb.add_sheet("Sheet1")
ws2 = wb.add_sheet("Sheet2")

# Populate Sheet1
ws1.write(0, 0, "Data")
for idx, num in enumerate([10, 20, 30, 20, 15, 30, 45], start=1):
    ws1.write(idx, 0, num)

# Populate Sheet2
ws2.write(0, 0, "Another Dataset")
ws2.write(1, 0, 5)
ws2.write(2, 0, 10)

# Save the file
wb.save("multi_sheet_xls.xls")

3. Generate CSV files and import into Gnumeric #

If you don’t mind a two-step process, create separate CSV files for each dataset, then import them into Gnumeric as individual sheets. This avoids all format compatibility headaches:

# Save Sheet1 data
with open("sheet1_data.csv", "w") as f:
    f.write("Data\n")
    for num in [10, 20, 30, 20, 15, 30, 45]:
        f.write(f"{num}\n")

# Save Sheet2 data
with open("sheet2_data.csv", "w") as f:
    f.write("Another Dataset\n")
    for num in [5,10,15]:
        f.write(f"{num}\n")

Then open Gnumeric, go to File > Import for each CSV, and assign them to new sheets.

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