ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Export DataSet and DataTable to Excel 2007 in C#

Updated on September 16, 2014
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

      miracle001314@yahoo.com 4 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”);

      }

    • profile image

      saul 4 years ago

      probando.....

    • profile image

      Bhaskar 4 years ago

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

    • profile image

      Pankaj 4 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

      Pankaj Mittal 4 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.

    • Bytes Of Code profile image
      Author

      RAJKISHOR SAHU 4 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

      Shiba 4 years ago

      Its only displaying last row value

    • profile image

      Ganesh 3 years ago

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

    Click to Rate This Article