不使用oledb将excel工作表数据导入datagrid

    using Excel = Microsoft.Office.Interop.Excel;

    private void ProcessExcel(string filepath)
    {

            Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass();

            Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel.Sheets sheets = theWorkbook.Worksheets;

            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

            Excel.Range range = worksheet.UsedRange;

            System.Array myvalues = (System.Array)range.Cells.Value2;

            int vertical = myvalues.GetLength(0);
            int horizontal = myvalues.GetLength(1);



            string[] headers = new string[horizontal];
            string[] data = new string[horizontal];


            DataTable ResultsHeader = New DataTable();
            DataSet ds = New DataSet();


            for (int x = 1; x <= vertical; x++)
            {
                    Utils.inicializarArrays(datos);
                    for (int y = 1; y <= horizontal; y++)
                    {
                        if (x == 1)
                        {
                            headers[y - 1] = myvalues.GetValue(x, y).ToString();
                        }
                        else
                        {
                            string auxdata = "";
                            if (myvalues.GetValue(x, y) != null)
                                auxdata = myvalues.GetValue(x, y).ToString();
                            data[y - 1] = auxdata;
                        }

                    }
                    if(x == 1) //headers
                    {
                            for(int w = 0; w < horizontal; w++)
                            {
                                    ResultsHeader.Columns.Add(New DataColumn(headers[w], GetType(string)));
                            }
                            ds.Tables.Add(ResultsHeader);
                    }
                    else
                    {
                            DataRow dataRow = ds.Tables[0].NewRow();
                            for(int w = 0; w < horizontal; w++)
                            {
                                    dataRow(headers[w]) = data[w]
                            }
                            ds.Tables[0].Rows.Add(dataRow);
                    }
            }
            DataView myDataView = new DataView();
            myDataView.Table = ds.Tables[0];
            MydataGrid.CurrentPageIndex = 0;
            MydataGrid.DataSource = myDataView;
            MydataGrid.DataBind();
    }

我参加聚会迟到了,但我还有一些值得补充的东西!我试过Juan的代码,但它并不是现成的。我在网上搜索了几个小时后对它做了一点修改,让它完全按照原来海报的要求来做(因为我需要做同样的事情)。我不得不将其他来源的代码拼凑在一起,不幸的是,我没有跟踪我尝试和更改的位和块,所以我不能对此发表太多评论。

以下代码在VisualStudio2008和.NET3.5中工作。此外,当数据放入数组时,格式也会丢失(例如,日期会变成双倍,这需要使用 DateTime.FromOADate() 把它改回去)。问题是,从编码的角度看,您无法判断值是实际的double还是日期,但如果您提前知道某列将是日期,则在将数据插入表时对其进行格式化。

private void processExcel(string filename)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    var missing = System.Reflection.Missing.Value;

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Open(filename, false, true, missing, missing, missing, true, Excel.XlPlatform.xlWindows, '\t', false, false, 0, false, true, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    Excel.Range xlRange = xlWorkSheet.UsedRange;
    Array myValues = (Array)xlRange.Cells.Value2;

    int vertical = myValues.GetLength(0);
    int horizontal = myValues.GetLength(1);

    DataTable dt = new DataTable();

    // must start with index = 1
    // get header information
    for (int i = 1; i <= horizontal; i++)
    {
        dt.Columns.Add(new DataColumn(myValues.GetValue(1,i).ToString()));
    }

    // Get the row information
    for (int a = 2; a <= vertical; a++)
    {
        object[] poop = new object[horizontal];
        for (int b = 1; b <= horizontal; b++)
        {
            poop[b - 1] = myValues.GetValue(a, b);
        }
        DataRow row = dt.NewRow();
        row.ItemArray = poop;
        dt.Rows.Add(row);
    }

    // assign table to default data grid view
    dataGridView1.DataSource = dt;

    xlWorkBook.Close(true, missing, missing);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

上述代码无效。

private void processExcel(string filename)
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    var missing = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Open(filename, false, true, missing, missing, missing, true, Excel.XlPlatform.xlWindows, '\t', false, false, 0, false, true, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    Excel.Range xlRange = xlWorkSheet.UsedRange;
    Array myValues = (Array)xlRange.Cells.Value2;

    int vertical = myValues.GetLength(0);
    int horizontal = myValues.GetLength(1);

    DataTable dt = new DataTable();

    // must start with index = 1
    // get header information
    try
    {
        for (int i = 1; i <= horizontal; i++)
        {
            dt.Columns.Add(new DataColumn(Convert.ToString(myValues.GetValue(1, i))));
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }


    // Get the row information
    for (int a = 2; a <= vertical; a++)
    {
        object[] rows = new object[horizontal];
        for (int b = 1; b <= horizontal; b++)
        {
            rows[b - 1] = myValues.GetValue(a, b);
        }
        DataRow row = dt.NewRow();
        row.ItemArray = rows;
        dt.Rows.Add(row);
    }

    // assign table to default data grid view
    dataGridView1.DataSource = dt;

    xlWorkBook.Close(true, missing, missing);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}