DOCSEARCHbyAlgolia
Submit Docs

PyExcelerate Documentation

repository·dev·Indexed 20 days ago

https://github.com/kz26/pyexcelerate

A high-performance Python library designed for writing Excel XLSX files with an emphasis on speed. It supports Python 2.7 and 3.4 through 3.8, offering optimized methods for writing bulk data via 2D arrays, individual cell value setting, range merging, and cell styling. The library includes support for PyInstaller and provides guidance on efficiently integrating with Pandas DataFrames using list conversion.

Tokens
2.2K
Snippets
13
Records
15
Agent score
19%

What's inside PyExcelerate

  1. Style cells and ranges

    dev

    Styling cells introduces significant overhead and can increase execution time up to 10x.

    Methods of Styling

    • set_cell_style(row, col, Style(...)): The fastest method. Note that subsequent calls to set_cell_style on the same cell override previous styles rather than merging them. To combine styles, pass all properties into a single Style object.
    • ws.get_cell_style(row, col).property = value: A faster way to modify existing styles.
    • ws[row][col].style.property = value: The most intuitive but slowest method.

    Styling Ranges, Rows, and Columns

    • Ranges: ws.range("A1", "C3").style.font.bold = True
    • Rows: ws.set_row_style(row_index, Style(...)) or ws[row_index].style.property = value
    • Columns: ws.set_col_style(col_index, Style(...))
    from pyexcelerate import Workbook, Color, Style, Font, Fill, Format

wb = Workbook()
ws = wb.new_sheet("sheet name")
# Combined style to avoid overrides
ws.set_cell_style(1, 1, Style(font=Font(bold=True), format=Format('mm/dd/yy')))
wb.save("output.xlsx")

  3. Work with Pandas DataFrames

    dev

    PyExcelerate does not support passing a Pandas DataFrame directly to new_sheet. For maximum efficiency, convert the DataFrame to a list of lists using .values.tolist(). This is faster because PyExcelerate is optimized for list-based data.

    # If headers are not needed
ws = wb.new_sheet("sheet name", data=df.values.tolist())

  5. Write bulk data to a new worksheet

    dev

    The fastest way to write data is to provide a 2D array (list of lists) directly when creating a new sheet using wb.new_sheet(name, data=data).

    from pyexcelerate import Workbook

data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
wb = Workbook()
wb.new_sheet("sheet name", data=data)
wb.save("output.xlsx")

  6. Write bulk data to a specific range

    dev

    You can write a 2D array to a specific range of cells. This is faster than writing cell-by-cell. Use ws.range("START_CELL", "END_CELL").value = data.

    from pyexcelerate import Workbook

wb = Workbook()
ws = wb.new_sheet("test")
ws.range("B2", "C3").value = [[1, 2], [3, 4]]
wb.save("output.xlsx")

  7. Write bulk data to a new sheet

    dev

    The fastest way to write data is to pass a 2D array directly to new_sheet using the data parameter.

    from pyexcelerate import Workbook

data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]] # data is a 2D array

wb = Workbook()
bw.new_sheet("sheet name", data=data)
bw.save("output.xlsx")

  8. Style entire rows or columns

    dev

    You can style an entire row or column using set_row_style(row_index, Style) or set_col_style(col_index, Style). This is more efficient than styling individual cells.

    from pyexcelerate import Workbook, Color, Style, Fill

bw = Workbook()
ws = wb.new_sheet("sheet name")

# Style a row
ws.set_row_style(1, Style(fill=Fill(background=Color(255,0,0,0))))

# Style a column
ws.set_col_style(2, Style(fill=Fill(background=Color(255,0,0,0))))

  9. Select cells by name

    dev

    You can access and set values for specific cells using Excel-style coordinate strings via the .cell("name") method.

    from pyexcelerate import Workbook

bw = Workbook()
ws = wb.new_sheet("sheet name")
ws.cell("A1").value = 12
bw.save("output.xlsx")

  10. Merge a range of cells

    dev

    To merge cells, define a range using .range(start_cell, end_cell) and call the .merge() method.

    from pyexcelerate import Workbook

bw = Workbook()
ws = wb.new_sheet("sheet name")
ws[1][1].value = 15
ws.range("A1", "B1").merge()
bw.save("output.xlsx")

  11. Write individual cell data

    dev

    There are two primary ways to set cell values:

    1. Faster: Use set_cell_value(row, col, value). This is useful for setting numbers, formulas (e.g., "=SUM(A1,B1)"), or datetime objects.
    2. Fast: Use the coordinate access pattern ws[row][col].value = value.
    from datetime import datetime
from pyexcelerate import Workbook

# Faster approach
bw = Workbook()
ws = wb.new_sheet("sheet name")
ws.set_cell_value(1, 1, 15)
ws.set_cell_value(1, 3, "=SUM(A1,B1)")

# Fast approach
ws[1][1].value = 15
ws[1][4].value = datetime.now()

  12. Write individual cell values

    dev

    You can set cell values using two primary methods:

    1. set_cell_value(row, col, value): Faster for bulk operations. Supports numbers, formulas (as strings), and datetime objects.
    2. ws[row][col].value = value: More intuitive syntax, but slightly slower.

    Note: PyExcelerate uses 1-based indexing for rows and columns.

    from datetime import datetime
from pyexcelerate import Workbook

wb = Workbook()
ws = wb.new_sheet("sheet name")
# Using set_cell_value
ws.set_cell_value(1, 1, 15)
ws.set_cell_value(1, 3, "=SUM(A1,B1)")
ws.set_cell_value(1, 4, datetime.now())

# Using bracket notation
ws[1][2].value = 20
wb.save("output.xlsx")