Link to home
Start Free TrialLog in
Avatar of radhakrishan1
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\application


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.Spreadsheet")

      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 GenerateWorksheetFromRecordset(objrecordset, 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(iCol).AutoFitColumns
        Next
      End If
    End Sub    


    Function SaveWorksheet(strFileName)
      'Save the worksheet to a specified filename
      On Error Resume Next
      Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)

      SaveWorksheet = (Err.Number = 0)
    End Function


    Sub StreamWorksheet()
      'Save the worksheet in a temporary file
      Dim strFileName, objFSO

      Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
      strFileName = strTmpDir & objFSO.GetBaseName(objFSO.GetTempName) & ".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("Scripting.FileSystemObject")
       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.QueryString("DateFrom")
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.Connection")
Conn.Open "provider=sqloledb;data source=JRLDataServer1;initial catalog=JRLEdiValidation;user id=sa;password=lincolnroad;"
set ObjCommand=server.CreateObject("ADODB.Command")
set objRecordSet = Server.CreateObject("ADODB.Recordset")
set objRecordSet.ActiveConnection = Conn
objRecordSet.CursorType = adOpenForwardOnly
objRecordSet.CursorLocation = adUseClient

With ObjCommand
      .ActiveConnection = Conn
      .CommandText = "spSundayReportExcel"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@DateFor", adDate, adParamInput)
      .Parameters("@DateFor")=ldDateFor
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.GenerateWorksheetFromRecordset 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>
ASKER CERTIFIED SOLUTION
Avatar of mberumen
mberumen

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gary
No comment has been added lately, so it's time to clean up this TA.
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