Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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,535 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

564 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