Excel automation: openpyxl, XlsxWriter, ExcelJS

This week’s 12 prices in the practice shop’s report add up to 2,238 rupees. Write that total into a workbook as a formula with openpyxl and a script that reads the file gets None; write it with XlsxWriter and the script gets 0, while the spreadsheet app on everyone’s screen shows the right number, so nobody notices.

Here the weekly report is built three ways: with XlsxWriter in Python, checked with openpyxl, and again with ExcelJS in Node. You will see what an .xlsx file holds, pick a library by the job, store totals that every reader sees, and keep titles from web pages from turning into formulas.

A workbook is a ZIP of XML parts

An .xlsx file looks like one document, but it is a ZIP package of XML files, which the Office Open XMLOffice Open XML: The file formats of .xlsx, .docx and .pptx, standardised as ECMA-376 and ISO/IEC 29500. A file is a ZIP package of XML parts, such as a workbook part, one part for each sheet and a part for the styles. standard, ECMA-376, calls parts. The program below writes a workbook of two products with XlsxWriter and opens it again as the ZIP file it is:

"""A two-product workbook from XlsxWriter, opened as the ZIP file it is: its parts, one row of cells, its texts, its format."""
import re
import zipfile

import xlsxwriter

from week import PRODUCTS, RUPEES, rupees

wb = xlsxwriter.Workbook("two_products.xlsx")
money = wb.add_format({"num_format": RUPEES})
ws = wb.add_worksheet("Prices")
for r, (sku, name, pack, last, this) in enumerate(sorted(PRODUCTS)[:2]):
    ws.write_string(r, 0, sku)
    ws.write_string(r, 1, name)
    ws.write_number(r, 2, rupees(this), money)
wb.close()

package = zipfile.ZipFile("two_products.xlsx")
print("parts of the ZIP package:")
for info in package.infolist():
    print(f"  {info.filename:<28} {info.file_size:>5} bytes of XML")
sheet = package.read("xl/worksheets/sheet1.xml").decode()
print("row 1 in xl/worksheets/sheet1.xml:")
print(" ", re.search(r'<row r="1".*?</row>', sheet).group())
print("texts in xl/sharedStrings.xml:", re.findall(r"<t>(.*?)</t>", package.read("xl/sharedStrings.xml").decode()))
styles = package.read("xl/styles.xml").decode()
print("style 1 in xl/styles.xml:", re.findall(r"<xf [^>]*/>", re.search(r"<cellXfs.*?</cellXfs>", styles).group())[1])
print("format 164 in xl/styles.xml:", re.search(r"<numFmt [^>]*/>", styles).group())

parts of the ZIP package:
  [Content_Types].xml           1167 bytes of XML
  _rels/.rels                    587 bytes of XML
  xl/_rels/workbook.xml.rels     697 bytes of XML
  xl/worksheets/sheet1.xml       698 bytes of XML
  xl/workbook.xml                550 bytes of XML
  xl/sharedStrings.xml           257 bytes of XML
  xl/styles.xml                 1043 bytes of XML
  xl/theme/theme1.xml           6994 bytes of XML
  docProps/core.xml              592 bytes of XML
  docProps/app.xml               784 bytes of XML
row 1 in xl/worksheets/sheet1.xml:
  <row r="1" spans="1:3"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c><c r="C1" s="1"><v>104</v></c></row>
texts in xl/sharedStrings.xml: ['NB-0003', 'Basmati rice', 'NB-0014', 'Toor dal']
style 1 in xl/styles.xml: <xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
format 164 in xl/styles.xml: <numFmt numFmtId="164" formatCode="&quot;₹&quot;#,##0.00"/>

