In order to create multiple sheets in MS Excel file, we need to connect the MS Excel using Oledb database provider. To connect the MS Excel engine through OledDb, we must have
If you do not install this Data connectivity component, you may end up getting following error.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
So
download it and install on the machine where your code would be running.
Lets write the code now
Connection string to be used to connect to MS Excel engine through C# is following.
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=E:\\Z-Test\\WebSite7\\MultipleExcelSheet.xlsx;" +
"Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"";Remember that if you are keeping this connection string in the web.config and retrieving it through ConfigurationManager, some encoding and decoding error may occur so we have written it directly in the C# code.
The actual code that will create an excel sheet and insert record into the excel sheet is following
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
// creates the excel sheet
string sheetSql1 = "CREATE TABLE [MySheet1] (id varchar(50), name varchar(50), BirthDate varchar(50))";
using (OleDbCommand cmd = new OleDbCommand(sheetSql1, conn))
{
cmd.ExecuteNonQuery();
}
// now add records into the excel sheet just created
sheetSql1 = "INSERT INTO [MySheet1] (id, name, BirthDate) VALUES (@id, @name, @BirthDate)";
using (OleDbCommand cmd = new OleDbCommand(sheetSql1, conn))
{
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@name", "Sheo Narayan");
cmd.Parameters.AddWithValue("@BirthDate", "02/02/2012");
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = new OleDbCommand(sheetSql1, conn))
{
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@name", "Ramesh Narayan");
cmd.Parameters.AddWithValue("@BirthDate", "02/04/2012");
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = new OleDbCommand(sheetSql1, conn))
{
cmd.Parameters.AddWithValue("@id", 1);
cmd.Parameters.AddWithValue("@name", "Shreeharsh Narayan");
cmd.Parameters.AddWithValue("@BirthDate", "02/02/2011");
cmd.ExecuteNonQuery();
}
//////////////////////////////////////
// create second sheet
string sheetSql2 = "CREATE TABLE [MySheet2] (AutoId varchar(50), FullName varchar(50), Age varchar(50))";
using (OleDbCommand cmd = new OleDbCommand(sheetSql2, conn))
{
cmd.ExecuteNonQuery();
}
// insert record into the second sheet
sheetSql2 = "INSERT INTO [MySheet2] (AutoId, FullName, Age) VALUES (@AutoId, @FullName, @Age)";
using (OleDbCommand cmd = new OleDbCommand(sheetSql2, conn))
{
cmd.Parameters.AddWithValue("@AutoId", 1);
cmd.Parameters.AddWithValue("@FullName", "Shreeharsh Narayan");
cmd.Parameters.AddWithValue("@Age", "20");
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = new OleDbCommand(sheetSql2, conn))
{
cmd.Parameters.AddWithValue("@AutoId", 2);
cmd.Parameters.AddWithValue("@FullName", "RameshNNarayan");
cmd.Parameters.AddWithValue("@Age", "25");
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = new OleDbCommand(sheetSql2, conn))
{
cmd.Parameters.AddWithValue("@AutoId", 3);
cmd.Parameters.AddWithValue("@FullName", "N Narayan");
cmd.Parameters.AddWithValue("@Age", "59");
cmd.ExecuteNonQuery();
}
conn.Close();
}In the above code snippet, we have created an instance of the
OleDbConnection and
OleDbCommand and attached the connection string and sql to execute.
The Sql to execute are following
- sheetSql1 - "CREATE ...." is used to create a excel sheet (table) named "MySheet1" with the column and their data type defined.
- sheetSql1 - "INSERT ...." is used to insert record into this sheet (table)
Notice that while inserting record, we have instantiated the OleDbCommand separately for each record as setting different values for fields into the same instance of OledDbCommand doesn't create new record each time.
Similarly, we have done the same for the second sheet. At last we have closed the connection.
Hope this example would be useful. Thanks for reading. If you are looking for real time how to tutorials of ASP.NET MVC,
click here.