See What is Hot Cell Technology? for more information. |
HotCell Technology allows you to use client-side Excel workbooks to communicate with servers to perform tasks such as database updates.
This demo downloads a workbook of employee information populated by ExcelTemplate. From within Excel, edit employee address information and update the server-side database. |
//-------------------------------------------------------------- //--- SoftArtisans OfficeWriter HotCell Basic Form Update Sample //--- //--- HotCells allow you to submit data from your Excel worksheet //--- directly back to the database on the server. This allows //--- you to use Excel as a rich client web form. //--- //--- In addition to this servlet code, //--- look at the VBA code in the Excel template workbook. //--- //--- This page displays the web form and generates the //--- Employee Data workbook with ExcelTemplate //--- //--- (c) 2009 SoftArtisans, Inc. //--- Support: http://support.softartisans.com //--- Sales: sales@softartisans.com //-------------------------------------------------------------- using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using SoftArtisans.OfficeWriter.ExcelWriter; namespace SoftArtisans.OfficeWriter.HotCell.Samples { /// <summary> /// Summary description for GetEmployeeDataSheet. /// </summary> public class GetEmployeeDataSheet : System.Web.UI.Page { private int employeeId; private string connString = System.Configuration.ConfigurationManager.AppSettings["connString"]; protected System.Web.UI.HtmlControls.HtmlSelect drpEmployee; protected System.Web.UI.HtmlControls.HtmlInputButton btnDataMarkers; private void btnDataMarkers_ServerClick(object sender, System.EventArgs e) { this.employeeId = Convert.ToInt32(drpEmployee.Value); GenerateEmployeeDataSheet(); } /// <summary> /// Get the employee data sheet HotCell workbook from which database updates can be made /// </summary> private void GenerateEmployeeDataSheet() { /* Use ExcelTemplate to populate the employee data sheet HotCell template * and stream it to the client */ DataTable EmployeeDt = GetEmployeeData(); /* Create an instance of ExcelTemplate */ ExcelTemplate xlt = new ExcelTemplate(); /* Set PreserveStrings = true to make Excel treat * numeric strings as text */ xlt.PreserveStrings = true; /* Open the template workbook */ string templatePath = Page.MapPath("./BasicFormUpdateTemplate.xls");; xlt.Open(templatePath); /* Bind the Employee DataTable to the template * %%=Emp.* */ DataBindingProperties bindingProperties = xlt.CreateDataBindingProperties(); xlt.BindData(EmployeeDt, "Emp", bindingProperties); /* Set the postURL in a hidden cell in the workbook * This is the URL to which the HotCell workbook should post * database updates. Use this technique to be sure that the * HotCell workbook always posts back to the correct server */ string fullUrl = Page.Request.Url.ToString(); string postUrl = fullUrl.Substring(0, fullUrl.LastIndexOf("/")) + "/DatabaseUpdate.asmx/UpdateDatabase"; xlt.BindCellData(postUrl, "HotCellPostUrl", bindingProperties); /* Process the template to populate real data * into the data markers */ xlt.Process(); /* Stream the populated HotCell template to the client * response: Current instance of HttpServletResponse to stream the book * "EmployeeForm.xls": File name for the SaveAs dialog * false: do not open in browser plug in, open the workbook in Excel */ xlt.Save(Page.Response, "EmployeeForm.xls", false); } /// <summary> /// Query the database for employee information /// </summary> /// <returns>DataTable of employee info for the selected employee ID</returns> private DataTable GetEmployeeData() { /* SQL to retrieve details for the selected employee */ string sql = "SELECT HumanResources.Employee.EmployeeID, " + "Person.Contact.FirstName, Person.Contact.LastName, " + "Person.Contact.Title, Person.Address.AddressLine1 As Address, " + "Person.Address.City, Person.StateProvince.StateProvinceCode As State, " + "Person.Address.PostalCode, Person.Contact.Phone FROM HumanResources.Employee " + "INNER JOIN Person.Contact ON Person.Contact.ContactID=HumanResources.Employee.ContactID " + "INNER JOIN HumanResources.EmployeeAddress " + "ON HumanResources.EmployeeAddress.EmployeeID=HumanResources.Employee.EmployeeID " + "INNER JOIN Person.Address ON Person.Address.AddressID=HumanResources.EmployeeAddress.AddressID " + "INNER JOIN Person.StateProvince ON Person.StateProvince.StateProvinceID=Person.Address.StateProvinceID " + "WHERE HumanResources.Employee.EmployeeID = @employeeId"; DataTable dt = new DataTable(); using(SqlConnection conn = new SqlConnection(this.connString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@employeeId", this.employeeId); SqlDataAdapter adpt = new SqlDataAdapter(cmd); adpt.Fill(dt); } return dt; } private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.btnDataMarkers.ServerClick += new System.EventHandler(this.btnDataMarkers_ServerClick); this.Load += new System.EventHandler(this.Page_Load); } #endregion } } |
//-------------------------------------------------------------- //--- SoftArtisans OfficeWriter HotCell Basic Form Update Sample //--- //--- HotCells allow you to submit data from your Excel worksheet //--- directly back to the database on the server. This allows //--- you to use Excel as a rich client web form. //--- //--- In addition to this servlet code, //--- look at the VBA code in the Excel template workbook. //--- //--- This webservice is called by the HotCell workbook to update //--- employee information in the database //--- //--- (c) 2009 SoftArtisans, Inc. //--- Support: http://support.softartisans.com //--- Sales: sales@softartisans.com //-------------------------------------------------------------- using System; using System.ComponentModel; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web.Services; namespace SoftArtisans.OfficeWriter.HotCell.Samples { /// <summary> /// Updates a record in the Address table for a specific employee /// </summary> public class DatabaseUpdate : System.Web.Services.WebService { private string connString = System.Configuration.ConfigurationManager.AppSettings["connString"]; public DatabaseUpdate() { //CODEGEN: This call is required by the ASP.NET Web Services Designer InitializeComponent(); } /// <summary> /// Update Address information for an employee. /// </summary> /// <param name="employeeId">ID of the employee to update</param> /// <param name="address">Updated address</param> /// <param name="city">Updated city</param> /// <param name="stateCode">Updated 2 character state code</param> /// <param name="postalCode">Updated zip</param> /// <param name="phone">Updated phone number</param> [WebMethod] public void UpdateDatabase(int employeeId, string address, string city, string stateCode, string postalCode, string phone) { /*Validate state value against database*/ string strInvalidState=""; /*This SELECT query will verify that state code exists in database*/ string strValidState = "SELECT StateProvinceID FROM Person.StateProvince " + "WHERE StateProvinceCode = @stateCode"; /*Create SqlConnection and SqlCommand to handle select query */ SqlConnection ConnState = new SqlConnection(connString); SqlCommand IsStateCmd = new SqlCommand(strValidState, ConnState); IsStateCmd.Parameters.AddWithValue("@stateCode", stateCode); /* Open the connection and execute the query */ ConnState.Open(); SqlDataReader dr = IsStateCmd.ExecuteReader(CommandBehavior.CloseConnection); /* No returned rows indicates that entered state does not exist in database and invalid */ if(!dr.HasRows) strInvalidState="invalid state entered"; dr.Close(); ConnState.Close(); if(strInvalidState!="") { strInvalidState=strInvalidState; throw new Exception(strInvalidState); } /* Create two UPDATE queries to update the two tables in the database with the changed values */ string UpdateSQL = "UPDATE Person.Address SET AddressLine1 = @address, City = @city, " + "StateProvinceId = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = @stateCode), " + "PostalCode = @postalCode " + "WHERE AddressID = (SELECT AddressID FROM HumanResources.EmployeeAddress WHERE EmployeeID = @employeeId)"; string UpdatePhoneSQL = "UPDATE Person.Contact SET Phone = @phone " + "WHERE ContactID = (SELECT ContactID FROM HumanResources.Employee WHERE EmployeeID = @employeeId)"; using(SqlConnection Conn = new SqlConnection(connString)) { /* Create an SqlCommand to handle the update query */ SqlCommand UpdateCmd = new SqlCommand(UpdateSQL, Conn); /* The Phone field is in a different table, so we need to send a second query */ SqlCommand PhoneUpdateCmd = new SqlCommand(UpdatePhoneSQL, Conn); /* Add Parameters for every updateable value in the query */ UpdateCmd.Parameters.AddWithValue("@address", address); UpdateCmd.Parameters.AddWithValue("@city", city); UpdateCmd.Parameters.AddWithValue("@stateCode", stateCode); UpdateCmd.Parameters.AddWithValue("@postalCode", postalCode); UpdateCmd.Parameters.AddWithValue("@employeeId", employeeId); PhoneUpdateCmd.Parameters.AddWithValue("@phone", phone); PhoneUpdateCmd.Parameters.AddWithValue("@employeeId", employeeId); /* Open the connection and execute the query */ Conn.Open(); int rowsAffected = UpdateCmd.ExecuteNonQuery(); PhoneUpdateCmd.ExecuteNonQuery(); Conn.Close(); if(rowsAffected < 1) throw new Exception("Rows updated was: " + rowsAffected.ToString()); return; } } #region Component Designer generated code //Required by the Web Services Designer private IContainer components = null; /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { } /// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if(disposing && components != null) { components.Dispose(); } base.Dispose(disposing); } #endregion } } |
Template: BasicFormUpdateTemplate.xls Output: SampleOutput.xls |