Convert Excel to HTML
I have a requirement to preview excel files in browser. I am currently trying to convert Excel file to HTML using the OpenXML SDK and OpenXml Power tool. Using the SmlDataRetriever I could retrieve the XElement data from the Excel sheet but I do not know what needs to be done further to convert this to HTML. Is there any possible ways to convert Eelement data into a valid HTML so that it can be previewed? Is there any other methods in which in which excel can be converted to html. I cannot use the paid libraries for this work. I could not find any examples in the XSLT transformation of Excel XML to HTML. This is the format I receive from SmlDataRetriever:
<Data> <DataProps> <sheetFormatPr defaultColWidth="9.25" defaultRowHeight="15" dyDescent="0.25" /> </DataProps> <Row RowNumber="1"> <Cell Ref="A1" ColumnId="A" ColumnNumber="0" Type="s"> <CellProps /> <Value>Make </Value> <DisplayValue>Make </DisplayValue> </Cell> <Cell Ref="B1" ColumnId="B" ColumnNumber="1" Type="s"> <CellProps /> <Value>Miles </Value> <DisplayValue>Miles </DisplayValue> </Cell> <Cell Ref="C1" ColumnId="C" ColumnNumber="2" Type="s"> <CellProps /> <Value>Cost</Value> <DisplayValue>Cost</DisplayValue> </Cell> </Row> <Row RowNumber="2"> <Cell Ref="A2" ColumnId="A" ColumnNumber="0" Type="s"> <CellProps /> <Value>Ford</Value> <DisplayValue>Ford</DisplayValue> </Cell> <Cell Ref="B2" ColumnId="B" ColumnNumber="1"> <CellProps /> <Value>40000</Value> <DisplayValue>40000</DisplayValue> </Cell> <Cell Ref="C2" ColumnId="C" ColumnNumber="2" Style="1"> <CellProps numFmtId="6" formatCode=""$"#,##0_);[Red]\("$"#,##0\)" applyNumberFormat="1"> <font> <sz val="11" /> <color theme="1" /> <name val="Calibri" /> <family val="Swiss" /> <scheme val="minor" /> </font> </CellProps> <Value>3500</Value> <DisplayValue>$3,500</DisplayValue> </Cell> </Row> <Row RowNumber="3"> <Cell Ref="A3" ColumnId="A" ColumnNumber="0" Type="s"> <CellProps /> <Value>Chevi</Value> <DisplayValue>Chevi</DisplayValue> </Cell> <Cell Ref="B3" ColumnId="B" ColumnNumber="1"> <CellProps /> <Value>55000</Value> <DisplayValue>55000</DisplayValue> </Cell> <Cell Ref="C3" ColumnId="C" ColumnNumber="2" Style="1"> <CellProps numFmtId="6" formatCode=""$"#,##0_);[Red]\("$"#,##0\)" applyNumberFormat="1"> <font> <sz val="11" /> <color theme="1" /> <name val="Calibri" /> <family val="Swiss" /> <scheme val="minor" /> </font> </CellProps> <Value>4200</Value> <DisplayValue>$4,200</DisplayValue> </Cell> </Row> <Row RowNumber="4"> <Cell Ref="A4" ColumnId="A" ColumnNumber="0" Type="s"> <CellProps /> <Value>Tata</Value> <DisplayValue>Tata</DisplayValue> </Cell> <Cell Ref="B4" ColumnId="B" ColumnNumber="1"> <CellProps /> <Value>51000</Value> <DisplayValue>51000</DisplayValue> </Cell> <Cell Ref="C4" ColumnId="C" ColumnNumber="2" Style="1"> <CellProps numFmtId="6" formatCode=""$"#,##0_);[Red]\("$"#,##0\)" applyNumberFormat="1"> <font> <sz val="11" /> <color theme="1" /> <name val="Calibri" /> <family val="Swiss" /> <scheme val="minor" /> </font> </CellProps> <Value>3800</Value> <DisplayValue>$3,800</DisplayValue> </Cell> </Row> </Data> 
The transformation can be achieved using a fairly simple xsl sheet. If you start with the identity sheet you can create a rule for each node type.
I’ve included the single default rule that should be unchanged. The Cell rule transforms <Cell> elements into <td> elements. You’ll need to add in rules for the other input elements.
xsltproc i.xsl source.xml > o.html i.xsl
<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!-- This is the identity transformation, it is the default action --> <xsl:template match="@*|node()"> <xsl:copy> <xsl:apply-templates select="@*|node()"/> </xsl:copy> </xsl:template> <!-- This transforms Cell to td --> <xsl:template match="Cell"> <td> <xsl:apply-templates select="@*|node()"/> </td> </xsl:template> </xsl:stylesheet> This will result in the following output:
o.html
<?xml version="1.0"?> <Data> <DataProps> <sheetFormatPr defaultColWidth="9.25" defaultRowHeight="15" dyDescent="0.25"/> </DataProps> <Row RowNumber="1"> <td Ref="A1" ColumnId="A" ColumnNumber="0" Type="s"> <CellProps/> <Value>Make </Value> <DisplayValue>Make </DisplayValue> </td> <td Ref="B1" ColumnId="B" ColumnNumber="1" Type="s"> <CellProps/> <Value>Miles </Value> <DisplayValue>Miles </DisplayValue> </td> <td Ref="C1" ColumnId="C" ColumnNumber="2" Type="s"> <CellProps/> <Value>Cost</Value> <DisplayValue>Cost</DisplayValue> </td> </Row> <Row RowNumber="2"> ... 
