How to create multiple sheets into MS Excel programmatically in C#?

Sheonarayan
Posted by in C# category on for Advance level | Points: 250 | Views : 3693 red flag

In this article, we shall learn how to create multiple sheet into ms excel file programmatically in C#.


 Download source code for How to create multiple sheets into MS Excel programmatically in C#?

Recommendation
Read How to read MS Excel file and populate into GridView before this article.

Introduction

In previous articles, we have already learnt how to generate MS Excel file and read MS Excel file in C#. In this article, we shall learn how to create multiple sheets in MS Excel file programmatically using C#.

Background

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 007 Office System Driver: Data Connectivity Components installed on our system. 

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
  1. sheetSql1 - "CREATE ...." is used to create a excel sheet (table) named "MySheet1" with the column and their data type defined.
  2. 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.
Recommendation
Read How to work with Transactions in ASP.NET MVC ? after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)