Reading and Writing Excel From JavaScript Java and C Sharp

Sooner or later every application is asked to read an Excel file or to produce one. Sales sends a price list, finance wants the ledger as a workbook, a partner uploads a sheet of customers. The question "which library do I use" has an answer in every language, and the answers differ less than the documentation suggests. Underneath, they all unzip an archive of XML, map cells to the language's types and stumble over the same few problems.

This is a reference across eight languages. It names the mainstream library for each, summarizes the shared concepts and the shared traps, and points to the spokes with full examples for JavaScript, Java, C Sharp, PHP, R, Go, Ruby and Rust. Python has its own set of posts, on openpyxl and pandas, which this guide links to where the comparison helps.

The libraries at a glance

The detailed posts: reading Excel files in Node with exceljs, reading Excel in the browser with SheetJS, reading and writing Excel in Java with Apache POI, using EPPlus or ClosedXML for Excel in C Sharp, reading Excel files in PHP with PhpSpreadsheet, reading Excel files in R with readxl, writing Excel files in Go with Excelize, reading Excel in Ruby with Roo and creating Excel files in Rust with rust_xlsxwriter.

What they all do

Every one of these libraries performs the same job. It opens the xlsx archive (a zip), reads the workbook part to learn the sheet names, reads the shared strings table that holds the distinct text values, then parses a worksheet part cell by cell. Each cell carries a coordinate, a type and a value. Text cells hold an index into the shared strings. Numbers are stored as decimal text. A date is a number whose style says it is a date. A formula cell carries both the formula text and the last calculated result.

Knowing the file's anatomy explains most behavior. The libraries differ in how they present it: some give you an object per cell, some give arrays of values, some stream events. The structure underneath does not change (how an xlsx file is a zip of XML explains it with examples).

Writing is the reverse: the library assembles cells, a styles table, a shared strings table and the workbook part into the archive. It does not run Excel and does not calculate formulas. The consequence of that, repeated in every ecosystem, is that a workbook you write contains formulas with no cached results until a real spreadsheet application opens and saves it.

Types: the first trap

A cell's type decides what you get back, and the four types cause most bugs.

Numbers are floating point. Excel stores numbers as IEEE doubles. A whole number is 100.0 in many languages unless the library converts. Integers above 2^53 lose precision, and Excel itself keeps only 15 significant digits. A 16-digit identifier typed as a number was rounded before your code saw it (Excel number precision and the 15 digit limit).

Identifiers are text that looks like numbers. Phone numbers, postal codes, account numbers and product codes must be strings. If the cell holds text (the cell was formatted as text before entry), the library returns the string with its zeros. If it holds a number, the zeros are gone and cannot be recovered. When writing, state the type: use the string setter, not the generic one that infers. When reading, prefer the text-reading options for identifier columns (keeping leading zeros in Excel).

Dates are numbers with a format. A library either recognizes date formats and returns a date object, or returns the serial number. The conversion has traps in every language: time zones (a date at midnight UTC displayed in a zone west of UTC shows the previous day), the 1904 date system of old Mac files, and the fact that a date typed as text is still text. Read dates in a way that you have tested on a date near midnight (converting Excel serial dates to real dates).

Formulas have two faces. A formula cell has text and a cached result. Depending on the library and the call, you get one or the other, sometimes an object holding both. Cached results exist only if the program that saved the file calculated them. A file saved by Excel has them. A file written by a library does not.

