How to Export DataTable to Excel in C#

Saad Aslam Feb 02, 2024

Csharp Csharp Excel

Add Reference Package in Visual Studio
Export DataTable to Excel Using C#

This tutorial will teach how to export datatable to an Excel file in C# programming language. Adding a reference to build an excel file and exporting it is necessary before moving on with this project.

Add Reference Package in Visual Studio

The ExcelMapper package will be included to perform various Excel functions. To do that, follow the instructions outlined below.

Open Visual Studio, create a Console Application, and name it.
Right-click on the Solution Explorer panel and Select Manage NuGet Packages.
Now click on the Browse option, search for ExcelMapper and install it.

Let us understand it with a code example.

Export `DataTable` to Excel Using `C#`

To begin, import the following libraries.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Spire.Xls;

Now, generate a new instance of the Workbook object and save it with the name workBook.

Workbook workBook = new Workbook();
workBook.Worksheets.Clear();

Include a new instance of the Worksheet class and call it workSheet.

Worksheet workSheet = workBook.Worksheets.Add("DataTableToExcel");

Create a new object of type DataTable with the variable name dataTable.

DataTable dataTable = new DataTable();

Create new columns labeled Roll Number, Student Name, and Section.

dataTable.Columns.Add("Roll Number", typeof(String));
dataTable.Columns.Add("Student Name", typeof(String));
dataTable.Columns.Add("Section", typeof(String));

We must now instantiate a new object of the DataRow class and name it dtr.

DataRow dtr = dataTable.NewRow();

Next, we will enter the data into the column named DataRow.

dtr[0] = "BC210120319";
dtr[1] = "Iftikhar Aslam";
dtr[2] = "BS (CS)";
dataTable.Rows.Add(dtr);
dtr = dataTable.NewRow();
dtr[0] = "BC210203291";
dtr[1] = "Saad Aslam";
dtr[2] = "BS (IT)";
dataTable.Rows.Add(dtr);
dtr = dataTable.NewRow();

In the spreadsheet we just made, you should insert the column labeled DataTable.

workSheet.InsertDataTable(dataTable, true, 1, 1, true);

At this point, you should give the document a name before saving it as an Excel file.

workBook.SaveToFile(@"E:\DataTableToExcel.xlsx", ExcelVersion.Version2016);

Complete Source Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Spire.Xls;

namespace ExportDataTableToExcelBySaad {
  class Program {
    static void Main(string[] args) {
      Workbook workBook = new Workbook();
      workBook.Worksheets.Clear();
      Worksheet workSheet = workBook.Worksheets.Add("DataTableToExcel");
      DataTable dataTable = new DataTable();
      dataTable.Columns.Add("Roll Number", typeof(String));
      dataTable.Columns.Add("Student Name", typeof(String));
      dataTable.Columns.Add("Section", typeof(String));
      DataRow dtr = dataTable.NewRow();
      dtr[0] = "BC210120319";
      dtr[1] = "Iftikhar Aslam";
      dtr[2] = "BS (CS)";
      dataTable.Rows.Add(dtr);
      dtr = dataTable.NewRow();
      dtr[0] = "BC210203291";
      dtr[1] = "Saad Aslam";
      dtr[2] = "BS (IT)";
      dataTable.Rows.Add(dtr);
      dtr = dataTable.NewRow();
      workSheet.InsertDataTable(dataTable, true, 1, 1, true);
      workBook.SaveToFile(@"E:\DataTableToExcel.xlsx", ExcelVersion.Version2016);
    }
  }
}

Output:

Export DataTable to Excel

Author: Saad Aslam

I'm a Flutter application developer with 1 year of professional experience in the field. I've created applications for both, android and iOS using AWS and Firebase, as the backend. I've written articles relating to the theoretical and problem-solving aspects of C, C++, and C#. I'm currently enrolled in an undergraduate program for Information Technology.

Add Reference Package in Visual Studio

Export DataTable to Excel Using C#

Related Article - Csharp Excel

Export `DataTable` to Excel Using `C#`