Export Data to Excel in C#

In this article, we will show you how to export data to Excel in C# and VB.NET from various data sources such as an array, collection of custom objects, DataTable, DataView, DataGrid, GridView, HTML, JSON and CSV.

• Export Array to Excel in C#
  • Export ArrayList to Excel
  • Export Collection of Custom Objects to Excel
• Copy Rows and Columns from one Excel file to Another
• Export DataTable to Excel in C#
  • Export Data of Selective DataColumns to Excel
  • Export DataView to Excel
• Export Data from DataGrid and GridView to Excel
• Export HTML formatted Data to Excel
  • Export HTML File to Excel
• Export JSON Data to Excel in C#
• Export CSV Data to Excel in C#

Export Data to Excel in C# with Aspose.Cells API

Aspose.Cells for .NET is a powerful spreadsheet manipulation API that lets you create, edit, or convert Excel files within .NET applications. The API’s easy to use methods enable you to perform Excel automation features seamlessly in a few lines of code. NuGet is the easiest way to download and install Aspose.Cells API for .NET. Open Manage NuGet Packages window and type “Aspose.Cells” in the search text box to find the Aspose.Cells .NET package. Finally, click the Install button to install the latest version of the package.

C# Export Array to Excel - Short Tutorial

We can export an array (one-dimensional or two-dimensional) of a reference type or value type to an Excel document. We use the ImportArray method of the Cells collection to export data to a spreadsheet from an array. The overloaded versions of the ImportArray method are the following.

A typical overload takes the following parameters:

• Array, the array object that you are exporting content from.
• Row number, the row number of the first cell (zero-based) that the data will be exported to.
• Column number, the column number of the first cell (zero-based) that the data will be exported to.
• Is vertical, a Boolean value that specifies whether to export data vertically or horizontally.

The following are the steps to export an array to Excel file in C#.

1. Create a Workbook object. A Workbook class represents a Microsoft Excel file.
2. Get a reference to the desired worksheet. The Workbook class contains a Worksheets collection that allows access to each worksheet in an Excel file.
3. Call ImportArray method of a Cells collection to export an array to the worksheet at the specified row and column. The Worksheet class provides a Cells collection.
4. Save the Excel file using Workbook.Save(string) method.

The following code sample shows how to export an array of String to an Excel file in C#.

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Creating an array containing names as string values
string[] names = new string[] { "Laurence Chen", "Roman Korchagin", "Kyle Huang" };

// Exporting the array of names to first row and first column vertically
worksheet.Cells.ImportArray(names, 0, 0, true);

// Saving the Excel file
workbook.Save("StringsArray.xlsx");

Similarly, we can export a two-dimensional array to an Excel file. The following code sample shows how to export two-dimensional array to an Excel file in C#.

// Creating a two-dimensional array of integers
int[,] array2D = new int[4, 2] { { 1, 2 }, { 3, 4 }, { 5, 6 }, { 7, 8 } };

// Exporting a two-dimensional array at the first row and first column of the worksheet
worksheet.Cells.ImportArray(array2D, 0, 0);

C# Export ArrayList to Excel

To export data from an ArrayList to worksheet, call the Cells collection’s ImportArrayList method. The ImportArrayList method takes the following parameters:

• Array list represents the ArrayList object you are exporting.
• Row number represents the row number of the first cell that the data will be exported to.
• Column number represents the column number of the first cell that the data will be exported to.
• Is vertical a Boolean value that specifies whether to export data vertically or horizontally.

The following code sample shows how to export an ArrayList to an Excel file in C#.

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Instantiating an ArrayList object
ArrayList list = new ArrayList();

// Add few names to the list as string values
list.Add("Laurence Chen");
list.Add("Roman Korchagin");
list.Add("Kyle Huang");
list.Add("Tommy Wang");

// Exporting the contents of ArrayList vertically at the first row and first column of the worksheet. 
worksheet.Cells.ImportArrayList(list, 0, 0, true);

// Saving the Excel file
workbook.Save("ArrayListExport.xlsx");

C# Export Collection of Custom Objects to Excel

To export data from a collection of custom objects to a worksheet, we use ImportCustomObjects method. There are two overloaded versions of this method.

