<Inserting data into a Spreadsheet ⁄ >

19491

2008-06-14

mySnippets asp.net

Download

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');

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
using System.Data.OleDb;
using System.Text;
using System.Collections;
 
/// <summary>
/// Class Excel
/// This class will allow us to insert values in an excel spreadsheet
/// </summary>
/// <author>pedrocorreia.net</author>
public class Excel {
    private string _filename;
    private string _sheetName;
    private ArrayList _fields = new ArrayList();
    private ArrayList _values = new ArrayList();
 
    /// <summary>
    /// Constructor method
    /// </summary>
    /// <param name="filename">Filename</param>
    /// <param name="sheetName">Sheet name</param>
    /// <param name="values">Values</param>
    /// <param name="fields">Columns/ Fields</param>
    /// <remarks>Values and Fields array sizes must match</remarks>
    public Excel(string filename, string sheetName, ArrayList values, ArrayList fields) {
        if (values.Count != fields.Count){
            throw new Exception("SIZE_ARRAY_FIELDS_VALUES_NOT_MATCH"); 
        }
        this.SheetName = sheetName;
        this.Filename = filename;
        this.Values = values;
        this.Fields = fields;
    }
 
    /// <summary>
    /// Getter/ Setter ArrayList Values
    /// </summary>
    public ArrayList Values { 
        set { this._values = value; } 
        get { return this._values; } 
    }
 
    /// <summary>
    /// Getter/ Setter ArrayList Fields
    /// </summary>
    public ArrayList Fields { 
        set { this._fields = value; } 
        get { return this._fields; } 
    }
 
    /// <summary>
    /// Getter/ Setter SheetName
    /// </summary>
    public string SheetName { 
        set { this._sheetName = value; } 
        get { return this._sheetName; } 
    }
 
    /// <summary>
    /// Getter/ Setter Filename
    /// </summary>
    public string Filename { 
        set { this._filename = value; } 
        get { return this._filename; } 
    }
 
    /// <summary>
    /// Insert values
    /// </summary>
    public void InsertRow(){
        if (this.Values.Count != this.Fields.Count){
            throw new Exception("SIZE_ARRAY_FIELDS_VALUES_NOT_MATCH");
        }
 
        StringBuilder sbFields = new StringBuilder();
        StringBuilder sbValuesParams = new StringBuilder();
        string iSQL, value, field;
        int numFields = this._fields.Count;
        OleDbConnection myConnection = new OleDbConnection(this._ConnectionString());
        OleDbCommand myCommand = new OleDbCommand();
 
        sbFields.Append("(");
        sbValuesParams.Append("(");
 
        for (int i = 0; i < numFields; i++){
            field = this._fields[i].ToString();
            value = this._values[i].ToString();
 
            //add field to sql instruction
            sbFields.Append(field);
            //add parameter to sql instruction
            sbValuesParams.Append(String.Format("@{0}", field));
            //fill parameter
            myCommand.Parameters.Add(new OleDbParameter(String.Format("@{0}", field), value));
 
            if (i < numFields - 1){
                sbFields.Append(", ");
                sbValuesParams.Append(", ");
            }
        }
        sbFields.Append(")");
        sbValuesParams.Append(")");
 
        //create sql insert instruction
        iSQL = String.Format(
            "Insert Into [{0}$] {1} Values {2}",
            this.SheetName,
            sbFields.ToString(),
            sbValuesParams.ToString()
        );
 
        //prepare OleDbCommand
        myCommand.Connection=myConnection;
        myCommand.CommandText=iSQL;
 
        try {
            //insert values and close connection
            myConnection.Open();
            myCommand.Connection = myConnection;
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        catch (Exception ex){
            myCommand.Dispose();
            myConnection.Close();
            //not recommend, but we'll just throw the exception again...
            throw new Exception(ex.Message.ToString());
        }
    }
 
    /// <summary>
    /// Get the Excel ConnectionString
    /// </summary>
    /// <returns>String</returns>
    private string _ConnectionString() {
        return String.Format(
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";" +
            "Extended Properties=\"Excel 8.0;HDR=Yes;\"",
            this.Filename);
    }
}

Now we'll create a simple aspx file with just one label so that the user gets a message:

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Default.aspx.cs" Inherits="Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Inserting Data into an Excel Spreadsheet</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblResult" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

This will be our code-behind file:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
 
public partial class Default : System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e) {
        string xlsFilename = Server.MapPath("~/my_files/my_file.xls"); //xls filename
        string xlsSheetName = "MySheet"; //xls spreadsheet
        ArrayList xlsFields = new ArrayList(); //arraylist with the fields/columns names
        ArrayList xlsValues = new ArrayList(); //arraylist with the values
        StringBuilder str = new StringBuilder();
 
        //field names
        xlsFields.Add("id");
        xlsFields.Add("name");
        xlsFields.Add("address");
        xlsFields.Add("phone");
        //
 
        //field values
        xlsValues.Add("1"); //field: id
        xlsValues.Add("pedrocorreia.net"); //field: name
        xlsValues.Add("This is my Address, Av. 456"); //field: address
        xlsValues.Add("(351) 98 765 43 21"); //field: phone
        //
 
        //create object
        Excel xls = new Excel(xlsFilename, xlsSheetName, xlsValues, xlsFields);
 
        //insert record
        try{
            xls.InsertRow();
            str.Append("One record added successfully");
        }
        catch (Exception ex){
            //please take a note that a more elaborated catch should be done,
            //this is just an example
            str.Append("An error has occured with the following description: " + ex.Message.ToString());
        }
 
 
 
        //let's add another record, since we already specified the field array
        //we only have to specifcy the new values
        xlsValues.Clear(); //clear the values array
        xlsValues.Add("2"); //field: id
        xlsValues.Add("Other Name"); //field: name
        xlsValues.Add("This is my Other Address, Av. 789"); //field: address
        xlsValues.Add("(351) 12 345 67 89"); //field: phone
        xls.Values = xlsValues; //specify new values
 
        try{ //insert record
            xls.InsertRow();
            str.Append("<br/>One more record added successfully");
        }
        catch (Exception ex){
            //please take a note that a more elaborated catch should be done,
            //this is just an example
            str.Append("<br/>An error has occured with the following description: " + ex.Message.ToString());
        }
 
        lblResult.Text = str.ToString();
    }
}

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

19491

2008-06-14

mySnippets asp.net

Download

Bookmark This