<pedrocorreia.net ⁄>
corner
<mySearch ⁄> <mySearch ⁄>

corner
 
corner
<mySnippets order="rand" ⁄> <mySnippets order="rand" ⁄>

corner
 
corner
<myContacts ⁄> <myContacts ⁄>

<email ⁄>


pc@pedrocorreia.net

<windows live messenger ⁄>


pedrojacorreia@hotmail.com

<myCurriculum type="pdf" ⁄>


Download
corner
 
corner
<myBlog show="last" ⁄> <myBlog show="last" ⁄>

corner
 
corner
<myNews show="rand" ⁄> <myNews show="rand" ⁄>

corner
 
corner
<myNews type="cat" ⁄> <myNews type="cat" ⁄>

corner
 
corner
<myQuote order="random" ⁄> <myQuote order="random" ⁄>

corner
 
corner
<myPhoto order="random" ⁄> <myPhoto order="random" ⁄>

<pedrocorreia.net ⁄>
corner
 
corner
<myAdSense ⁄> <myAdSense ⁄>

corner
 
corner
<myVisitorsMap ⁄> <myVisitorsMap ⁄>

corner
 
 

<Inserting data into a Spreadsheet ⁄ >




clicks: 17874 17874 2008-06-14 2008-06-14 goto mySnippets mySnippets asp.net  Download  Bookmark This Bookmark This



Spreadsheets, so well known for Microsoft Excel or OpenOffice Calc, are one of the most powerful tools used in *Office* suites (such as Microsoft Office, OpenOffice, StarOffice, [your preferred suite here], ...). In this snippet I'll try to demonstrate a possible way to insert values in that type of files, in this case, xls files.