1. ImportCustomObjects(ICollection list, String[] propertyNames, Boolean isPropertyNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Boolean insertions, String dateFormatString, Boolean convertStringToNumber)
2. ImportCustomObjects(ICollection list, Int32 firstRow, Int32 firstColumn, ImportTableOptions options)

We will explore each overloaded method one by one. The description of the parameters of the first overloaded method is given below:

• list The collection of the custom objects.
• propertyNames Names of properties of the object to export. If it is null, all properties will be exported.
• isPropertyNameShown Indicates whether the property names will be exported to the first row.
• firstRow The row number of the first cell to export to.
• firstColumn The column number of the first cell to export to.
• rowNumber Number of objects to be exported.
• insertRows Indicates whether extra rows are added to fit data.
• dateFormatString Date format string for cells.
• convertStringToNumber Indicates if this method will try to convert string to a number.

In the following example, we are exporting a list of Person objects to an Excel document in C#. Please note that we are exporting only two properties (Name and Age) of a Person object.

// Instantiate a new Workbook
Workbook book = new Workbook();
// Obtaining the reference of the worksheet
Worksheet sheet = book.Worksheets[0];

// Define List
List<Person> list = new List<Person>();

list.Add(new Person("Mike", 25, "Software Engineer"));
list.Add(new Person("Steve", 30, "Doctor"));
list.Add(new Person("Billy", 35, "Teacher"));

// We pick only Name and Age columns, not all, to export to the worksheet         
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list,
    new string[] { "Name", "Age" }, // propertyNames
    true, // isPropertyNameShown
    0, // firstRow
    0, // firstColumn
    list.Count, // Number of objects to be exported
    true, // insertRows
    null, // dateFormatString
    false); // convertStringToNumber

// Save the Excel file
book.Save("ExportedCustomObjects.xlsx");
       
public class Person
{
    public string Name { get; set; }

    public int Age { get; set; }

    public string Occupation { get; set; }

    public Person(string name, int age, string occupation)
    {
        Age = age;
        Name = name;
        Occupation = occupation;
    }
}

Now we explore the second overloaded method of ImportCustomObjects. The description of the parameters of the method is given below:

• list The list of custom objects.
• firstRow The row number of the first cell to export to.
• firstColumn The column number of the first cell to export to.
• options ImportTableOptions object.

The ImportTableOptions parameter provides several options for exporting data into cells. Some of them are given below:

• CheckMergedCells Does an Excel document contain merged cells.
• ColumnIndexes Integer array of column indexes (0-based) to export from the data source. null means all columns should be exported.
• ConvertGridStyle Indicates whether to apply the style of the grid view to cells.
• ConvertNumericData A boolean value that indicates whether the string value should be converted to numeric or date value.
• DateFormat Gets or sets date format string for cells with exported DateTime values.
• DefaultValues Default value for the cell in the table is null.
• InsertRows Indicates whether new rows should be added for exporting data records.
• IsFieldNameShown Indicates whether the field names should be exported.
• IsFormulas Indicates whether the data are formulas.
• IsHtmlString Indicates whether the data contains HTML tags. If we set the value to true, HTML formatting will remain preserved while exporting data to an Excel document.
• NumberFormats Gets or sets the number formats
• ShiftFirstRowDown Indicates whether the first row should be shifted down when inserting rows.
• TotalColumns Gets or sets total columns’ count to export from a data source. -1 means all columns of the given data source.
• TotalRows Gets or sets total rows’ count to export from the data source. -1 means all rows of the given data source.

In the following example, we are exporting data from a collection of objects to a worksheet containing merged cells. We are setting the value of ImportTableOptions.CheckMergedCells property to true as the Excel document contains merged cells.

// Opening an existing Workbook.
Workbook workbook = new Workbook("SampleMergedTemplate.xlsx");
List<Product> productList = new List<Product>();

// Creating a collection of Products
for (int i = 0; i < 3; i++)
{
    Product product = new Product
    {
        ProductId = i,
        ProductName = "Test Product - " + i
    };
    productList.Add(product);
}

ImportTableOptions tableOptions = new ImportTableOptions();
// Set CheckMergedCells property to true
tableOptions.CheckMergedCells = true;
tableOptions.IsFieldNameShown = false;

//Export data to excel template (in second row, first column) 
workbook.Worksheets[0].Cells.ImportCustomObjects((ICollection)productList, 1, 0, tableOptions);
workbook.Save("SampleMergedTemplate_out.xlsx", SaveFormat.Xlsx);

public class Product
{
    public int ProductId { get; set; }

    public string ProductName { get; set; }
}

C# Copies Rows and Columns from one Excel file to Another

We can programmatically copy rows and columns from one Excel document to another. When a row (or column) is copied, the data contained in it, including formulas – with updated references – and values, comments, formatting, hidden cells, images, and other drawing objects are also copied. We can also copy rows and columns within the same worksheet or across different worksheets in an Excel document. Aspose.Cells provides the following methods to copy rows and columns.

• CopyRow(Cells sourceCells, int sourceRowIndex, int destinationRowIndex) Copies data of a single row.
• CopyRows(Cells sourceCells, int sourceRowIndex, int destinationRowIndex, int rowNumber) Copies data of multiple rows.
• CopyColumn(Cells sourceCells, int sourceColumnIndex, int destinationColumnIndex) Copies data of a single column.
• CopyColumns(Cells sourceCells, int sourceColumnIndex, int destinationColumnIndex, int columnNumber) Copies data of multiple columns.

The following example code shows how to copy rows from one Excel document to another in C#.

// Open the source excel file.
Workbook srcWorkbook = new Workbook("Source_Workbook.xlsx");

// Instantiate the destination excel file.
Workbook destWorkbook = new Workbook();

// Get the first worksheet of the source workbook.
Worksheet srcWorksheet = srcWorkbook.Worksheets[0];

// Get the first worksheet of the destination workbook.
Worksheet desWorksheet = destWorkbook.Worksheets[0];

// Copy all the rows of the first worksheet of source Workbook to
// the first worksheet of destination Workbook.
desWorksheet.Cells.CopyRows(srcWorksheet.Cells, 0, 0, srcWorksheet.Cells.MaxDisplayRange.RowCount);

// Save the excel file.
destWorkbook.Save("Destination_Workbook.xlsx");

The following example code shows how to copy specific rows of one Excel document to another.

// Open the source excel file.
Workbook srcWorkbook = new Workbook("Source_Workbook.xlsx");

// Instantiate the destination excel file.
Workbook destWorkbook = new Workbook();

// Get the first worksheet of the source workbook.
Worksheet srcWorksheet = srcWorkbook.Worksheets[0];

// Get the first worksheet of the destination workbook.
Worksheet desWorksheet = destWorkbook.Worksheets[0];

// Copy the second row of the source Workbook to the first row of destination Workbook.
desWorksheet.Cells.CopyRow(srcWorksheet.Cells, 1, 0);

// Copy the fourth row of the source Workbook to the second row of destination Workbook.
desWorksheet.Cells.CopyRow(srcWorksheet.Cells, 3, 1);

// Save the excel file.
destWorkbook.Save("Destination_Workbook.xlsx");

We can similarly copy columns’ data from one Microsoft Excel document to another using CopyColumn or CopyColumns method.

C# Export DataTable to Excel

Data from ADO.NET objects such as DataTable, DataColumn, and DataView can be exported to Excel worksheets. To export data from a DataTable, we call the ImportData method of Cells collection. There are many overloaded versions of the ImportData method but we use the following:

public int ImportData(
	DataTable table,
	int firstRow,
	int firstColumn,
	ImportTableOptions options
)

The description of the parameters is given below:

• table DataTable object to be exported.
• firstRow The row number of the first cell to export to.
• firstColumn The column number of the first cell to export to.
• optionsType ImportTableOptions object.

In the following code sample, we are creating a DataTable object that has three columns and two rows. And exporting it to an Excel worksheet.

// Instantiating a Workbook object            
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Products");

// Adding columns to the DataTable object
dataTable.Columns.Add("Product ID", typeof(int));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(int));

// Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();

// Adding data to the row
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;

// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

// Creating another empty row in the DataTable object
dr = dataTable.NewRow();