Ten parts make up even this small file. Cell C1 is stored as <c r="C1" s="1"><v>104</v></c>: the number 104 and a style, 1. The rupee sign is not in the cell. Style 1 points at number formatnumber format: The code a spreadsheet uses to show a number, such as "₹"#,##0.00 or 0.0%, kept with the styles of a workbook. It changes how a value looks, never the value: 104 shown as ₹104.00 still adds up as 104. 164, "₹"#,##0.00, which tells a spreadsheet app to show ₹104.00, while the stored value stays 104, so it adds up like any number. Text cells hold an index into sharedStrings.xml, where each distinct text is stored once. Step through the parts that one cell touches:

Three things can sit in one cell, and the rest of this lesson needs all three: a value, a number format that changes only how the value looks, and in some cells a formula with its last result.

Pick the library by the job: read, edit or write

The three libraries of this lesson divide the work differently. openpyxl reads and writes .xlsx files: it can open a workbook someone else made, change cells and save it, and with data_only=True it returns the values stored at the last calculation instead of the formulas (openpyxl tutorial). It holds a whole workbook in memory, about 50 times the size of the file by its own documentation, unless you use its read-only or write-only mode.

XlsxWriter only writes. Its documentation names one disadvantage, that it “cannot read or modify existing Excel XLSX files”, and claims in return more Excel features than the other Python modules: tables, charts and conditional formats among them (XlsxWriter). ExcelJS reads and writes .xlsx in Node, from files or streams. pandas sits on top of the Python two: read_excel reads .xlsx with openpyxl unless told otherwise, and to_excel writes with XlsxWriter when it is installed and with openpyxl when it is not. In Node, the npm package xlsx of SheetJS stopped at 0.18.5 on the npm registry, and SheetJS publishes newer versions only from its own CDN (SheetJS).

Answer four questions about your job, and the table marks the libraries that fit it:

In short: XlsxWriter for new reports with formats and charts, openpyxl when a program must read or change an existing workbook, ExcelJS when the job runs in Node, and pandas when the data already sits in a DataFrame.

XlsxWriter builds the report, openpyxl checks it

The weekly report has a Prices sheet with one row per product and a Summary sheet with a chart. XlsxWriter builds it in four moves: every cell with the method of its type, write_string() for text and write_number() with a format for money; an Excel tableExcel table: A named range of a sheet with a header row, an optional totals row and a style of its own, such as the table Prices over A1:G14. Formulas can name its columns, as in SUBTOTAL(109,Prices[This week]). over the rows, with a totals row; the header row frozen with freeze_panes(1, 0); and a bar chart of the week’s changes on the Summary sheet. Then openpyxl, a second library, opens the file and looks at each part, as anyone who receives it will:

"""The weekly price report, built with XlsxWriter 3.2.9 and then opened with openpyxl 3.1.5 to check every part."""
import zipfile

import openpyxl
import xlsxwriter

from week import HEADERS, PERCENT, PRODUCTS, RUPEES, WEEK, rupees


def build(path):
    rows = sorted(PRODUCTS)
    last_total = sum(p[3] for p in rows if p[3] is not None)
    this_total = sum(p[4] for p in rows)
    wb = xlsxwriter.Workbook(path)
    money = wb.add_format({"num_format": RUPEES})
    pct = wb.add_format({"num_format": PERCENT})
    ws = wb.add_worksheet("Prices")
    for r, (sku, name, pack, last, this) in enumerate(rows, start=1):
        ws.write_string(r, 0, sku)  # text from the shop is written as text, always
        ws.write_string(r, 1, name)
        ws.write_string(r, 2, pack)
        if last is not None:
            ws.write_number(r, 3, rupees(last), money)
            ws.write_number(r, 5, rupees(this - last), money)
            ws.write_number(r, 6, round((this - last) / last, 4), pct)  # 0.0612 shows as 6.1%
        ws.write_number(r, 4, rupees(this), money)
    ws.add_table(0, 0, len(rows) + 1, 6, {
        "name": "Prices",
        "total_row": True,
        "columns": [
            {"header": HEADERS[0], "total_string": "Total"},
            {"header": HEADERS[1]},
            {"header": HEADERS[2]},
            {"header": HEADERS[3], "total_function": "sum", "total_value": rupees(last_total), "format": money},
            {"header": HEADERS[4], "total_function": "sum", "total_value": rupees(this_total), "format": money},
            {"header": HEADERS[5], "format": money},
            {"header": HEADERS[6], "format": pct},
        ],
    })
    ws.freeze_panes(1, 0)  # the header row stays in view
    ws.autofit()

    summary = wb.add_worksheet("Summary")
    summary.write_string(0, 0, f"Week {WEEK}: {len(rows)} products")
    chart = wb.add_chart({"type": "bar"})
    chart.add_series({"name": "Change (₹)", "categories": ["Prices", 1, 1, len(rows), 1],
                      "values": ["Prices", 1, 5, len(rows), 5]})
    chart.set_legend({"none": True})
    summary.insert_chart(2, 0, chart)
    wb.close()


