create an excel from a dataset

A simple way to convert dataset to excel sheet…

 Create a C# class file with the name WorkbookEngine.cs. Add the following code into it

public static void CreateWorkbook(DataSet ds, String path, string fileNam)

{

XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);

XslTransform xt = new XslTransform();

StreamReader reader =newStreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), “Excel.xsl”));

XmlTextReader xRdr = new XmlTextReader(reader);

xt.Load(xRdr, null, null);StringWriter sw = newStringWriter();

xt.Transform(xmlDataDoc, null, sw, null);

StreamWriter myWriter = newStreamWriter(path + fileNam);

myWriter.Write (sw.ToString());

myWriter.Close ();

}

 

The following code should be available in a file named Excel.xsl

 <xsl:stylesheet version=”1.0″
    xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:xsl=”http://www.w3.org/1999/XSL/Transform
 xmlns:msxsl=”urn:schemas-microsoft-com:xslt”
 xmlns:user=”urn:my-scripts”
 xmlns:o=”urn:schemas-microsoft-com:office:office”
 xmlns:x=”urn:schemas-microsoft-com:office:excel”
 xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” > 
<xsl:template match=”/”>
  <Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:o=”urn:schemas-microsoft-com:office:office”
    xmlns:x=”urn:schemas-microsoft-com:office:excel”
    xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
    xmlns:html=”http://www.w3.org/TR/REC-html40“>
    <xsl:apply-templates/>
  </Workbook>
</xsl:template>
<xsl:template match=”/*”>
  <Worksheet>
  <xsl:attribute name=”ss:Name”>
  <xsl:value-of select=”local-name(/*/*)”/>
  </xsl:attribute>
    <Table x:FullColumns=”1″ x:FullRows=”1″>
      <Row>
        <xsl:for-each select=”*[position() = 1]/*”>
          <Cell><Data ss:Type=”String”>
          <xsl:value-of select=”local-name()”/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
  </Worksheet>
</xsl:template>
<xsl:template match=”/*/*”>
  <Row>
    <xsl:apply-templates/>
  </Row>
</xsl:template>
<xsl:template match=”/*/*/*”>
  <Cell><Data ss:Type=”String”>
    <xsl:value-of select=”.”/>
  </Data></Cell>
</xsl:template>
</xsl:stylesheet>

Add this file also to the solution.

 

On the solution explorer right click on the Excel.xsl file and select properties.

In build action choose “Embedded Resource”.

 

 

In our coding class add the following line to invoke all this.

WorkbookEngine.CreateWorkbook(<our Dataset name>, “Our file path to store the excel sheet”, \\OurExcelFileName.xls);

 

Save all the files and run the code.

Our excel file will be created!

Advertisements