?
Solved

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

Posted on 2004-09-23
3
Medium Priority
?
1,531 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
J_Mak earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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