def check(path):
    """The reader's side: open the file with another library and look at each part the report promises."""
    wb = openpyxl.load_workbook(path)
    values = openpyxl.load_workbook(path, data_only=True)
    ws = wb["Prices"]
    parts = zipfile.ZipFile(path).namelist()
    return [
        ("two sheets", wb.sheetnames),
        ("header frozen", ws.freeze_panes),
        ("table", {name: ref for name, ref in ws.tables.items()}),
        ("this week, NB-0003", (ws["E2"].value, ws["E2"].number_format)),
        ("change, NB-0003", (ws["G2"].value, ws["G2"].number_format)),
        ("total formula", ws["E14"].value),
        ("total a script reads", values["Prices"]["E14"].value),
        ("charts", [p for p in parts if p.startswith("xl/charts/")]),
    ]


if __name__ == "__main__":
    build("weekly_report.xlsx")
    for what, got in check("weekly_report.xlsx"):
        print(f"{what:<22} {got}")

two sheets             ['Prices', 'Summary']
header frozen          A2
table                  {'Prices': 'A1:G14'}
this week, NB-0003     (104, '"₹"#,##0.00')
change, NB-0003        (0.0612, '0.0%')
total formula          =SUBTOTAL(109,[This week])
total a script reads   2238.0
charts                 ['xl/charts/chart1.xml']

"""One week of the practice shop's prices, as the store of the last module keeps them: whole paise, so sums are exact.

Every product is invented. Kokum is new this week, so it has no price for last week. One listing title starts with
"==SALE==", the way some sellers decorate their titles; it is text, and it must stay text.
"""
WEEK = 18
PRODUCTS = [  # sku, name, pack, last week's price, this week's price (paise)
    ("NB-0003", "Basmati rice", "1 kg", 9800, 10400),
    ("NB-0014", "Toor dal", "1 kg", 15200, 15200),
    ("NB-0021", "Green tea", "250 g", 24500, 23900),
    ("NB-0027", "Jaggery", "1 kg", 7400, 7900),
    ("NB-0035", "Turmeric", "500 g", 13600, 13600),
    ("NB-0042", "Mustard oil", "1 l", 18900, 19800),
    ("NB-0050", "Poha", "500 g", 4800, 4600),
    ("NB-0063", "Cardamom", "100 g", 31500, 33000),
    ("NB-0071", "Ghee", "1 kg", 64000, 64000),
    ("NB-0084", "==SALE== Peanuts", "1 kg", 16800, 15500),
    ("NB-0093", "Cumin", "100 g", 6500, 6900),
    ("NB-0241", "Kokum", "250 g", None, 9000),
]
HEADERS = ["SKU", "Product", "Pack", "Last week", "This week", "Change", "Change %"]
RUPEES = '"₹"#,##0.00'  # the number format of every money cell: the value stays a number
PERCENT = "0.0%"


def rupees(paise):
    """Paise to rupees for a cell; None stays None (an empty cell)."""
    return None if paise is None else paise / 100

