C# GemBox Excel Import Error

Question

I am trying to import an excel file into a data table using GemBox and I keep getting this error:

Invalid data value when extracting to DataTable at SourceRowIndex: 1, and SourceColumnIndex: 1.

As far as I can tell my code is correct and my file is file fine. Does anyone have any ideas?

Thanks.

ExcelWorksheet Ew = ExFi.Worksheets[0];

            for (int i = 0; i < Ew.Columns.Count; ++i)
            {
                if (Ew.Rows[0].Cells[0, i].Value != null)
                    dsTable.Columns.Add(Ew.Rows[0].Cells[0, i].Value.ToString(), typeof(string));
            }

            try
            {
                Ew.ExtractToDataTable(dsTable, Ew.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, Ew.Rows[1], Ew.Columns[0]);
            }

Answer 1

I am having the same issue. Have you found an answer?

Regards

Answer 2

GemBox.Spreadsheet component doesn't automatically convert numbers to strings in ExtractToDataTable() method.

That's mainly because of the culture issues; someone would expect that number 12.4 is converted to "12.4" and someone else to "12,4".

So if your Excel file has cell with the value of type int, and corresponding column is of type string -> an exception would be thrown. To override that, you can use ExcelWorksheet.ExtractDataEvent.

Here's sample:

        // 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));

        // Manage ExtractDataError.WrongType error
        ws.ExtractDataEvent += (sender, e) =>
            {
                if (e.ErrorID == ExtractDataError.WrongType)
                {
                    e.DataTableValue = e.ExcelValue == null ? null : e.ExcelValue.ToString();
                    e.Action = ExtractDataEventAction.Continue;
                }
            };

        // Extract data to DataTable
        ws.ExtractToDataTable(dt, 1000, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);

Answer 3

Answer from Gembox above