Click here to Skip to main content
12,295,976 members (60,161 online)
Click here to Skip to main content
Add your own
alternative version

Stats

89.6K views
31 bookmarked
Posted

Export DataSet into Excel using C# Excel Interop

, 1 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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)

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.

You may also be interested in...

Comments and Discussions

 
QuestionConvert error Pin
Member 1170698711-May-16 6:08
memberMember 1170698711-May-16 6:08 
QuestionHow to create Excel file from code Pin
Member 1210882118-Apr-16 3:35
memberMember 1210882118-Apr-16 3:35 
QuestionWhat if creating the excel file dynamically? Pin
baldeep khurana26-Feb-16 4:59
memberbaldeep 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
memberedgar.asp24-Jun-15 5:37 
GeneralPerformance issue Pin
Member 467491118-Jun-15 17:47
memberMember 467491118-Jun-15 17:47 
QuestionMessage Automatically Removed Pin
22-Aug-14 5:25
membermsdevtech22-Aug-14 5:25 
QuestionSee also this: Pin
Dietmar Schoder29-Jul-14 5:21
professionalDietmar Schoder29-Jul-14 5:21 
QuestionThank you. Pin
Niveaulimbo2-Jul-14 0:05
memberNiveaulimbo2-Jul-14 0:05 
AnswerRe: Thank you. Pin
Abdallah Al-Dalleh10-Jul-14 1:41
memberAbdallah Al-Dalleh10-Jul-14 1:41 
QuestionThanks Pin
AMIT_BHAGAT11-Jun-14 1:43
memberAMIT_BHAGAT11-Jun-14 1:43 
QuestionAdditional Code necessary Pin
pbraun5-Jun-14 12:46
memberpbraun5-Jun-14 12:46 
GeneralRe: Additional Code necessary Pin
Yugandhar Lakkaraju5-Jun-14 17:14
memberYugandhar Lakkaraju5-Jun-14 17:14 
QuestionThank You Pin
Member 1034185527-May-14 2:21
memberMember 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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160525.2 | Last Updated 2 Apr 2014
Article Copyright 2014 by Yugandhar Lakkaraju
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid