• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

translate dataset to xml

I've gotten the a dataset table document translated (using XSLCompiledTransform and an .xslt) into a XMLSpreadsheet that can be read by Excel

Problem is that the .xslt is hardcoded for this particular datatable format.   I'm going to do the xlate for many datatables with different formats, and the files will change during development.

So I'd like to generate the .xslt dynamically for each dataset table.   I can see the mechanics of creating and writing to the .xslt file, etc. and  at the heart of it I can imagine something like:

For each wrkCol as column in datatable,
  xsltString = xsltString &  GeneratePieceOfXSLT_SpecificToColumn (wrkCol)
next

And I hope to learn enough of the xml node language to incorporate that, too.

Question:  are there tools to make this easier, or examples that are already worked out along these lines?

Any guidance on this would be appreciated.

Thanks!


0
codequest
Asked:
codequest
  • 3
  • 3
1 Solution
 
Bob LearnedCommented:
I took an entirely different approach, using an XML document fragment, and generated the XML workbook document.  I like the idea of using XML transformations, but I haven't gone down that road as of yet.

What does your .xslt look like?

Bob
0
 
codequestAuthor Commented:
Here are research links I used:

Decisive
http://support.microsoft.com/Default.aspx?kbid=319180
http://www.w3schools.com/xsl/default.asp

Supportive
http://www.codeproject.com/csharp/Excel2XML.asp
http://blogs.msdn.com/xmlteam/articles/Introducing_XslCompiledTransform.aspx

I had to use outputsettings at xmlwriter.create (documentation at msdn) to get the <?xml blahblah ?>
to appear as the first line in the result, without which Excel would not recognize the document.

Basic approach (suggested somewhere in this research) was

1) Build the result layout in Excel with one header row and one row of data, and then save that to XMLSpreadsheet format.

2) Open that document as text and copy and paste that entire document to a new XLST document.  

3) In that new XSLT document, wrap the header information and template selecting and matching provided in the above links, around the  whole XMLSpreadsheet just copied in (there's an example of this somewhere in these links).

4) Adjust the XSLT document to produce output xml that looks just like the XMLSpreadsheet information, which may or may not have any anomolies.

5) Tailor the source and results layouts to match, following the above examples, and using the <xsl:value-of select="blahblah"/>'s to transfer the data.  

That's the part that needs more automation, using the column names from the dataset, to generate the "tailor the source and results layouts to match" automagic, for any datatable, in both directions.






0
 
Bob LearnedCommented:
Until I delve into those references, I don't quite see how the steps you described could be useful.  It sounds like you are trying to create a generic, automated process, that just auto-magically converts a DataSet to an Excel spreadsheet.  The steps you described seem to involve some manual steps.

Bob
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
codequestAuthor Commented:
Thanks for the message.

The steps listed above were derived from the references.   The main idea is that it's possible to set up a template .xml for output to excel, which can then be modified programmatically.    The specific modification has to do with table/column mappings, how they are expressed in DataSet.GetXML versus how they are expressed in XMLSpreadsheet (I/O of .xls).

The specific question was whether there is any existing code for this, but I suspect from the responses (and the research, and products available) that that 1) it's not that hard, and 2) it's not been posted for any-use download (or it's hiding very well).

At any rate, I'll leave this out here a bit...though the thought process has been helpful in getting me to see I should just bite the bullet and write it.



0
 
Bob LearnedCommented:
I do have something that I use with my ASP.NET pages to create an XML document that Excel can open like a spreadsheet.  It is a working class, but I would like to see if .xslt would be a better, faster process than what I am doing now.

Bob
0
 
codequestAuthor Commented:
I think that further answers the question that this would be best built custom from scratch.   Having just played around with XmlWriter a bit I can see that it's not going to be that difficult.   Thanks for the dialog on this!

 
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now