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

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 2

I had the some issue, i overcame it by explicitly accessing worksheet cells

DataTable dtResult = new DataTable();
int nRows = ws.Rows.Count;
int nCols = 3; //change this according to number of columns in your sheet, for some reason ws.columns.count returns 0
for (int i = 0; i < nCols ; i++)
    dtResult.Columns.Add();

for (int i = 0; i < nRows; i++)
{
    if (ws.Cells[i, 0].Value != null)
        dtResult.Rows.Add(ws.Cells[i, 0].Value.ToString(), ws.Cells[i, 1].Value.ToString(), ws.Cells[i, 2].Value.ToString());
}

return dtResult;

Answer 3

I had the same problem. I'd been using Typed DataSets so when I tried to populate one of my tables I had that error.

The problem here is the decimal numbers in the Excel file. Initialy I assigned the columns with decimal values to System.Decimal and that error was thrown.

The solution is to change the Type of the Column to System.Double. Why? I don't know, but it worked.