Solved

ASP to XLS Code Explanation

Posted on 2003-10-30
6
328 Views
Last Modified: 2007-12-19
Ive been trying to find information on how to export ASP to XLS and there doesnt seem to be much out there.  I was wondering if anyone knew the rules about how to do this.  I would like to be able to create multiple worksheet in one file as well as adjusting the fonts, alignment, etc.

This is some of the code i have been playing around with:

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add

ExcelBook.Worksheets(1).Cells(2, 2).Font.Name = "Verdana"
ExcelBook.Worksheets(1).Cells(2, 2).Font.Size = 10
ExcelBook.Worksheets(1).Cells(2, 2).Font.Italic = true
ExcelBook.Worksheets(1).Cells(2, 2).Value="Hello world"
ExcelBook.Worksheets(1).Cells(2, 2).HorizontalAlignment = 1
ExcelBook.Worksheets(1).Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs "c:\yourfile.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing

I thought changing the worksheet # would allow me to input data to a new worksheet but it doesnt appear to work that way.  Any help would be appreciated.
0
Comment
Question by:TSchuman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 2

Expert Comment

by:weareu
ID: 9657182
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
Set ExcelSheet1 = ExcelBook.Worksheets(1)

ExcelSheet1.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet1.Cells(2, 2).Font.Size = 10
ExcelSheet1.Cells(2, 2).Font.Italic = true
ExcelSheet1.Cells(2, 2).Value="Hello sheet 1"
ExcelSheet1.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet1.Cells(2, 2).Font.Color = RGB(0,0,0)

Set ExcelSheet2 = ExcelBook.Worksheets.Add
ExcelSheet2.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet2.Cells(2, 2).Font.Size = 10
ExcelSheet2.Cells(2, 2).Font.Italic = true
ExcelSheet2.Cells(2, 2).Value="Hello sheet 2"
ExcelSheet2.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet2.Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs "c:\yourfile.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing
0
 

Author Comment

by:TSchuman
ID: 11636993
Weareu,

I tried your solution above but when i attempt to run it, It just sits there and nothing happens.  Any ideas?
0
 
LVL 2

Accepted Solution

by:
weareu earned 125 total points
ID: 11644277
I have tried the example by copy and pasting it on a few different machines and configurations. It seems to work fine and write a file in your c:\ folder called 'yourfile.xls'. In the browser itself it would not do anything no. If you would like to have the excel file downloaded to the users PC use the following code:

<%
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
Set ExcelSheet1 = ExcelBook.Worksheets(1)

ExcelSheet1.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet1.Cells(2, 2).Font.Size = 10
ExcelSheet1.Cells(2, 2).Font.Italic = true
ExcelSheet1.Cells(2, 2).Value="Hello sheet 1"
ExcelSheet1.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet1.Cells(2, 2).Font.Color = RGB(0,0,0)

Set ExcelSheet2 = ExcelBook.Worksheets.Add
ExcelSheet2.Cells(2, 2).Font.Name = "Verdana"
ExcelSheet2.Cells(2, 2).Font.Size = 10
ExcelSheet2.Cells(2, 2).Font.Italic = true
ExcelSheet2.Cells(2, 2).Value="Hello sheet 2"
ExcelSheet2.Cells(2, 2).HorizontalAlignment = 1
ExcelSheet2.Cells(2, 2).Font.Color = RGB(0,0,0)

ExcelBook.SaveAs Server.MapPath("yourfile.xls")
ExcelApp.Application.Quit
Set ExcelApp = Nothing

Response.Write "<SCRIPT>document.location.href = 'yourfile.xls';</SCRIPT>"
%>

Do not use Response.Redirect since this will not change the response type... This way the browser will request and download the file just written to the same path as this file is in. If this example returns an error, you might need to grant rights to your IIS user. Alternatively if you are confident that your web-server is secure you could make your administrator user your IIS user.
0
 

Author Comment

by:TSchuman
ID: 11646563
Thanks Weareu, that did it!

Im not sure what was wrong before but when i would run the ASP it would just sit there like it was caught in an endless loop.  Having the page response.write the file after its done must have been what was missing.

Thanks again for your help.
0

Featured Post

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!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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