pylightxl

Common errors

• KeyError: 'Sheet1' (or any other sheet name)

  cause The specified worksheet name does not exist in the Excel file, or there is a typo in the sheet name. This can also occur if attempting to read an unsupported file format like `.xls` which pylightxl cannot parse.
  fix Double-check the exact spelling and case of the worksheet name. You can list available sheets using `db.ws_names`. If the file is an `.xls` file, convert it to `.xlsx` or `.xlsm` format first.

• AttributeError: 'str' object has no attribute 'ws'

  cause This error typically occurs when `xl.readxl()` returns a file path string (perhaps due to an error during reading or incorrect usage) instead of a `pylightxl.Database` object, and a worksheet method like `.ws()` is then called on that string.
  fix Ensure that `xl.readxl()` successfully returns a `pylightxl.Database` object by assigning its result to a variable (e.g., `db = xl.readxl(fn='your_file.xlsx')`) and verifying the file exists and is valid. The `fn` argument should be the file path string or a `Pathlib.Path` object.

• ValueError: invalid literal for int() with base 10: 'some_text'

  cause In older versions of pylightxl (prior to v1.57), this could occur when parsing Excel files with non-standard sheet IDs or malformed XML tags, where the library attempts to convert a non-integer string into an integer.
  fix Update pylightxl to version 1.57 or newer. This issue was specifically addressed to improve support for non-standard sheet IDs. If the issue persists with the latest version, the Excel file might be corrupt or severely malformed.

Warnings

• deprecated The `formula` argument in indexing methods (e.g., `db.ws().index(..., formula=True)`) was deprecated in version 1.55. It has been replaced by the `output` argument (e.g., `output='f'`) for greater flexibility.
  Fix: Update your code to use `output='f'` for formulas, `output='v'` for values, or `output='c'` for comments instead of the `formula` boolean argument.
• gotcha When writing to an existing Excel workbook using `xl.writexl()`, pylightxl currently only supports writing cell values/formulas/strings. Any existing macros, buttons, graphs, formatting, or other customizations in the original file will be removed.
  Fix: Be aware of this limitation and either ensure existing files do not contain complex features you wish to preserve, or back up files before writing.
• gotcha pylightxl does not support reading or writing `.xls` files (Microsoft Excel 97-2003 format). It primarily supports `.xlsx` and `.xlsm` for reading, and `.xlsx` for writing.
  Fix: Convert `.xls` files to a supported format like `.xlsx` or `.xlsm` before attempting to read them with pylightxl.
• gotcha Formulas written into an Excel file by pylightxl will not be calculated until the user opens the file in Excel. pylightxl itself does not perform formula calculations.
  Fix: Inform users that they need to open and save the Excel file to see calculated results for formulas. If immediate calculation is needed, consider pre-calculating values in Python or using a library that supports formula evaluation.

Install

• pip install pylightxl Install latest version

Imports

• pylightxl

  import pylightxl as xl

Quickstart

This quickstart demonstrates how to create a simple Excel file, write data including a formula, and then read data back from it using cell addresses, indices, and row iteration. It also shows how to access cell formulas.

import pylightxl as xl
import os

# Create a dummy Excel file for reading example
db_write = xl.Database()
db_write.add_ws(ws='Sheet1')
db_write.ws(ws='Sheet1').update_index(row=1, col=1, val='Header 1')
db_write.ws(ws='Sheet1').update_index(row=1, col=2, val='Header 2')
db_write.ws(ws='Sheet1').update_index(row=2, col=1, val=100)
db_write.ws(ws='Sheet1').update_index(row=2, col=2, val='=A2*2')

output_file = 'example_output.xlsx'
xl.writexl(db_write, output_file)
print(f"Created '{output_file}' for reading example.")

# Read from the Excel file
if os.path.exists(output_file):
    db_read = xl.readxl(fn=output_file)
    print(f"\nReading data from '{output_file}':")
    
    # Access data by address
    cell_a1_val = db_read.ws(ws='Sheet1').address(address='A1')
    print(f"Cell A1 (value): {cell_a1_val}")
    
    # Access formula by index
    cell_b2_formula = db_read.ws(ws='Sheet1').index(row=2, col=2, output='f')
    print(f"Cell B2 (formula): {cell_b2_formula}")
    
    # Iterate through rows
    print("\nAll data in Sheet1:")
    for row in db_read.ws(ws='Sheet1').rows:
        print(row)
else:
    print(f"Error: '{output_file}' not found.")

# Clean up the created file
if os.path.exists(output_file):
    os.remove(output_file)
    print(f"Cleaned up '{output_file}'.")