Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Transform dataset to excel with schema information

Posted on 2007-10-03
4
Medium Priority
?
2,889 Views
Last Modified: 2013-11-18
Hi, I use xml xsl transformation to export datasets as excel file. Here is the code, The only thing I need is my Excel.xsl do not look schema information. So it transform all the data as ss:Type="String". I need an xsl that take cares of orjinal data types and convert according to that. So do you have or know an excel.xsl that convert with schema, my current xsl is below.

// Dataset ds is ready
StringWriter sw = new StringWriter();

StringWriter stw = new StringWriter();                

ds.WriteXml(stw, XmlWriteMode.WriteSchema);
XmlDataDocument xdd = new XmlDataDocument();
xdd.LoadXml(stw.ToString());

XslCompiledTransform xslCompiledTransform = new XslCompiledTransform();
xslCompiledTransform.Load(HttpContext.Current.Server.MapPath("/include/TransformXSLs/Excel.xsl"));
xslCompiledTransform.Transform(xdd, null, sw);


// 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="/*/*/*">
  <xsl:choose>
    <xsl:when test=".='-999999'">
      <Cell>
        <Data ss:Type="String">
         
        </Data>
      </Cell>
    </xsl:when>
    <xsl:otherwise>
      <Cell>
        <Data ss:Type="String">
          <xsl:value-of select="."/>
        </Data>
      </Cell>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>


</xsl:stylesheet>
0
Comment
Question by:karanba
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:bungHoc
ID: 20004987
You can try this:
Convert DataSet to XML using XmlDataDocument, then perform XSL Transform and last return as Excel

Here is the method that does the trick. I'm not sure how it would handle dataset with a couple of tables though :D
public static string WriteToExcel(DataSet ds)
{
      XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
      XslTransform xt = new XslTransform();
      StreamReader reader = new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), "Excel.xsl"));
         XmlTextReader xRdr = new XmlTextReader(reader);

         xt.Load(xRdr, null, null);
         StringWriter sw = new StringWriter();
         xt.Transform(xmlDataDoc, null, sw, null);
         
      return sw.ToString();
}

0
 

Author Comment

by:karanba
ID: 20005055
what is WorkbookEngine, what to include for this?
0
 
LVL 7

Accepted Solution

by:
bungHoc earned 1500 total points
ID: 20005102
Sorry, not enough coffee yet. Here is full code.

using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Xsl;

namespace ExcelUtil
{
       public class WorkbookEngine
       {
 
             public static string WriteToExcel(DataSet ds)
            {
                  XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
                  XslTransform xt = new XslTransform();
                  StreamReader reader = new StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), "Excel.xsl"));
                     XmlTextReader xRdr = new XmlTextReader(reader);
      
                     xt.Load(xRdr, null, null);
                     StringWriter sw = new StringWriter();
                     xt.Transform(xmlDataDoc, null, sw, null);
         
                  return sw.ToString();
            }
       }
}
0
 

Author Comment

by:karanba
ID: 20012424
Ok, I think I could not tell want I want excatly, but I get solution by writing a simple method for converting dataset to xml and it works, The point that I need is to set types with xsl. thanks for your trys.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

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…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

810 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