This snippet will be based in a few assumptions:
- a worksheet name's necessary (I'll explain this later on)
- worksheet must have columns names, although it's not strictly necessary, in this snippet I'll be using a schema which will be based in field names (I'll explain this later on);


We can see all this operations just a normal database, on which we'll have:
- a database name: in this case it'll be the xls filename;
- a data table: in this case, it'll be the worksheet name;
- some fields: in this case it'll be the column names;

Let's suppose we have the following structure:


MySheet (id, name, address, phone);

Worksheet name=> MySheet

Column names=> id, name, address, phone



Here's a screenshot:



We'll use Microsoft Jet OLEDB 4.0, for instance you can also use this to interact with Microsoft Access databases, this lets you play with more things.

Excel.cs, this class will be responsible for the communication with the xls file, it will establish de connection, insert the values and close the connection.

Please just take a note at the private method that returns the ConnectionString. On "Extended Properties" you'll see the following setting: "HDR=Yes;", well what's this for? Basically tells to the Jet Engine that the first row will be treated as the column names, if we had "HDR=No;", the column names would be, for example: A1, B1, C1, ...,Xn1
This allows us, for example, to do something like this: insert into [MySheet$] (id, name) values (1,'pedrocorreia.net');

  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Web;
  5. using System.Web.Security;
  6. using System.Web.UI;
  7. using System.Web.UI.WebControls;
  8. using System.Web.UI.WebControls.WebParts;
  9. using System.Web.UI.HtmlControls;
  10.  
  11. using System.Data.OleDb;
  12. using System.Text;
  13. using System.Collections;
  14.  
  15. /// <summary>
  16. /// Class Excel
  17. /// This class will allow us to insert values in an excel spreadsheet
  18. /// </summary>
  19. /// <author>pedrocorreia.net</author>
  20. public class Excel {
  21. private string _filename;
  22. private string _sheetName;
  23. private ArrayList _fields = new ArrayList();
  24. private ArrayList _values = new ArrayList();
  25.  
  26. /// <summary>
  27. /// Constructor method
  28. /// </summary>
  29. /// <param name="filename">Filename</param>
  30. /// <param name="sheetName">Sheet name</param>
  31. /// <param name="values">Values</param>
  32. /// <param name="fields">Columns/ Fields</param>
  33. /// <remarks>Values and Fields array sizes must match</remarks>
  34. public Excel(string filename, string sheetName, ArrayList values, ArrayList fields) {
  35. if (values.Count != fields.Count){
  36. throw new Exception("SIZE_ARRAY_FIELDS_VALUES_NOT_MATCH");
  37. }
  38. this.SheetName = sheetName;
  39. this.Filename = filename;
  40. this.Values = values;
  41. this.Fields = fields;
  42. }
  43.  
  44. /// <summary>
  45. /// Getter/ Setter ArrayList Values
  46. /// </summary>
  47. public ArrayList Values {
  48. set { this._values = value; }
  49. get { return this._values; }
  50. }
  51.  
  52. /// <summary>
  53. /// Getter/ Setter ArrayList Fields
  54. /// </summary>
  55. public ArrayList Fields {
  56. set { this._fields = value; }
  57. get { return this._fields; }
  58. }
  59.  
  60. /// <summary>
  61. /// Getter/ Setter SheetName
  62. /// </summary>
  63. public string SheetName {
  64. set { this._sheetName = value; }
  65. get { return this._sheetName; }
  66. }
  67.  
  68. /// <summary>
  69. /// Getter/ Setter Filename
  70. /// </summary>
  71. public string Filename {
  72. set { this._filename = value; }
  73. get { return this._filename; }
  74. }
  75.  
  76. /// <summary>
  77. /// Insert values
  78. /// </summary>
  79. public void InsertRow(){
  80. if (this.Values.Count != this.Fields.Count){
  81. throw new Exception("SIZE_ARRAY_FIELDS_VALUES_NOT_MATCH");
  82. }
  83.  
  84. StringBuilder sbFields = new StringBuilder();
  85. StringBuilder sbValuesParams = new StringBuilder();
  86. string iSQL, value, field;
  87. int numFields = this._fields.Count;
  88. OleDbConnection myConnection = new OleDbConnection(this._ConnectionString());
  89. OleDbCommand myCommand = new OleDbCommand();
  90.  
  91. sbFields.Append("(");
  92. sbValuesParams.Append("(");
  93.  
  94. for (int i = 0; i < numFields; i++){
  95. field = this._fields[i].ToString();
  96. value = this._values[i].ToString();
  97.  
  98. //add field to sql instruction
  99. sbFields.Append(field);
  100. //add parameter to sql instruction
  101. sbValuesParams.Append(String.Format("@{0}", field));
  102. //fill parameter
  103. myCommand.Parameters.Add(new OleDbParameter(String.Format("@{0}", field), value));
  104.  
  105. if (i < numFields - 1){
  106. sbFields.Append(", ");
  107. sbValuesParams.Append(", ");
  108. }
  109. }
  110. sbFields.Append(")");
  111. sbValuesParams.Append(")");
  112.  
  113. //create sql insert instruction
  114. iSQL = String.Format(
  115. "Insert Into [{0}$] {1} Values {2}",
  116. this.SheetName,
  117. sbFields.ToString(),
  118. sbValuesParams.ToString()
  119. );
  120.  
  121. //prepare OleDbCommand
  122. myCommand.Connection=myConnection;
  123. myCommand.CommandText=iSQL;
  124.  
  125. try {
  126. //insert values and close connection
  127. myConnection.Open();
  128. myCommand.Connection = myConnection;
  129. myCommand.ExecuteNonQuery();
  130. myConnection.Close();
  131. }
  132. catch (Exception ex){
  133. myCommand.Dispose();
  134. myConnection.Close();
  135. //not recommend, but we'll just throw the exception again...
  136. throw new Exception(ex.Message.ToString());
  137. }
  138. }
  139.  
  140. /// <summary>
  141. /// Get the Excel ConnectionString
  142. /// </summary>
  143. /// <returns>String</returns>
  144. private string _ConnectionString() {
  145. return String.Format(
  146. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";" +
  147. "Extended Properties=\"Excel 8.0;HDR=Yes;\"",
  148. this.Filename);
  149. }
  150. }








Now we'll create a simple aspx file with just one label so that the user gets a message:
  1. <%@ Page Language="C#" AutoEventWireup="true"
  2. CodeFile="Default.aspx.cs" Inherits="Default" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  4. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5. <html xmlns="http://www.w3.org/1999/xhtml" >
  6. <head runat="server">
  7. <title>Inserting Data into an Excel Spreadsheet</title>
  8. </head>
  9. <body>
  10. <form id="form1" runat="server">
  11. <div>
  12. <asp:Label ID="lblResult" runat="server" Text="Label"></asp:Label>
  13. </div>
  14. </form>
  15. </body>
  16. </html>




This will be our code-behind file:
  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Collections;
  5. using System.Web;
  6. using System.Web.Security;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  9. using System.Web.UI.WebControls.WebParts;
  10. using System.Web.UI.HtmlControls;
  11. using System.Text;
  12.  
  13. public partial class Default : System.Web.UI.Page {
  14. protected void Page_Load(object sender, EventArgs e) {
  15. string xlsFilename = Server.MapPath("~/my_files/my_file.xls"); //xls filename
  16. string xlsSheetName = "MySheet"; //xls spreadsheet
  17. ArrayList xlsFields = new ArrayList(); //arraylist with the fields/columns names
  18. ArrayList xlsValues = new ArrayList(); //arraylist with the values
  19. StringBuilder str = new StringBuilder();
  20.  
  21. //field names
  22. xlsFields.Add("id");
  23. xlsFields.Add("name");
  24. xlsFields.Add("address");
  25. xlsFields.Add("phone");
  26. //
  27.  
  28. //field values
  29. xlsValues.Add("1"); //field: id
  30. xlsValues.Add("pedrocorreia.net"); //field: name
  31. xlsValues.Add("This is my Address, Av. 456"); //field: address
  32. xlsValues.Add("(351) 98 765 43 21"); //field: phone
  33. //
  34.  
  35. //create object
  36. Excel xls = new Excel(xlsFilename, xlsSheetName, xlsValues, xlsFields);
  37.  
  38. //insert record
  39. try{
  40. xls.InsertRow();
  41. str.Append("One record added successfully");
  42. }
  43. catch (Exception ex){
  44. //please take a note that a more elaborated catch should be done,
  45. //this is just an example
  46. str.Append("An error has occured with the following description: " + ex.Message.ToString());
  47. }
  48.  
  49.  
  50.  
  51. //let's add another record, since we already specified the field array
  52. //we only have to specifcy the new values
  53. xlsValues.Clear(); //clear the values array
  54. xlsValues.Add("2"); //field: id
  55. xlsValues.Add("Other Name"); //field: name
  56. xlsValues.Add("This is my Other Address, Av. 789"); //field: address
  57. xlsValues.Add("(351) 12 345 67 89"); //field: phone
  58. xls.Values = xlsValues; //specify new values
  59.  
  60. try{ //insert record
  61. xls.InsertRow();
  62. str.Append("<br/>One more record added successfully");
  63. }
  64. catch (Exception ex){
  65. //please take a note that a more elaborated catch should be done,
  66. //this is just an example
  67. str.Append("<br/>An error has occured with the following description: " + ex.Message.ToString());
  68. }
  69.  
  70. lblResult.Text = str.ToString();
  71. }
  72. }





After running this example script, you can open xls file and (hopefully :)) see something like this:







As you can imagine, if you can see this is a simple database, you'll be allowed to make queries; to update data; etc, etc, in a future snippet I'll try to demonstrate a simple way to update data.


Just some last minute notes, the "ASP.NET Machine Account" must have write permissions to our xls file, only giving write permissions to "Internet Guest Account" won't be enough; in this snippet I almost didn't care about exceptions (yes, shame on me ^_^''), although it's an important thing to care about.



If you have any doubt or found any error, please drop me an email









clicks: 17874 17874 2008-06-14 2008-06-14 goto mySnippets mySnippets asp.net  Download  Bookmark This Bookmark This