Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ASP to XLS Code Explanation

Posted on 2003-10-30
6
Medium Priority
?
331 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 375 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

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

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/…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 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