Skip to main content
Row

WorksheetExtensions.CreateDataTable(Worksheet, CellRange, Boolean) Method

Creates an empty data table from the specified cell range.

You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this method in production code.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Docs.v24.1.dll

NuGet Package: DevExpress.Document.Processor

Declaration

public static DataTable CreateDataTable(
    this Worksheet sheet,
    CellRange range,
    bool rangeHasHeaders
)

Parameters

Name Type Description
sheet Worksheet

A Worksheet object that is a worksheet containing the specified range.

range CellRange

A CellRange of cells that will be transformed to a data table.

rangeHasHeaders Boolean

true, to use the content of the first row in a range as column names in a data table; otherwise, false.

Returns

Type Description
DataTable

A DataTable object that is the resulting data table.

Remarks

The CreateDataTable method creates an empty data table. The number of columns in a data table is the same as the number of columns in the specified range.

Pass true as the rangeHasHeaders parameter to use the first row to define column names. The first row determines the column data types if the rangeHasHeaders parameter is set to false.

Use the CellValue.Type of data contained in a cell to determine the column data type for the resulting data table.

Note

If the cell value type does not match the column type in the resulting data table, the exception Conversion error (Subscribe to the CellValueConversionError event) may occur. You can validate cell value types and handle the DataTableExporter.CellValueConversionError event to prevent this behavior.

You can also call the CreateDataTable(Worksheet, CellRange, Boolean, Boolean) method overload. In this case, all columns in the resulting table will have the string type.

Example

The example below shows how to export a cell range to a System.Data.DataTable object.

DataExport_Simple

View Example

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;

Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
CellRange range = worksheet.Selection;
bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked;

// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);

//Validate cell value types. If cell value types in a column are different, the column values are exported as text.
int firstDataRowIndex = rangeHasHeaders ? 1 : 0;
int rowCount = range.RowCount;
if (firstDataRowIndex < rowCount) {
    for (int col = 0; col < range.ColumnCount; col++) {
        CellValueType cellType = range[firstDataRowIndex, col].Value.Type;
        for (int r = firstDataRowIndex + 1; r < rowCount; r++) {
            if (cellType != range[r, col].Value.Type) {
                dataTable.Columns[col].DataType = typeof(string);
                break;
            }
        }
    }
}

// Create the exporter that obtains data from the specified range, 
// skips the header row (if required) and populates the previously created data table. 
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;

// Perform the export.
exporter.Export();
void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e)
{
MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
e.DataTableValue = null;
e.Action = DataTableExporterAction.Continue;
}
See Also