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 control the conversion process for every cell (analyze data and modify values as required).

To export cell values to a data table, perform the following steps.

  1. Create an empty DataTable that will be able to hold data of the worksheet range.

    An empty DataTable, which will fit data contained in the specified worksheet range, can be created 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.

    IMPORTANT

    The WorksheetExtensions class is defined in DevExpress.Docs.v20.1.dll. Add this assembly to your project to use worksheet extensions. You need an active license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.

  2. Create a DataTableExporter instance using the WorksheetExtensions.CreateDataTableExporter method.
  3. Impement a custom converter for a specific DataTable column if required.

    To accomplish this, create a class that implements the ICellValueToColumnTypeConverter interface. The ICellValueToColumnTypeConverter.Convert method should perform the required conversion. The Convert method is called for each cell that is exported to the specified column. The converter transforms DateTime values into strings in MMMM-yyyy format and displays the "N/A" text if a cell contains an error.

    class MyConverter : ICellValueToColumnTypeConverter {
        public bool SkipErrorValues { get; set; }
        public CellValue EmptyCellValue { get; set; }
    
        public ConversionResult Convert(Cell readOnlyCell, CellValue cellValue, Type dataColumnType, out object result) {
            result = DBNull.Value;
            ConversionResult converted = ConversionResult.Success;
            if (cellValue.IsEmpty) {
                result = EmptyCellValue;
                return converted;
            }
            if (cellValue.IsError) {
                // You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
                //return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
                result = "N/A";
                return ConversionResult.Success;
            }
            result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue);
            return converted;
        }
    }
    
  4. Specify export options.

    Create a new instance of the DataTableExportOptions class and specify the required options. Add an instance of the previously implemented custom converter to the collection of custom converters available through the DataTableExportOptions.CustomConverters property.

  5. Call the DataTableExporterExtensions.Export method.
    Workbook wbook = new Workbook();
    wbook.LoadDocument("TopTradingPartners.xlsx");
    Worksheet worksheet = wbook.Worksheets[0];
    CellRange range = worksheet.Tables[0].Range;

    DataTable dataTable = worksheet.CreateDataTable(range, true);
    // Change the data type of the "As Of" column to text.
    dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");

    DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
    exporter.CellValueConversionError += exporter_CellValueConversionError;
    MyConverter myconverter = new MyConverter();
    exporter.Options.CustomConverters.Add("As Of", myconverter);
    // Set the export value for empty cell.
    myconverter.EmptyCellValue = "N/A";
    exporter.Options.ConvertEmptyCells = true;

    exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;

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