I birthed an Excel spreadsheet

By Levi Clancy for לוי on

▶︎ View related▼︎ Tap to hide

Mind. Blown.

So, it turns out that an Excel file is actually a Zip file containing multiple directories and files. Go ahead: use Google Drive to make a spreadsheet. Download it as an Excel file. (You can also do this directly in Excel, but you'll need to use the unzip command.) Then change the file extension of your .xlsx file to .zip and open the Zip file. You will find a merry little directory containing various subdirectories and files which altogether make an Excel file when zipped back together.

Roll up your sleeves: this is actually pretty easy, but will involve a little more work than just writing to a single file. This is for a basic spreadsheet without any formatting, for now.

You need this setup of files and directories,

Tap to open

Represented as text,

+ Workbook.xlsx (Zip file) + _rels (Directory) • .rels (Hidden file) + xl (Directory) + _rels (Directory) • workbook.xml.rels (XML file) + drawings (Directory) • drawing1.xml (XML file) + worksheets (Directory) + _rels (Directory) • sheet1.xml.rels (XML file) • sheet1.xml (XML file) • sharedStrings.xml (XML file) • styles.xml (XML file) • workbook.xml (XML file) • [Content_Types].xml (XML file)

Learn how to make the files,

[Content_Types].xml

Here is a basic file,

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"> <Default ContentType="application/xml" Extension="xml"/> <Default ContentType="application/vnd.openxmlformats-package.relationships+xml" Extension="rels"/> <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" PartName="/xl/worksheets/sheet1.xml"/> <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" PartName="/xl/sharedStrings.xml"/> <Override ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" PartName="/xl/drawings/drawing1.xml"/> <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" PartName="/xl/styles.xml"/> <Override ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" PartName="/xl/workbook.xml"/> </Types>

If you have multiple sheets then add a sheet2.xml and a drawing2.xml, then a sheet3.xml and a drawing3.xml, and so forth.

workbook.xml.rels

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/> <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/> <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/> </Relationships>

If you have multiple sheets then add another <Relationship> tag specifying sheet2.xml, sheet3.xml, and so forth while incrementing rId higher as well,

<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/> <Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/> <Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet3.xml"/> ... and so forth

.rels

Save this as .rels in the _rels folder. It is a hidden file so I forgot to check for this initially, but it is required,

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/> </Relationships>

sharedStrings.xml

This file contains all the unique values that a cell can possible have. Then instead of specifying strings in the actual spreadsheet, you can just reference this. This makes spreadsheets cleaner and potentially saves space, if there are lots of duplicate cell values.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <si><t>Header1</t></si> <si><t>Header2</t></si> <si><t>Header3</t></si> <si><t>Header4</t></si> <si><t>Row1-1</t></si> <si><t>Row1-2</t></si> <si><t>Row1-3</t></si> <si><t>Row1-4</t></si> <si><t>Row2-1</t></si> <si><t>Row2-2</t></si> <si><t>Row2-3</t></si> <si><t>Row2-4</t></si> <si><t>HeaderA</t></si> <si><t>HeaderB</t></si> <si><t>HeaderC</t></si> <si><t>HeaderD</t></si> </sst>

Then if you want to use Header1 in a cell, then just specify that cell as <v>0</v>, as that is the first string in the sharedStrings.xml document. Header2 would be <v>1</v>, and so forth. This is also a really nifty way to quickly propagate out changes across all worksheets in a document.

styles.xml

You can specify various styles. Here, we provide only plain text formatting.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"> <fonts count="2"> <font><sz val="10.0"/><color rgb="FF000000"/><name val="Arial"/></font> <font/> </fonts> <fills count="2"> <fill><patternFill patternType="none"/></fill> <fill><patternFill patternType="lightGray"/></fill> </fills> <borders count="1"> <border/> </borders> <cellStyleXfs count="1"> <xf borderId="0" fillId="0" fontId="0" numFmtId="0" applyAlignment="1" applyFont="1"/></cellStyleXfs> <cellXfs count="3"> <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0" applyAlignment="1" applyFont="1"> <alignment readingOrder="0" shrinkToFit="0" vertical="bottom" wrapText="0"/></xf> <xf borderId="0" fillId="0" fontId="1" numFmtId="0" xfId="0" applyFont="1"/> <xf borderId="0" fillId="0" fontId="1" numFmtId="0" xfId="0" applyAlignment="1" applyFont="1"> <alignment readingOrder="0"/></xf> </cellXfs> <cellStyles count="1"> <cellStyle xfId="0" name="Normal" builtinId="0"/> </cellStyles> <dxfs count="0"/> </styleSheet>

workbook.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"> <workbookPr/> <sheets> <sheet state="visible" name="Sheet1" sheetId="1" r:id="rId3"/> </sheets> <definedNames/> <calcPr/> </workbook>

Like with workbook.xml.rels, you can easily add more sheets by incrementing the sheetId and r:id. Also, you can specify custom name parameters as the worksheet names,

<sheet state="visible" name="Main sheet" sheetId="1" r:id="rId3"/> <sheet state="visible" name="Supplementary data" sheetId="2" r:id="rId4"/> <sheet state="visible" name="Appendix" sheetId="3" r:id="rId5"/>

sheet1.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mx="http://schemas.microsoft.com/office/mac/excel/2008/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:mv="urn:schemas-microsoft-com:mac:vml" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"> <sheetPr><outlinePr summaryBelow="0" summaryRight="0"/></sheetPr> <sheetViews><sheetView workbookViewId="0"/></sheetViews> <sheetFormatPr customHeight="1" defaultColWidth="14.43" defaultRowHeight="15.75"/> <sheetData> <row r="1"> <c r="A1" s="2" t="s"><v>0</v></c> <c r="B1" s="2" t="s"><v>1</v></c> <c r="C1" s="2" t="s"><v>2</v></c> <c r="D1" s="2" t="s"><v>3</v></c> </row> <row r="2"> <c r="A2" s="2" t="s"><v>4</v></c> <c r="B2" s="2" t="s"><v>5</v></c> <c r="C2" s="2" t="s"><v>6</v></c> <c r="D2" s="2" t="s"><v>7</v></c> </row> <row r="3"> <c r="A3" s="2" t="s"><v>8</v></c> <c r="B3" s="2" t="s"><v>9</v></c> <c r="C3" s="2" t="s"><v>10</v></c> <c r="D3" s="2" t="s"><v>11</v></c> </row> <row r="4"> <c r="A4" s="1"><v>1.0</v></c> <c r="B4" s="1"><v>2.0</v></c> <c r="C4" s="1"><v>3.0</v></c> <c r="D4" s="1"><v>4.0</v></c> </row> </sheetData> <drawing r:id="rId1"/> </worksheet>

This would output this worksheet,

Header1Header2Header3Header4

Row1-1

Row1-2

Row1-3

Row1-4

Row2-1

Row2-2

Row2-3

Row2-4

1

2

3

4

Note that specifying an integer requires providing at least one decimal place (e.g. 1.0) while just providing a whole number (e.g. 1) references the sharedStrings.xml file instead.

sheet1.xml.rels

Every worksheet has one of these files, so in addition to sheet1.xml.rels you may also have sheet2.xml.rels, sheet3.xml.rels, and so forth. They are all the same except for the <Relationship> tag.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing1.xml"/> </Relationships>

Then repeat for the rest of the worksheets as follows, for example for sheet2.xml you would make sheet2.xml.rels as follows,

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing2.xml"/> </Relationships>

drawing1.xml

All the drawing files will be the same if your Excel file is text-only,

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:cx="http://schemas.microsoft.com/office/drawing/2014/chartex" xmlns:cx1="http://schemas.microsoft.com/office/drawing/2015/9/8/chartex" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:dgm="http://schemas.openxmlformats.org/drawingml/2006/diagram"/>

Then repeat for the rest of the worksheets as drawing2.xml, drawing3.xml, and so forth.

Studies

https://docs.microsoft.com/en-us/office/open-xml/working-with-the-shared-string-table

levi.life|1535283260_M2S5ZGYLN6
close