Parse Excel Files in Python, JavaScript, and Java

Overview

Sooner or later a business user hands you an Excel file instead of a clean CSV. Parsing .xlsx programmatically lets you automate data ingestion, generate reports, and validate exports — and unlike most file formats, every major language has a mature library for it. This recipe covers reading and writing Excel in Python (openpyxl/pandas), JavaScript (SheetJS), and Java (Apache POI). For a deeper Python-only guide with pandas, see Read and Write Excel Files in Python.

When to Use

• Importing data that business users deliver as Excel instead of CSV
• Generating formatted reports with multiple sheets, styles, or formulas
• Validating data exports against a source-of-truth database
• Converting legacy Excel-based workflows into automated pipelines

Skip it when the source is already a database (query it directly instead of round-tripping through Excel) or when the file is actually CSV — flat formats parse much faster; see Parse CSV Files.

Choosing the right library:

Solution

The snippets below are copy-paste ready. Runnable versions for all three languages live in the companion repository.

Python

openpyxl is the standard for modern .xlsx files. pandas uses it as the engine behind read_excel, so installing both covers analysis and cell-level work.

pip install openpyxl pandas

from openpyxl import load_workbook

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

for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

# Writing
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Product", "Quantity", "Price"])
ws.append(["Widget", 100, 19.99])
wb.save("output.xlsx")

JavaScript

SheetJS (the xlsx package) is the most common Excel library for Node.js and the browser.

npm install xlsx

import xlsx from "xlsx";

const workbook = xlsx.readFile("data.xlsx");
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = xlsx.utils.sheet_to_json(sheet);
console.log(data);

// Writing
import xlsx from "xlsx";

const ws = xlsx.utils.aoa_to_sheet([["Name", "Age"], ["Alice", 30]]);
const wb = xlsx.utils.book_new();
xlsx.utils.book_append_sheet(wb, ws, "People");
xlsx.writeFile(wb, "output.xlsx");

Java

Apache POI is the enterprise standard for Excel on the JVM.

<!-- Maven -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.3.0</version>
</dependency>

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;

public class ExcelParser {
    public static void main(String[] args) throws Exception {
        try (Workbook wb = new XSSFWorkbook(new FileInputStream("data.xlsx"))) {
            Sheet sheet = wb.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(cell + "\t");
                }
                System.out.println();
            }
        }
    }
}

Explanation

An .xlsx file is a ZIP archive of XML documents: workbook.xml lists the sheets, sheetN.xml holds the cells, and sharedStrings.xml deduplicates text so a string stored once can be referenced a thousand times. Every library above is a friendlier API over that structure.

Two details explain most parsing surprises:

• Dates are numbers. Excel stores dates as floating-point serials (days since 1900 or 1904 depending on the workbook). Whether you get a datetime or a raw 44927.0 depends on the cell’s number format, so check the parsed type before assuming.
• Formulas vs. values. A formula cell holds both the formula string and a cached result. openpyxl with data_only=False returns =SUM(A1:A10); with data_only=True it returns the cached value — which is None if a script generated the file and Excel never opened it.

Every library also has a low-memory mode that yields rows instead of materializing the whole sheet — the next section covers it.

Working with Large Files

A 50MB workbook can consume 500MB+ of RAM loaded naively. The escape hatches per ecosystem:

Language	Normal API	Low-memory API
Python (read)	load_workbook()	load_workbook(..., read_only=True) + wb.close()
Python (write)	Workbook()	Workbook(write_only=True)
JavaScript	xlsx.readFile()	No true streaming reader — convert per-sheet with xlsx.utils.sheet_to_csv
Java	XSSFWorkbook	XSSFReader (SAX-style event API)

from openpyxl import load_workbook

# Streams rows instead of loading the whole workbook
wb = load_workbook("big.xlsx", read_only=True, data_only=True)
ws = wb["Sheet1"]
for row in ws.iter_rows(values_only=True):
    process(row)
wb.close()  # required in read_only mode: releases the file handle

When the file is just a data source nobody will open in Excel, converting it to CSV first is often the pragmatic move — you lose formatting and formulas but gain a format that streams trivially. libreoffice --headless --convert-to csv big.xlsx does it in one step. If you need to export app data to both formats, Export Data to CSV and Excel covers that flow.

Variants

Language	Library	Strengths	Limitations
Python	openpyxl	Full .xlsx read/write: styles, formulas, charts	Needs read_only mode on huge files
Python	pandas	read_excel() returns a DataFrame; engine="calamine" (pandas 2.2+) is much faster	Drops formatting
Python	xlrd	Reads legacy .xls	.xlsx support removed in xlrd 2.0
JavaScript	SheetJS xlsx	Browser + Node, exports to CSV/JSON	Cell styling requires the Pro build
Java	Apache POI	Enterprise standard, formulas and charts	Heavy memory footprint
Java	FastExcel	Streaming writer, low memory	Write-focused

Best Practices

• Validate sheet names before indexing. Users rename sheets; wb["Data"] on a renamed sheet raises KeyError. Check wb.sheetnames or access by position.
• Map columns by header, not index. Build {"Name": 0, "Age": 1} from the header row so a reordered column doesn’t silently corrupt the import.
• Handle merged cells explicitly. Libraries return the value only in the top-left cell of a merged range; the rest come back None.
• Close workbooks. Context managers in Python, try-with-resources in Java, and wb.close() in read_only mode release handles that otherwise leak in long-running import jobs.
• Prefer .xlsx over .xls. The XML format allows ~1M rows per sheet (vs 65k) and has far better library support.

Common Mistakes

• Using xlrd on .xlsx. xlrd 2.0 dropped the format entirely; for old .xls files use xlrd<2.0 or python-calamine.
• Expecting data_only=True to return values on generated files. The formula cache only exists after a real spreadsheet app saves the file — compute in Python instead.
• Materializing huge sheets. iter_rows with bounds streams; grabbing every cell object upfront defeats it.
• Hard-coding column indexes. Columns get reordered in real files all the time; map headers instead.
• Assuming cell types. cell.value can be str, float, datetime, or None depending on formatting; check cell.data_type in openpyxl when it matters.

Security Considerations

Excel files cross trust boundaries constantly (uploads, email attachments), and .xlsx has its own traps:

• Formula injection: cells starting with =, +, -, or @ execute as formulas when someone opens the file in Excel. Prefix or reject user-controlled values you write into workbooks — same class of bug as CSV injection.
• Zip bombs: .xlsx is a ZIP, so a 10MB upload can decompress into gigabytes of XML. Enforce upload size limits, and check decompressed sizes before extracting (zipfile.infolist() exposes file_size per entry).
• XXE in sheet XML: the format is XML all the way down. If you ever parse the internals yourself instead of using openpyxl/POI, use a hardened parser like defusedxml.
• Macros: .xlsm files can embed VBA. Reading them is safe — openpyxl’s keep_vba=True preserves but never runs macros — just don’t forward macro-enabled files to users without a reason.

See Also

• openpyxl documentation — the full Python API for workbooks, styles, and charts.
• SheetJS xlsx docs — the JavaScript library used above.
• Apache POI — the Java components for Excel (XSSFWorkbook, XSSFReader).
• Read and Write Excel Files in Python — the deeper pandas/openpyxl version of this task.