C# 读取Excel数据导出到DataGridView显示_excel导出datagridview

方式二 Excel引擎读取 Microsoft.Office.Interop.Excel

使用方式：引用 Microsoft.Office.Interop.Excel 调用Excel读取

优点：不受Excel版本限制，可指定sheet读取

缺点：读取速度慢，几千数据10分钟

代码如下：

    public static List<DataTable> excelToDataTable(string filePath,bool hasTitle = false)
    {
        List<DataTable> dtList = new List<DataTable>();
        int iRowCount;
        int iColCount;

        Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Sheets sheets;
        object oMissiong = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Workbook workbook = null;
        DataTable dt = new DataTable();

        try
        {
            if (app == null) return null;
            workbook = app.Workbooks.Open(filePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
            sheets = workbook.Worksheets;

            //将数据读入到DataTable中
            Microsoft.Office.Interop.Excel.Worksheet worksheet = sheets.get_Item(1);
            if (worksheet == null) return null;
            iRowCount = worksheet.UsedRange.Rows.Count;
            iColCount = worksheet.UsedRange.Columns.Count;
            //生成列头
            for (int i = 0; i < iColCount; i++)
            {
                var name = "column" + i;
                if (hasTitle)
                {
                    var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
                    if (!string.IsNullOrWhiteSpace(txt)) name = txt;
                }
                while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                dt.Columns.Add(new DataColumn(name, typeof(string)));
            }
            //生成行数据
            Microsoft.Office.Interop.Excel.Range range;
            int rowIdx = hasTitle ? 2 : 1;
            for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
            {
                DataRow dr = dt.NewRow();
                for (int iCol = 1; iCol <= iColCount; iCol++)
                {
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                    dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
                }
                dt.Rows.Add(dr);
            }
           dtList.Add(dt);
            for (int i = 2; i <= sheets.Count; i++)
            {
                worksheet=sheets.get_Item(i);//读取第一张表 
                iRowCount = worksheet.UsedRange.Rows.Count;
                iColCount = worksheet.UsedRange.Columns.Count;
                //生成列头
                for (int j = 0; j < iColCount; j++)
                {
                    var name = "column" + i;
                    if (hasTitle)
                    {
                        var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, j + 1]).Text.ToString();
                        if (!string.IsNullOrWhiteSpace(txt)) name = txt;
                    }
                    while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                    dt.Columns.Add(new DataColumn(name, typeof(string)));
                }
                //生成行数据
                Microsoft.Office.Interop.Excel.Range range1;
                int rowIdx1 = hasTitle ? 2 : 1;
                for (int iRow = rowIdx1; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
                        dr[iCol - 1] = (range1.Value2 == null) ? "" : range1.Text.ToString();
                    }
                    dt.Rows.Add(dr);
                }
                dtList.Add(dt);
            }
            return dtList;
        }
        catch { return null; }
        finally
        {
            workbook.Close(false, oMissiong, oMissiong);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            workbook = null;
            app.Workbooks.Close();
            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96