Export Data from Worksheet in .NET

Overview

This article explains how to export your Worksheet data to a DataTable using C#. It covers the following topics

Format: Excel

Format: XLS

Format: XLSX

Format: 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 allows them to export their worksheet data to a DataTable. As we know that DataTable is 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. The ExportDataTable method takes the following parameters to export worksheet data as a DataTable object:

  • Row number – the row number of the first cell from which data will be exported.
  • Column number – the column number of the first cell from which the data will be exported.
  • 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

Code Steps:

  1. Load your Excel file in a Workbook object.
    • The Workbook object can load Excel file formats, e.g., XLS, XLSX, XLSM, ODS, etc.
  2. Access the first Worksheet in the Excel file.
  3. Choose your export area, e.g., 7 rows and 2 columns starting from the first cell of the DataTable.
  4. Use the ExportDataTable method to export the data into a 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 different data types) then we can export the worksheet content by calling the ExportDataTableAsString method of the Cells class. The 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 a DataTable where a flag is available to skip the header row in the exported data. The following code exports a range of data to a DataTable with an argument ExportTableOptions, which contains the ExportColumnName flag. It is set to true if header information is present, in which case the header will be excluded from the data, and set to false if no header is present and all rows are considered data.

Advanced topics

  • Export Excel Data to DataTable without any Formatting
  • Export HTML String Value of the Cells to the DataTable
  • Export Visible Rows Data from Worksheet
  • Ignore Hidden Columns while Exporting Worksheet Data to Data Table
  • Rename duplicate columns automatically while exporting worksheet data
Import Data into Worksheet Get Cell String Value with and without Formatting