radhakrishan1
asked on
Opening Excel File in ASP
Im able to generate Excel File from a Recordset, its being save also onto the Destination. If I run on my machine, it runs perfectly fine.
Problem :
When I run on the Server, it doesn't run. It saves the file on the destination directorty but doesn't open.
Everything on the web server is saved on D:\inetpub\wwwroot\applica tion
Urgent help needed :
Problem is may be with the Path :
<%
Option Explicit
Class ExcelGen
Private objSpreadsheet
Private iColOffset
Private iRowOffset
Private strTmpDir
Sub Class_Initialize()
Set objSpreadsheet = Server.CreateObject("OWC.S preadsheet ")
iRowOffset = 2
iColOffset = 2
strTmpDir = "C:\Inetpub\wwwroot\"
End Sub
Sub Class_Terminate()
Set objSpreadsheet = Nothing 'Clean up
'Remove out of date spreadsheets
CleanUpSpreadsheets
End Sub
Public Property Let ColumnOffset(iColOff)
If iColOff > 0 then
iColOffset = iColOff
Else
iColOffset = 2
End If
End Property
Public Property Let RowOffset(iRowOff)
If iRowOff > 0 then
iRowOffset = iRowOff
Else
iRowOffset = 2
End If
End Property
Sub GenerateWorksheetFromRecor dset(objre cordset, bolAutofitColumns)
'Populates the Excel worksheet based on a Recordset's contents
'Start by displaying the titles
If objrecordset.EOF then Exit Sub
Dim objField, iCol, iRow, iMaxColVal
iCol = iColOffset
iRow = iRowOffset
objSpreadsheet.Cells(iRow- 2, iCol+1).Value = "Sunday Allocation Report"
objSpreadsheet.Cells(iRow- 2, iCol+1).Font.Bold = True
objSpreadsheet.Cells(iRow- 2, iCol+1).Font.Underline = True
For Each objField in objrecordset.Fields
objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
objSpreadsheet.Cells(iRow, iCol).Font.Bold = True
objSpreadsheet.Cells(iRow, iCol).Font.Underline = True
iCol = iCol + 1
Next 'objField
iMaxColVal = iCol - 1
'Display all of the data
Do While Not objrecordset.EOF
iRow = iRow + 1
iCol = iColOffset
For Each objField in objrecordset.Fields
If IsNull(objField.Value) then
objSpreadsheet.Cells(iRow, iCol).Value = ""
Else
objSpreadsheet.Cells(iRow, iCol).Value = objField.Value
End If
iCol = iCol + 1
Next 'objField
objrecordset.MoveNext
Loop
If bolAutofitColumns then 'autofit columns
For iCol = iColOffset to iMaxColVal
objSpreadsheet.Columns(iCo l).AutoFit Columns
Next
End If
End Sub
Function SaveWorksheet(strFileName)
'Save the worksheet to a specified filename
On Error Resume Next
Call objSpreadsheet.ActiveSheet .Export(st rFileName, 0)
SaveWorksheet = (Err.Number = 0)
End Function
Sub StreamWorksheet()
'Save the worksheet in a temporary file
Dim strFileName, objFSO
Set objFSO = Server.CreateObject("Scrip ting.FileS ystemObjec t")
strFileName = strTmpDir & objFSO.GetBaseName(objFSO. GetTempNam e) & ".xls"
Set objFSO = Nothing
if SaveWorksheet(strFileName) then
Response.Redirect strFileName
end if
End Sub
Private Sub CleanUpSpreadsheets()
Dim objFS
Dim objFolder
Dim objFile
set objFS = Server.CreateObject("Scrip ting.FileS ystemObjec t")
set objFolder = objFS.GetFolder(strTmpDir)
'Loop through each file in the strTmpDir folder
for each objFile in objFolder.Files
'Delete Spreadsheets older than 10 minutes
If DateDiff("n", objFile.DateLastModified, now) > 10 then
objFS.DeleteFile strTmpDir & objFile.Name, True
end if
next
set objFolder = nothing
set objFS = nothing
End Sub
End Class
%>
<HTML>
<HEAD>
<TITLE>Export To Excel File</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<link href="JRLMIS3.css" type="text/css" rel="stylesheet">
<!-- #include file="adovbs.inc" -->
<%
Response.ContentType = "application/vnd.ms-excel"
'Dim Conn, ObjCommand, ldDateFor, objRecordSet,ldDateFor, WhichDate
Session.LCID = 2057
Dim WhichDate, Conn, ObjCommand, ObjRecordSet, ldDateFor
WhichDate=Request.QueryStr ing("DateF rom")
If Len(WhichDate)=8 Then
ldDateFor=CDate(Left(CStr( WhichDate) ,2) & "/" & Mid(CStr(WhichDate),3,2) & "/" & Right(CStr(WhichDate),4))
Else
ldDateFor=CDate(Left(CStr( WhichDate) ,1) & "/" & Mid(CStr(WhichDate),2,2) & "/" & Right(CStr(WhichDate),4))
End If
'Making Connection to Database & Creating the Recordset
set Conn = Server.CreateObject("ADODB .Connectio n")
Conn.Open "provider=sqloledb;data source=JRLDataServer1;init ial catalog=JRLEdiValidation;u ser id=sa;password=lincolnroad ;"
set ObjCommand=server.CreateOb ject("ADOD B.Command" )
set objRecordSet = Server.CreateObject("ADODB .Recordset ")
set objRecordSet.ActiveConnect ion = Conn
objRecordSet.CursorType = adOpenForwardOnly
objRecordSet.CursorLocatio n = adUseClient
With ObjCommand
.ActiveConnection = Conn
.CommandText = "spSundayReportExcel"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@DateFor ", adDate, adParamInput)
.Parameters("@DateFor")=ld DateFor
end with
set objRecordSet = ObjCommand.Execute
Dim objExcel
Set objExcel = New ExcelGen
objExcel.RowOffset = 3
objExcel.ColumnOffset = 1
'Dump the recordset contents to the spreadsheet and
'AutoFit the columns
objExcel.GenerateWorksheet FromRecord set objrecordset, True
'Stream the spreadsheet to the user
objExcel.StreamWorksheet
'Clean up!
Set objExcel = Nothing
objrecordset.Close
Set objrecordset = Nothing
objRecordSet.Close
Set objRecordSet = Nothing
%>
</BODY>
</HTML>
Problem :
When I run on the Server, it doesn't run. It saves the file on the destination directorty but doesn't open.
Everything on the web server is saved on D:\inetpub\wwwroot\applica
Urgent help needed :
Problem is may be with the Path :
<%
Option Explicit
Class ExcelGen
Private objSpreadsheet
Private iColOffset
Private iRowOffset
Private strTmpDir
Sub Class_Initialize()
Set objSpreadsheet = Server.CreateObject("OWC.S
iRowOffset = 2
iColOffset = 2
strTmpDir = "C:\Inetpub\wwwroot\"
End Sub
Sub Class_Terminate()
Set objSpreadsheet = Nothing 'Clean up
'Remove out of date spreadsheets
CleanUpSpreadsheets
End Sub
Public Property Let ColumnOffset(iColOff)
If iColOff > 0 then
iColOffset = iColOff
Else
iColOffset = 2
End If
End Property
Public Property Let RowOffset(iRowOff)
If iRowOff > 0 then
iRowOffset = iRowOff
Else
iRowOffset = 2
End If
End Property
Sub GenerateWorksheetFromRecor
'Populates the Excel worksheet based on a Recordset's contents
'Start by displaying the titles
If objrecordset.EOF then Exit Sub
Dim objField, iCol, iRow, iMaxColVal
iCol = iColOffset
iRow = iRowOffset
objSpreadsheet.Cells(iRow-
objSpreadsheet.Cells(iRow-
objSpreadsheet.Cells(iRow-
For Each objField in objrecordset.Fields
objSpreadsheet.Cells(iRow,
objSpreadsheet.Cells(iRow,
objSpreadsheet.Cells(iRow,
iCol = iCol + 1
Next 'objField
iMaxColVal = iCol - 1
'Display all of the data
Do While Not objrecordset.EOF
iRow = iRow + 1
iCol = iColOffset
For Each objField in objrecordset.Fields
If IsNull(objField.Value) then
objSpreadsheet.Cells(iRow,
Else
objSpreadsheet.Cells(iRow,
End If
iCol = iCol + 1
Next 'objField
objrecordset.MoveNext
Loop
If bolAutofitColumns then 'autofit columns
For iCol = iColOffset to iMaxColVal
objSpreadsheet.Columns(iCo
Next
End If
End Sub
Function SaveWorksheet(strFileName)
'Save the worksheet to a specified filename
On Error Resume Next
Call objSpreadsheet.ActiveSheet
SaveWorksheet = (Err.Number = 0)
End Function
Sub StreamWorksheet()
'Save the worksheet in a temporary file
Dim strFileName, objFSO
Set objFSO = Server.CreateObject("Scrip
strFileName = strTmpDir & objFSO.GetBaseName(objFSO.
Set objFSO = Nothing
if SaveWorksheet(strFileName)
Response.Redirect strFileName
end if
End Sub
Private Sub CleanUpSpreadsheets()
Dim objFS
Dim objFolder
Dim objFile
set objFS = Server.CreateObject("Scrip
set objFolder = objFS.GetFolder(strTmpDir)
'Loop through each file in the strTmpDir folder
for each objFile in objFolder.Files
'Delete Spreadsheets older than 10 minutes
If DateDiff("n", objFile.DateLastModified, now) > 10 then
objFS.DeleteFile strTmpDir & objFile.Name, True
end if
next
set objFolder = nothing
set objFS = nothing
End Sub
End Class
%>
<HTML>
<HEAD>
<TITLE>Export To Excel File</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<link href="JRLMIS3.css" type="text/css" rel="stylesheet">
<!-- #include file="adovbs.inc" -->
<%
Response.ContentType = "application/vnd.ms-excel"
'Dim Conn, ObjCommand, ldDateFor, objRecordSet,ldDateFor, WhichDate
Session.LCID = 2057
Dim WhichDate, Conn, ObjCommand, ObjRecordSet, ldDateFor
WhichDate=Request.QueryStr
If Len(WhichDate)=8 Then
ldDateFor=CDate(Left(CStr(
Else
ldDateFor=CDate(Left(CStr(
End If
'Making Connection to Database & Creating the Recordset
set Conn = Server.CreateObject("ADODB
Conn.Open "provider=sqloledb;data source=JRLDataServer1;init
set ObjCommand=server.CreateOb
set objRecordSet = Server.CreateObject("ADODB
set objRecordSet.ActiveConnect
objRecordSet.CursorType = adOpenForwardOnly
objRecordSet.CursorLocatio
With ObjCommand
.ActiveConnection = Conn
.CommandText = "spSundayReportExcel"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@DateFor
.Parameters("@DateFor")=ld
end with
set objRecordSet = ObjCommand.Execute
Dim objExcel
Set objExcel = New ExcelGen
objExcel.RowOffset = 3
objExcel.ColumnOffset = 1
'Dump the recordset contents to the spreadsheet and
'AutoFit the columns
objExcel.GenerateWorksheet
'Stream the spreadsheet to the user
objExcel.StreamWorksheet
'Clean up!
Set objExcel = Nothing
objrecordset.Close
Set objrecordset = Nothing
objRecordSet.Close
Set objRecordSet = Nothing
%>
</BODY>
</HTML>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept Answer by mberumen
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
GaryC123
EE Cleanup Volunteer