Chapter 14 - Excel Spreadsheets (JavaScript)

Here's a JavaScript-flavoured version of the same concepts, with small JS/Node examples for each idea using the xlsx (SheetJS) package.

---

Intro and setup

• Goal: use xlsx (SheetJS) to read/write .xlsx files, automating repetitive spreadsheet tasks from Node.js.
• Like openpyxl, xlsx works on files, not the live Excel app.
• Install with npm install xlsx.

const XLSX = require("xlsx");

---

Reading Excel files

Workbooks, sheets, cells

Same structure: a workbook contains sheets, each sheet has columns (A, B, ...) and rows (1, 2, ...), and a cell is identified by address like B3.

Opening a workbook

const XLSX = require("xlsx");

const wb = XLSX.readFile("example3.xlsx");
console.log(typeof wb);  // 'object'

Getting sheets from the workbook

console.log(wb.SheetNames);            // ['Sheet1', 'Sheet2', 'Sheet3']
const sheet = wb.Sheets["Sheet1"];      // sheet object

wb.SheetNames is an array of names; wb.Sheets[name] gives the sheet object.

Getting cells from a sheet

const XLSX = require("xlsx");
const wb = XLSX.readFile("example3.xlsx");
const sheet = wb.Sheets["Sheet1"];

console.log(sheet["A1"].v);   // cell value (e.g. date serial number)
console.log(sheet["B1"].v);   // 'Apples'
console.log(sheet["C1"].v);   // 73

Cell objects have .v (raw value), .w (formatted text), .t (type: 's' string, 'n' number, 'd' date).

You can also convert to a 2D array or JSON:

const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });
console.log(data[0]);  // ['2035-04-05...', 'Apples', 73]

Size of the used area

const range = XLSX.utils.decode_range(sheet["!ref"]);
console.log(range.e.r + 1);  // max row (1-based)
console.log(range.e.c + 1);  // max column (1-based)

Converting between column letters and numbers

const XLSX = require("xlsx");

console.log(XLSX.utils.encode_col(0));   // 'A'  (0-based)
console.log(XLSX.utils.encode_col(26));  // 'AA'

console.log(XLSX.utils.decode_col("A"));   // 0
console.log(XLSX.utils.decode_col("AA"));  // 26

Note: SheetJS uses 0-based column indices internally.

Getting ranges as JSON

const rows = XLSX.utils.sheet_to_json(sheet);
for (const row of rows) {
  console.log(row);  // { Date: ..., Fruit: 'Apples', Quantity: 73 }
}

sheet_to_json uses the first row as headers by default.

Review checklist

To read a cell:

1. const XLSX = require("xlsx")
2. const wb = XLSX.readFile(...)
3. Get sheet via wb.Sheets[name]
4. Access cell via sheet["B3"].v or convert to JSON with XLSX.utils.sheet_to_json(sheet)

---

Project: Gather Census Statistics

Same task: from a census spreadsheet, count tracts and total population per county.

const XLSX = require("xlsx");
const fs = require("fs");

const wb = XLSX.readFile("censuspopdata.xlsx");
const sheet = wb.Sheets["Population by Census Tract"];
const rows = XLSX.utils.sheet_to_json(sheet);

const countyData = {};

for (const row of rows) {
  const state = row["State"];     // column header names
  const county = row["County"];
  const pop = row["Population"];

  countyData[state] ??= {};
  countyData[state][county] ??= { tracts: 0, pop: 0 };
  countyData[state][county].tracts += 1;
  countyData[state][county].pop += Number(pop);
}

fs.writeFileSync(
  "census2010.json",
  JSON.stringify(countyData, null, 2)
);
console.log("Done.");

Use ??= (nullish coalescing assignment) to ensure nested objects exist — equivalent to Python's setdefault.

Later:

const data = JSON.parse(fs.readFileSync("census2010.json", "utf-8"));
const anchorage = data["AK"]["Anchorage"];
console.log(anchorage.pop, anchorage.tracts);

---

Writing Excel documents

Creating and saving workbooks

const XLSX = require("xlsx");

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([["Hello", "World"]]);
XLSX.utils.book_append_sheet(wb, ws, "Spam Bacon Eggs Sheet");

XLSX.writeFile(wb, "example_new.xlsx");

aoa_to_sheet converts a 2D array (array of arrays) into a sheet object.

Creating sheets with data

const XLSX = require("xlsx");

const wb = XLSX.utils.book_new();

const data = [
  ["Name", "Age"],
  ["Alice", 30],
  ["Bob", 25],
];
const ws = XLSX.utils.aoa_to_sheet(data);
XLSX.utils.book_append_sheet(wb, ws, "People");

XLSX.writeFile(wb, "people.xlsx");

Writing values to specific cells

const XLSX = require("xlsx");

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet");

ws["A1"] = { v: "Hello, world!", t: "s" };
ws["!ref"] = "A1:A1";  // update range

XLSX.writeFile(wb, "hello.xlsx");

Or build from a 2D array and let SheetJS handle cell references automatically.

---

Project: Update a Spreadsheet

Same task: fix incorrect prices in a produce sales spreadsheet.

const XLSX = require("xlsx");

const wb = XLSX.readFile("produceSales3.xlsx");
const sheet = wb.Sheets["Sheet"];
const rows = XLSX.utils.sheet_to_json(sheet);

const PRICE_UPDATES = {
  Garlic: 3.07,
  Celery: 1.19,
  Lemon: 1.27,
};

// Find the range to iterate cells directly
const range = XLSX.utils.decode_range(sheet["!ref"]);
for (let r = 1; r <= range.e.r; r++) {  // skip header (row 0)
  const nameCell = sheet[XLSX.utils.encode_cell({ r, c: 0 })];  // column A
  const priceCell = XLSX.utils.encode_cell({ r, c: 1 });        // column B
  if (nameCell && PRICE_UPDATES[nameCell.v] !== undefined) {
    sheet[priceCell] = { v: PRICE_UPDATES[nameCell.v], t: "n" };
  }
}

XLSX.writeFile(wb, "updatedProduceSales3.xlsx");

---

Styling cells: fonts

SheetJS Community Edition has limited styling. For full styling support, use xlsx-style or exceljs:

npm install exceljs

const ExcelJS = require("exceljs");

const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("Sheet");

ws.getCell("A1").value = "Hello, world!";
ws.getCell("A1").font = { size: 24, italic: true };

ws.getCell("A3").value = "Bold Times New Roman";
ws.getCell("A3").font = { name: "Times New Roman", bold: true };

await wb.xlsx.writeFile("styles3.xlsx");

Useful font properties: name, size, bold, italic — same as Python's Font keyword args.

---

Formulas

SheetJS can write formula cells; Excel calculates them when opened.

const XLSX = require("xlsx");

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet([]);
XLSX.utils.book_append_sheet(wb, ws, "Sheet");

ws["A1"] = { v: 200, t: "n" };
ws["A2"] = { v: 300, t: "n" };
ws["A3"] = { f: "SUM(A1:A2)" };  // 'f' for formula
ws["!ref"] = "A1:A3";

XLSX.writeFile(wb, "writeFormula3.xlsx");

The f property holds the formula string (without the = prefix). SheetJS does not evaluate formulas — Excel does when you open the file.

---

Overall idea of the chapter

Chapter 14 shows how to automate Excel tasks from JavaScript: reading workbooks/sheets/cells with xlsx (SheetJS), building data structures from rows, writing and updating cells, and writing formulas. For styling, exceljs provides full font/color support. The key pattern is the same: load workbook → loop rows → read or write cells → save.