ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming

Export DataSet and DataTable to Excel 2007 in C#

Updated on March 28, 2018
Source

How to Export Data from DataTable or DataSet into Excel 2007

There are many tutorials explaining how to export data from C# DataTable into Excel 2003 or older. There are very few, however, about how to export into Excel 2007. Specifically, there is very little available information about how to export DataSet or DataTable into Excel 2007.

This tutorial provides the simplest way to make these exports. The article is divided into two sections:

• How to export data from DataTable into Excel 2007

• How to export data from DataTable into Excel with charts

Using EPPlus Open Source Library to Download Data into Excel 2007

The best free online code library to use to help export data into Excel 2007 is the EPPlus open source library. This site offers simple lines of code. Before we begin, let's look at the site's Excel 2007-supported features.

EPPlus 3.1 and 4.0 Features to Support Excel 2007

Version 3.1 of EPPlus offers the following features to support Excel 2007/2010:

• Cell ranges

• Cell styling (border, color, fill, font, number, alignments)

• Charts

• Pictures

• Shapes

• Comments

• Tables

• Protection

• Encryption

• Pivot tables

• Data validation

• Conditional formatting

• VBA

However, EPPlus 4.0 is now available in Beta 2 for testing. New features include:

• Replaced packaging API with DotNetZip (this will remove any problems with isolated storage and enable multi-threading)

• New cell store with less memory consumption and faster row inserts

• Formula parser to calculate formulas in a workbook, worksheet, or in a specified range

Sample DataTable Application

Below is a sample application I created to demonstrate how to use the EPPlus library. It is a simple application, with one DataGridView and two buttons. The DataGridView shows which data we have exported and the two buttons have two different functions.

The first button, Export to Excel 2007, will create an Excel 2007 file with data available in a DataTable (“dtEmployee”). The second button, Export to Excel with Charts, will export DataTable (“dtEmployeeWithCharts”) into Excel with a pie chart.

