Solved

ASP to XLS Code Explanation

Posted on 2003-10-30
6
325 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
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 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