Advanced Upload Example: Updating Tabular Data

This functions similarly to the previous sample, except it uploads the entire workbook to the server for processing instead of just the individual changed cell requests.

The sample works as follows:

1. Generate.asp creates a worksheet with tabular data using the ExcelTemplate object.
2. Each time a cell is changed, the cell's address and new value is stored in memory within the VBA code. The entire workbook, and a list of all the changed cells, is uploaded to the server.
3. On the server-side, the file transfer control SoftArtisans FileUp saves the workbook and ExcelWriter opens the workbook to read the values. Note that SoftArtisans FileUp is not included with ExcelWriter.
4. To properly configure this sample for use with your own data, do the following:
   1. Use the included template.xls as a starting point since it includes the necessary VBA code to process the cell changes.
   2. Open template.xls and go to Tools > Macros > Visual Basic editor. In the Initialize module, configure the URL to which the data should be posted.
   3. In response.asp, the server-side response script, edit the oColDict array so that the script can be aware of the database column names that correpond to each Excel worksheet column. Also, set the variables directly beneath oColDict: "pkeyCol" and "TableName". Like in the Advanced POST Example, this script needs to be aware of the primary key column and the table name. However, unlike in the Advanced POST Example, this information is configured on the server.

Run the Sample

Important Things to Note About This Sample

1. start.asp
   
   
   1. Like the Simple Sample #2, this technique requires the SoftArtisans.XLWAssistant object to handle the file upload.
   2. This start.asp script is provided to allow the client to have a chance to download the SoftArtisans.XLWAssistant object before loading the generated workbook.
   
   
2. response.asp
   
   
   1. The server-side script needs to be made aware of the names of the database columns for each Excel worksheet column. You need to initialize the oColDict array with these values. For example, if the sixth column of the worksheet contains data from the Customer.FirstName column, set the oColDict element as follows:

      oColDict(6) = "Customer.FirstName"
      	

      Do this for all columns in your tabular worksheet.
   2. You also need to set the pkeyCol and tableName variables on the server-side.

Top

Copyright © 2003, SoftArtisans, Inc.