Export data to Excel | .NET Excel Library

Overview

The Syncfusion .NET Excel (XlsIO) library provides support for exporting data to Excel from various data sources such as data tables, arrays, collections, CSV, and TSV. Also allows exporting data from Excel to data tables, collections and nested classes.

Exporting data to Excel works on these platforms: .NET MAUI, ASP.NET, ASP.NET MVC, Blazor, Windows Forms, UWP, WinUI, WPF, and Xamarin.

Learn about export data from data sources to Excel and vice-versa

.NET export data

Export data to Excel in C#

How to export data from the data table to Excel in C#

Data from ADO.NET objects such as data tables, data columns, and data views can be exported to Excel worksheets.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the first worksheet of the workbook into IWorksheet.
Export data from the data table to Excel through the ImportDataTable() method of IWorksheet.
Save the workbook object to a MemoryStream object.

Here is an example of how to export data from the data table to Excel in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0]; 

    //Initialize the DataTable
    DataTable table = SampleDataTable();

    //Export data from DataTable to the worksheet
    worksheet.ImportDataTable(table, true, 1, 1);

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
        workbook.Save(outputStream); 
    }
}

private DataTable SampleDataTable()
{
    //Create a DataTable with four columns
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    //Add five DataRows
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

    return table;
}

This option enables you to export:

To a specific cell or named range.
Data with or without headers.
Data based on the first-row cell value or each row cell value in a data table column.
Large amounts of data in seconds.

How to export data from an array to Excel in C#

Exporting one-dimensional or two-dimensional arrays to Excel worksheets is supported by XlsIO.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the first worksheet of the workbook into IWorksheet.
Export data from array to Excel through the ImportArray() method of IWorksheet.
Save the workbook object to a MemoryStream object.

Here is an example of how to export data from an array to Excel in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0]; 

    //Initialize the object array
    object[] array = new object[4] { "Total Income", "Actual Expense", "Expected Expenses", "Profit" };

    //Export data from object array to the worksheet
    worksheet.ImportArray(array, 1, 1, false);

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
        workbook.Save(outputStream); 
    }
}

This option enables you to export:

To a specific cell.
As a one-dimensional array vertically or horizontally.
Data as hyperlinks.

How to export data from a collection to Excel in C#

Export the collection with the desired data to an Excel worksheet.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the first worksheet of the workbook into IWorksheet.
Create a collection.
Export data from the collection to Excel through the ImportData() method of IWorksheet.
Save the workbook object to a MemoryStream object.

Here is an example of how to export data from a collection to Excel in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0]; 

    //Initialize the collection
    IList<Customer> reports = GetSalesReports();

    //Export data from the collection to the worksheet
    worksheet.ImportData(reports, 2, 1, false);

    //Save the document into a stream
    using (MemoryStream outputStream = new MemoryStream()) 
    { 
        workbook.Save(outputStream); 
    }
}

//Gets a list of sales reports
public List<Customer> GetSalesReports()
{
    List<Customer> reports = new List<Customer>();
    reports.Add(new Customer("Andy Bernard", "45000", "58000"));
    reports.Add(new Customer("Jim Halpert", "34000", "65000"));
    reports.Add(new Customer("Karen Fillippelli", "75000", "64000"));
    reports.Add(new Customer("Phyllis Lapin", "56500", "33600" ));
    reports.Add(new Customer("Stanley Hudson", "46500", "52000"));
    return reports;
}

//Customer details
public class Customer
{
    [DisplayNameAttribute("Sales Person Name")]
    public string SalesPerson { get; set; }
    [Bindable(false)]
    public string SalesJanJun { get; set; }
    public string SalesJulDec { get; set; }

    public Customer(string name, string janToJun, string julToDec)
    {
        SalesPerson = name;
        SalesJanJun = janToJun;
        SalesJulDec = julToDec;
    }
}

Learn various export data options and how to export data from nested collection objects to Excel.

This enables you to export:

From N number of collections.
From nested collections.
Data with or without headers.
Images.
Data as hyperlinks.

How to save a CSV file as Excel in C#

Comma-separated value (CSV) files are helpful in generating tabular data or lightweight reports with a few columns and multiple rows. Excel opens such files to make the data easier to read.

The Syncfusion .NET Excel library supports opening and saving CSV files in seconds. The below code example shows how to open a CSV file, also save it as XLSX file.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Load a CSV file into IWorkbook.
Save the workbook object to a MemoryStream object.

Here is an example of how to save a CSV file as Excel in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using(FileStream inputStream = new FileStream(“Sample.csv”, FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream);

        //Save the document into a stream
        using (FileStream outputStream = new FileStream(“Output.xlsx”, FileMode.Create, FileAccess.ReadWrite)) 
        { 
            workbook.Save(outputStream); 
        }
    }
}

Click here to know about saving a TSV file as Excel.

Export data from Excel in C#

How to export data from an Excel document to a data table in C#

The data in an Excel document can be exported to the data table.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Load an Excel file into IWorkbook.
Get the worksheet with data into IWorksheet.
Export the Excel data to DataTable using ExportDataTable() method of IWorksheet.

Here is an example of how to export data from Excel to a data table in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using(FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[0];

        //Read data from the worksheet and export to the DataTable
        DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
    }
}

How to export data from Excel to a collection object in C#

The data in Excel document can be exported to a collection object.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Load an Excel file into IWorkbook.
Get the worksheet with data into IWorksheet.
Export the Excel data to a collection object using ExportData() method of IWorksheet.

Here is an example of how to export data from Excel to a collection object in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using(FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[0];

        //Export worksheet data into a collection object
        List<Report> collectionObject = worksheet.ExportData<Report>(1, 1, 10, 3);
    }
}

public class Report
{
    [DisplayNameAttribute("Sales Person Name")]
    public string SalesPerson { get; set; }
    [Bindable(false)]
    public string SalesJanJun { get; set; }
    public string SalesJulDec { get; set; }

    public Report()
    {

    }
}

How to export data from an Excel document to a nested collection object in C#

The data in an Excel document can be exported to a nested collection object.

Install Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize ExcelEngine and IApplication objects.
Load an Excel file into IWorkbook.
Get the worksheet with data into IWorksheet.
Export the Excel data to a nested collection object using ExportData() method of IWorksheet.

Here is an example of how to export data from Excel to a nested collection object in C# using the Syncfusion .NET Excel library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using(FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream);
        IWorksheet worksheet = workbook.Worksheets[0];

        //Map column headers in the worksheet with class properties. 
        Dictionary<string, string> mappingProperties = new Dictionary<string, string>();
        mappingProperties.Add("Customer ID", "CustId");
        mappingProperties.Add("Customer Name", "CustName");
        mappingProperties.Add("Customer Age", "CustAge");
        mappingProperties.Add("Order ID", "CustOrder.Order_Id");
        mappingProperties.Add("Order Price", "CustOrder.Price");

        //Export worksheet data into a nested class object.
        List<Customer> nestedClassObject = worksheet.ExportData<Customer>(1, 1, 10, 5, mappingProperties);
    }
}

//Customer details class
public partial class Customer
{
    public int CustId { get; set; }
    public string CustName { get; set; }
    public int CustAge { get; set; }
    public Order CustOrder { get; set; }
    public Customer()
    {

    }
}

//Order details class
public partial class Order
{
    public int Order_Id { get; set; }
    public double Price { get; set; }
    public Order()
    {

    }
}