Every part is where the report promises it: two sheets, the header frozen above A2, a table called Prices over A1:G14 with its totals row in row 14, the rupee format on the money, and one chart part in the package. A change of 600 paise on 9,800 is stored as 0.0612 and shown as 6.1 %, because the format, not the value, adds the percent sign. The total reads 2238.0 to a script only because the program stored it, which the next section explains.

A formula’s result is only a stored guess

A formula cell holds two things: the formula, and the value it had when it was last calculated, the cached valuecached value: The result a formula cell stores next to its formula: from the last time a spreadsheet app calculated it, or whatever the program that wrote the file put there. A reader that does not calculate sees only this value.. Spreadsheet apps calculate formulas; these Python and Node libraries do not. The ExcelJS README says so plainly: it “cannot process the formula to generate a result, it must be supplied”. So the program that writes the file decides the cached value, and three common ways of writing the same total store three different ones:

"""One total, =SUM(E2:E13), written three ways, then read back the way a script or a viewer reads it: data_only=True."""
import re
import zipfile

import openpyxl
import xlsxwriter

from week import PRODUCTS, rupees

prices = [rupees(p[4]) for p in sorted(PRODUCTS)]  # this week's 12 prices, E2:E13
total = rupees(sum(p[4] for p in PRODUCTS))  # computed from whole paise, exactly

wb = openpyxl.Workbook()
ws = wb.active
for r, price in enumerate(prices, start=2):
    ws.cell(row=r, column=5, value=price)
ws["E14"] = "=SUM(E2:E13)"
wb.save("openpyxl.xlsx")

for name, cached in [("xlsxwriter.xlsx", None), ("xlsxwriter_value.xlsx", total)]:
    wb = xlsxwriter.Workbook(name)
    ws = wb.add_worksheet()
    ws.write_column(1, 4, prices)
    if cached is None:
        ws.write_formula("E14", "=SUM(E2:E13)")
    else:
        ws.write_formula("E14", "=SUM(E2:E13)", None, cached)  # the value readers see until a recalculation
    wb.close()

print("the total from the records:", total)
for name in ["openpyxl.xlsx", "xlsxwriter.xlsx", "xlsxwriter_value.xlsx"]:
    package = zipfile.ZipFile(name)
    cell = re.search(r'<c r="E14".*?</c>', package.read("xl/worksheets/sheet1.xml").decode()).group()
    flag = re.search(r'fullCalcOnLoad="\w+"', package.read("xl/workbook.xml").decode()).group()
    seen = openpyxl.load_workbook(name, data_only=True).active["E14"].value
    print(name)
    print("  cell E14 in the file:", cell)
    print("  recalculate on opening:", flag)
    print("  a script reads:", repr(seen))

the total from the records: 2238.0
openpyxl.xlsx
  cell E14 in the file: <c r="E14"><f>SUM(E2:E13)</f><v /></c>
  recalculate on opening: fullCalcOnLoad="1"
  a script reads: None
xlsxwriter.xlsx
  cell E14 in the file: <c r="E14"><f>SUM(E2:E13)</f><v>0</v></c>
  recalculate on opening: fullCalcOnLoad="1"
  a script reads: 0
xlsxwriter_value.xlsx
  cell E14 in the file: <c r="E14"><f>SUM(E2:E13)</f><v>2238.0</v></c>
  recalculate on opening: fullCalcOnLoad="1"
  a script reads: 2238.0

openpyxl left the value empty, so a script reads None. XlsxWriter stored 0, as its documentation says it does, and a script reads 0, a total that looks like a number and is wrong. Only the third file, whose program passed the total it had computed from whole paise, reads 2238.0. All three ask the spreadsheet app to recalculate when it opens them (fullCalcOnLoad="1"), which is why a person looking at the sheet sees the right total. Follow the total from the program to its readers:

