Putting plain data into excel sheet

Now we will finally get to the point, where we generate some real excel badass. The important file is xl/worksheets/sheet1.xml file. Open it within your favourite XML editor, search for the text you written into the sheet and... Notice that there's none.

Intermezzo

Excel uses shared strings concept for all strings within the entire file. While that makes sense from automated standpoint, all we want to do is to generate the worksheet & don't get insane at the same time.

We don't have to use the sharedStrings, and the XSL Excel engine does not support it. We generate all strings directly within the worksheet instead, as it makes navigation & maintenance a lot easier.

For the transformation between shared-strings worksheet and inline worksheet, we have developed a tiny script in php. So, let's convert the worksheet into human-readable format:

1. Copy xl/worksheets/sheet1.xml into utils direcotry, and name it as sheet.xml
2. Copy xl/sharedStrings.xml into utils directory
3. $ php sharedStringsConvertor.php

This will produce parsedSheet.xml, which we are going to use as a blueprint for your sheet. As first step should be only producing valid excel worksheet from the system, we will copy/paste it's contents into template.xsl file, into the sheetContents variable:

<?xml version='1.0'?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:include href="2012.engine.xsl" />

    <xsl:template match="/">
        <xsl:call-template name="generate_excel">
            <xsl:with-param name="sheetContents">
                <!-- contents of the parsedSheet.xml -->
            </xsl:with-param>
        </xsl:call-template>
    </xsl:template>
</xsl:stylesheet>

There are two things to watch out at this point - for so-called rIds at the very end of the worksheet:

<hyperlinks>
    <hyperlink ref="H8" r:id="rId1"/>
    <hyperlink ref="H5:L5" r:id="rId2" display="@foglcz"/>
    <hyperlink ref="H6:L6" r:id="rId3" display="in/foglcz"/>
</hyperlinks>
<printOptions horizontalCentered="1" verticalCentered="1"/>
<pageMargins left="0.70866141732283472" right="0.70866141732283472" top="0.74803149606299213" bottom="0.74803149606299213" header="0.31496062992125984" footer="0.31496062992125984"/>
<pageSetup paperSize="9" orientation="landscape" r:id="rId4"/>
<drawing r:id="rId5"/>

First, the hyperlinks are not yet supported, so get rid of them. (there's an issue for that.) Second:

• the drawing MUST have rId1
• the pageSetup MUST have rId2

So, we will rewrite the worksheet into following snippet:

<pageSetup paperSize="9" orientation="landscape" r:id="rId2"/>
<drawing r:id="rId1"/>

Moving forward

Recompile! Recopy binary files! Repackage into zip! Rename the zip! Done!

The goal is to generate .xlsx file, which Excel finds comfortable - ie. dies not scream about invalid formats. That's entirely possible, it's just tidy work in the beggining.

Navigation

1. Preparing excel files
2. Preparing the first output
3. Putting plain data into excel sheet
4. Appending images
5. Creating multiple worksheets
6. Printer settings
7. Macro-enabled workbooks
8. Wrapping up