使用Gembox.Spreadsheet提取到数据表的无效数据类型

Thought I'd start a new thread as the old one's been dead for 2 years.

以为我会开始一个新线程，因为旧的已经死了2年。

I've been trying to import Excel data into an app for about a week now and this is the closest I've come : I'm using Gembox, as Access is not a viable option, but I just can't seem to get the bloody ExtractToDataTable method to work as it should. The exception message reads thus: "Invalid Data Value when extracting to DataTable at SourceRowIndex:1 and SourceColumnIndex:4" I've tried the 2 available solutions from back in the day but they're not helping.

我一直试图将Excel数据导入应用程序大约一个星期了，这是我最接近的：我正在使用Gembox，因为Access不是一个可行的选择，但我似乎无法得到血腥的ExtractToDataTable方法可以正常工作。因此异常消息读取：“在SourceRowIndex中提取到DataTable时无效的数据值：1和SourceColumnIndex：4”我在当天尝试了2个可用的解决方案，但他们没有帮助。

Any help would be immensey appreciated. Dani

任何帮助都将受到极大的赞赏。达尼

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("FUCKIN HELL"); }
        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;
    }
}

}

2 个解决方案

#1

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.

当然，所有内容都作为字符串添加到数据表中，这对于我们的目的是完全可以接受的。

#2

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

您还可以尝试DataGridViewConverter，这是一个用于在GemBox.Spreadsheet 3.7中导出/导入DataGridView到Excel文件的API。

You can find samples of DataGridViewConverter usage on the following links: http://www.gemboxsoftware.com/SampleExplorer/Spreadsheet/Windows.Forms/Windows.Forms/cs http://www.gemboxsoftware.com/support/articles/datagridview-to-excel-export-import

您可以在以下链接中找到DataGridViewConverter用法的示例：http：//www.gemboxsoftware.com/SampleExplorer/Spreadsheet/Windows.Forms/Windows.Forms/cs http://www.gemboxsoftware.com/support/articles/datagridview -to-Excel的进出口

#1