?
Solved

Opening Excel File in ASP

Posted on 2003-03-11
2
Medium Priority
?
295 Views
Last Modified: 2008-03-03
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>
0
Comment
Question by:radhakrishan1
[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 Comments
 
LVL 4

Accepted Solution

by:
mberumen earned 180 total points
ID: 8113386
my guess is that your problem is with your StreamWorksheet statement,

when you do it from your workstation you have direct access to the path, whereas web users do not.

objExcel.StreamWorksheet

I would try a different approach, perhaps a javascript section that opens the file in a new window or redirects the current page..

objRecordSet.Close
 Set objRecordSet = Nothing
%>
<script language=javascript>
window.href.location="yourfilenamehere.xls";

</script>
</BODY>
</HTML>

But then we would have problems if the file gets deleted before it has a chance to be opened by the browser...

how about deleting everything but the newly generated file?

so each user requesting a spreadsheet would delete the temp files created by other users







0
 
LVL 58

Expert Comment

by:Gary
ID: 9352460
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
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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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