Writing Excel files

As of 26.09, a rule can build an Excel workbook (.xlsx) from its data and hand it to the people who need it. A workbook is written once, to an in-memory buffer, and the same bytes can then go to two places:

Destination	Function	Where the user finds it
File Management	additional_output	Smart Integration → File Management in the console, or the storage API
An email	send_email(..., attachments=...)	Their inbox

A rule can do either or both. Both functions are available to flow rules (FlowRule) only. A transform rule cannot write files.

A complete example

The rule below writes the flow's readings to a workbook with a readings sheet and a summary sheet, then stores it in File Management:

import io

import pandas as pd

from ewx_public.flow_rule import FlowRule
from ewx_public.rule_result import RuleResult


class MonthlyReadingsReport(FlowRule):
    def prepare_context(self, **kwargs):
        return {}

    def apply(self, file_prefix: str = "readings", **kwargs):
        frame = self.dataframe.copy()
        # Excel cannot store a timezone. Convert to UTC and drop it, or to_excel
        # raises "Excel does not support datetimes with timezones".
        frame.index = frame.index.tz_convert("UTC").tz_localize(None)
        frame.index.name = "timestamp (UTC)"

        buffer = io.BytesIO()
        with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
            frame.to_excel(writer, sheet_name="Readings")
            frame.describe().to_excel(writer, sheet_name="Summary")

        timestamp = self.flow_timestamp.strftime("%Y%m%dT%H%M%S")
        filename = f"{file_prefix}_{self.datasource.id}_{timestamp}.xlsx"

        self.additional_output(
            filename=filename,
            file_content=buffer.getvalue(),
            tags=[self.datasource.id, "monthly-report"],
        )
        self.rule_logger.info(f"Stored {filename} in File Management")
        return RuleResult()

The rest of this page goes through each step.

1. Prepare the DataFrame

self.dataframe is indexed by timezone-aware timestamps, normally in UTC. Excel has no notion of a timezone, so remove it before writing:

frame = self.dataframe.copy()
frame.index = frame.index.tz_convert("UTC").tz_localize(None)

Work on a copy, as above. Changing self.dataframe in place changes it for every rule that runs after this one in the flow.

If the reader expects local time, convert to their timezone before dropping it, and name the index so the column header says which time it is:

frame.index = frame.index.tz_convert("Europe/Amsterdam").tz_localize(None)
frame.index.name = "timestamp (Europe/Amsterdam)"

note

A local-time sheet has a repeated hour on the night the clocks go back, and a missing hour when they go forward. If the workbook is going to be processed by another system, UTC is the safer choice.

Choose the columns the reader needs, and rename them if channel classifiers aren't meaningful to them:

frame = frame[["E_CONS", "E_PROD"]].rename(
    columns={"E_CONS": "Consumption (kWh)", "E_PROD": "Production (kWh)"}
)

2. Write the workbook to a buffer

Rules cannot write to disk. Write the workbook to an io.BytesIO buffer and keep the bytes. Both xlsxwriter and openpyxl are installed in the rule runtime, and pandas uses either one as its Excel engine.

For a single sheet, pass the buffer straight to to_excel:

import io

buffer = io.BytesIO()
frame.to_excel(buffer, engine="xlsxwriter", sheet_name="Readings")
content = buffer.getvalue()

For several sheets, use pd.ExcelWriter in a with block. The workbook is only complete once the block has closed, so call getvalue() after it, not inside it:

import io

import pandas as pd

buffer = io.BytesIO()
with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Readings")
    frame.describe().to_excel(writer, sheet_name="Summary")
content = buffer.getvalue()

note

ExcelWriter.save() was removed in pandas 2. Older rules that call writer.save() fail with an AttributeError. Use a with block, or call writer.close(). See the Pandas 2 Migration Guide.

Formatting the sheet

With the xlsxwriter engine, writer.book and writer.sheets give you the underlying workbook and worksheets. Use them to set column widths, number formats and frozen header rows:

with pd.ExcelWriter(
    buffer, engine="xlsxwriter", datetime_format="yyyy-mm-dd hh:mm"
) as writer:
    frame.to_excel(writer, sheet_name="Readings")

    workbook = writer.book
    worksheet = writer.sheets["Readings"]
    value_format = workbook.add_format({"num_format": "0.000"})

    worksheet.set_column(0, 0, 18)  # timestamp column
    worksheet.set_column(1, len(frame.columns), 14, value_format)
    worksheet.freeze_panes(1, 1)  # keep the header row and timestamps in view

