Generating Excel reports on a server that has no Excel

Sooner or later someone asks for "just a spreadsheet, like the one we make by hand." You write it, it works on your laptop, you deploy it, and it dies - because the server has no Microsoft Office installed and never will.

This is one of those problems that looks trivial and has about six sharp edges. Here is the full set, in the order you will hit them.

Why the obvious approach fails

The tempting solution is COM automation - driving Excel through its object model:

import win32com.client
excel = win32com.client.Dispatch("Excel.Application")   # needs Excel installed

This is a trap even when Excel is installed. COM automation of Office is explicitly unsupported by Microsoft in unattended server contexts. It leaves zombie EXCEL.EXE processes, deadlocks when a modal dialog appears with nobody to click OK, and behaves differently depending on which user session it runs under.

The fix is not to install Office on the server. It is to write the file format directly.

Option 1: CSV, and the two details everyone gets wrong

If the recipient just wants to open it, sort it, and filter it, CSV is enough - and it has no dependencies at all. But naive CSV arrives broken for most European users:

import csv

def write_csv(rows, path, cols):
    with open(path, "w", newline="", encoding="utf-8-sig") as fh:
        w = csv.DictWriter(fh, fieldnames=cols, delimiter=";",
                           extrasaction="ignore")
        w.writeheader()
        w.writerows(rows)

Three things there are doing real work:

encoding="utf-8-sig" writes a byte-order mark. Excel does not sniff UTF-8; without the BOM it falls back to the system codepage and every accented character turns to mojibake. This is the single most common "your export is broken" bug report.

delimiter=";" matches the list separator on locales where the decimal separator is a comma - most of continental Europe. With a comma delimiter, the entire row lands in column A.

newline="" stops Python's universal newlines from doubling up \r\n on Windows, which otherwise gives you a blank row between every record.

If your audience is mixed-locale, you cannot satisfy both with one file. Either ship two, or move to a real workbook - which brings us to:

Option 2: a real .xlsx with openpyxl

When you need multiple sheets, formatting, or frozen headers, write the actual format:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

def write_xlsx(rows, cols, path, sheet_name="Report"):
    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name

    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill("solid", fgColor="12324F")

    ws.append(cols)
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill

    for row in rows:
        ws.append([row.get(c, "") for c in cols])

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions

    wb.save(path)

No Excel required - openpyxl writes the XML that lives inside an .xlsx (it is a zip file of XML, which you can verify by renaming one to .zip).

Column widths, because auto-fit does not exist

There is no "autofit" in the file format - Excel computes it at render time, and openpyxl is not Excel. You approximate it:

def autosize(ws, cols, max_width=60):
    for i, col in enumerate(cols, start=1):
        longest = max(
            [len(str(col))] +
            [len(str(c.value)) for c in ws[get_column_letter(i)] if c.value]
        )
        ws.column_dimensions[get_column_letter(i)].width = min(longest + 2, max_width)

Cap the width. One long description field will otherwise produce a column three screens wide.

The gotchas that will actually bite you

Timezone-aware datetimes raise. The xlsx format has no timezone concept:

value = value.replace(tzinfo=None) if isinstance(value, datetime) else value

Strings starting with = become formulas. If your data contains user-supplied text, this is a CSV/formula injection risk as well as a rendering bug:

if isinstance(value, str) and value[:1] in ("=", "+", "-", "@"):    value = "'" + value

Illegal control characters raise, not warn. Scraped text loves to carry them:

import re
ILLEGAL = re.compile(r"[\000-\010\013\014\016-\037]")
value = ILLEGAL.sub("", value) if isinstance(value, str) else value

31 characters, and no []:*?/\ in sheet names. Exceed it and the file is silently corrupt in some readers.

Memory. openpyxl holds the whole workbook in RAM. Past ~100k rows, use write-only mode:

wb = Workbook(write_only=True)
ws = wb.create_sheet("Report")
for row in rows:
    ws.append([row.get(c, "") for c in cols])   # streams, never fully in memory

Option 3: when the config file is the real problem

A subtler version of the same trap: reading configuration from a spreadsheet. It feels convenient - non-technical colleagues can edit it - and it fails the same way, because reading .xlsx through Office automation needs Office.

Move configuration to JSON and keep the spreadsheet for output only:

{
  "recipients": ["[e-mail]"],  "min_score": 3,
  "send_email": true
}

Two notes from having got this wrong. First, be consistent about types - if a flag is the string "true" in one place and boolean true in another, someone will eventually write 1 and spend an afternoon on it. Pick one and document it. Second, fields your code does not actually read should be deleted, not left in. A config file with dead keys is worse than no config file: someone will change one, nothing will happen, and they will lose an hour before concluding the system is broken.

The rule underneath all of this

Never depend on a desktop application being present on a server. Not Excel, not Word, not Outlook, not a PDF printer driver. Every one of those has a library that writes the format directly, and the library version will run headless, in parallel, without a user session, inside a container, and at 3am when nobody can dismiss a dialog box.

The five minutes you save with Dispatch("Excel.Application") cost a day the first time it hangs in production - and it hangs at the worst possible moment, because the worst possible moment is exactly when nobody is logged in to click OK.

---

I build automation and reporting systems in Python and UiPath, mostly in places where the production environment is nothing like the developer's laptop.