Click or drag to resize
ExcelWorksheetExtractToDataTable Method
Extracts data to DataTable with specified extract options, see ExtractToDataTableOptions.

Namespace: GemBox.Spreadsheet
Assembly: GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1000
Syntax
public void ExtractToDataTable(
	DataTable dataTable,
	ExtractToDataTableOptions options
)

Parameters

dataTable
Type: System.DataDataTable
Destination DataTable.
options
Type: GemBox.SpreadsheetExtractToDataTableOptions
Options that describe how data will be extracted, see ExtractToDataTableOptions.
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