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, TSV, and Microsoft Grid controls. 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 Core, 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.WinForms NuGet package in your .NET Framework 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.
Data as hyperlinks.
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.WinForms NuGet package in your .NET Framework 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.WinForms NuGet package in your .NET Framework 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.WinForms NuGet package in your .NET Framework 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.

How to export data from the Grid controls to Excel in C#

Exporting data from the Microsoft Grid controls such as DataGrid, GridView, and DataGridView to an Excel worksheet is very simple.

DataGrid to Excel

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];

    DataGrid dataGrid = new DataGrid();
    dataGrid.DataSource = GetDataTable();

    //Export from the DataGrid to worksheet
    worksheet.ImportDataGrid(dataGrid, 1, 1, true, true);

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

private DataTable GetDataTable()
{
    //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;
}

GridView to Excel

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];

    GridView gridView = new GridView();
    gridView.DataSource = GetDataTable();

    //Export from the GridView to worksheet
    worksheet.ImportGridView(gridView, 1, 1, true, true);

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

private DataTable GetDataTable()
{
    //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;
}

DataGridView to Excel

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];

    DataGridView dataGridView = new DataGridView();
    dataGridView.DataSource = GetDataTable();

    //Export from the DataGridView to worksheet
    worksheet.ImportDataGridView(dataGridView, 1, 1, true, true);

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

private DataTable GetDataTable()
{
    //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;
}

Export data from Excel in C#

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

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

Install Syncfusion.XlsIO.WinForms NuGet package in your .NET Framework 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.WinForms NuGet package in your .NET Framework 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.WinForms NuGet package in your .NET Framework 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()
    {

    }
}

Click here for more examples of exporting data from data sources to Excel and vice-versa*.

References

RESOURCES

CASE STUDY

Syncfusion’s file format components helped me create the reports I needed, fast. – J. Pereira, Software Developer.

The libraries have been built from scratch and refined for more than a decade to provide blazing-fast performance, comprehensive API, and compatibility across the latest and older versions of these files.

CONTINUE READING View all Customer Stories

VIDEOS

Syncfusion File Format Libraries - Manipulate Excel, Word, PowerPoint, and PDF files

Read and write Excel, Word, PDF, and PowerPoint files. Also includes integrated visualization capabilities. Advanced features include support for pivot tables, pivot charts, mail-merge, and extensive formatting.

E-BOOK

Succinctly Series: Statistics Using Excel Succinctly

Succinctly Series: Statistics Using Excel Succinctly

- by Charles Zaiontz

CONTINUE READING View all E-books

Syncfusion .NET Excel Library Resources

Learning