C# - How to export database tables to Excel File dynamically in C#

Scenario : Download Script

You are working as Dot Net or C# Developer. You got this requirement where you have to Export all the Tables from a SQL Server Database to Excel File. Each Table should be export as SchemaName_TableName as Sheet to single Excel File.

So let's say you have 10 user tables in a Database and you would like to export all of them. The C# program should be able to create an Excel File with 10 sheets and load data from each table to separate excel sheet.

Below script can be used to export all the tables from a database to excel file. Provide the database name, Excel file name and path where it should be created in variables and you are all set.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//added below name spaces
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            //the datetime and Log folder will be used for error log file in case error occured
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {
                //Declare Variables
                //Provide the excel file name you like to create
                string ExcelFileName = "TechBrothersIT";
                //Provide the folder in which you would like to create Excel file
                string FolderPath = @"C:\Destination\";
                string DatabaseName = "TechBrothersIT";
                ExcelFileName = ExcelFileName + "_" + datetime;


                OleDbConnection Excel_OLE_Con = new OleDbConnection();
                OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                //Construct ConnectionString for Excel
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                //drop Excel file if exists
                File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

                //Create Connection to SQL Server Database from which you like to export tables to Excel
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog ="+DatabaseName+"; " + "Integrated Security=true;";


                //Read list of Tables with Schema from Database that you want to write to Excel file
                string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName FROM   sys.tables WHERE  is_ms_shipped = 0";

               
                SqlCommand cmd = new SqlCommand(query, SQLConnection);
                SQLConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                SQLConnection.Close();

                //Loop through datatable(dt) that has schema and table names
                foreach (DataRow dt_row in dt.Rows)
                {
                    string SchemaName = "";
                    string TableName = "";
                    object[] array = dt_row.ItemArray;
                    SchemaName = array[0].ToString();
                    TableName = array[1].ToString();

                    //Load Data into DataTable from SQL ServerTable
                    string queryString =
                      "SELECT * from " + SchemaName + "." + TableName;
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, SQLConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);


                    //Get Header Columns
                    string TableColumns = "";

                    // Get the Column List from Data Table so can create Excel Sheet with Header
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataColumn column in table.Columns)
                        {
                            TableColumns += column + "],[";
                        }
                    }

                    // Replace most right comma from Columnlist
                    TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
                    TableColumns = TableColumns.Remove(TableColumns.Length - 2);

                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                    Excel_OLE_Cmd.CommandText = "Create table [" + SchemaName 
                                                 + "_" + TableName + "] (" 
                                                 + TableColumns + ")";
                    Excel_OLE_Cmd.ExecuteNonQuery();


                    //Write Data to Excel Sheet from DataTable dynamically
                    foreach (DataTable table in ds.Tables)
                    {
                        String sqlCommandInsert = "";
                        String sqlCommandValue = "";
                        foreach (DataColumn dataColumn in table.Columns)
                        {
                            sqlCommandValue += dataColumn + "],[";
                        }

                        sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                        sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                        sqlCommandInsert = "INSERT into [" + SchemaName + "_" + TableName + "] (" + sqlCommandValue + ") VALUES(";

                        int columnCount = table.Columns.Count;
                        foreach (DataRow row in table.Rows)
                        {
                            string columnvalues = "";
                            for (int i = 0; i < columnCount; i++)
                            {
                                int index = table.Rows.IndexOf(row);
                                columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                            }
                            columnvalues = columnvalues.TrimEnd(',');
                            var command = sqlCommandInsert + columnvalues + ")";
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();
                        }

                    }
                    Excel_OLE_Con.Close();
                }
            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

I executed above program and it generated an Excel file with number of sheets equal to tables in my database and export data to those sheets from tables as shown below.

How to Export Multiple Tables from a database to Excel file in C#