<email ⁄>
<windows live messenger ⁄>
<myCurriculum type="pdf" ⁄>
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(); } }