Solved

transform a DataSet to spreadsheet XML for Excel by using Visual Basic .NET and ASP.NET

Posted on 2004-09-23
3
1,528 Views
Last Modified: 2013-11-25
I have got some reports which are to be exported to excel in a single workbook on multiple sheets.

This is the article where I got a sample.
http://www.kbalertz.com/Feedback_319180.aspx

It works fine. Now my qustion is how to make it work for multiple sheets having different formats and data tables.

I think this asks for some XSL expertise. I have basic knowledge of xsl but not detailed.

My environment is Win2K, .Net 1.0, IIS 5, Excel 2002 SP1. Database SQL Server 2000

Please help.
0
Comment
Question by:puranik_p
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
J_Mak earned 500 total points
ID: 12139482
Try something like this:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" 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:spreadsheet2" xmlns:html="http://www.w3.org/TR/REC-html40">
   <xsl:output method="xml"/>
   <xsl:template match="/">
      <Workbook>
         <xsl:apply-templates select="SampleNode1"/>
      </Workbook>
   </xsl:template>
   <xsl:template match="SampleNode1">
      <!-- Here you could create styles like the following -->
      <Styles>
         <Style ss:ID="BOLD">
            <Font x:Family="Swiss" ss:Bold="1"/>
         </Style>
         <Style ss:ID="TIME">
            <NumberFormat ss:Format="hhmmssss"/>
         </Style>
         <Style ss:ID="TEXT">
            <NumberFormat ss:Format="@"/>
         </Style>
         <Style ss:ID="DATE">
            <NumberFormat ss:Format="YYYYMMDD"/>
         </Style>
      </Styles>
      <Worksheet ss:Name="Foo">
         <Table>
            <Column ss:Width="117.75"/>
            ..... more columns.......
            <Row>
               <Cell ss:StyleID="BOLD">
                  <Data ss:Type="String">Report</Data>
               </Cell>
            </Row
            ..... more rows.........
         </Table>
      </Worksheet>
      <Worksheet ss:Name="Bar">
         <Table>
            <Column ss:Width="117.75"/>
            ..... more columns.......
            <Row>
               <Cell ss:StyleID="BOLD">
                  <Data ss:Type="String">Report</Data>
               </Cell>
            </Row
            ..... more rows.........
         </Table>
      </Worksheet>
   </xsl:template>
</xsl:stylesheet>

The above I have given you shows how to create 2 worksheets and you can do them in different formats simply by using styles and XSL elements. ALso have a look at this useful link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

It details everything you can do to import your XML into Excel. Hope that helps!
0
 
LVL 14

Author Comment

by:puranik_p
ID: 12140645
It works. Thanks.
And the link too is very helpful.
0
 
LVL 7

Expert Comment

by:J_Mak
ID: 12140736
Your welcome!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question