// Adding data to the row
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;

// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

// Setting IsFieldNameShown property to true will add column names // of the DataTable to the worksheet as a header row
ImportTableOptions tableOptions = new ImportTableOptions();
tableOptions.IsFieldNameShown = true;

// Exporting the contents of DataTable at the first row and first column.
worksheet.Cells.ImportData(dataTable, 0, 0, tableOptions);

// Saving the Excel file
workbook.Save("DataTable_Eport.xlsx");

Export Data of Selective DataColumns to Excel in C#

We can export selective DataColumns of a DataTable or DataView to an Excel document. As discussed earlier, the ImportData method accepts an argument of type ImportTableOptions. The ImportTableOptions class has a ColumnIndexes property that accepts an array of columns indexes (zero-based) that we want to export. In the following code sample, we are exporting only two DataColumns of a DataTable to an Excel Worksheet.

// Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Products");

// Adding columns to the DataTable object
dataTable.Columns.Add("Product ID", typeof(int));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(int));

// Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();

// Adding data to the row
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;

// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

// Creating another empty row in the DataTable object
dr = dataTable.NewRow();

// Adding data to the row
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;

// Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

// Instantiate a new Workbook
Workbook book = new Workbook();

Worksheet sheet = book.Worksheets[0];

// Create export options
ImportTableOptions importOptions = new ImportTableOptions();
// Sets the columns (0-based) to export from data source.
// null means all columns should be exported.
importOptions.ColumnIndexes = new int[] { 0, 1 };
importOptions.IsFieldNameShown = true;

// Exporting the values of 1st and 2nd columns of the data table
sheet.Cells.ImportData(dataTable, 0, 0, importOptions);

book.Save("DataColumsExport.xlsx");

Export Data from DataView to Excel in C#

A DataView is a view on a DataTable that can be customized to present a subset of data from the DataTable. We use the following overloaded version of the ImportData method to export data from DataView to an Excel document.

public int ImportData(
	DataView dataView,
	int firstRow,
	int firstColumn,
	ImportTableOptions options
)

We know there are two ways to create a DataView. We can use the DataView constructor, or we can create a reference to the DefaultView property of the DataTable. In the following code sample, we are using the later way to create a DataView.

worksheet.Cells.ImportData(dataTable.DefaultView, 0, 0, options);

Export Data from DataGrid and GridView to Excel in C#

Aspose.Cells library allows us to export data from Microsoft Grid controls such as DataGrid and GridView to an Excel worksheet. It provides ImportDataGrid method, to export data from a DataGrid and ImportGridView method to export data from a GridView.

There are many overloaded versions of the ImportDataGrid method but a typical overload takes the following parameters:

• dataGrid, the DataGrid object that we’re exporting content from.
• firstRow, the row number of the first cell that the data will be exported to.
• firstColumn, the column number of the first cell that the data will be exported to.
• insertRows, a Boolean property that indicates whether extra rows should be added to the worksheet to fit data.
• importStyle, a Boolean property that indicates whether cell style should be exported.

The following code example shows how to export data from DataGrid to an Excel file in C#.

// Create a DataTable object and set it as the DataSource of the DataGrid.
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("Product ID", typeof(int));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(int));

DataRow dr = dataTable.NewRow();
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;
dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;
dataTable.Rows.Add(dr);

// Now take care of DataGrid
DataGrid dg = new DataGrid();
dg.DataSource = dataTable;
dg.DataBind();

// We have a DataGrid object with some data in it.
// Lets export it to an Excel worksheet.

// Creat a new workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

// Exporting the contents of the DataGrid to the worksheet
worksheet.Cells.ImportDataGrid(dg, 0, 0, false);

// Save it as Excel file
workbook.Save("ExportDataGrid.xlsx");

Export HTML formatted Data to Excel in C#

Aspose.Cells lets you export HTML formatted data to an Excel worksheet. The API parses HTML formatted text while exporting data and convert the HTML into formatted cell values. In the following sample code, DataTable contains HTML formatted text and we are exporting it to an Excel Document using ImportData method.

// Prepare a DataTable with some HTML formatted values
DataTable dataTable = new DataTable("Products");

