Invalid Data Value using Gembox.Spreadsheet extracting to a datatable

Question

I'm using Gembox to try and import Excel data into an app, but I just can't seem to get the ExtractToDataTable method to work as it should.

The exception message reads

"Invalid Data Value when extracting to DataTable at SourceRowIndex:1 and SourceColumnIndex:4"

Here's my code:

private void button1_Click(object sender, EventArgs e)
{
    string path = openFileDialog1.FileName;
    dataGridView1.DataSource = PopulateDataTable(PopulateExcelWorkSheet(path)).DefaultView;
}

public ExcelWorksheet PopulateExcelWorkSheet(string path)
{
    path = String.Empty;
    // DataTable daTable = new DataTable();
    ExcelFile exFile = new ExcelFile();
    exFile.LoadXlsx(@"filepath", XlsxOptions.None);

    ExcelWorksheet ws = exFile.Worksheets["Sheet1"];
    return ws;
}

public DataTable PopulateDataTable(ExcelWorksheet ws)
{
    DataTable daTable = CreateDataTable(ws.Rows.Count, ws.Columns.Count);
    int rowsInWs = ws.Rows.Count;
    int colsInWS = ws.Columns.Count;
    try
    {
        ws.ExtractToDataTable(daTable, rowsInWs, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[1], ws.Columns[1]);
    }
    catch {MessageBox.Show("ARGH"); }
    string mew = daTable.Rows[0][0].ToString();
    int rowCount = daTable.Rows.Count;
    int columnCount = daTable.Columns.Count;
    string uhm = String.Format("Rows in dataTable : {0} , Columns in dataTable {1} , Rows in WS: {2} , Columns in WS {3}", rowCount.ToString(), columnCount.ToString(), rowsInWs.ToString(), colsInWS.ToString());

    MessageBox.Show(uhm);

    string mes = ws.Rows[0].Cells[3].Value.ToString();
    MessageBox.Show(mes);
    return daTable;
}

public DataTable CreateDataTable(int rows, int columns)
{
    DataTable skeleton = new DataTable();

    for (int i = 0; i <= rows; i++)
    {
        skeleton.Rows.Add();
    }

    for (int x = 0; x <= columns; x++)
    {
        skeleton.Columns.Add();
    }
    return skeleton;
}

}

score 4 · Accepted Answer · answered Mar 08 '11 at 17:42

Here is the code that I am using that has been working fine for me:

private DataTable ReadExcelFile(string flatFilePath, bool firstRowHasHeaders)
    {
        SpreadsheetInfo.SetLicense("MY KEY");
        ExcelFile excelFile = new ExcelFile();
        excelFile.LoadXls(flatFilePath);

        int unnamed = 0;

        int cols;
        string[] columns;

        int curRow = 0;
        int curCol = 0;

        DataTable dataTable = new DataTable();
        ExcelWorksheet worksheet = excelFile.Worksheets[0];

        for (cols = 0; cols < worksheet.Rows[0].AllocatedCells.Count; cols++)
        {
            if (firstRowHasHeaders)
            {
                if (worksheet.Rows[0].Cells[cols].Value != null)
                    dataTable.Columns.Add(worksheet.Rows[0].Cells[cols].Value.ToString());
                else
                {
                    dataTable.Columns.Add("Unnamed Column " + (++unnamed));
                }

                curRow = 1;
            }
            else
            {
                dataTable.Columns.Add("Column " + (cols + 1));
            }
        }

        for (; curRow < worksheet.Rows.Count; curRow++)
        {
            columns = new string[cols];
            for (curCol = 0; curCol < cols; curCol++)
            {
                if (worksheet.Rows[curRow].Cells[curCol].Value == null)
                    columns[curCol] = "";
                else
                    columns[curCol] = worksheet.Rows[curRow].Cells[curCol].Value.ToString();
            }
            dataTable.Rows.Add(columns);
        }

        return dataTable;
    }

Granted, everything is added as a string to the data table and this is perfectly acceptable for our purposes.

Thanks man, I'll buy you a virtual beer some time. Only thing is it stops filling at 150 entries, any ideas ? — Dani, Mar 09 '11 at 08:39
I have never ran into that. My guess is it's either a license limitation (are you assigning your license key in the first line of the method?) or AllocatedCells.Count is not detecting your data correctly. — Dan Waterbly, Mar 09 '11 at 14:14
just saw that the row limit is because of the free-ness ... thanks for the code though, saved me loads of effort. Think it's possible to make a 600 row sheet into 4 tables then just join them? — Dani, Mar 10 '11 at 06:35

Mario Z · Answer 2 · 2019-11-19T11:01:22.953

You can also try DataGridViewConverter, an API for exporting/import DataGridView to Excel file in GemBox.Spreadsheet.

You can find here DataGridViewConverter example.

var workbook = ExcelFile.Load("input.xlsx");

// From ExcelFile to DataGridView.
DataGridViewConverter.ExportToDataGridView(
    workbook.Worksheets.ActiveWorksheet,
    this.dataGridView1,
    new ExportToDataGridViewOptions() { ColumnHeaders = true });

Invalid Data Value using Gembox.Spreadsheet extracting to a datatable

2 Answers2