DEV Community

Meltem İntepeler
Meltem İntepeler

Posted on

Six SheetJS details that matter when you convert Excel to CSV

#javascript #webdev #beginners #csv

I build Filewhisk, a set of file tools that run entirely in the browser, and this week I added Excel to CSV, Excel to JSON and CSV to Excel on top of SheetJS 0.20.3. SheetJS is excellent and I'd use it again. But it is a faithful parser, not an opinionated converter. Several of its defaults are correct for a library and wrong for a person who just wants their spreadsheet in another format.

To avoid testing SheetJS against itself, I built the test workbook without it. I wrote the SpreadsheetML by hand with Python's zipfile, so I knew exactly which bytes were in every cell, and I read the output back with xml.etree. Here is what came out.

1. A formula with no cached result can come back as 0

An .xlsx cell with a formula normally stores the last calculated result next to it:

<c r="J2"><f>C2*2</f><v>6.28318</v></c>
Enter fullscreen mode Exit fullscreen mode

Files written by scripts and reporting tools often leave the result out, because they never ran a calculation engine:

<c r="J3"><f>C3*2</f></c>
Enter fullscreen mode Exit fullscreen mode

With default options SheetJS simply doesn't create that cell, and sheet_to_json gives null. That's fine. But I needed cellStyles: true, because it's the option that gives you hidden rows and columns (!rows, !cols). With it on, the same cell appears as:

{ t: 'z', f: 'C3*2', v: 0 }
Enter fullscreen mode Exit fullscreen mode

sheet_to_json and sheet_to_csv still skip it. Any code that reads cell.v directly, which you will do the moment you care about formats, gets a zero. In a totals column that looks exactly like real data. I check for it explicitly:

if (cell.t === 'z' || cell.v === undefined || cell.v === null) {
  if (cell.f) noCache.push(ref);   // report it, don't invent a value
  return null;
}
Enter fullscreen mode Exit fullscreen mode

2. .w is what Excel shows, not what the cell holds

With cellNF: true, each numeric cell carries both the stored value v and the formatted text w:

Cell v z (format) w
C2 3.14159 0.00 3.14
B5 1234567890123 General 1.23457E+12
D2 1234.5 "$"#,##0.00 $1,234.50

Using w reproduces exactly what Excel's own Save As → CSV does. Microsoft's documentation says the CSV format saves "only the text and values as they are displayed in cells of the active worksheet". So 3.14159 leaves as 3.14 and the 13-digit ID leaves in scientific notation. I write v, and I report every cell where w would have rounded.

Telling rounding apart from formatting was trickier than I expected. 2.50 vs 2.5 and $1,234.50 vs 1234.5 lose nothing; 3.14 vs 3.14159 does. I strip currency symbols, thousands separators and a trailing % from w, parse what's left, and compare numerically.

One exception: a zero-padding format such as 00000 on the stored number 721. There the displayed 00721 is the data (a postcode), so I write w.

3. String(v) adds noise Excel never shows 

String(0.1 + 0.2)   // "0.30000000000000004"
Enter fullscreen mode Exit fullscreen mode

Excel keeps 15 significant digits and displays 0.3. Writing JavaScript's full representation into a CSV puts noise into every column of calculated values. I round to Excel's precision first:

const excelNumber = (v) => String(parseFloat(v.toPrecision(15)));
Enter fullscreen mode Exit fullscreen mode

4. Dates: skip cellDates and parse the serial yourself

With cellDates: true, a date cell comes back as a JS Date set to midnight UTC. toISOString() is right, but the local getters are not everywhere. Reading the same 15 March 2024 cell with the process time zone set to America/New_York:

cell.v.toISOString()          // "2024-03-15T00:00:00.000Z"
cell.v.getDate()              // 14
cell.v.toLocaleDateString()   // "3/14/2024"
Enter fullscreen mode Exit fullscreen mode

Anyone west of UTC who formats with the usual local methods gets the day before. I keep cellDates: false, detect date formats with XLSX.SSF.is_date(cell.z), and split the serial into components with no time zone involved:

const p = XLSX.SSF.parse_date_code(cell.v, { date1904 });
const iso = `${p.y}-${pad(p.m)}-${pad(p.d)}`;
Enter fullscreen mode Exit fullscreen mode

date1904 comes from wb.Workbook.WBProps.date1904. Workbooks from old Mac versions of Excel count days from 1904. Forget it and every date is four years and one day early.

5. Text containing _x0041_ is written unescaped

This one surprised me most. OOXML uses _xHHHH_ to escape characters XML can't carry. A carriage return inside a cell is written as _x000D_. That means a literal _x0041_ in your data has to be written as _x005F_x0041_, with the underscore escaped.

SheetJS doesn't do that when writing. I wrote a cell containing a_x0041_b, and SheetJS itself read it back as:

aAb
Enter fullscreen mode Exit fullscreen mode

That's what the spec says a reader should do with an unescaped _x0041_, so other readers will do the same. It's rare in real data, but when it happens it silently changes a value. The fix before writing:

const protect = (s) => s.replace(/_x([0-9A-Fa-f]{4})_/g, '_x005F_x$1_');
Enter fullscreen mode Exit fullscreen mode

While I was there, I also switched on bookSST: true. By default SheetJS writes text cells as t="str", a type the spec defines for formula results. The shared-strings table (t="s") is how Excel itself stores text, so that's what the files use now.

6. The mini build can't read .xls

xlsx.mini.min.js is 280 KB, against 952 KB for the full build, so I wanted it for everything. It writes XLSX fine. It can't read legacy .xls or .xlsb:

parse_xlscfb is not defined
parse_sty_bin is not defined
Enter fullscreen mode Exit fullscreen mode

I load the mini build for CSV → Excel and the full build for the two reader pages. Both are lazy-loaded when the first file is dropped, so neither page pays for them on page load.

The CSV → Excel direction has its own traps

Going the other way, the enemy is Excel's type guessing when you open a CSV. 00721 becomes 721, a 16-digit ID keeps only 15 digits, and MARCH1 becomes a date. Rather than write cells one at a time, I analyse each column first. If any value in a column has a leading zero or more than 15 significant digits, the whole column is written as text. Otherwise half a postcode column becomes numbers, and sorting and lookups break. Only unambiguous ISO dates (2024-03-15) become real dates. Anything starting with =, +, - or @ is stored as a text cell, so it can't run as a formula.

How I tested it

  • Test workbooks come from a small Python script that writes SpreadsheetML by hand, so SheetJS is never both the writer and the reader.
  • The .xlsx files the tool produces are unzipped and parsed with xml.etree, with _xHHHH_ decoded as ECMA-376 specifies. I check cell types, number formats, autofilter ranges and sheet names from the XML itself.
  • CSV output goes through Python's csv module.

That's 89 assertions across the three pages. The problem in section 5 turned up because the test read the XML itself instead of asking SheetJS: SheetJS reads its own output back as aAb without complaint.

If you'd like to see it working: Excel to CSV, Excel to JSON and CSV to Excel. Everything runs in the browser, and the report under each conversion lists every cell it treated differently from Excel.

Top comments (1)

Collapse
 
launchgatecheck profile image
Launch Gate •

Building the test workbook by hand with zipfile so you're not testing SheetJS against itself is the right call. The w vs v rounding check is a good one too.

Two things on the CSV output side that trip people up after conversion:

  • BOM. If users reopen the CSV in Excel on Windows, a UTF-8 file without a BOM gets read as the local codepage, so "Muñoz" comes back as "Muñoz". Adding \uFEFF at the start fixes Excel. But some importers (older Python scripts, a few e-commerce importers) then see the BOM as part of the first header name. A toggle, or at least a note, saves support questions.
  • Delimiter. In German, French or Dutch Excel, list separator is ; and a comma CSV opens as one column. Some tools offer a "semicolon for Excel (EU)" option, or write sep=, as the first line, which Excel respects but most other parsers treat as data.

Do you write a BOM by default right now?