XlsxWriter takes the cached value as the last argument of write_formula(), or as total_value in a table’s totals row; ExcelJS takes { formula, result }. XlsxWriter’s documentation adds that applications without a calculation engine, such as viewers and converters, display only the stored results (XlsxWriter).

ExcelJS writes the same workbook from Node

In Node, ExcelJS builds the same report. A cell’s value is a number, a string, or an object such as { formula, result }. Formats are numFmt strings, the frozen header is a view, { state: 'frozen', ySplit: 1 }, and addTable() places the header, the rows and a totals row in one call. The README shows how to ask for a recalculation on opening, calcProperties.fullCalcOnLoad = true, and the program sets it:

// The weekly price report built with ExcelJS 4.4.0 in Node: the same sheet, formats, frozen header and table.
import ExcelJS from 'exceljs';
import { HEADERS, PERCENT, PRODUCTS, RUPEES, WEEK, rupees } from './week.mjs';

const rows = [...PRODUCTS].sort((a, b) => a[0].localeCompare(b[0]));
const lastTotal = rows.reduce((sum, p) => sum + (p[3] ?? 0), 0);
const thisTotal = rows.reduce((sum, p) => sum + p[4], 0);

const wb = new ExcelJS.Workbook();
wb.calcProperties.fullCalcOnLoad = true; // ask the spreadsheet app to recalculate when it opens the file
const ws = wb.addWorksheet('Prices', { views: [{ state: 'frozen', ySplit: 1 }] });
ws.addTable({
  name: 'Prices',
  ref: 'A1',
  totalsRow: true,
  columns: HEADERS.map((name, i) => (i === 0 ? { name, totalsRowLabel: 'Total' } : i === 3 || i === 4 ? { name, totalsRowFunction: 'sum' } : { name })),
  rows: rows.map(([sku, name, pack, last, now]) => [
    sku, name, pack, rupees(last), rupees(now),
    last === null ? null : rupees(now - last),
    last === null ? null : Math.round(((now - last) / last) * 10000) / 10000,
  ]),
});
// ExcelJS never calculates a formula: the totals row gets the results the records give.
ws.getCell('D14').value = { formula: 'SUBTOTAL(109,Prices[Last week])', result: rupees(lastTotal) };
ws.getCell('E14').value = { formula: 'SUBTOTAL(109,Prices[This week])', result: rupees(thisTotal) };
for (const col of [4, 5, 6]) ws.getColumn(col).numFmt = RUPEES;
ws.getColumn(7).numFmt = PERCENT;
const summary = wb.addWorksheet('Summary');
summary.getCell('A1').value = `Week ${WEEK}: ${rows.length} products`;
await wb.xlsx.writeFile('exceljs_report.xlsx');

console.log('sheets:', wb.worksheets.map((w) => w.name).join(', '));
console.log('row 2:', ws.getRow(2).values.slice(1).join(' | '));
console.log('row 11:', ws.getRow(11).values.slice(1).join(' | '));
console.log('totals row:', ['A14', 'D14', 'E14'].map((a) => JSON.stringify(ws.getCell(a).value)).join(' | '));
console.log('E2 format:', ws.getCell('E2').numFmt, '| frozen rows:', ws.views[0].ySplit);

sheets: Prices, Summary
row 2: NB-0003 | Basmati rice | 1 kg | 98 | 104 | 6 | 0.0612
row 11: NB-0084 | ==SALE== Peanuts | 1 kg | 168 | 155 | -13 | -0.0774
totals row: "Total" | {"formula":"SUBTOTAL(109,Prices[Last week])","result":2130} | {"formula":"SUBTOTAL(109,Prices[This week])","result":2238}
E2 format: "₹"#,##0.00 | frozen rows: 1

