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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: