Need to dynamically generate Excel document 3

[gbaughma]

I need to create an excel document on the fly from an ASP page. I *would* just use the method of generating it through HTML tables, HOWEVER, I need to be able to create a true Excel file with multiple sheets populated with data.

I'm sure this is going to include "Server.CreateObject("Excel.Application")" for example, but I'm having the darnedest time finding code examples for it.

OWC won't work, because it doesn't support multiple sheets. My idea is to launch excel on the server, build the document, then either save it with a link to download it, or stream it to the end user.

Anybody have some programming examples on how to do this with ASP? Thanks in advance!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[vicvirk]

I found this, it may help or guide you in the right direction???

http://www.webdesignforum.com/5442-...t-excel-file-asp-idea-might-work-php-too.html

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[ggriffit]

check out

http://www.greggriffiths.org/webdev/both/excel/

which covers this topic.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[vicvirk]

check out

http://www.greggriffiths.org/webdev/both/excel/

which covers this topic.

Nice demo, greg...

But your simple and server side example won't allow for multiple sheets and your ActiveX solution will only work in IE.



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[gmmastros]

I suggest you take a look here:

http://blogs.lessthandot.com/index..../ASPNET/export-table-to-excel-with-grid-lines

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Thank you for all the links. I did find something on 4guysfromrolla (I really want to meet them... they rock!) that got me going in the correct direction.

It uses OWC, but streams the content type as vnd.ms-excel, so you can do multiple sheets.

Stars to everyone who replied, for taking time to reply.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[tyutghf]

Is there any chance you could post your code or a link to the 4guysfromrolla page? I wanted to do this a while ago but ended up making multiple excel fiels instead of one with sheets.

 

[gbaughma]

Sure... have a look....

Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"

Dim objWorkbook, c, objSheet1, objSheet2, objSheet3, objSheet4
Dim intCol, intRow
Public objCommand
Public objRS
Dim strSQL ' SQL Cat string

Set objWorkbook = CreateObject("OWC11.Spreadsheet")
objWorkbook.Worksheets.Add ' Only 3 are created by default

Set objSheet1 = objWorkbook.Worksheets(1)
objSheet1.Name = "SheetOneName"
Set objSheet2 = objWorkbook.Worksheets(2)
objSheet2.Name = "SheetTwoName"
Set objSheet3 = objWorkbook.Worksheets(3)
objSheet3.Name = "SheetThreeName"
Set objSheet4 = objWorkbook.Worksheets(4)
objSheet4.Name = "SheetFourName"

Set c = objWorkbook.Constants

objSheet1.Activate
' on the EFW2LabSMP Worksheet
With objWorkBook.ActiveSheet
    .Cells(1,1) = "#sys_sample_code"
    .Cells(1,2) = "sample_type_code"
    .Cells(1,3) = "sample_matrix_code"
    .Cells(1,4) = "sample_source"
.
. ... a whole bunch of SQL code to set the cells...
.
.
objWorkbook.ActiveSheet.Columns("A:R").AutoFit

End With

Response.Write objWorkbook.XMLData

CloseDB

' Clean up
Set objSheet1 = Nothing
Set objSheet2 = Nothing
Set objSheet3 = Nothing
Set objSheet4 = Nothing
Set objWorkbook = Nothing

Response.End

Obviously, snipped a little... but you can see how to set the cells and such.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com