Export Data from Worksheet in .NET
Overview
This article explains how to export your Worksheet data to DataTable using C#. It covers the following topics
Format: Excel
- C# Excel to DataTable
- C# Convert Excel to DataTable
- C# Import Excel to DataTable
- C# Export to DataTable from Excel
Format: XLS
- C# XLS to DataTable
- C# Convert XLS to DataTable
- C# Import XLS to DataTable
- C# Export to DataTable from XLS
Format: XLSX
- C# XLSX to DataTable
- C# Convert XLSX to DataTable
- C# Import XLSX to DataTable
- C# Export to DataTable from XLSX
Format: ODS
- C# ODS to DataTable
- C# Convert ODS to DataTable
- C# Import ODS to DataTable
- C# Export to DataTable from ODS
How to Export Excel Data Using C#
How to Export Data from Worksheet
Aspose.Cells not only facilitates its users to import data to worksheets from external data sources but also allow them to export their worksheet data to a DataTable. As we know that DataTable is the part of ADO.NET and is used to hold data. Once the data is stored in a DataTable, it can be used in any way according to the requirements of users. Developers can also store this data (stored in DataTable) directly to a database if they wish. So, we can see that it becomes easier for the developers to manipulate worksheet data if it is exported to a DataTable.
How to Export Data to DataTable Using Aspose.Cells
Developers can easily export their worksheet data to a DataTable object by calling either ExportDataTable or ExportDataTableAsString method of the Cells class. Both methods are used in different scenarios, which are discussed below in more detail.
Columns Containing Strongly Typed Data
We know that a spreadsheet stores data as a sequence of rows and columns. If all values in the columns of a worksheet are strongly typed (that means all values in a column must have the same data type) then we can export the worksheet content by calling the ExportDataTable method of the Cells class. ExportDataTable method takes the following parameters to export worksheet data as DataTable object:
- Row number, the row number of the first cell data will be exported from.
- Column number, the column number of the first cell the data will be exported from.
- Number of rows, the number of rows to export.
- Number of columns, the number of columns to export.
- Export column names, a Boolean property that indicates whether the data in the first row of the worksheet should be exported as column names of the DataTable or not.
Steps: Exporting Data to DataTable
- Steps: Excel to DataTable in C#
- Steps: Convert Excel to DataTable in C#
- Steps: Import Excel to DataTable in C#
- Steps: Export to DataTable from Excel in C#
Code Steps:
- Load your Excel file in Workbook object.
- Workbook object can load Excel file formats e.g. XLS, XLSX, XLSM, ODS etc.
- Acces the first Worksheet in the Excel file.
- Choose your export area e.g. 7 rows and 2 columns starting from 1st cell of DataTable.
- Use ExportDataTable method to export the data into DataTable.
Columns Containing Non-Strongly Typed Data
If all values in the columns of a worksheet are not strongly typed (that means the values in a column may have the different data types) then we can export the worksheet content by calling the ExportDataTableAsString method of the Cells class. ExportDataTableAsString method takes the same set of parameters as that of the ExportDataTable method to export worksheet data as a DataTable object.
How to Export Range with Header
Data from a range can be exported to DataTable where a flag is available to skip header row in the exported data. Following code exports a range of data to DataTable with an argument ExportTableOptions which contains ExportColumnName flag. It is set to true if header information is there, hence it will not be included in data and set to false if no header is there and all rows are to be considered as data.