C#读取Excel表，输出DataTable

最新推荐文章于 2023-09-26 11:13:33 发布

转载最新推荐文章于 2023-09-26 11:13:33 发布 · 82 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/delicious28/archive/2010/04/01/1702299.html

本文介绍了一种使用Microsoft Excel对象模型从Excel文件中读取数据并将其转换为.NET Framework中的DataTable的方法。此过程包括打开Excel文件、读取工作表中的数据、创建DataTable对象并将数据填充到DataTable中。

        public System.Data.DataTable GetExcelData(string excelFilePath)
        {
            try
            {
                Excel.Application app = new Excel.Application();
                Excel.Sheets sheets;
                Excel.Workbook workbook;
                System.Data.DataTable dt = new System.Data.DataTable();
                if (app == null)
                {
                    return null;
                }

                object oMissiong = System.Reflection.Missing.Value;

                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

                //将数据读入到DataTable中——Start   

                sheets = workbook.Worksheets;
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
                if (worksheet == null)
                    return null;

                string cellContent;
                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                Excel.Range range;
                for (int iRow = 1; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();

                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        range = (Excel.Range)worksheet.Cells[iRow, iCol];

                        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();

                        if (iRow == 1)
                        {
                            dt.Columns.Add(cellContent);
                        }
                        else
                        {
                            dr[iCol - 1] = cellContent;
                        }
                    }

                    if (iRow != 1)
                        dt.Rows.Add(dr);
                }

                //将数据读入到DataTable中——End   
                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;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                return dt;
            }
            catch
            {
                return null;
            }
        }

转载于:https://www.cnblogs.com/delicious28/archive/2010/04/01/1702299.html