// The week of week.py for Node: the same products and prices in whole paise. Every product is invented.
export const WEEK = 18;
export const PRODUCTS = [ // sku, name, pack, last week's price, this week's price (paise)
  ['NB-0003', 'Basmati rice', '1 kg', 9800, 10400],
  ['NB-0014', 'Toor dal', '1 kg', 15200, 15200],
  ['NB-0021', 'Green tea', '250 g', 24500, 23900],
  ['NB-0027', 'Jaggery', '1 kg', 7400, 7900],
  ['NB-0035', 'Turmeric', '500 g', 13600, 13600],
  ['NB-0042', 'Mustard oil', '1 l', 18900, 19800],
  ['NB-0050', 'Poha', '500 g', 4800, 4600],
  ['NB-0063', 'Cardamom', '100 g', 31500, 33000],
  ['NB-0071', 'Ghee', '1 kg', 64000, 64000],
  ['NB-0084', '==SALE== Peanuts', '1 kg', 16800, 15500],
  ['NB-0093', 'Cumin', '100 g', 6500, 6900],
  ['NB-0241', 'Kokum', '250 g', null, 9000],
];
export const HEADERS = ['SKU', 'Product', 'Pack', 'Last week', 'This week', 'Change', 'Change %'];
export const RUPEES = '"₹"#,##0.00';
export const PERCENT = '0.0%';
export const rupees = (paise) => (paise === null ? null : paise / 100);

Does the Node file say what the Python one says? The next program builds the XlsxWriter report again and reads the file that ExcelJS wrote, exceljs_report.xlsx, cell by cell:

"""The XlsxWriter report and the ExcelJS one (exceljs_report.xlsx, written by weekly_report.mjs), cell by cell."""
import zipfile

import openpyxl

from weekly_report import build

build("xlsxwriter_report.xlsx")
files = {"XlsxWriter": "xlsxwriter_report.xlsx", "ExcelJS": "exceljs_report.xlsx"}
stored = {who: openpyxl.load_workbook(path)["Prices"] for who, path in files.items()}
read = {who: openpyxl.load_workbook(path, data_only=True)["Prices"] for who, path in files.items()}

cells = [(row, col) for row in range(1, 15) for col in range(1, 8)]
a, b = stored["XlsxWriter"], stored["ExcelJS"]
values = [c for c in cells if read["XlsxWriter"].cell(*c).value != read["ExcelJS"].cell(*c).value]
formulas = [c for c in cells if a.cell(*c).value != b.cell(*c).value and c not in values]
formats = [c for c in cells if a.cell(*c).number_format != b.cell(*c).number_format]
print(f"Prices sheet, {len(cells)} cells")
print(f"  the same value for a reader: {len(cells) - len(values)}")
print(f"  the same text in the file:   {len(cells) - len(values) - len(formulas)}")
for c in formulas:
    print(f"    {a.cell(*c).coordinate}: {a.cell(*c).value}  |  {b.cell(*c).value}")
print(f"  the same number format:      {len(cells) - len(formats)}")
print("    the others:", ", ".join(f"{a.cell(*c).coordinate} ({a.cell(*c).value or 'empty'})" for c in formats))
for who, path in files.items():
    ws = stored[who]
    charts = [p for p in zipfile.ZipFile(path).namelist() if p.startswith("xl/charts/")]
    print(f"{who:<10} frozen at {ws.freeze_panes}, table {dict(ws.tables.items())}, B11 {ws['B11'].value!r}"
          f" as {'text' if ws['B11'].data_type == 's' else 'a formula'}, charts: {len(charts)}")

Prices sheet, 98 cells
  the same value for a reader: 98
  the same text in the file:   96
    D14: =SUBTOTAL(109,[Last week])  |  =SUBTOTAL(109,Prices[Last week])
    E14: =SUBTOTAL(109,[This week])  |  =SUBTOTAL(109,Prices[This week])
  the same number format:      89
    the others: D1 (Last week), E1 (This week), F1 (Change), G1 (Change %), D13 (empty), F13 (empty), G13 (empty), F14 (empty), G14 (empty)
