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.
}