<Inserting data into a Spreadsheet ⁄ >

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

<%@ 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>

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();
    }
}