Export DataSet and DataTable to Excel with C#

Saving DataSet or DataTable instace to an Excel spreadsheet with C#

Some time ago I wrote an article on how to export data to Excel from C# code. Essentially it exports data to file which can be opened in Excel but it does not produce the actual Microsoft Office Excel document.

I recently wrote an article onĀ Working with Excel files in .NET without using COM in C#, so I decided to use this approach to create extension methods for DataTable and DataSet classes. The approach is quite simple and it is based on looping though DataTable structure and executing CRUD commands though Microsoft OLEDB driver.

The method is written as a extension method which makes it super easy to use with any instance of DataTable in the code as long as the namespace where extension method is delacred is in use in the code.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.IO;
using System.Data.OleDb;

namespace ExcelExport
{
    public static class Extensions
    {
        public static void ExportToExcel(this DataTable dataTable, String filePath, bool overwiteFile = true)
        {
            if (File.Exists(filePath) && overwiteFile)
            {
                File.Delete(filePath);
            }

            var conn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=Yes;IMEX=0';";
            using (OleDbConnection connection = new OleDbConnection(conn))
            {
                connection.Open();
                using (OleDbCommand command = new OleDbCommand())
                {
                    command.Connection = connection;

                    List<String> columnNames = new List<string>();
                    foreach (DataColumn dataColumn in dataTable.Columns)
                    {
                        columnNames.Add(dataColumn.ColumnName);
                    }

                    String tableName = !String.IsNullOrWhiteSpace(dataTable.TableName) ? dataTable.TableName : Guid.NewGuid().ToString();
                    command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.Select(c => $"[{c}] VARCHAR").ToArray())});";
                    command.ExecuteNonQuery();


                    foreach (DataRow row in dataTable.Rows)
                    {
                        List<String> rowValues = new List<string>();
                        foreach (DataColumn column in dataTable.Columns)
                        {
                            rowValues.Add((row[column] != null && row[column] != DBNull.Value) ? row[column].ToString() : String.Empty);
                        }
                        command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.Select(c => $"[{c}]"))}) VALUES ({String.Join(",", rowValues.Select(r => $"'{r}'").ToArray())});";
                        command.ExecuteNonQuery();
                    }
                }

                connection.Close();
            }
        }
    }
}

    
Note

The code uses Microsoft.Jet.OLEDB.4.0 provider which makes it applicable only for x86 platform. In case you want to compile your application for x64 you have to replace it with Microsoft.ACE.OLEDB.12.0 or Microsoft.ACE.OLEDB.16.0 provider which is available for download from Microsoft download website for both x86 and x64 https://www.microsoft.com/en-us/download/details.aspx?id=54920

The code will generate Excel sheet with the TableName of the DataTable class instance, but since the DataTable instance quite often does not have the TableName defined and since name of the sheet is mandatory as it will be used as a reference for the INSERT commands, default value for the sheet will be taken which is generated Guid string. This will be the actual sheet name in the final output Excel docuemnt. To avoid this and end up with a meaningful name make sure you assign TableName property of the DataTable instance.

For the simplicity, all column data types are set to VARCHAR. In case you want the exact data type for each column in output Excel file, additional mapping between .NET and Excel data types needs to be implemented in code which is not in the scope of this article.

Now to do a quick test with DataTable export to Microsoft Office Excel file

        private static void TestDataTable()
        {
            var dataTable = new DataTable();
            dataTable.TableName = "DC";
            dataTable.Columns.Add("id");
            dataTable.Columns.Add("Name");
            dataTable.Columns.Add("Email");

            var row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Bruce Wayne";
            row["Email"] = "batman@superheroes.com";
            dataTable.Rows.Add(row);

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Clark Kent";
            row["Email"] = "superman@superheroes.com";
            dataTable.Rows.Add(row);

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Peter Parker";
            row["Email"] = "spiderman@superheroes.com";
            dataTable.Rows.Add(row);

            dataTable.AcceptChanges();

            dataTable.ExportToExcel(@"c:\temp\exported.xls");
        }
    

The result Excel file will have one sheet with name DC and sample data from the DataTable

Export Datatable

Now the next thing is to export DataSet to Excel. Since DataSet is basically a container for one or more DataTable instances, we can reuse the method for exporting DataTable

        public static void ExportToExcel(this DataSet dataSet, String filePath, bool overwiteFile = true)
        {
            if (File.Exists(filePath) && overwiteFile)
            {
                File.Delete(filePath);
            }

            foreach(DataTable dataTable in dataSet.Tables)
            {
                dataTable.ExportToExcel(filePath, false);
            }

        }
    

Now to do the test with sample DataSet with some data

private static void TestDataSet()
        {
            var dataSet = new DataSet();

            var dataTable = new DataTable();
            dataTable.TableName = "DC";
            dataTable.Columns.Add("id");
            dataTable.Columns.Add("Name");
            dataTable.Columns.Add("Email");

            var row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Bruce Wayne";
            row["Email"] = "batman@superheroes.com";
            dataTable.Rows.Add(row);

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Clark Kent";
            row["Email"] = "superman@superheroes.com";
            dataTable.Rows.Add(row);

            dataTable.AcceptChanges();
            dataSet.Tables.Add(dataTable);


            dataTable = new DataTable();
            dataTable.TableName = "Marvel";
            dataTable.Columns.Add("id");
            dataTable.Columns.Add("Name");
            dataTable.Columns.Add("Email");
            dataTable.Columns.Add("Color");

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Bruce Banner";
            row["Email"] = "hulk@superheroes.com";
            row["Color"] = "green";
            dataTable.Rows.Add(row);

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Tony Stark";
            row["Email"] = "ironmane@superheroes.com";
            row["Color"] = "red";
            dataTable.Rows.Add(row);

            row = dataTable.NewRow();
            row["id"] = Guid.NewGuid().ToString();
            row["Name"] = "Peter Parker";
            row["Email"] = "spiderman@superheroes.com";
            row["Color"] = "red";
            dataTable.Rows.Add(row);

            dataTable.AcceptChanges();
            dataSet.Tables.Add(dataTable);


            dataSet.ExportToExcel(@"c:\temp\exported.xls");
        }
    

The output is Excel document with two sheets name DC and Marvel

Export Dataset

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform includion ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article