[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to get SQL data to a preformatted Excel sheet

Posted on 2006-05-03
8
Medium Priority
?
262 Views
Last Modified: 2008-02-26
Hi Experts,

I've seen numerous articles on this but nothing comes close, or I just don't see it, to what I'm struggling with so pls bare with me.

I have an example excel file with formatting and all of how the it should look in the end.
If I save this as XML i get something like :  

Very much formatting:
 <Style ss:ID="s67">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <NumberFormat ss:Format="0"/>
  </Style>
  <Style ss:ID="s68">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="0"/>
  </Style>
  <Style ss:ID="s69">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="mmm\ yy"/>
  </Style>

The more data part :

    <Cell ss:StyleID="s45" ss:Formula="=RC[1]+R[-1]C"><Data ss:Type="Number">605</Data></Cell>
    <Cell ss:StyleID="s45" ss:Formula="=RC[1]+R[-1]C"><Data ss:Type="Number">486</Data></Cell>
    <Cell ss:StyleID="s45" ss:Formula="=RC[1]+R[-1]C"><Data ss:Type="Number">366</Data></Cell>
    <Cell ss:StyleID="s45" ss:Formula="=RC[2]+R[-1]C"><Data ss:Type="Number">245</Data></Cell>
    <Cell ss:StyleID="s46"/>
    <Cell ss:StyleID="s45"><Data ss:Type="Number">123</Data></Cell>
    <Cell ss:StyleID="s45"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s45"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s45"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s72"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s44"/>
    <Cell ss:StyleID="s32"/>
    <Cell ss:StyleID="s32"><Data ss:Type="String">  % change YTD</Data></Cell>
    <Cell ss:StyleID="s32"/>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>

In total about 2200 lines.....

First I guess I need to put placeholders everywhere where I need to place data but how, still no clue.
Do I give every cell where I need to put something in a different name ?

When I have this figured out I guess then I need to move the result on sql query into this XML file and open it as excel file in the users browser
, haven't found this out either.

In a later stage I need to repeatingly use this XML file to create a very big excel file but first things first.
Let's first try to get a small one working.

Many thanks for helping me out, i'm swimming allready sometime with this.

0
Comment
Question by:RonaldZaal
  • 5
  • 3
8 Comments
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16596805
The reality of this is that you can just put out a <TABLE> with TR and TD elements with any formatting that you would do with a table and Excel will read it.

If you want to get really fancy and use the StyleId=s47, etc then you can, but all of that is simply Excel keeping track in it's own way of the styles that it is using on the cells.

The bottom line is that as long as you emit a complete <TABLE></TABLE> tag with rows and columns that Excel will open it and you will see it as you would in a browser.  Then you would save it as an XLS file and you have it.

If you are looking to put it into an XML file like above, you are going to go through a lot of work tying together the styles that need to exist in the headers, etc.

or

You could use a Repeater with the rows and columns defined and the binding where the data should go like:

<Cell ss:StyleID="s47"><Data ss:Type="Number"><%# Eval("datafieldname") %></Data></Cell>

And have all the other stuff with styles defined above. and then the binding can add the data points.

But the bottom line is that it will just be well formed XML with stuff in it and it will work in Excel and you will just have to tweak the binding of the repeater and getting the output.

HTH
Ben.
0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 16597093
Hi Ben,

Thanks for the post.
Do I understand it correctly that I could change the cells where I would need data which where like :
<Cell ss:StyleID="s47"><Data ss:Type="Number">0</Data></Cell>

with you example like :
<Cell ss:StyleID="s47"><Data ss:Type="Number"><%# Eval("datafieldname") %></Data></Cell>

How would I get the data from a sql query into this xml file ?  
I guess with
<% Response.ContentType = "application/vnd.ms-excel" %>
But how to use the XML file ?

many thanks,
Ronald
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16597882
Well, the concept is a little like what you indicated.

It is helpful when you set the ContentType = "application/vnd.ms-excel" and then set the content disposition to be an attachment and it will prompt them with a Save As dialog like if you were downloading an exe.  But if you just want it to show up in an ie window in Excel, then your contenttype thing works great.

But on the getting the data out, if you are going to use the binding method, then you would want to capture what the result is, and that is a little tricker than building a string with the data in it.

If you wanted to get the HTML of the control that you used binding with (Eval("datafieldname")) then you could override the Render event in the page

private override void Render( ... ) {

}

And capture the render stuff in the control and save it out and send it out as the response instead of sending out an html page to the browser.  Which means you can change the Response.ContentType etc.

But before we go to implementation, what is your intended use of this type of page and how will people get to it, etc.  Knowing the flow of how people will get to the point of wanting the Excel will help, because you could have a page that is just to send out the Excel and it would naturally render out what you want.  So help me to understand what it is you want to accomplish (steps wise, not final outcome) and we can go from there.

Ben.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Author Comment

by:RonaldZaal
ID: 16598217

Yeah, but further then concept....  but let's go!
On a pre-page the user selects a month / year / report                  12 reports possible so far and growing, month selection possible back to 1998

Clicking on a select button in a dbgrid sends the selected report etc to the page on which I want the final excel to open.

On this "Final Excel show yourself page" I check sended data  with : FromPage = CType(Context.Handler, Reports_index)

With the user credentials and the Frompage data I do a repeated select on a sql db every time with a small difference, at the moment I think 7 times.

The result of this select should go everytime into the preformatted excel / xml file, and everytime adding the result below the first result in the excel / xml file
or add a worksheet, depending on the sql query result.

Yes, this is an extra "must" like also the the returning data may be dbnull, in which case nothing should be added.

Is this possible the way I am trying to do it now or ....

Ronald.




0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 16615973
Hi,
To let you in on at least some progress.
With SQL XML 3.0 I got myself a virtual directory setup where I can place XML files.
Now I'm trying with XML AUTO / XML EXPLICIT to get some data back into it.

What would now the process be to make from my final excel xls file a template to use to combine this with the XML data file?  
0
 
LVL 25

Accepted Solution

by:
DBAduck - Ben Miller earned 1500 total points
ID: 16616399
Well, with XML you could use XSLT to get this done, but it would be out of my scope of real expertise.  I have seen it done and I know many Microsoft MVPs that know how to do this sort of thing, but I am not sure how many are in here.

I think that this is simpler than it is sounding.  It really depends on the structure that is coming out of the database and what the resultset looks like.  If you have a complete document that illustrates the formatting, etc, then it is a matter of setting up some DataBinding and capture of output and then to send it out to the browser.  This I know how to do, but looking at your xml above, it should not be very difficult with some more information.

I will take a look at an example of a formatted xls and save it as xml and play with it.
0
 
LVL 5

Author Comment

by:RonaldZaal
ID: 16630473
Hi Ben,
I made some progress with the problem and even learned something more about XML
Thanks for the pointers and if you have another minute then the story continues on :
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21841946.html

Cheers,
Ronald
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
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…
Suggested Courses
Course of the Month19 days, 19 hours left to enroll

872 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