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"/>

More on the page setup & drawings on separate sections of this documentation.

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.

Now, the entire generation happens within the sheetContents variable. There's several XML options, that are listed in respective section, but for following will suffice:

• all rows are <row r="{row_number}">
• all cells within respective rows are shown as <c r="{cell_number}" s="style_number_dont_touch_this">, where cell_number is AE150 - so, the generation is particularly easy.
• when you want to have text or anything within the cell, here's the snippet:

<c r="it_should_already_be_there" s="you_dont_want_to_touch_this" t="inlineStr_also_not_relevant">
    <is>
        <t>YOUR TEXT BELONGZ TO UZ</t>
    </is>
</c>

One reason for generating

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