XlsxWriter frozen at A2, table {'Prices': 'A1:G14'}, B11 '==SALE== Peanuts' as text, charts: 1
ExcelJS    frozen at A2, table {'Prices': 'A1:G14'}, B11 '==SALE== Peanuts' as text, charts: 0

All 98 cells give a reader the same value. Two formulas are written differently, [This week] against Prices[This week], and both refer to the same column of the same table. Nine number formats differ only on headers and empty cells, because an ExcelJS column format reaches every cell of its column. Both files freeze the header, hold the same table and keep the ==SALE== title as text. The one real difference is the chart: the ExcelJS README describes no way to add one, so its report has none. A Node report that needs one can draw the chart as an image and add it with addImage(), knowing that a picture does not change when the numbers do.

Text from a page must stay text in a cell

Pages carry titles that start with =: some sellers decorate their titles that way, and a hostile page can carry a whole formula. In a workbook, the library decides whether such a string becomes a formula. The program writes two titles four ways and looks at what each cell holds:

"""Two titles from the shop's pages written into a cell four ways: is the cell text, or a formula?"""
import re
import zipfile

import openpyxl
import xlsxwriter

TITLES = ["==SALE== Peanuts", '=HYPERLINK("https://offers.nimbu.test/x","Offer")']


def openpyxl_file(path, as_text):
    wb = openpyxl.Workbook()
    for r, title in enumerate(TITLES, start=1):
        cell = wb.active.cell(row=r, column=1, value=title)
        if as_text:
            cell.data_type = "s"  # the fix: keep it a string, whatever its first character
    wb.save(path)


def xlsxwriter_file(path, as_text):
    wb = xlsxwriter.Workbook(path)
    ws = wb.add_worksheet()
    for r, title in enumerate(TITLES):
        if as_text:
            ws.write_string(r, 0, title)  # the fix: write_string() never makes a formula
        else:
            ws.write(r, 0, title)  # write() turns a string that starts with = into a formula
    wb.close()


ways = [
    ("openpyxl, cell.value = title", openpyxl_file, False),
    ("openpyxl, then data_type = 's'", openpyxl_file, True),
    ("XlsxWriter, write()", xlsxwriter_file, False),
    ("XlsxWriter, write_string()", xlsxwriter_file, True),
]
for n, (label, make, as_text) in enumerate(ways):
    path = f"titles{n}.xlsx"
    make(path, as_text)
    xml = zipfile.ZipFile(path).read("xl/worksheets/sheet1.xml").decode()
    cells = re.findall(r'<c r="A\d"[^>]*>.*?</c>', xml)
    kinds = ["formula" if "<f>" in c else "text" for c in cells]
    print(f"{label:<32} A1 {kinds[0]:<8} A2 {kinds[1]}")

openpyxl, cell.value = title     A1 formula  A2 formula
openpyxl, then data_type = 's'   A1 text     A2 text
XlsxWriter, write()              A1 formula  A2 formula
XlsxWriter, write_string()       A1 text     A2 text

openpyxl’s plain assignment and XlsxWriter’s write() turned both titles into formulas: ==SALE== Peanuts into a formula that is not even valid, and the HYPERLINK text into a working link in the report. openpyxl makes a formula of every string that starts with =, and XlsxWriter’s write() does the same while its strings_to_formulas option is on, which is the default (XlsxWriter). The fixes are one line each: write_string() in XlsxWriter, and data_type = "s" after the assignment in openpyxl. ExcelJS needed neither, because it makes a formula only from an object with a formula key, as the comparison showed for row 11.

Practise: the numbers behind the report

The exercise computes every number the report shows from whole paise, the rows and the two totals; its sample tests use this lesson’s cases, from the new product without a last price to the title that starts with ==SALE==:

from decimal import ROUND_HALF_UP, Decimal


def report_rows(products):
    """One row per product, sorted by SKU: [sku, name, pack, last, this, change, change_pct]."""
    # Replace this: floats lose paise, a new product's missing price becomes 0, and the order is the input's.
    rows = []
    for sku, name, pack, last, this in products:
        last = (last or 0) / 100
        rows.append([sku, name, pack, last, this / 100, this / 100 - last, (this / 100 - last) / (last or 1)])
    return rows


