Show the Working

OTS to CSV

Your spreadsheet never leaves your browser, and nothing is downloaded to make this work. An OTS is a ZIP of XML and your browser can already unzip; the rest is reading.

…or drop one anywhere on this page.

The data on its own, for anything that reads a spreadsheet.

The most-wanted direction, and the one where the repeat counts matter most: a column landing one place to the left is a column of numbers against the wrong names, and nothing about the file looks wrong afterwards. You choose the separator, because a CSV opened in much of Europe wants semicolons.

The one thing OpenDocument does that nothing else does

An OTS does not store a thousand empty cells. It stores one cell and a count: table:number-columns-repeated. That is a sensible way to keep a file small, and it means a row that looks like three cells may be three hundred columns wide.

It gives two opposite ways to get a spreadsheet wrong. A cell repeated five times in the middle of a row means everything after it sits five columns further along — so a converter that ignores the attribute puts every later column in the wrong place. Silently. In a file that opens perfectly in LibreOffice. A column of numbers ends up against the wrong names and nothing looks amiss.

And LibreOffice pads every row out to the full width of the sheet, so a three-column table ends with a repeat in the thousands. Expand that literally and four values become a sixteen-thousand-column table. Both are handled here, and both are tested against odfpy — with the naive answer worked out alongside and checked to be different, because a test that would also pass with the broken version is not a test.

A formatted number is not its own text

Every cell carries two things: what it is and what it shows. A cell holding 1200 formatted as currency stores office:value="1200" alongside a display string of “1,200.00” or “£1,200”. Read the display and every formatted number in your file becomes a string with a comma in it, which sorts wrongly and adds up to nothing. The value is what is read; for dates, where the displayed form is usually the one you typed, that is kept instead.

What does not come across

Formulas. A spreadsheet stores the last answer beside each formula, and the answer is what is read — which is what you were looking at, and all a table can hold. Colours, column widths, merged cells, conditional formatting, charts and pivot tables do not come across either. A table is the values and their shape.

A template is the spreadsheet, with a different label

An .ots and an .ods are the same file. Same ZIP, same content.xml, same table:table elements, same repeat counts. One string differs, in the mimetype entry at the front of the package: …opendocument.spreadsheet-template where a plain sheet says …opendocument.spreadsheet. That is the whole of it.

Nothing here was written for the template. The reader already opened one, because the check that refuses a text document asks whether the mimetype contains the word “spreadsheet” rather than whether it matches a name exactly — and spreadsheet-template does. This page exists because the file on your disk says .ots, and you should not have to know that it makes no difference.

It does not open .sxc, the OpenOffice 1.x format from before OpenDocument, and it says so rather than failing quietly. If you hand it an .odt or an .odp it will tell you which it is and point at the right page.

Where to go next