FlowingDev

Excel Files: The Universal Data Suitcase (And How to Unpack It)

Learn how Excel files (.xlsx, .xls) structure data in spreadsheets and why converting them to formats like CSV, JSON, or HTML is a key developer skill.

Try the tool: Excel Viewer & Converter

In one sentence

Excel files are the de facto standard for business data, packaging tabular information, formulas, and formatting into a single file that often needs to be programmatically unpacked into simpler, web-friendly formats like CSV or JSON.

The problem it solves

In the beginning, there was the ledger book. Then came the digital spreadsheet. VisiCalc on the Apple II was the first "killer app," turning a personal computer from a hobbyist's toy into a serious business tool. It was followed by Lotus 1-2-3, which dominated the DOS era. Then, Microsoft Excel arrived and, powered by the rise of Windows, became the undisputed champion of the spreadsheet world.

For decades, businesspeople, analysts, scientists, and pretty much everyone else have used Excel to organize, calculate, and visualize data. It's an incredibly powerful and intuitive user interface for tabular information. You can add colors, create charts, write complex formulas, and drag-and-drop your way to a beautiful report.

And therein lies the problem for developers.

An Excel file isn't just data; it's a whole application state. It contains formatting, charts, macros, pivot tables, and multiple "sheets" of data. The native file formats—the classic binary .xls and the modern .xlsx—are complex and designed for the Excel application, not for a simple script or a web server.

If a developer needs the raw data from a spreadsheet—say, to populate a database, display on a website, or feed into another process—they're faced with a challenge. They don't care about the pretty blue header or the pie chart. They just want the numbers and text. Trying to parse a proprietary binary file is a recipe for a headache.

This is the gap that Excel conversion fills. It acts as a universal translator, cracking open the complex Excel suitcase and neatly laying out its contents in simple, universally understood formats that developers love. It separates the data from the presentation, which is a core principle of good software design.

How it works under the hood

The magic of viewing and converting an Excel file boils down to understanding its internal structure. The classic .xls format is a gnarly, proprietary binary beast (BIFF), but thankfully the modern .xlsx format is much more approachable.

What's inside an .xlsx file?

Here's the big secret: an .xlsx file is actually a ZIP archive in disguise. Seriously. If you take any .xlsx file, change its extension to .zip, and unzip it, you'll find a collection of folders and XML files.

A typical structure looks something like this:

my-spreadsheet.xlsx/
├── _rels/
├── docProps/
│   ├── app.xml
│   └── core.xml
└── xl/
    ├── _rels/
    ├── theme/
    ├── worksheets/
    │   ├── sheet1.xml
    │   └── sheet2.xml
    ├── styles.xml
    ├── workbook.xml
    └── sharedStrings.xml

The gold is in the xl/ directory.

  • workbook.xml: Defines the overall workbook, including the names of the sheets (e.g., "Q4 Sales," "Customer List").
  • worksheets/sheetN.xml: Contains the data for each individual sheet. This is where you find the rows and cells.
  • sharedStrings.xml: Here's a clever optimization. If you type the same piece of text (like "In Stock") 1,000 times in your sheet, Excel doesn't store 1,000 copies. It stores it once in sharedStrings.xml and each cell simply references it by an index.
  • styles.xml: This handles all the formatting—fonts, colors, borders, and number formats (like currency or dates).

A cell in sheet1.xml might look like this: <c r="A1" t="s"><v>0</v></c>. This doesn't seem to contain the cell's value! Let's decode it:

  • c: It's a cell.
  • r="A1": Its location is cell A1.
  • t="s": The t stands for type, and s means "shared string." This is a clue!
  • <v>0</v>: The v (value) is 0. This is the index into the sharedStrings.xml file.

To find the actual content of cell A1, a converter needs to open sharedStrings.xml and find the 0th string entry. This multi-file lookup is what makes parsing .xlsx non-trivial.

Converting to CSV (Comma-Separated Values)

CSV is the simplest, most universal format for tabular data. Converting a sheet to CSV is a logical process:

  1. Pick a worksheet (e.g., sheet1.xml).
  2. Iterate through each <row> element.
  3. For each row, iterate through each <c> (cell) element.
  4. For each cell, extract the value. If it's a shared string (t="s"), look it up in sharedStrings.xml. If it's a number, grab it directly.
  5. Join the cell values for the row with a comma.
  6. Append a newline character at the end of each row.

One tricky part is handling commas or quotes within the data itself. The CSV standard (RFC 4180) says that if a value contains a comma, the whole value should be enclosed in double quotes. E.g., "Doe, John".

Converting to JSON (JavaScript Object Notation)

JSON offers more structural flexibility than CSV, so there's no single "correct" way to convert a spreadsheet. Two common patterns emerge:

  1. Array of Arrays: This mirrors the structure of a CSV. Each row in the sheet becomes an inner array, and the whole sheet is one big outer array. Simple and compact.

    [
  ["Name", "SKU", "In Stock"],
  ["Flux Capacitor", "FC-1985", 88],
  ["Tardis Key", "TK-1963", 1]
]

  2. Array of Objects: This is often more useful for developers. The first row of the sheet is treated as headers (keys), and each subsequent row becomes a JSON object. This adds semantic meaning to the data.

    [
  {
    "Name": "Flux Capacitor",
    "SKU": "FC-1985",
    "In Stock": 88
  },
  {
    "Name": "Tardis Key",
    "SKU": "TK-1963",
    "In Stock": 1
  }
]

