How to get SQL data to a preformatted Excel sheet

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.

LVL 5
RonaldZaalAsked:
Who is Participating?
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
RonaldZaalAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
RonaldZaalAuthor Commented:

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
 
RonaldZaalAuthor Commented:
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
 
RonaldZaalAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.