openpyxl跨平台问题：Linux下Excel损坏及列表索引越界报错

Hey there! Let's tackle these two openpyxl issues one by one—they're both pretty common, so I've got some solid fixes for you:

This error usually pops up when openpyxl hits unexpected or malformed data in your Excel file. Here's how to resolve it:

• Check for corrupted worksheets: Sometimes Excel files have hidden sheets with invalid dimension data, or sheets that got damaged during previous edits. First, open the original file in Excel manually—if Excel prompts to repair the file or throws an error, that's your culprit. Repair it in Excel first, then re-run your script.
• Update openpyxl to the latest version: Older versions have bugs handling complex Excel features like merged cells, pivot tables, or conditional formatting. Run this command to upgrade:

  pip install --upgrade openpyxl
  

• Avoid accessing non-existent cells: If your script iterates beyond the actual used range of a sheet, you'll hit this index error. Use worksheet.max_row and worksheet.max_column to get the real bounds instead of hardcoding numbers:

  from openpyxl import load_workbook
  
  wb = load_workbook("your_file.xlsx")
  ws = wb.active
  # Iterate only over used cells
  for row in range(1, ws.max_row + 1):
      for col in range(1, ws.max_column + 1):
          cell = ws.cell(row=row, column=col)
          # Your cell operations here
  

• Use load flags to bypass formatting bugs: If you're only working with cell values (not formulas or formatting), try loading the workbook with these flags to skip problematic elements:

  wb = load_workbook("your_file.xlsx", read_only=True, data_only=True)
  

This is almost always related to cross-system file handling quirks. Try these fixes:

• Use cross-platform path handling: Windows uses backslashes (\) while Linux uses forward slashes (/). Hardcoding paths breaks things. Use Python's pathlib to build paths that work everywhere:

  from pathlib import Path
  from openpyxl import Workbook
  
  save_dir = Path("/home/your_user/documents")
  save_path = save_dir / "output.xlsx"
  
  wb = Workbook()
  ws = wb.active
  ws["A1"] = "Test content"
  wb.save(save_path)
  

• Use context managers to ensure proper file closure: If you don't close the workbook correctly, Linux might not flush all data to the file, causing corruption. Always use with statements:

  with load_workbook("input.xlsx") as wb:
      ws = wb.active
      ws["B2"] = "Updated value"
      wb.save("output.xlsx")
  

• Verify file permissions: Make sure the directory where you're saving the file has write permissions. Check with this terminal command:

  ls -ld /path/to/your/save/directory
  

  If needed, adjust permissions with:

  chmod +w /path/to/your/save/directory
  

• Save via binary file stream (rare fix): In some edge cases, saving the workbook to a binary file object fixes corruption:

  with open("output.xlsx", "wb") as f:
      wb.save(f)
  

内容的提问来源于stack exchange，提问作者Алексей Нестерчук