Blank cells, error cells (#N/A) and booleans complete the set. Decide how each is represented in your program, and test them.

Dates across the languages

Because dates cause the most data errors, a short cross-language note. The conversion from a serial n is 1899-12-30 + n days for the 1900 date system (the odd origin accounts for Excel's historical leap year bug, Excel's 1900 leap year bug). Fractions are time of day. In JavaScript, exceljs and SheetJS can return Date objects, interpreted in UTC. In Java, POI offers DateUtil.getJavaDate and getLocalDateTimeCellValue. In C Sharp, DateTime.FromOADate(n) converts a serial. PHP has Date::excelToDateTimeObject. R's readxl returns POSIXct for date cells and you convert with as.Date. Go has excelize.ExcelDateToTime. Ruby's Roo returns Date objects. Rust's calamine returns a date-time type or a serial.

Whatever the language, store dates in your database as dates, in ISO form in files, and be explicit about the time zone. Never format a date with the machine's locale when writing a data file.

Memory: the second trap

Most of these libraries build an in-memory model of the whole workbook. An xlsx expands when parsed: a few hundred thousand rows with a dozen columns can need gigabytes. The symptoms are an out-of-memory error, a killed container or a service that slows when someone uploads a big file.

Each ecosystem has the same remedy, a streaming mode:

• Node: exceljs WorkbookReader and WorkbookWriter.

• Java: SXSSFWorkbook for writing, XSSFReader with a SAX handler for reading.

• C Sharp: the Open XML SDK's OpenXmlReader and OpenXmlWriter.

• PHP: OpenSpout, or PhpSpreadsheet with read filters.

• Go: Excelize Rows and NewStreamWriter.

• Ruby: Roo's each_row_streaming.

• Rust: calamine, which is efficient, and add_worksheet_with_constant_memory for writing.

• R: convert to CSV or Parquet first and read that.

Streaming gives up random access and often styling and merged-cell information. The pattern in code is always the same: read a row, handle it, drop it, and write results in batches. If the same big workbook is read repeatedly, convert it once to Parquet or CSV and stop parsing xlsx (reading a 500 MB Excel file without running out of memory and converting a huge Excel file to CSV or Parquet).

A worksheet holds at most 1,048,576 rows and 16,384 columns, and a cell at most 32,767 characters. Writers differ in whether they enforce the limits or let you write a file Excel then repairs. Check string lengths and row counts before writing.

Merged cells, hidden rows and layout

Report-style workbooks contain structure that is meaningful to people and awkward for programs. Merged cells hold a value only in the top left cell in the file format, though some libraries copy it to the others. Hidden rows and columns are still in the file. Header rows can span several lines. Subtotal rows are mixed in with the data. Trailing rows may carry formatting only and appear as empty rows after the data.

A reading program should find the table it wants by name or by a marker, not by position; skip rows that are entirely empty; fill merged values down where the layout means "same as above"; and refuse a file whose header does not match what it expects (cleaning Excel data before analysis). Do not assume the first sheet is the data sheet.

Untrusted files

A workbook uploaded by a user, or received from a partner, is untrusted input, and parsing it happens in your process. Treat it like any other upload.

• Size limits at the web server and in code, before parsing.

• A zip check. An xlsx is a zip, and a small file can expand to gigabytes. Libraries have some protection (Apache POI has configurable zip bomb limits; Excelize has unzip size options); use them and do not disable them to make a file fit.

• Time and memory limits. Parse in a worker with a timeout and a memory cap, not inside the web request.

• Structure validation. Check the sheet name, header and types, and report errors by row.

• No execution. Never evaluate cell text as code or as a query. Do not enable macros. Do not follow hyperlinks or external references.

• No HTML injection. Escape cell text before putting it on a page.

• Current libraries. Parser vulnerabilities are fixed in releases; keep dependencies updated and be careful with unmaintained packages (the stale xlsx package on npm is an example).

On the write side, the equivalent hazard is formula injection: a user-supplied string that begins with =, +, - or @ can be interpreted by Excel as a formula when someone opens the file, with consequences from data exfiltration to command execution on older configurations. Write user text with an explicit string type, or prefix it, and never let a user-controlled value reach a formula-writing call (escaping formula characters in a CSV export explains the same attack through CSV).

Writing: habits that apply everywhere

Whatever the library, a good write path has the same properties.

State types. Use the string setter for identifiers, the number setter for numbers and the date setter with a number format for dates. Inference is convenient and wrong often enough to hurt.

Create styles once. Styles, formats and fonts are objects in a shared table. Building one per cell makes files huge and slow, and can hit the limit of cell formats. Define the header style, the date style and the money style at the top and reuse them.

Write headers, freeze the header row, set an autofilter and set column widths. These are the details that make a generated file feel finished, and each library has a call for them.

Do not write formulas unless the recipient needs them. If you do, remember that there are no cached results. A reader that does not calculate (a preview pane, a web viewer, a program that reads values) sees blanks. Write values for files that machines or viewers read.

Name the sheets validly: at most 31 characters, none of \ / ? * [ ] :, unique ignoring case.

Test by opening the result in Excel. Libraries are more forgiving than Excel, so a clean reload in the same library is weaker evidence than a manual open.

Choosing a library

A decision guide that follows the situation, not the language.

• Small files, simple needs: the mainstream library for your language, with defaults.

• Large uploads or exports: the streaming mode from the start, designed in, not retrofitted after the first outage.

• Browser-side parsing: SheetJS, from the vendor's current distribution, in a Web Worker, with limits.

• Complex formatted reports: a library with a rich style API (Apache POI, EPPlus, exceljs, Excelize, Caxlsx, rust_xlsxwriter), or a template that you fill.

• Reading messy report layouts in R: tidyxl with unpivotr.

• **Old .xls files:** Apache POI in Java, NPOI in .NET, xlrd in Python, or convert once with LibreOffice (batch converting XLS files to XLSX).

• Licensing sensitivity: read the license before adopting. EPPlus from version 5 requires a commercial license for commercial use; ClosedXML, POI, exceljs, PhpSpreadsheet and Excelize are under permissive or open-source licenses (confirm for the version you use).

Testing a workbook feature

Treat the Excel code like any other code. Keep a small folder of fixture workbooks, saved by Excel, by LibreOffice and by a script, containing the cases that have broken you: a date at midnight, a text-formatted number with leading zeros, a merged range, a formula with a cached result and one without, an empty row in the middle, a hidden sheet, a very long string, a duplicate header, a title block. Run the reader on all of them in the test suite and assert the exact output. When production brings a new surprise, add the file to the folder before fixing the bug.

For writers, reopen the generated file with a reader and assert on structure and values, and run a basic validation. A control total compared with the source data catches the mistakes that formatting tests do not (testing that a generated Excel report is correct describes the layered approach in Python; it carries over to any language).

Alternatives to using a spreadsheet library

Sometimes the best answer is not to use one.

Exchange CSV or Parquet. If the consumer is another program, a plain file avoids every trap above. Keep the workbook for people (Excel CSV and sharing data with other tools).

Convert once, outside your service. A script or a batch job converts incoming workbooks to CSV or Parquet, and the service reads those.

Let a database do the work. Load the sheet into a staging table and use SQL for validation and transformation (staging Excel data in a table).

Use a template. For a complex layout, ship a workbook that a designer built, and have the code fill in the data cells. It is far less code than reproducing the formatting.

Offer a form. If people upload a sheet to enter data, a web form validates at entry and avoids the workbook entirely.

One task, eight languages

To see how little the languages differ, take one task and describe its shape in each: read a sheet called Orders, skip the header, read an identifier as text, a date and an amount, reject rows that fail validation and report their line numbers.

In Node with exceljs you load the workbook, get the worksheet by name, loop with eachRow from row two, take the identifier from the cell and convert it to a string, check that the date cell is a Date, check that the amount is a number, and push errors with the row number. In Java with POI you open the file with WorkbookFactory, get the sheet, loop over rows after the first, use DataFormatter for the identifier, DateUtil.isCellDateFormatted for the date and getNumericCellValue for the amount, and collect errors. In C Sharp with ClosedXML you open the workbook, take the worksheet's used range, skip the header row, call GetString on the identifier, check the data type of the date and amount cells and accumulate errors.

In PHP with PhpSpreadsheet you create a reader with data-only mode, load the one sheet, loop over rows and convert the date serial with the Date helper. In R with readxl you call read_excel with col_types set, then check the date and amount columns for NA. In Go with Excelize you iterate with Rows, read the columns as raw strings and parse the date serial and the amount with error returns. In Ruby with Roo you stream rows with each_row_streaming and check cell values. In Rust with calamine you match each cell against the Data enum, which forces you to handle every variant.

The parts that never change are the ones that matter: the explicit statement of what each column is, the validation that does not stop at the first error, the line numbers in the messages and the count of rows read compared with the count in the sheet. A team that writes this in one language can write it in another in an afternoon, because the design is the same.

Encodings, special characters and sheet names

Workbooks store text as UTF-8 in the XML, so most libraries return correct Unicode strings, and the problems appear at the edges. Characters outside the Basic Multilingual Plane, such as many emoji, count as two units in Excel's length limits. Control characters below 32 are not allowed in the XML, and a writer that does not strip them produces a file Excel must repair. Line breaks inside a cell are stored as \n, and display only if the cell has wrap enabled.

Sheet names have their own rules: at most 31 characters, no \ / ? * [ ] :, no leading or trailing apostrophe and unique ignoring case. If the name comes from data, such as a customer or a region, sanitize it and keep a mapping. Reading by name should handle the case where the sheet is missing with a clear error. File names for downloads should be ASCII-safe or properly encoded in the response header.

Right-to-left text, combining characters and unusual scripts work in the file format; display depends on Excel and its fonts. Test with representative names from your real customers, including accents, apostrophes and long strings.

Observability and maintenance

Spreadsheet code is easy to forget until it fails. Log, at the import boundary, the file name, size, number of sheets, rows read, rows rejected and time taken. Keep the rejected rows with reasons for a while, since support will be asked about them. Record the library versions in the build, and watch the changelog of the library for security fixes and behavior changes. Several of these libraries have changed licenses, method names or default limits between major versions, and a silent upgrade is how a working import stops working.

Write down, next to the code, what the importer expects: sheet name, header, columns, types and limits. When a sender changes the file, the diff between the document and the failure message tells you where. Revisit the choice of library when the size of the files or the licensing changes; moving from one to another is easier when the reading and writing are behind a small interface of your own.

Performance expectations, stated carefully

People ask which library is fastest, and the honest answer is that it depends on the file and the machine, and that most of the time it does not matter. For a few thousand rows, any of them finishes in a blink. The differences appear with hundreds of thousands of rows, where pure-interpreted libraries that build an object per cell are slow and memory hungry, and libraries written in compiled languages, or in a streaming style, are faster and leaner. If you need a number, measure on your files with the method in benchmarking Excel readers in Python with a fair test: fixed files, fresh processes, several repetitions, peak memory from the operating system and a check that every reader returned the same data.

Where the time goes is also worth knowing. Unzipping and parsing XML dominate reading. Building styles and shared strings dominates writing. Formulas cost nothing to write and everything to calculate, which is why generated files should avoid thousands of them. When the profile shows that the workbook is the bottleneck of a pipeline, convert it once to a faster format and move on.

A word on documentation for the next developer

The person who inherits spreadsheet code is usually in a hurry and has a broken import in front of them. Make their job short. Put a comment at the top of each importer saying which sender and which sheet it handles, the columns it expects, the date system and time zone it assumes, and the library version it was written against. Keep one sample file per importer in the repository, with personal data removed, so that they can reproduce the parse in a minute.

List the decisions that are not obvious from the code: why identifiers are read as text, why a particular sheet is skipped, why blank rows are dropped only at the end, why amounts are converted to decimals at the boundary. Each of those decisions was a bug once, and the explanation is what stops someone from reverting it by accident. A short README beside the code, and a link to the post-mortem of the last incident, is worth more than clever abstractions.

When a spreadsheet library is the wrong tool

The data is huge. A workbook is not a store for millions of rows.

You need calculation identical to Excel. No library matches it. Calculate in your code, or use a spreadsheet engine.

The recipients are machines. Send CSV, JSON or Parquet.

The workbook is a presentation. If humans must see charts, conditional formats and a precise layout, produce it from a template, or from a reporting tool.

You cannot control the input. If every sender uses a different layout, per-sender adapters or a conversion service will be easier to maintain than one general reader.

Where to go next

For a language you use, open its spoke from the list near the top. For the memory problem, reading a 500 MB Excel file without running out of memory. For the Python side of the same topics, reading and writing Excel files with openpyxl and working with Excel in pandas. For conversion to other formats, the next guide in this series is about turning workbooks into CSV, JSON, XML and YAML.

The references are Microsoft's page on Excel specifications and limits, the open standard ECMA-376 for Office Open XML and the OWASP page on CSV injection, which covers the formula risk. To convert a small result to JSON, use the CSV to JSON converter.