With ExcelWriter, there are several ways to populate an Excel workbook with data from a database, including ExcelTemplate, Cells.GetDataFromRecordset, and Cells.CopyFromRecordset. ExcelWriter Version 4 includes HotCell Technology - the ability to update a server-side data source directly from client-side Excel.

This solution utilizes client-side Excel VBA code to detect when changes have been made to a worksheet. The address and value of each modified cell is submitted to the server in order to modify the data source. There are two different upload methods that you can use to submit the changed cell data back to the server:

1. Upload the entire workbook to the server
   
   This method uploads the entire edited workbook back to the server. When you issue the command to update the data source, ExcelWriter Assistant* (included in XLWAssis.cab) uploads the entire workbook back to the server along with information about which cells have changed. On the server, ExcelWriter opens the uploaded workbook, reads the values of the changed cells, and updates the data source.
   
   
2. POST changed-cell instructions to the server
   
   This method submits cell information in an HTTP POST request. On the server, the receiving ASP script parses the information and updates the data source. This solution is far less network-intensive because it just posts bits of information instead of the entire workbook. However, the POST method is more complicated than the upload method and requires more code on both the client and server.

This package contains two samples for each of the transfer methods mentioned above. For each of the transfer methods, there are sample applications that show you how to use HotCells when you know exactly which cells in your workbook will be editable (such as in a form), and ones that show you how to use HotCells to update large tabular data sheets where the specific cells aren't precisely known.

Depending on your application needs, you can choose which approach to use in order to customize the solution to your own needs. Here are some examples of usage scenarios that may assist you in deciding which techniques to develop:

• POST/specific cell -- Use the specific cell approach when you know precisely which cells will be editable. For example, if you know only cells B5 and D10 will be HotCells. This solution is the easiest as it requires the least and simplest code on both the client and server sides. Also, it's not network intensive because only simple HTTP requests containing form data will be made to the server. POST is the recommended transfer method if you are working with specific cells.
• POST/any cell -- Because the specific cells that will be edited aren't known, this solution requires significantly more code on the client and server-side in order to function. There's a working sample included to illustrate how this works. Like the POST/specific-cell example, this approach is not network intensive.
• Upload/specific cell* -- This technique uploads the entire workbook to the server, where it is opened with ExcelWriter and parsed. There are two requirements for the upload technique: SoftArtisans XLWAssistant (included with ExcelWriter 4 Server edition), and since we will be uploading a file, SoftArtisans FileUp (not included). This technique is more network intensive than the POST samples because it requires that the entire workbook be uploaded to the server.
• Upload/any cell* -- This is the recommended transfer method if you are working with "any cells", where the specific cells are not known. It requires far less code on the client-side.

  * This feature is not available in ExcelWriterSE, ExcelWriterLE, and ExcelWriterFree.

Top

Simple POST Example: Editing Cells with Known Positions

This sample uses a form to update employee information in the Northwind Traders database. In Excel, edit the employee's first and last names, save the changes, and click Update to send the data back to the server. ExcelWriter is used to populate an Excel workbook with employee information from the database and stream the workbook to the client.

Run the Sample

The sample works as follows:

1. Form.asp loads and you can select an Employee ID for the individual whose information you would like to edit.
   
   
2. Form.asp submits data to GetEmployeeDataSheet.asp, which uses ExcelWriter's ExcelTemplate object to populate the form - Template.xls - with the employee's information. Template.xls contains a VBA subroutine that will fire when the user clicks Update:

   Sub btnUpdate_OnClick
   	'--- Code
   End Sub

   Note how the two values of the editable cells are read from the workbook:
   

   employeeID = Range("B8").Value
   firstName = Range("B9").Value
   lastName = Range("B10").Value

   This solution uses Microsoft's XMLHTTP object to perform the HTTP request:

   Set oHTTP = CreateObject("Msxml2.XMLHTTP")

3. In Excel, you can edit the employee's information. When you click Update, the VBA code reads the values from the two editable cells and submits it to the server script Update.asp in the form of standard HTML Form data.
   
   
4. Update.asp reads the submitted form data, forms a SQL UPDATE statement, and executes the query, updating the data source. The values submitted from the Excel VBA are handled on the server as form elements, using ASP's Request.Form:

   employeeID = Request.Form("employeeID")
   firstName = Request.Form("firstName")
   lastName = Request.Form("lastName")

   Note how the SQL statement is formed with the values submitted from the client:

   strSQL = "UPDATE Employees SET FirstName='" & firstName & _
   	"', LastName='" & lastName & _ 
   	"' WHERE EmployeeID = " & employeeID

Top

Copyright © 2003, SoftArtisans, Inc.