Solved

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

Posted on 2004-09-23
3
1,530 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 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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