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

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

Code Steps:

  1. Load your Excel file in Workbook object.
    • Workbook object can load Excel file formats e.g. XLS, XLSX, XLSM, ODS etc.
  2. Acces the first Worksheet in the Excel file.
  3. Choose your export area e.g. 7 rows and 2 columns starting from 1st cell of DataTable.
  4. 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.

Advance 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