How to: Export a Worksheet Range to a DataTable

  • 4 minutes to read

You can export data from a worksheet cell range to a DataTable. In this case, worksheet columns are transformed into DataTable columns. Cell values are used to populate the DataTable, and you can specify conversion methods and take control of the conversion process for every cell (analyze the data and modify the value as required ).

To export cell values to a data table, the following steps are required:

  1. Add a reference to the DevExpress.Docs.v20.1.dll assembly to your project. Note that the Universal Subscription or an additional Office File API Subscription is required to distribute this assembly.
  2. Create a DataTableExporter instance using the WorksheetExtensions.CreateDataTableExporter method.
  3. Call the DataTableExporterExtensions.Export method of the DataTableExporter.

You can easily create an empty DataTable, which will fit the data contained in the specified worksheet range by using the CreateDataTable method of the Worksheet (WorksheetExtensions.CreateDataTable). The newly created DataTable contains the same number of columns as the worksheet range. Column data types are set automatically by analyzing the content of the first row in a range that contains data. Column names can be obtained from the first row of a range if the rangeHasHeaders method parameter is set to true.

NOTE

The CreateDataTable method is located in the WorksheetExtensions object. When you add a reference to the assembly containing this extension, all extension methods are added to the available Worksheet methods. You can call the method using the Worksheet.CreateDataTable notation.

The following code demonstrates how to export a selected range to a DataTable.

DataExport_Simple

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.
            for (int col = 0; col < range.ColumnCount; col++)
            {
                CellValueType cellType = range[0, col].Value.Type;
                for (int r = 1; r < range.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;
        }