Of course! Here's a comprehensive guide to using pyexcelerator in Python.

What is pyexcelerator?

pyexcelerator is a Python library for reading from and writing to Microsoft Excel (.xls) files. It's a pure Python library, meaning it doesn't require any external dependencies like Microsoft Excel or COM interfaces.

Key Characteristics:

• Format: Primarily works with the legacy .xls format (Excel 97-2003). It does not support the modern .xlsx format.
• Pure Python: Easy to install and run on any platform without complex dependencies.
• Simple API: Offers a straightforward, non-OO (Object-Oriented) interface, which can be very quick for simple tasks.

Installation

First, you need to install the library using pip. Open your terminal or command prompt and run:

pip install pyexcelerator

Core Concepts

pyexcelerator works with a few main components:

• Workbook: Represents an entire Excel file. You create a workbook and then add sheets to it.
• Worksheet: Represents a single sheet within a workbook. You get a worksheet by name from the workbook.
• write(): The main function for writing data. You use it to write a value into a specific cell.
  • Syntax: worksheet.write(row, col, value)
  • Important: Rows and columns are 0-indexed. So, row=0 is the first row, col=0 is the first column (A).
• save(): A method on the Workbook object to save the file to disk.

Writing to an Excel File (.xls)

This is the most common use case for pyexcelerator. Let's create a simple spreadsheet.

Example: Creating a Basic Spreadsheet

import pyexcelerator as xls
# 1. Create a new workbook
workbook = xls.Workbook()
# 2. Add a new worksheet to the workbook
# The first sheet is named by default, but you can name it explicitly.
worksheet = workbook.add_sheet('Sales_Data')
# 3. Write data to cells
# Headers
worksheet.write(0, 0, 'Product')
worksheet.write(0, 1, 'Q1 Sales')
worksheet.write(0, 2, 'Q2 Sales')
worksheet.write(0, 3, 'Total')
# Data rows
worksheet.write(1, 0, 'Laptop')
worksheet.write(1, 1, 150)
worksheet.write(1, 2, 200)
worksheet.write(2, 0, 'Mouse')
worksheet.write(2, 1, 300)
worksheet.write(2, 2, 350)
# 4. Calculate and write a total
# We can write formulas as strings
worksheet.write(1, 3, '=B2+C2') # Formula for Laptop Total
worksheet.write(2, 3, '=B3+C3') # Formula for Mouse Total
# 5. Save the workbook to a file
# The .xls extension is important
workbook.save('sales_report.xls')
print("Excel file 'sales_report.xls' created successfully!")

When you open sales_report.xls, you will see a formatted table with the data and formulas.

Reading from an Excel File (.xls)

Reading is also quite straightforward. You use the xls.parse_xls() function.

Example: Reading Data from a Spreadsheet

Let's read the sales_report.xls file we just created.

import pyexcelerator as xls
# The file to read
filename = 'sales_report.xls'
# 1. Parse the Excel file
# This returns a dictionary where keys are sheet names and values are lists of rows
try:
    workbook_data = xls.parse_xls(filename)
    # 2. Access the data by sheet name
    # In our example, the sheet is named 'Sales_Data'
    if 'Sales_Data' in workbook_data:
        sheet_data = workbook_data['Sales_Data']
        print(f"--- Reading data from sheet: 'Sales_Data' ---")
        # 3. Iterate through the rows and print the data
        # Each row is a list/tuple of cell values
        for i, row in enumerate(sheet_data):
            # row[0] is the first cell (column A), row[1] is the second (column B), etc.
            print(f"Row {i}: {row}")
    else:
        print(f"Sheet 'Sales_Data' not found in {filename}")
except FileNotFoundError:
    print(f"Error: The file '{filename}' was not found.")

Output of the reading script:

--- Reading data from sheet: 'Sales_Data' ---
Row 0: ('Product', 'Q1 Sales', 'Q2 Sales', 'Total')
Row 1: ('Laptop', 150, 200, '=B2+C2')
Row 2: ('Mouse', 300, 350, '=B3+C3')

Note on Reading Formulas: pyexcelerator reads the formula string itself (e.g., '=B2+C2'), not the calculated result. It does not evaluate the formulas.

Formatting Cells (A Basic Example)

While pyexcelerator's formatting options are more limited than modern libraries like openpyxl, you can still apply basic styles like bold, fonts, and number formats.

This requires importing the Style module.

import pyexcelerator as xls
from pyexcelerator import Style
# Create a workbook and worksheet
workbook = xls.Workbook()
worksheet = workbook.add_sheet('Formatted_Sheet')
# 1. Create a style object
bold_font = Style.Font()
bold_font.bold = True # Set font to bold
# 2. Apply the style when writing
worksheet.write(0, 0, 'This is Bold', bold_font)
worksheet.write(1, 0, 'This is Normal')
# You can also create a number format style
# '0.00' formats the number to two decimal places
number_style = Style.NumberFormat(format_str='0.00')
worksheet.write(2, 0, 'Price', bold_font)
worksheet.write(2, 1, '1234', number_style) # Will be displayed as 1234.00
worksheet.write(3, 0, 'Total', bold_font)
worksheet.write(3, 1, 5678.9, number_style) # Will be displayed as 5678.90
workbook.save('formatted_sheet.xls')
print("Formatted Excel file 'formatted_sheet.xls' created!")

Important Considerations and Limitations

Before choosing pyexcelerator for a new project, be aware of its significant drawbacks:

1. .xls Only: This is the biggest limitation. The .xls format is obsolete. Most modern applications use the .xlsx format (Office Open XML). If you need to create .xlsx files, you must use a different library like openpyxl or xlsxwriter.

2. No Formula Evaluation: As mentioned, it writes formulas as strings but does not calculate their values. The end-user will see the formula (e.g., =B2+C2) in the cell, not the result (e.g., 350).

3. Limited Formatting: The styling capabilities are very basic compared to newer libraries. You can't do much with colors, borders, cell merging, etc.

4. Development Status: pyexcelerator is an old library and is no longer actively maintained. You might encounter bugs or issues that will not be fixed.

Modern Alternatives for .xlsx Files

For any new project, I strongly recommend using a modern library that supports .xlsx.

openpyxl (Recommended for reading and writing)

pip install openpyxl

Example (Writing with openpyxl):

from openpyxl import Workbook
# Create a workbook
wb = Workbook()
ws = wb.active= "Sales_Data"
# Write data
ws['A1'] = 'Product'
ws['B1'] = 'Q1 Sales'
ws['C1'] = 'Q2 Sales'
ws['A2'] = 'Laptop'
ws['B2'] = 150
ws['C2'] = 200
# Write a formula (openpyxl evaluates it on save)
ws['D2'] = '=B2+C2'
wb.save('sales_report.xlsx')

xlsxwriter (Recommended for writing, especially with formatting)

xlsxwriter has excellent formatting capabilities but can only write files, not read them.

pip install xlsxwriter

Summary: When to Use pyexcelerator?