Click or drag to resize
ExtractToDataTableOptions Class
Represents options for extracting data from ExcelWorksheet to DataTable.
Inheritance Hierarchy
SystemObject
  GemBox.SpreadsheetExtractToDataTableOptions

Namespace: GemBox.Spreadsheet
Assembly: GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1000
Syntax
public sealed class ExtractToDataTableOptions

The ExtractToDataTableOptions type exposes the following members.

Constructors
  NameDescription
Public methodExtractToDataTableOptions(String, Int32)
Initializes an instance of ExtractToDataTableOptions class.
Public methodExtractToDataTableOptions(Int32, Int32, Int32)
Initializes an instance of ExtractToDataTableOptions class.
Top
Properties
  NameDescription
Public propertyExtractDataOptions
Gets or sets ExtractDataOptions.
Public propertyMappingCollection
Gets the collection used for mapping ExcelWorksheet columns to DataTable columns.
Public propertyNumberOfRows
Gets or sets the number of rows being extracted from ExcelWorksheet.
Public propertyStartColumn
Gets or sets the index of the first column of data being extracted from ExcelWorksheet.
Public propertyStartRow
Gets or sets the index of the first row of data being extracted from ExcelWorksheet.
Top
Events
  NameDescription
Public eventExcelCellToDataTableCellConverting
Event that fires when converting ExcelCell value to DataTable cell value.
Top
Examples

Following code demonstrates how to handle ExcelCellToDataTableCellConverting. Code demonstrates how to make generic conversion from unsupported type to string.

private static void ExtractDataEvent()
{
    // Create new ExcelFile
    ExcelFile ef = new ExcelFile();

    // Add sheet
    ExcelWorksheet ws = ef.Worksheets.Add("Sheet1");

    // Fill sheet
    for (int i = 0; i < 5; i++)
    {
        ws.Cells[i, 0].Value = i;   // Integer value
        ws.Cells[i, 1].Value = "Row: " + i; // String value
    }

    // Initialize DataTable
    DataTable dt = new DataTable();
    dt.Columns.Add("id", typeof(string));
    dt.Columns.Add("text", typeof(string));

    ExtractToDataTableOptions options = new ExtractToDataTableOptions(0, 0, ws.Rows.Count);
    options.ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow;

    // Define custom conversion for incompatible types.
    options.ExcelCellToDataTableCellConverting += (sender, e) =>
        {
            if (!e.IsDataTableValueValid)
            {
                e.DataTableValue = e.ExcelCell.Value == null ? null : e.ExcelCell.Value.ToString();
                e.Action = ExtractDataEventAction.Continue;
            }
        };

    // Extract data to DataTable
    ws.ExtractToDataTable(dt, options);
}
See Also