The full set of formatting options is in the XlsxWriter documentation.

xlsx only

Always create .xlsx. The old binary .xls format cannot be written, because pandas removed the only engine that could write it. Giving an .xlsx workbook an .xls name makes Excel warn about a format mismatch when the file is opened.

3. Deliver the workbook

Store it in File Management

Pass the bytes to additional_output:

self.additional_output(
    filename=f"report_{self.datasource.id}.xlsx",
    file_content=content,
    tags=[self.datasource.id, "monthly-report"],
)

• file_content must be bytes: the value of buffer.getvalue(), not the buffer itself. Passing the BytesIO object raises TypeError: file_content must be str or bytes while the rule runs.
• tags are how the file is found again, both in the console and through the API. Tag the datasource id plus something that identifies the report.
• filename does not have to be unique. Every call creates a new entry in File Management, so writing report.xlsx twice gives you two files with the same name rather than replacing the first one. Put the datasource id and the flow timestamp in the name so the entries can be told apart.
• market_adapter_id should be left out. Setting it hands the file to that market adapter for ingestion, which only makes sense if the adapter can read the workbook back in.

Email it

Wrap the same bytes in an Attachment:

from ewx_public.domain.models.attachment import Attachment

self.send_email(
    to_emails=["operations@example.com"],
    subject="Monthly readings report",
    content="<p>This month's readings are attached.</p>",
    attachments=[Attachment(filename="report.xlsx", content=content)],
)

The content type is set from the .xlsx extension. One email can carry at most 10 attachments, totalling 10 MB. Email must be enabled for your environment; see Sending Emails for the setup and every other option.

To file the report and email it, call both functions with the same content.

4. Download the workbook

From the console

Open Smart Integration → File Management, search by filename or tag, and click the filename to download it. Your role needs permission to read storage.

Through the API

Search the files with GET /storage/files/search, or fetch one with GET /storage/files/<id>. Each file in the response has a signedUrl, and downloading that URL returns the workbook. Write the download in binary mode ("wb"). How to retrieve files from the platform has a complete script.

note

Do not use GET /storage/files/content/<id> for a workbook. That endpoint returns a file's contents as text, and it answers 400 Bad Request for an Excel file because a workbook is binary.

5. Test the rule locally

The ewx-public testing tools record each additional_output call, so a test can open the workbook the rule produced:

import io

import pandas as pd

from ewx_public.testing import (
    assert_additional_output,
    make_datasource,
    make_testable,
    make_timeseries_df,
)
from rules.monthly_readings_report import MonthlyReadingsReport


def test_writes_a_readable_workbook():
    rule = make_testable(
        MonthlyReadingsReport,
        datasource=make_datasource(id="meter-001", channels=["E_CONS"]),
        dataframe=make_timeseries_df(columns=["E_CONS"]),
    )

    rule.run()

    assert_additional_output(rule, count=1)
    output = rule.backend.additional_outputs[0]
    assert output["filename"].endswith(".xlsx")
    assert "meter-001" in output["tags"]
    # The platform rejects anything but str or bytes; the local backend does not.
    assert isinstance(output["file_content"], bytes)

    sheets = pd.read_excel(io.BytesIO(output["file_content"]), sheet_name=None)
    assert list(sheets) == ["Readings", "Summary"]
    assert len(sheets["Readings"]) == 24

assert_additional_output filters by filename= and market_adapter_id=. Its count parameter works like the other assertion helpers: None means at least one file, 0 means none, and an integer means exactly that many. To check an emailed workbook, use assert_email_sent(rule, attachment_filename="report.xlsx").

Troubleshooting

Error or symptom	Cause	Fix
ValueError: Excel does not support datetimes with timezones	The index still carries a timezone	frame.index = frame.index.tz_convert("UTC").tz_localize(None)
TypeError: file_content must be str or bytes, got BytesIO	The buffer was passed instead of its contents	Pass buffer.getvalue()
AttributeError: 'XlsxWriter' object has no attribute 'save'	Pandas 1 code	Use a with pd.ExcelWriter(...) block
No Excel writer 'xlwt'	Trying to write .xls	Write .xlsx
The downloaded file will not open	It was saved as text, for example by a script that decodes the download	Save the bytes unchanged (open(path, "wb"))
The stored file is empty (0 bytes)	getvalue() was called inside the with pd.ExcelWriter(...) block	Call it after the block has closed
The file is ingested, or ingestion fails	market_adapter_id was set	Leave it out for reports