learned/learning/to learn RSS 2.0
# Thursday, 16 April 2009

Select or Insert or Update or Delete rows in Excel using .NET. i.e., CRUD operations in EXCEL using .NET. We can literally use the excel sheet as a database, just the connection string matters. Note: 1. This code cannot be used for password protected excel sheets. 2. Delete does not work. See below for explanations. There might be some other good way to do this, if so, let me know.

 

Insert a row in an excel sheet

 

protected void btn_create_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    // and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                            Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    // The sheet name should be followed by $ sign

    objCommand.CommandText = "INSERT INTO [SHEETNAME$](COLUMN_INT, COLUMN_VARCHAR) VALUES(@COLUMN_INT, @COLUMN_VARCHAR)";

 

    objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

    objParameter.Value = 3;

    objCommand.Parameters.Add(objParameter);

 

    objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

    objParameter.Value = "testing";

    objCommand.Parameters.Add(objParameter);

 

    objConnection.Open();

    objCommand.ExecuteNonQuery();

    objConnection.Close();

}

 

Select rows in an Excel sheet

 

protected void btn_read_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    //and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                                Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    // If the excel sheet you are dealing with does not have header, your query will be SELECT F1, F2 FROM [SHEETNAME$]

    objCommand.CommandText = "SELECT COLUMN_INT as COLUMN1, COLUMN_VARCHAR AS COLUMN2 FROM [SHEETNAME$]";

 

    objDataAdapter = new OleDbDataAdapter();

    objDataSet = new DataSet();

    objDataAdapter.SelectCommand = objCommand;

 

    objConnection.Open();

    objDataAdapter.Fill(objDataSet);

    objConnection.Close();

}

 

Update a row in an excel sheet

 

protected void btn_update_Click(object sender, EventArgs e)

{

    OleDbConnection objConnection;

    OleDbCommand objCommand;

    OleDbParameter objParameter;

 

    // Here HDR= YES represents that header is present

    // If the excelsheet you are dealing with does not have header, you can mention HDR = NO,

    // and while fetching columns you have to mention F1, F2 rather than the column names

    objConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Excel\CRUD_EXCEL.xls;

                                                                                                            Extended Properties=""Excel 8.0;HDR=YES;""");

    objCommand = new OleDbCommand();

 

    objCommand.Connection = objConnection;

    objCommand.CommandType = CommandType.Text;

 

    objCommand.CommandText = "UPDATE [SHEETNAME$] SET COLUMN_VARCHAR =  @COLUMN_VARCHAR WHERE COLUMN_INT = @COLUMN_INT";

 

    objParameter = new OleDbParameter("@COLUMN_VARCHAR", OleDbType.VarChar);

    objParameter.Value = "PQR";

    objCommand.Parameters.Add(objParameter);

 

    objParameter = new OleDbParameter("@COLUMN_INT", OleDbType.Integer);

    objParameter.Value = 3;

    objCommand.Parameters.Add(objParameter);

 

    objConnection.Open();

    objCommand.ExecuteNonQuery();

    objConnection.Close();

}

 

Delete

 

protected void btn_delete_Click(object sender, EventArgs e)

{

        As far as I know ADO does not support Excel row delete

 

        You are more restricted in deleting Excel data than data from a relational data source.

        In a relational database, "row" has no meaning or existence apart from "record";

        in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

 

        1. Delete an entire record at once or you receive the following error message:

            Deleting data in a linked table is not supported by this ISAM.

            You can only delete a record by blanking out the contents of each individual field.

        2. Delete the value in a cell containing an Excel formula or you receive the following error message:

            Operation is not allowed in this context.

        3. You cannot delete the empty spreadsheet row(s) in which the deleted data was located,

            and your recordset will continue to display empty records corresponding to these empty rows.

}

 

Thursday, 16 April 2009 18:10:39 (GMT Daylight Time, UTC+01:00)  #    Comments [0] -
.NET
Comments are closed.
Navigation
Archive
<2017 August>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Gokulnath
Sign In
Statistics
Total Posts: 41
This Year: 0
This Month: 0
This Week: 0
Comments: 47
Themes
Pick a theme:
All Content © 2017, Gokulnath
DasBlog theme 'Business' created by Christoph De Baene (delarou)
The new movement has made a number of changes. First, precise instantaneous jump calendar display tag heuer replica large switching time is scheduled for midnight. The power required for this process will slowly build up within hours. Furthermore, LANGE 1 escapement now available eccentric balance weight balance wheel and homemade free omega uk watch sprung. Means provided in the hand-carved balance wheel splint omega replica underneath, 21,600 vibrations per hour. This table also hublot replica retains the reliable double-barrel, power reserve of 72 hours.