DirectedInsight
  Home | Javascript | ASP | Database | Solutions | Misc.
NEWEST
Different ways to show the user they're wrong.
Helpful
Some simple queries that can save you some research time
NEWER
Complete AJAX example.
COOL
Dynamic file upload fields

small DI oval Manipulate an Excel File

Occasionally we need to upload an Excel file from a user and do something with the file that they upload, whether it is as simple as counting the number of rows or actually adding data to the worksheet. After quite a bit of searching the web and never quite finding anyone or any examples that did exactly what I needed I pieced together the following from various other examples I did find. This will probably not do exactly what you need it to do either, but it might give you a decent starting point.

In this example what we are doing is getting the file from the user through a file upload button on a previous page, counting the number of rows in the file, adding a column of data to the file that they uploaded, and then saving it with a new name.
The example uses an upload component called aspSmartUpload to do the actual upload, you may have a different way so your code would be different in that respect.

small DI oval The code.... 

<%
Set oSU = Server.CreateObject("aspSmartUpload.SmartUpload")
oSU.Upload

if oSU.form("postcardPost") = "true" then    ' make sure it came from the correct page
    filename = oSU.Files(1).FileName
    if fileName <> "" then             ' did they really upload a file
        saveName = mailListLocation & filename
        oSU.Files(1).saveAs(saveName)  
        
        ' count the rows in the spreadsheet and add the job id to it
        newName = "C:\uploadedFiles\" & session("jobID")
        Set xlApp = server.createobject("Excel.application")
        xlApp.Visible = False
        xlApp.Workbooks.Open saveName
        Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
        
        lastRow = xlApp.Range("A1").SpecialCells(11).row
        xlsheet.Cells(1,10).Value = "JobNumber" 
        
        for i = 2 to lastRow
            xlsheet.Cells(i,10).Value = session("jobID")
        next

        xlsheet.saveas newName      ' save it with the new name
        xlApp.quit
        set xlApp = nothing
        
        ' delete the uploaded one
        Set oFS = CreateObject("Scripting.FileSystemObject")
        Set f2 = oFS.GetFile(saveName)
        f2.Delete
        set f2 = nothing
        set oFS = nothing                  
    end if ' end if they uploaded a list
end if ' end if is posted from the right page
    
Set oSU = nothing
%>
<html>
<head>
</head>
<body>
last row = <%=lastRow%>.
</body>
</html>

The session("jobID") is value stored in the session for each user to hold the job ID associated with their order. We need to add the job id to each row in the sheet as it is a mailing list that will be merged and sorted with other lists and so each row needs to refer to the job it was uploaded for.

One gotcha I discovered while testing and playing with various things is that if there is an error on the page after you've opened the uploaded file but before you've 'quit' the Excel object, it can still hold a lock on the file. If it does this, check the task manager of the server the ASP is hosted on and kill the EXCEL.exe process - after making sure yours is the only one running.

This is a fairly simplistic example but that was all we needed at the time. Keep in mind that for this example it relies on knowing the exact layout of the file being uploaded (number of columns) and the sheet being accessed needs to be the first sheet in the workbook. There are a number of other functions and properties that can be used to further manipulate and access the data. A good starting point to discover new methods would be the MSDN.

If you have a question, comment, bug fix, or addition let us know. We'll add it to the demo with the proper credit. comments@directedinsight.com

small DI oval