An Excel converter tool must choose which format to produce, or offer the user a choice.

Converting to HTML/Markdown

Since a spreadsheet is fundamentally a table, converting it to HTML or Markdown is a natural fit. The process involves mapping the spreadsheet grid to the respective table syntax.

For HTML, this means:

  • The sheet becomes a <table>.
  • The first row can become a <thead> with <th> (table header) cells.
  • Subsequent rows become <tr> (table row) elements inside a <tbody>.
  • Each cell becomes a <td> (table data) element.

For Markdown, the syntax is more concise but achieves the same result, using pipes | to separate cells and hyphens - to create the header divider.

Name SKU In Stock
Flux Capacitor FC-1985 88
Tardis Key TK-1963 1

Real-world stories

The Quarterly Report Panic

The marketing team just dropped their quarterly campaign results into a shared drive. It's a gorgeous 12-sheet Excel file, full of conditional formatting, pivot tables, and charts that summarize clicks, conversions, and ad spend. A developer, Jen, is tasked with getting the raw data from the "Paid Social" sheet into the company's internal analytics dashboard. Manually copy-pasting 5,000 rows is a non-starter—it's slow and a single slip-up could corrupt the data. Instead, Jen uses a converter to pull just the "Paid Social" sheet and transform it into JSON. She writes a 10-line script to loop through the JSON array and push each object to the dashboard's API. The whole process takes five minutes.

Lesson: Conversion automates the bridge between human-friendly business reports and machine-readable data, saving time and preventing errors.

The Legacy System Migration

A small manufacturing company is finally upgrading its 15-year-old inventory management system. The problem? The only way to get data out of the old system is via a "Print to Excel" function that generates a .xls file. The new cloud-based ERP system, however, only accepts bulk data imports via CSV. The formats are incompatible. The project manager, David, feared they'd have to pay a pricey consultant. But an engineer found a tool that could read the old binary .xls format and convert it to modern, clean CSV. They processed years of inventory data in an afternoon, mapping old columns to new ones and successfully migrating the system over a weekend.

Lesson: Excel conversion tools are essential middleware for interoperability, especially when bridging the gap between legacy and modern systems.

The Static Site Content Engine

A local non-profit wants to display a schedule of upcoming workshops on their website. Their web developer, Maria, built them a simple site using a static site generator. The non-profit's director, who is not technical, needs to update the schedule frequently. Instead of teaching him a complex Content Management System (CMS), Maria sets up a shared Excel file with columns for "Date," "Workshop Title," and "Instructor." As part of her website's build process, a script automatically pulls the latest version of this Excel file, converts it to JSON, and uses that data to dynamically generate the events page. The director just updates a spreadsheet, and the website updates automatically a minute later.

Lesson: For simple, tabular content, an Excel file can serve as a surprisingly effective and user-friendly "headless CMS."

Common mistakes and traps

  • Ignoring data types. An Excel cell knows if it's a number, a date, or text. A naive conversion can flatten everything to strings. 123 becomes "123", and the date 10/20/2025 might become the string "10/20/2025" or, worse, its internal serial number representation (45950). This can break calculations and sorting.
  • Forgetting about multiple sheets. Many users just process the first sheet in a workbook. Always check if the .xlsx file contains other sheets with crucial data. A file named sales.xlsx might have sheets for "2022," "2023," and "2024."
  • Mishandling merged cells. In Excel, you can merge cells B2 and C2 to make one big cell. A simple converter will see data in B2 and nothing in C2, creating a null value in your output and misaligning your data. Good converters need to be aware of the merged cell metadata.
  • Losing the logic of formulas. A cell might display $150, but its actual content is a formula like =SUM(A2:A10) * 1.05. When you convert the sheet, you get the calculated value (150), not the formula. The underlying logic is lost. This is usually desired, but it's a "trap" if you needed to understand the calculation itself.
  • Trusting the header row implicitly. When converting to an array of JSON objects, the first row is assumed to be the keys. If that row is empty, has duplicate names ("Notes", "Notes"), or contains characters that are invalid in certain contexts, your conversion will fail or produce weird results.

Why it belongs on your radar

Developers live in a world of APIs, databases, and structured text formats like JSON, XML, and YAML. The rest of the world often lives in Microsoft Excel. You will inevitably find yourself at the border between these two worlds.

You should think about Excel conversion whenever:

  • You need to programmatically consume data provided by a non-technical user.
  • You need to provide a data export feature for business users who want to "play with the numbers in Excel."
  • You're migrating data from an old system that can only export to .xls or .xlsx.
  • You're building an automation pipeline that needs to extract data from a report.
  • You want to use a spreadsheet as a simple data source for a website or application.

Being able to fluently translate data from the Excel ecosystem to your own is not just a handy trick; it's a fundamental skill for building software that integrates smoothly into real-world business workflows.

Go deeper

Theory done. Time to get your hands dirty — 100% in your browser.

Try the tool: Excel Viewer & Converter