Click here to Skip to main content
14,579,769 members
Articles » Languages » C# » General » Revisions

Export DataSet into Excel using C# Excel Interop

Rate this:
4.86 (20 votes)
Please Sign up or sign in to vote.
4.86 (20 votes)
1 Apr 2014CPOL
This article is all about how to save the dataset into excel using C# excel interop
This is an old version of the currently published reference.

Introduction

This article explains how to export the DataSet into Excel using C# excel interop API.

Using the code

Before starting code make sure add excel interop reference (Microsoft.Office.Interop.Excel) from add references in visual studio.

Here I have create namespace alias Excel for the Microsoft.Office.Interop.Excel namepsace.

Usually in projects we need to export the data from DataSet to excel this would be a common task in most of the projects in such cases we can use the below code to export the DataSet into Excel.

using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportDataSetToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();

            //Create an Emplyee DataTable
            DataTable employeeTable = new DataTable("Employee");
            employeeTable.Columns.Add("Employee ID");
            employeeTable.Columns.Add("Employee Name");
            employeeTable.Rows.Add("1", "ABC");
            employeeTable.Rows.Add("2", "DEF");
            employeeTable.Rows.Add("3", "PQR");
            employeeTable.Rows.Add("4", "XYZ");

            //Create a Department Table
            DataTable departmentTable = new DataTable("Department");
            departmentTable.Columns.Add("Department ID");
            departmentTable.Columns.Add("Department Name");
            departmentTable.Rows.Add("1", "IT");
            departmentTable.Rows.Add("2", "HR");
            departmentTable.Rows.Add("3", "Finance");

            //Create a DataSet with the existing DataTables
            DataSet ds = new DataSet("Organization");
            ds.Tables.Add(employeeTable);
            ds.Tables.Add(departmentTable);

            p.ExportDataSetToExcel(ds);
        }

        /// <summary>
        /// This method takes DataSet as input paramenter and it exports the same to excel
        /// </summary>
        /// <param name="ds"></param>
        private void ExportDataSetToExcel(DataSet ds)
        {
            //Creae an Excel application instance
            Excel.Application excelApp = new Excel.Application();
            
            //Create an Excel workbook instance and open it from the predefined location
            Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx");

            foreach (DataTable table in ds.Tables)
            {
                //Add a new worksheet to workbook with the Datatable name
                Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;

                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }

                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                    }
                }
            }

            excelWorkBook.Save();
            excelWorkBook.Close();
            excelApp.Quit();

        }
    }
}
Output

Image 1

Image 2

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Yugandhar Lakkaraju
Software Developer
India India
I am a .NET C# developer with 3 years of experience. Knowledge sharing is my hobby.

Comments and Discussions

Discussions on this specific version of this article. Add your comments on how to improve this article here. These comments will not be visible on the final published version of this article.

Reference
Posted 1 Apr 2014

Stats

216.1K views
36 bookmarked