How to get missing "style "attribute related to columns, while reading XLSX template using OpenXml?

Question

What is the objective?

Pump data into xlsx file from a data source("Data Table" for now).

What has been so far?

1. A new XLSX file was created. Columns were added and same columns were also formatted. Now this file is being used as a template.

2. Using "OpenXML" SDK, I am able to pump the data to this excel template.

3. After opening the file, I could see that the cells were not formatted properly.

4. There is a similar code for another project. When I compared the style related XML, realized that the "style" attribute is missing.

Refer the following(Where things are working)

`<x:cols xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:col min="1" max="1" width="11.44140625" *style="27"* customWidth="1" />
    <x:col min="2" max="2" width="12.88671875" style="27" bestFit="1" customWidth="1" />
</x:cols>`

Refer the following(Where things are NOT working)

`<x:cols xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:col min="1" max="1" width="14" customWidth="1" />
    <x:col min="2" max="2" width="20.26171875" customWidth="1" />
    <x:col min="3" max="3" width="16.83984375" customWidth="1" />
    <x:col min="4" max="4" width="15" customWidth="1" />
</x:cols>`

There is no style information.

Reviewed the XLSX file again. I could see the formatting in the ribbon and on the cells. Seems some issue with XLSX template. I been reading http://officeopenxml.com/anatomyofOOXML-xlsx.php to understand the object model to sort the issue.

Expectation

I need to solve the formatting issue. Looks issue is with XLSX template only. But don't know where I am lacking while creating the XLSX template.

Answer 1

You task look like mine.

XML define style in specific cell (in the most case)

In the sheet__.xml, when __ is a number you can find it in workbook.xml, the xml file has some sub element:

• <sheetPr>
• <dimension>
• <sheetViews>
• <cols>
• <sheetData>
• ...

The <sheetData> element contains <row> elements, inside <row> is some <cell>.

The <cell> look like:

<c r="E6" s="2">
    <v>10.0</v>
</c>

There is an attribute s, in this case, equal to 2.

The styles defined in xl/styles.xml

In xl/styles.xml, some styles was defined, for example:

<styleSheet>
    <numFmts count="13"> ... </numFmts>
    <fonts count="64"> ... </font>
    <fills count="36"> ... </font>
    <borders count="15"> ... </borders>
    <cellStyleXfs count="65"> ... </cellStyleXfs>
    <cellXfs count="142"> 
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
        <xf numFmtId="0" fontId="4" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1">
            <alignment horizontal="left" vertical="center"/>
        </xf>
        <xf numFmtId="183" fontId="4" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="left" vertical="center"/>
        </xf>
        ...
    </cellXfs>
    ...
</styleSheet>

What it mean:

Your cell E6 has s="2", so it will get the third element in cellXfs list:

    <xf numFmtId="183" fontId="4" fillId="0" borderId="1" xfId="0" applyFont="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="left" vertical="center"/>
    </xf>

So, your cell should have numFmtId index is 183. The numFmts tag define formatCode of each used numFmtId.

<numFmts count="13">
    ...
    <numFmt numFmtId="180" formatCode="yyyy/mm/dd"/>
    <numFmt numFmtId="181" formatCode="\:"/>
    <numFmt numFmtId="182" formatCode="#,##0_ ;[Red]\-#,##0\ "/>
    <numFmt numFmtId="183" formatCode="#0.00%"/>
    <numFmt numFmtId="184" formatCode="\¥#,##0"/>
</numFmts>

Next styles after numFmt is fontId. In this case, it equals to 0.

The fonts tag look like:

<fonts count="64">
    <font>
        <sz val="9"/>
        <name val="Arial"/>
        <family val="3"/>
        <charset val="128"/>
    </font>
    <font>
        <sz val="9"/>
        <name val="Arial"/>
        <family val="3"/>
        <charset val="128"/>
    </font>
</fonts>

The fontId=0 take the first element in <fonts> tag. So your cell will have size = 9, font name = Arial ...

You can find more information in c-rex.