Click here to Skip to main content
15,870,297 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Reference

Export DataSet into Excel using C# Excel Interop

Rate me:
Please Sign up or sign in to vote.
4.86/5 (20 votes)
1 Apr 2014CPOL 247.2K   37   18
This article is all about how to save the dataset into excel using C# excel interop

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.

C#
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();

        }
    }
} 

License

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



Comments and Discussions

 
QuestionResponse.Write for Data table Pin
Member 1491993121-Aug-20 8:17
Member 1491993121-Aug-20 8:17 
Questiongetting data from excel file Pin
Member 131660772-May-17 0:23
Member 131660772-May-17 0:23 
PraiseThanks Pin
Member 1121207121-Oct-16 4:59
Member 1121207121-Oct-16 4:59 
QuestionConvert error Pin
Member 1170698711-May-16 6:08
Member 1170698711-May-16 6:08 
QuestionHow to create Excel file from code Pin
Member 1210882118-Apr-16 3:35
Member 1210882118-Apr-16 3:35 
QuestionWhat if creating the excel file dynamically? Pin
baldeep khurana26-Feb-16 4:59
baldeep khurana26-Feb-16 4:59 
QuestionError Pin
Basmeh Awad6-Sep-15 0:49
professionalBasmeh Awad6-Sep-15 0:49 
QuestionIIS without Excel Pin
edgar.asp24-Jun-15 5:37
edgar.asp24-Jun-15 5:37 
AnswerMessage Closed Pin
4-Feb-17 21:24
Ciprian Beldi4-Feb-17 21:24 
GeneralPerformance issue Pin
Member 467491118-Jun-15 17:47
Member 467491118-Jun-15 17:47 
QuestionSee also this: Pin
dietmar paul schoder29-Jul-14 5:21
professionaldietmar paul schoder29-Jul-14 5:21 
QuestionThank you. Pin
Niveaulimbo2-Jul-14 0:05
Niveaulimbo2-Jul-14 0:05 
AnswerRe: Thank you. Pin
Abdallah Al-Dalleh10-Jul-14 1:41
Abdallah Al-Dalleh10-Jul-14 1:41 
QuestionThanks Pin
AMIT_BHAGAT11-Jun-14 1:43
AMIT_BHAGAT11-Jun-14 1:43 
QuestionAdditional Code necessary Pin
pbraun5-Jun-14 12:46
pbraun5-Jun-14 12:46 
GeneralRe: Additional Code necessary Pin
Yugandhar Lakkaraju5-Jun-14 17:14
Yugandhar Lakkaraju5-Jun-14 17:14 
QuestionThank You Pin
Member 1034185527-May-14 2:21
Member 1034185527-May-14 2:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.