See the screenshot below to view the sample application. I have also provided the code I used to generate these two DataTables (“dtEmployee" and “dtEmployeeWithCharts”).

Please note that this application will delete the existing file and create a new one in the project’s bin folder.

Sample Application Code

private void GenerateDataTableWithRecords()
        {
            dtEmployee = new DataTable();

            dtEmployee.Columns.Add("EmployeeID", typeof(int));
            dtEmployee.Columns.Add("EmployeeName", typeof(string));
            dtEmployee.Columns.Add("Designation", typeof(string));

            dtEmployee.Rows.Add(1, "Bytes Of Code", "C# developer");
            dtEmployee.Rows.Add(2, "RAJ", "Software Engineer");
            dtEmployee.Rows.Add(3, "Vicky", "Student");
            dtEmployee.Rows.Add(4, "Me", "Programmer");

            dtEmployeeWithCharts = new DataTable();

            dtEmployeeWithCharts.Columns.Add("EmployeeID", typeof(int));
            dtEmployeeWithCharts.Columns.Add("EmployeeName", typeof(string));
            dtEmployeeWithCharts.Columns.Add("Salary", typeof(decimal));

            dtEmployeeWithCharts.Rows.Add(1, "Bytes Of Code", 52000);
            dtEmployeeWithCharts.Rows.Add(2, "RAJ", 63500);
            dtEmployeeWithCharts.Rows.Add(3, "Vicky", 10000);
            dtEmployeeWithCharts.Rows.Add(4, "Me", 25600);
        }

Sample Code Screenshot

Sample Application to Export DataTable to Excel
Sample Application to Export DataTable to Excel

How to Export Data from DataTable into Excel

To export DataTable into Excel, use the GenerateExcel method. This method has two parameters: the DataTable object you want to export, and the name of the Excel sheet.

The code flow is very simple. I have added inline comments in code which will help you to understand it step-by-step.

GenerateExcel Method Code

private void GenerateExcel(DataTable dataToExcel, string excelSheetName)
        {
            string fileName = "ByteOfCode";
            string currentDirectorypath = Environment.CurrentDirectory;
            string finalFileNameWithPath = string.Empty;

            fileName = string.Format("{0}_{1}", fileName, DateTime.Now.ToString("dd-MM-yyyy"));
            finalFileNameWithPath = string.Format("{0}\\{1}.xlsx", currentDirectorypath, fileName);

            //Delete existing file with same file name.
            if (File.Exists(finalFileNameWithPath))
                File.Delete(finalFileNameWithPath);

            var newFile = new FileInfo(finalFileNameWithPath);

            //Step 1 : Create object of ExcelPackage class and pass file path to constructor.
            using (var package = new ExcelPackage(newFile))
            {
                //Step 2 : Add a new worksheet to ExcelPackage object and give a suitable name
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelSheetName);

                //Step 3 : Start loading datatable form A1 cell of worksheet.
                worksheet.Cells["A1"].LoadFromDataTable(dataToExcel, true, TableStyles.None);

                //Step 4 : (Optional) Set the file properties like title, author and subject
                package.Workbook.Properties.Title = @"This code is part of tutorials available at http://bytesofcode.hubpages.com";
                package.Workbook.Properties.Author = "Bytes Of Code";
                package.Workbook.Properties.Subject = @"Register here for more http://hubpages.com/_bytes/user/new/";

                //Step 5 : Save all changes to ExcelPackage object which will create Excel 2007 file.
                package.Save();

                MessageBox.Show(string.Format("File name '{0}' generated successfully.", fileName)
                    , "File generated successfully!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

GenerateExcel Method Screenshot

Example of Exported DataTable to Excel
Example of Exported DataTable to Excel | Source

How to Export Data from DataTable to Excel with Charts

The method "GenerateExcelWithCharts” used to create a chart in Excel 2007 is the same as the “GenerateExcel” method, with minor code changes. Code block 35 – 43 contains the logic to add a chart to Excel.

Code for DataTable to Excel with Charts

 private void GenerateExcelWithCharts(DataTable dataToExcel, string excelSheetName)
        {
            string fileName = "ByteOfCode_WithCharts";
            string currentDirectorypath = Environment.CurrentDirectory;
            string finalFileNameWithPath = string.Empty;

            fileName = string.Format("{0}_{1}", fileName, DateTime.Now.ToString("dd-MM-yyyy"));
            finalFileNameWithPath = string.Format("{0}\\{1}.xlsx", currentDirectorypath, fileName);

            //Delete existing file with same file name.
            if (File.Exists(finalFileNameWithPath))
                File.Delete(finalFileNameWithPath);

            var newFile = new FileInfo(finalFileNameWithPath);

            using (var package = new ExcelPackage(newFile))
            {
                //Add a new table from the given datatable and start loading table form A1 cell.
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelSheetName);

                //Add a new table from the given datatable
                worksheet.Cells["A1"].LoadFromDataTable(dataToExcel, true, TableStyles.None);

                var tbl = worksheet.Tables[0];
                //if you want to show sum of any column then set below variable true
                tbl.ShowTotal = true;
                //we want show sum of salary and in datatable 3rd column is salary column.
                tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;

                //Set the file properties like title, author and subject
                package.Workbook.Properties.Title = @"This code is part of tutorials available at http://bytesofcode.hubpages.com";
                package.Workbook.Properties.Author = "Bytes Of Code";
                package.Workbook.Properties.Subject = @"Register here for more http://hubpages.com/_bytes/user/new/";

                var chart = worksheet.Drawings.AddChart("SalaryPie", eChartType.Pie3D);
                chart.Title.Text = "Employee Salary Chart"; // sets the charts title
                //posistion of chart on excel sheet
                chart.SetPosition(Row: 2, RowOffsetPixels: 5, Column: 3, ColumnOffsetPixels: 5);
                //Chart height and width
                chart.SetSize(PixelWidth: 320, PixelHeight: 300);
                //chart data range, first parameter is for legends and second parameter for data.
                chart.Series.Add("C2:C5", "B2:B5");
                chart.Style = eChartStyle.Style26;

                //Save all changes to excel sheet
                package.Save();

                MessageBox.Show(string.Format("File name '{0}' generated successfully.", fileName)
                    , "File generated successfully!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

Screenshot of DataTable to Excel with Chart

Example of Exported DataTable to Excel with Charts
Example of Exported DataTable to Excel with Charts | Source

© 2012 RAJKISHOR SAHU

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Ganesh 4 years ago

      ExcelPackage doesn't provide any built-in function called "LoadFromDataTable"

    • profile image

      Shiba 5 years ago

      Its only displaying last row value

    • Bytes Of Code profile image
      Author

      RAJKISHOR SAHU 5 years ago from Bangalore

      @pankaj : Please tell me which version of VS and framework you are using. I developed this project using VS 2010 and .net 4.0

    • profile image

      Pankaj Mittal 5 years ago

      I think it will also produced mutex error on web if multiple users come on a single server.

      Error 1 The type or namespace name 'OfficeOpenXml' could not be found (are you missing a using directive or an assembly reference?) D:\WebProjects\DataTableToExcel2007\Form1.cs 10 7 DataTableToExcel2007

      send me the solution.

    • profile image

      Pankaj 5 years ago

      Error 1 The type or namespace name 'OfficeOpenXml' could not be found (are you missing a using directive or an assembly reference?) D:\WebProjects\DataTableToExcel2007\Form1.cs 10 7 DataTableToExcel2007

    • profile image

      Bhaskar 5 years ago

      At least tell where the reference of ExcelPackage is...

    • profile image

      saul 5 years ago

      probando.....

    • profile image

      miracle001314@yahoo.com 5 years ago

      Hello, as for exporting data from datatable to Excel, I also can sugeest you another solutions, you can write an article in your blog by using below solutions, I believe you will feel it ios interesting by using amny solutions as well we different libraries. Please tell me if you write more articles about export to Excel topic.

      solution one is using Spire.XLS for .NET:

      using Spire.Xls;

      namespace datatabletoexcel

      {

      public partial class Form1 : Form

      {

      public Form1()

      {

      InitializeComponent();

      }

      private void Run_Click(object sender, EventArgs e)

      {

      Workbook workbook = new Workbook();

      Worksheet sheet = workbook.Worksheets[0];

      sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 2, 1, -1, -1);

      workbook.SaveToFile(“sample.xls”);

      ExcelDocViewer( workbook.FileName );

      }

      private void Form1_Load(object sender, System.EventArgs e)

      {

      Workbook workbook = new Workbook();

      workbook.LoadFromFile(@”D:\michelle\my file\FandH.xls”);

      Worksheet sheet = workbook.Worksheets[0];

      this.dataGridView1.DataSource = sheet.ExportDataTable();

      }

      private void ExcelDocViewer( string fileName )

      {

      try

      {

      System.Diagnostics.Process.Start(fileName);

      }

      catch{}

      }

      }

      }

      Another is a free data export component: https://exportdata.codeplex.com

      private void btnLoad_Click(object sender, EventArgs e)

      {

      using(OleDbConnection oleDbConnection = new OleDbConnection())

      {

      oleDbConnection.ConnectionString = this.textBox1.Text;

      OleDbCommand oleDbCommand = new OleDbCommand();

      oleDbCommand.CommandText = this.textBox2.Text;

      oleDbCommand.Connection = oleDbConnection;

      using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))

      {

      DataTable dt = new DataTable();

      da.Fill(dt);

      dataGridView1.DataSource = dt;

      }

      }

      }

      private void btnRun_Click(object sender, EventArgs e)

      {

      Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();

      Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();

      worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;

      worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;

      worksheet1.StartDataCol = ((System.Byte)(0));

      cellExport.Sheets.Add(worksheet1);

      cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;

      cellExport.SaveToFile(“20110223.xls”);

      }

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)