Parsing Excel Files: Sheets, Formulas, Merged Cells

Excel files (.xlsx, and the older .xls) are the format businesses most often send data in: price lists, inventory, reports, and exports from accounting systems. Unlike CSV, Excel isn't plain text — it's a structured document. Inside an .xlsx is a ZIP archive of XML describing the sheets, styles, and formulas. So you can't read it "as strings"; you need libraries that understand a workbook's internal layout. This article continues our overview of document parsing.

What makes Excel parsing hard

Unlike a flat CSV, an Excel workbook has several layers of complexity.

• Many sheets. Data may be spread across tabs, and you have to select the one you want explicitly.
• Formulas. A cell can store either a formula (=B2*C2) or its computed value, and which one you get depends on how you read it.
• Merged cells, which leave a header "hovering" over several columns and produce empty gaps in the data.
• Formats. A date may arrive not as 2026-06-01 but as the number 46174 (Excel's serial date), and a price may be a plain number with a stored currency format.

All of this has to be handled during excel data parsing.

Python

The go-to tool for .xlsx is openpyxl. It gives you full control over the workbook: sheets, cells, and merges. It's the most common Python excel parser for row-by-row work.

from openpyxl import load_workbook

wb = load_workbook("prices.xlsx", data_only=True)  # data_only=True -> values instead of formulas
ws = wb["Prices"]                                   # select the sheet by name

for row in ws.iter_rows(min_row=2, values_only=True):
    sku, name, price = row[0], row[1], row[2]
    print(sku, name, price)

Note data_only=True: without it, a cell containing a formula returns the formula text rather than the result. And remember that a computed value only exists if the file has been opened in Excel at least once — openpyxl does not evaluate formulas itself.

When you need analysis rather than a row walk — filters, aggregates, joining tables — use pandas, which reads a sheet into a DataFrame in one line (it uses openpyxl under the hood).

import pandas as pd

df = pd.read_excel("prices.xlsx", sheet_name="Prices")
print(df[df["price"] > 100])          # e.g. items over $100

# convert to CSV if needed
df.to_csv("prices.csv", index=False, encoding="utf-8-sig")

For the old binary .xls format, pandas uses a separate xlrd engine; modern .xlsx files go through openpyxl.

JavaScript / Node.js

In the JS ecosystem the de facto standard is SheetJS (the xlsx package). It works in both Node and the browser, reads almost any table format, and conveniently turns a sheet into an array of objects.

const XLSX = require("xlsx");

const wb = XLSX.readFile("prices.xlsx");
const sheet = wb.Sheets[wb.SheetNames[0]];   // first sheet
const rows = XLSX.utils.sheet_to_json(sheet); // array of objects keyed by header

for (const row of rows) {
  console.log(row.sku, row.name, row.price);
}

When you need to write complex workbooks with styles as well as read them, ExcelJS is a good fit.

PHP

The main library is PhpSpreadsheet (the successor to the old PHPExcel). It reads and writes both .xlsx and .xls and understands formulas and formats.

<?php
require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load("prices.xlsx");
$rows = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);

foreach (array_slice($rows, 1) as $row) { // skip the header
    echo $row["A"] . " — " . $row["B"] . " — " . $row["C"] . "\n";
}

Go

In Go the de facto standard is excelize. It reads sheets as rows and supports formulas, styles, and streaming for large files.

package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {
    f, err := excelize.OpenFile("prices.xlsx")
    if err != nil {
        panic(err)
    }
    defer f.Close()

    rows, _ := f.GetRows("Prices")
    for _, row := range rows[1:] {
        fmt.Println(row[0], row[1], row[2])
    }
}

C

On .NET, ClosedXML offers a clean API over the Open XML SDK; EPPlus is another popular choice.

using ClosedXML.Excel;

using var wb = new XLWorkbook("prices.xlsx");
var ws = wb.Worksheet("Prices");

foreach (var row in ws.RowsUsed().Skip(1)) // skip the header
{
    var sku   = row.Cell(1).GetString();
    var name  = row.Cell(2).GetString();
    var price = row.Cell(3).GetValue<double>();
    Console.WriteLine($"{sku} {name} {price}");
}

Handling merged cells and date serials

Two problems come up so often they're worth a dedicated snippet. Merged cells store their value only in the top-left cell; every other cell in the range reads back empty. A common fix is to unmerge and fill the value down before you process the rows:

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
ws = wb.active

for rng in list(ws.merged_cells.ranges):
    top_left = ws.cell(rng.min_row, rng.min_col).value
    ws.unmerge_cells(str(rng))
    for r in range(rng.min_row, rng.max_row + 1):
        for c in range(rng.min_col, rng.max_col + 1):
            ws.cell(r, c).value = top_left

For date serials — when a cell gives you a raw number like 46174 instead of a date — convert from Excel's epoch (accounting for the well-known 1900 leap-year bug by anchoring at 1899-12-30):

from datetime import datetime, timedelta

def excel_serial_to_date(serial):
    return datetime(1899, 12, 30) + timedelta(days=int(serial))

print(excel_serial_to_date(46174))   # 2026-06-01 00:00:00

Practical tips

Before you write a parser, open the file and understand its structure: where the data begins (the first rows are often taken up by a header and a logo), whether there are merged cells in the headers, and which sheet holds the data you want. If the header is non-standard, it's easier to tell the parser which row to start on than to guess. Normalize dates and numbers right after reading — bring them to a single format — because within one file they're often written inconsistently.

FAQ

What's the best way to read xlsx in Python? For row-by-row access use openpyxl; for tabular analysis use pandas (pd.read_excel), which sits on top of openpyxl.

How do I get computed values instead of formula text? In openpyxl pass data_only=True, and make sure the file was saved by Excel at least once so the cached results exist. Libraries like excelize and PhpSpreadsheet can also evaluate formulas directly.

Why are some cells empty when I read a merged header? Merged ranges keep their value only in the top-left cell. Unmerge and fill the value across the range (see the snippet above) before parsing.

Let us automate it

If the workbooks are similar in shape but multi-sheet, full of merged cells and formulas, and arrive on a regular schedule, we'll set up automated Excel parsing for you with output to a database or CSV for downstream processing — part of our data as a service offering. And when the reports don't come as Excel at all but are laid out for print, that's PDF parsing — see the matching article.