Click here to Skip to main content
Click here to Skip to main content

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.

Comments and Discussions

 
QuestionMessage Automatically Removed Pinmembermsdevtech22-Aug-14 6:25 
QuestionSee also this: PinprofessionalDietmar Schoder29-Jul-14 6:21 
QuestionThank you. PinmemberNiveaulimbo2-Jul-14 1:05 
AnswerRe: Thank you. PinmemberAbdallah Al-Dalleh10-Jul-14 2:41 
QuestionThanks PinmemberAMIT_BHAGAT11-Jun-14 2:43 
QuestionAdditional Code necessary Pinmemberpbraun5-Jun-14 13:46 
GeneralRe: Additional Code necessary PinmemberYugandhar Lakkaraju5-Jun-14 18:14 
QuestionThank You PinmemberMember 1034185527-May-14 3:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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
Web03 | 2.8.1411023.1 | Last Updated 2 Apr 2014
Article Copyright 2014 by Yugandhar Lakkaraju
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid