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>

Moving forward

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