dataTable.Columns.Add("Product ID", typeof(int));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(int));

DataRow dr = dataTable.NewRow();
dr[0] = 1;
// Make text italicize
dr[1] = "<i>Aniseed</i> Syrup";
dr[2] = 15;
dataTable.Rows.Add(dr);

dr = dataTable.NewRow();
dr[0] = 2;
// Make text bold
dr[1] = "<b>Boston Crab Meat</b>";
dr[2] = 123;
dataTable.Rows.Add(dr);

// Create export options
ImportTableOptions exportOptions = new ImportTableOptions();
exportOptions.IsFieldNameShown = true;
// Set IsHtmlString property to true as the data contains HTML tags. 
exportOptions.IsHtmlString = true;

// Create workbook
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells.ImportData(dataTable, 0, 0, exportOptions);

workbook.Save("HTMLFormattedData_Out.xlsx");

Export HTML File to Excel in C#

Aspose.Cells allows us to export an HTML file to Excel. The HTML file should be Microsoft Excel oriented, i.e., MS-Excel should be able to open it.

// An HTML file
string filePath = "Book1.html";

// Instantiate LoadOptions specified by the LoadFormat.
HtmlLoadOptions loadOptions = new HtmlLoadOptions(LoadFormat.Html);

// Create a Workbook object and open the HTML file.
Workbook wb = new Workbook(filePath, loadOptions);

// Save the file as Excel Document
wb.Save("Book1_out.xlsx");

Export JSON Data to Excel in C#

Sometimes we have a need to export JSON Data to an Excel document. With Aspose.Cells we can easily do this with a few lines of code. Aspose.Cells provides a JsonUtility class that has an ImportData method for exporting JSON data to an Excel document. The ImportData method accepts JsonLayoutOptions object as a parameter. The JsonLayoutOptions class represents the options of JSON layout and has the following properties.

• ArrayAsTable: Indicates whether the array should be processed as a table.
• ConvertNumericOrDate: Gets or sets a value that indicates whether the string in JSON is to be converted to numeric or date.
• DateFormat: Gets and sets the format of the date value.
• IgnoreArrayTitle: Indicates whether to ignore the title if the property of the object is an array.
• IgnoreNull: Indicates whether the null value should be ignored.
• IgnoreObjectTitle: Indicates whether to ignore the title if the property of the object is an object.
• NumberFormat: Gets and sets the format of the numeric value.
• TitleStyle: Gets and sets the style of the title.

In the following example code, we are exporting JSON data to an Excel file in C#.

// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

// Read JSON file
string jsonInput = File.ReadAllText("Sample.json");

// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = System.Drawing.Color.BlueViolet;
style.Font.IsBold = true;

// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.TitleStyle = style;
options.ArrayAsTable = true;

// Export JSON Data
JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options);

// Save Excel file
workbook.Save("ExportingJsonData.xlsx");

{
  "quiz": {
    "sport": {
      "q1": {
        "question": "Which one is correct team name in NBA?",
        "answer": "Huston Rocket"
      }
    },
    "maths": {
      "q1": {
        "question": "5 + 7 = ?",
        "answer": "12"
      },
      "q2": {
        "question": "12 - 8 = ?",
        "answer": "4"
      }
    }
  }
}

Export CSV Data to Excel in C#

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields.

The following code sample shows how we can open a CSV file and save it as an Excel file using Aspose.Cells library.

// Instantiate LoadOptions with CSV LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);

// Open CSV file as a Workbook object
Workbook wb = new Workbook("Business-Price.csv", loadOptions);

// Save the file as an Excel Documnt
wb.Save("CSVAsAnExcelDocument.xlsx");

Conclusion

In this post, you have seen how easily you can export data to Excel in C# from Array, DataTable, DataView, DataGrid and GridView. You have also seen how to export HTML, JSON, CSV Data to an Excel worksheet. Please check the documentation to learn more about these and several other features that Aspose.Cells API offers. If you have any questions, please feel free to contact us through our Support Forum.

See Also

• Create MS Excel Files Programmatically in C# without MS Office
• Import Data from JSON to Excel Worksheet in C# .NET
• Add Watermark to PDF in C#
• Create ZIP File in C#