Metadata-Version: 2.4
Name: excelipy
Version: 1.3.0
Summary: Wrapper around xlsxwriter to improve usability
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: numpy>=1.15
Requires-Dist: pandas>=2.2.3
Requires-Dist: pillow>=11.3.0
Requires-Dist: pydantic>=2.11.3
Requires-Dist: xlsxwriter>=3.2.2

# Excelipy

[![codecov](https://codecov.io/gh/choinhet/excelipy/graph/badge.svg?token=${CODECOV_TOKEN})](https://codecov.io/gh/choinhet/excelipy)

## Installation

You can install the package using pip:

```bash
pip install excelipy
```

Or add it as a dependency in your `pyproject.toml`:

```bash
uv add excelipy
```

## Appeal

### Declarative Syntax

This was created with less than 100 lines of code, in a declarative, clean, and easy to understand style:

![demo_example.png](static/demo_example.png)

### An auto-size that works

Default Parameters

![auto_size_1.png](tests/resources/output/image_output/auto_size_1.png)

Minimum Column Width

![auto_size_2.png](tests/resources/output/image_output/auto_size_2.png)

Maximum Column Width

![auto_size_0.png](tests/resources/output/image_output/auto_size_0.png)

```python
import pandas as pd
import excelipy as ep

long_text = "This is an avocado toast, Yay, Very happy with the avocado toast"
data = pd.DataFrame({"A": [1, 2, 3], "B": [4, "ha" * 50, 6], "C": [4, 5, 6]})
data.rename(columns={"A": long_text, "B": long_text}, inplace=True)
component = ep.Table(
    data=data,
    row_style={1: ep.Style(font_size=14)},
    min_col_size=10,
    max_col_size=20,
    wrap_header=True,
)
ep.save(
    excel=ep.Excel(
        path=out_path,
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[component],
                style=ep.Style(align="center", valign="vcenter"),
            )
        ],
    )
)
```

### Fonts

Auto-size works by measuring text, so it needs the metrics of the font a sheet
is written in - and a container normally has neither Calibri nor Arial
installed.

Nothing has to be installed for the fonts excelipy carries the widths of:

| Family | Regular | Bold |
| --- | --- | --- |
| Calibri (the default) | ✅ | ✅ |
| Cambria | ✅ | ✅ |
| Arial | ✅ | ✅ |
| Times New Roman | ✅ | ✅ |
| Courier New | ✅ | ✅ |

These are the advance widths and kerning pairs of the fonts themselves, so a
report generated in Airflow comes out laid out exactly as the same code lays it
out on a workstation - every column width and row height identical. A font that
*is* installed is measured from its own file instead, at the size and precision
the carried widths were read at, so the two paths agree to the pixel.

Any other family - Verdana, Segoe UI, Consolas - is measured from its file if
it is there and from a stand-in if it is not, and a stand-in is roughly a
seventh wider than Calibri, which shows up as columns wider than they need to
be and wrapped text given lines Excel does not draw. Fonts are looked for by
file name and, on Linux, through `fontconfig`, so installing the font or its
metric-compatible substitute is enough:

```dockerfile
RUN apt-get update && apt-get install -y --no-install-recommends \
    fonts-crosextra-carlito fonts-crosextra-caladea fonts-liberation \
 && rm -rf /var/lib/apt/lists/*
```

Falling back to a stand-in logs a warning rather than failing, and
`ep.font_measurement` reports how a family is being measured - `installed`,
`carried`, or `stand-in`. Only the last is a problem, and it is worth asserting
on wherever reports are generated unattended:

```python
assert ep.font_measurement("Verdana") != "stand-in"
assert ep.font_measurement("Verdana", bold=True) != "stand-in"
```

To see what a machine with no fonts of its own writes - to reproduce a
container's output on a workstation, say - set `EXCELIPY_NO_SYSTEM_FONTS=1`,
which ignores every font file that is installed:

```bash
EXCELIPY_NO_SYSTEM_FONTS=1 python report.py   # PowerShell: $env:EXCELIPY_NO_SYSTEM_FONTS=1
```

The carried widths are built by `tools/generate_font_metrics.py`, which reads
them off the metric-compatible faces (Carlito, Caladea and Liberation, all SIL
Open Font License 1.1) - run it to add a family.

## Usage

### Detailed Model Overview

Excelipy is built on top of Pydantic models, making it easy to define your Excel structure programmatically.

#### `Excel`

The top-level container for your workbook.

- `path`: Path to the output file (string, `Path`, or `BytesIO`).
- `sheets`: A list of `Sheet` objects.
- `nan_inf_to_errors`: If `True` (default), converts `NaN` and `Inf` values to Excel errors.

#### `Sheet`

Represents a single worksheet.

- `name`: The name of the sheet.
- `components`: A list of components (Tables, Text, etc.) to be rendered sequentially.
- `grid_lines`: Boolean to show or hide grid lines.
- `style`: A `Style` object to apply to the entire sheet (e.g., padding).

#### `Table`

The main component for rendering data.

- `data`: A `pandas.DataFrame`.
- `header_style`: A dictionary mapping column names to `Style` objects.
- `body_style`: A default `Style` for the table body.
- `column_style`: Styles for specific columns. Can be a `Style` object or a function that returns a `Style` based on
  cell value.
- `row_style`: Styles for specific rows.
- `header_filters`: Boolean to enable/disable Excel header filters.
- `merge_equal_headers`: Boolean. If `True`, adjacent columns with the same name will have their headers merged.

#### `Style`

Defines how cells look. Supports most common Excel formatting:

- **Font**: `bold`, `font_color`, `font_family`, `font_size`, `underline`.
- **Alignment**: `align` (left, center, right, etc.), `valign` (top, vcenter, bottom), `text_wrap`.
- **Border**: `border` (sets all sides) or individual `border_top`, `border_bottom`, etc. `border_color`.
- **Background**: `background` (hex color).
- **Format**: `numeric_format` (e.g., `"0.00%"`, `"#,##0.00"`).

#### Other Components

- `Text`: A simple text cell or merged range. Supports `width`, `height`, and `style`.
- `Link`: A clickable hyperlink (This can be nested inside a `Table` component).
- `Image`: Embeds an image from a `path`.
- `Fill`: Empty space component to push other components down or across.

### AI Integration

Excelipy exposes `ep.AI_GUIDE` — a prompt string describing its full API — and Pydantic-compatible schemas so any LLM
with structured output can generate excelipy components directly.

Pass `ep.AI_GUIDE` as the system prompt, use any component's `.model_json_schema()` to constrain the output, then
validate the result with `.model_validate()`. Any Excelipy model can be targeted — `ep.Table`, `ep.Text`, `ep.Sheet`,
etc. The more complex the schema, the stronger the model needs to be.

```python
import excelipy as ep
from pathlib import Path
from langchain_core.messages import SystemMessage, HumanMessage
from langchain_ollama import ChatOllama

model = ChatOllama(model="qwen2.5:7b")

# Any component can be targeted
target = ep.Table  # or ep.Text, ep.Sheet, ...

result = target.model_validate(
    model
    .with_structured_output(schema=target.model_json_schema())
    .invoke([
        SystemMessage(ep.AI_GUIDE),
        HumanMessage("Create a mocked social platform interaction table and style it"),
    ])
)

ep.save(ep.Excel(
    path=Path("ai_output.xlsx"),
    sheets=[ep.Sheet(name="Sheet1", components=[result])],
))
```

This works with any framework that supports structured output (LangChain, the OpenAI SDK, the Anthropic SDK, etc.) —
just swap the model and client.

### Examples

### Displaying a table

The most basic usage is to display a `pandas.DataFrame` as a table.

```python
import excelipy as ep

df = ...  # your pandas DataFrame

ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[ep.Sheet(name="Sheet1", components=[ep.Table(data=df)])],
    )
)
```

By default, `ep.Table` renders a `pandas.DataFrame` with standard formatting, including header filters and automatic
column width adjustment.

![displaying_a_table.png](tests/resources/output/image_output/displaying_a_table.png)

### Basic column formatting

You can apply styles to headers and specific columns. Here, we use `header_style` to bold and center the headers, and
`column_style` to format the "Value" column with two decimal places.

```python
ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[
                    ep.Table(
                        data=df,
                        header_style={
                            col: ep.Style(
                                bold=True, align="center", valign="vcenter"
                            )
                            for col in df.columns
                        },
                        column_style={"Value": ep.Style(numeric_format=",.2f")},
                    )
                ],
            )
        ],
    )
)
```

The output shows bold, centered headers and a formatted numeric column.

![basic_column_formatting.png](tests/resources/output/image_output/basic_column_formatting.png)

### Adding a title

You can add text components above or around your tables. In this example, an `ep.Text` component is added before the
`ep.Table`. We set its `width` to match the number of columns in the table and apply a background color and centered
alignment to create a unified title bar.

```python
num_cols = len(df.columns)
ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[
                    ep.Text(
                        text="Sales by Product",
                        width=num_cols,
                        style=ep.Style(
                            bold=True,
                            background="#ecedef",
                            align="center",
                            valign="vcenter",
                        ),
                    ),
                    ep.Table(
                        data=df,
                        header_style={
                            col: ep.Style(
                                bold=True, align="center", valign="vcenter"
                            )
                            for col in df.columns
                        },
                        column_style={"Value": ep.Style(numeric_format=",.2f")},
                    ),
                ],
            )
        ],
    )
)
```

The resulting Excel sheet features a stylized title spanning across the top of the table.

![adding_a_title.png](tests/resources/output/image_output/adding_a_title.png)

### Category coloring

Styles can be applied dynamically based on the cell content. By passing a function to `column_style`, we can change the
background and font color of the "Store" column depending on its value, making different categories easy to distinguish.

```python
def get_store_color(store: str) -> ep.Style:
    return ep.Style(
        background=store_colors[store],
        font_color=choose_font_color(store_colors[store]),
        bold=True,
    )


ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[
                    ep.Table(
                        data=df,
                        column_style={
                            "Value": ep.Style(numeric_format=",.2f"),
                            "Store": get_store_color,
                        },
                    ),
                ],
            )
        ],
    )
)
```

Each store now has its own unique color coding in the "Store" column.

![category_coloring.png](tests/resources/output/image_output/category_coloring.png)

### Merging columns

By giving multiple columns the same name and setting `merge_equal_headers=True` (which is the default), they will be
merged in the header. In this example, all columns are renamed to the same title, creating a single merged header over
the entire table. We also use `idx_column_style` to apply styles by column index instead of name.

```python
unified = "Sales by Product by Store"
df = df.rename(columns={col: unified for col in df.columns})

ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[
                    ep.Table(
                        data=df,
                        header_style={
                            col: ep.Style(
                                bold=True, align="center", valign="vcenter"
                            )
                            for col in df.columns
                        },
                        body_style=ep.Style(align="center", valign="vcenter"),
                        idx_column_style={
                            0: get_store_color,
                            2: ep.Style(numeric_format=",.2f"),
                        },
                        header_filters=False,
                    )
                ],
            )
        ],
    )
)
```

The header columns are merged into one, and the body cells are centered.

![merging_columns.png](tests/resources/output/image_output/merging_columns.png)

### Conditional formatting

You can also apply row-wise conditional formatting. Using the `@ep.row_wise` decorator on a styling function allows you
to access the entire row's data. Here, we highlight "Value" in red if it falls below the average for that product. We
also hide `grid_lines` and add `padding` to the sheet for a cleaner look.

```python
@ep.row_wise
def get_value_style(row) -> ep.Style:
    store, product, value = row
    prod_avg = avg_by_product[product]
    if value < prod_avg:
        return ep.Style(font_color="#ff0014", numeric_format=",.2f", bold=True)
    return ep.Style(numeric_format=",.2f", bold=True)


ep.save(
    excel=ep.Excel(
        path="output.xlsx",
        sheets=[
            ep.Sheet(
                name="Sheet1",
                components=[
                    ep.Table(
                        data=df,
                        idx_column_style={
                            0: get_store_color,
                            2: get_value_style,
                        },
                        header_filters=False,
                    ),
                    ep.Fill(width=num_cols),
                    ep.Text(
                        text="Products that sold below average are highlighted in red",
                        style=ep.Style(
                            bold=True,
                            valign="vcenter",
                            align="center",
                            border=3,
                            border_color="#ff0014",
                        ),
                        width=num_cols,
                        height=3,
                    ),
                ],
                grid_lines=False,
                style=ep.Style(padding=2),
            )
        ],
    )
)

```

The final output features conditional red text for below-average sales, a descriptive footer box, and no visible grid
lines.

![conditional_formatting.png](tests/resources/output/image_output/conditional_formatting.png)

### Extra

#### Component Groups

Excelipy supports grouping components together using the `ep.Group` model. This allows you to organize related
components into logical units, making it easier to manage complex sheet structures, fetch specific sections, and
potentially replace entire groups later.

**Why use groups?**

- **Organization**: Keep related components together (e.g., a title, table, and footer as one unit).
- **Reusability**: Define a group once and reuse it across multiple sheets.
- **Manipulation**: Fetch or replace entire sections of components programmatically.

**Basic usage:**

```python
import excelipy as ep

# Define a group of components
header_group = ep.Group(
    name="Header",
    components=[
        ep.Text(text="Monthly Sales Report"),
        ep.Fill(height=1),
    ]
)

# Use the group in a sheet
sheet = ep.Sheet(
    name="Sales",
    components=[
        header_group,
        ep.Table(data=pd.DataFrame(...)),
    ]
)

# Search for and replace the header group with a single text
for i, component in enumerate(list(sheet.components)):
    if isinstance(component, ep.Group) and component.name == "Header":
        sheet.components[i] = ep.Text(text="Sales Report")
        break
```

If you are working directly with sheet components and need to flatten nested `ep.Group` structures into a simple list,
you can use the `ep.unnest_components` utility function.