def report_totals(products):
    """(last_total, this_total) in rupees, each over the products that have a price that week."""
    return sum((p[3] or 0) for p in products) / 100, sum(p[4] for p in products) / 100

from decimal import Decimal

from rows import report_rows, report_totals

WEEK = [
    ("NB-0084", "==SALE== Peanuts", "1 kg", 16800, 15500),
    ("NB-0003", "Basmati rice", "1 kg", 9800, 10400),
    ("NB-0241", "Kokum", "250 g", None, 9000),
    ("NB-0050", "Poha", "500 g", 4800, 4600),
]


def test_one_row_per_product_sorted_by_sku():
    """the report lists products in SKU order"""
    assert [r[0] for r in report_rows(WEEK)] == ["NB-0003", "NB-0050", "NB-0084", "NB-0241"]


def test_rupees_come_from_whole_paise():
    """10400 paise is exactly 104.00 rupees"""
    row = report_rows(WEEK)[0]
    assert row[3:5] == [Decimal("98.00"), Decimal("104.00")]
    assert str(row[4]) == "104.00"


def test_change_and_percentage():
    """a rise and a fall, the percentage rounded half up to four places"""
    rows = {r[0]: r for r in report_rows(WEEK)}
    assert rows["NB-0003"][5:] == [Decimal("6.00"), Decimal("0.0612")]
    assert rows["NB-0084"][5:] == [Decimal("-13.00"), Decimal("-0.0774")]


def test_a_new_product_has_no_change():
    """no price last week: last, change and percentage stay empty"""
    rows = {r[0]: r for r in report_rows(WEEK)}
    assert rows["NB-0241"][3] is None and rows["NB-0241"][5:] == [None, None]
    assert rows["NB-0241"][4] == Decimal("90.00")


def test_titles_come_back_as_given():
    """a title that starts with = is still the same text"""
    rows = {r[0]: r for r in report_rows(WEEK)}
    assert rows["NB-0084"][1:3] == ["==SALE== Peanuts", "1 kg"]


def test_totals_count_each_week_s_prices():
    """the new product has no price last week, so it is in this week's total only"""
    assert report_totals(WEEK) == (Decimal("314.00"), Decimal("395.00"))

Then test yourself on the six ideas of this lesson, starting where your answers have been weakest:

Interview questions

Warm-up (fresher): openpyxl or XlsxWriter for a weekly report, and why?

XlsxWriter, when the job creates the report from scratch: it writes new .xlsx files with formats, tables and charts, and it cannot read or change an existing file, which a report job never needs. openpyxl, when the job must open a workbook someone else made, read its values, change some cells and save it, or check a report after writing it. With either one, compute every total in code and store it as the formula’s cached value, because neither library calculates, and write text from pages with the method for text. For sheets of hundreds of thousands of rows, use XlsxWriter’s constant_memory mode or openpyxl’s write-only mode.

Two harder questions start from this lesson: totals that a dashboard reads as zero, and a nightly workbook that runs out of memory. The module’s bank gives the first step of each and the questions an interviewer asks next:

Key takeaways

• An .xlsx file is a ZIP package of XML parts, and a cell holds a value, a style with its number format and, in a formula cell, the formula and its cached value.
• XlsxWriter writes new files with the most formatting features, openpyxl reads, edits and checks workbooks, ExcelJS does both in Node, and pandas reads with openpyxl and writes with either Python library.
• Build the report with the method of each cell’s type, then check the file with a second library before anyone else opens it.
• No library calculates formulas, so compute every total from the records and store it as the cached value.
• ExcelJS writes the same workbook as XlsxWriter, value for value, but no chart, since its README offers no way to make one.
• Write every title from a page with the method for text, so that none of them turns into a formula.