python-calamine

Warnings

• gotcha Calamine does not perform implicit data type conversions for numbers and may change strings into numbers, which can lead to unexpected data types if not handled explicitly. Excel numbers are read as floats.
  Fix: Be mindful of data types when reading. If using with Pandas, explicitly define dtypes if possible, or perform post-processing to cast to desired types. Calamine's philosophy prioritizes speed by avoiding these conversions.
• gotcha By default, `python-calamine` skips empty rows/columns before the actual data area. This might lead to unexpected results if leading empty cells are significant.
  Fix: To include empty areas, use `workbook.get_sheet_by_name('Sheet1').to_python(skip_empty_area=False)`.
• gotcha Some users reported performance degradation when reading data over VPN network connections starting from version 0.2.3.
  Fix: If experiencing slow reads over VPN, consider testing with local files or older versions (if compatible with your needs) to isolate the issue. Report specific performance issues with clear reproduction steps on the GitHub repository.
• gotcha Issues have been reported where the first two columns in old `.xls` files are read incorrectly.
  Fix: If working with old `.xls` files and encountering malformed data, verify the first two columns. Consider converting problematic `.xls` files to `.xlsx` format if possible, or using a different library for `.xls` files if `python-calamine` exhibits consistent errors.
• deprecated For `pandas` versions 2.0 and 2.1, using `python-calamine` as an engine for `read_excel` required a monkeypatch.
  Fix: Upgrade to `pandas` 2.2 or higher for built-in support, which automatically registers `python-calamine` as a `read_excel` engine.

Install

• pip install python-calamine PyPI
• conda install -c conda-forge python-calamine Conda-forge

Imports

• CalamineWorkbook

  from python_calamine import CalamineWorkbook

  Primary class for interacting with workbooks.

Quickstart

This quickstart demonstrates how to open an Excel file using `CalamineWorkbook.from_path`, retrieve sheet names, and extract data into a Python list of lists. By default, `python-calamine` skips empty rows and columns, but this behavior can be altered using `skip_empty_area=False` when calling `to_python()` on a sheet. For `pandas` users, versions 2.2 and above have built-in support for the 'calamine' engine in `read_excel`.

import os
from python_calamine import CalamineWorkbook

# Create a dummy Excel file for demonstration
# In a real scenario, 'file.xlsx' would already exist.
# For this quickstart, we'll simulate reading a file.
# You would replace 'file.xlsx' with your actual file path.

# Placeholder for creating a file, as we cannot write to disk directly in this environment.
# Assume 'dummy_file.xlsx' exists with at least one sheet named 'Sheet1'
# and some data like [['1', '2', '3'], ['4', '5', '6']].

try:
    # Attempt to open a non-existent file to demonstrate error handling
    # In a real application, ensure the file exists.
    workbook = CalamineWorkbook.from_path('dummy_file.xlsx')
    print(f"Sheet names: {workbook.sheet_names}")
    
    # Get data from the first sheet
    if workbook.sheet_names:
        sheet_data = workbook.get_sheet_by_name(workbook.sheet_names[0]).to_python()
        print(f"Data from '{workbook.sheet_names[0]}': {sheet_data}")
    
    # Example with skipping empty areas (default is True)
    # To suppress this, set skip_empty_area=False
    # sheet_data_with_empty = workbook.get_sheet_by_name(workbook.sheet_names[0]).to_python(skip_empty_area=False)
    # print(f"Data (including empty): {sheet_data_with_empty}")

except FileNotFoundError:
    print("Please ensure 'dummy_file.xlsx' exists in the current directory or provide a valid path.")
    print("For example, create a simple Excel file with content and try again.")
except Exception as e:
    print(f"An error occurred: {e}")