C#读取Excel到DataTable

最新推荐文章于 2024-07-18 11:45:17 发布

D_lunar

最新推荐文章于 2024-07-18 11:45:17 发布

阅读量2.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： C# 文章标签： c# excel

本文链接：https://blog.youkuaiyun.com/D_lunar/article/details/113991415

C# 专栏收录该内容

23 篇文章

订阅专栏

这篇博客介绍了如何使用C#将Excel文件中的数据读取到DataTable中。首先通过OpenFileDialog选择Excel文件，然后利用Microsoft.Office.Interop.Excel库打开文件并读取指定工作表的数据。接着，通过OleDbDataAdapter和DataSet实现数据填充到DataTable。最后，关闭资源并释放对象，确保进程安全退出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、将Excel中的表格数据读取到DataTable中：

OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Title = "打开Excel文件";
openFileDialog1.Filter = "Files|*.xls;*.xlsx";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
   {
       textBox1.Text = openFileDialog1.FileName;
       #region 读取excel到dt
       Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
       xApp.Visible = false;
       Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks.Open(openFileDialog1.FileName, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
       Microsoft.Office.Interop.Excel.Worksheet xSheet = xBook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
       string tableName = xSheet.Name;
       DataTable dt1 = DBhelper.getDataSetFromExcelTable(tableName, openFileDialog1.FileName);
       xBook.Close(true);
       Marshal.ReleaseComObject(xBook);
       Marshal.ReleaseComObject(xSheet);
       xBook = null;
       xSheet = null;
       System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
       System.Diagnostics.Process[] excelprocess2 = System.Diagnostics.Process.GetProcessesByName("WINEXCEL");
       foreach (System.Diagnostics.Process pr in excelprocess2)
       {
            pr.Kill();//停止关联进程
       }
       #endregion
  }

2、读取Excel中指定表中的表格数据到DataTable中的公共类

        /// <summary>
        /// 读取Excel中指定表中的数据到DataTable中
        /// </summary>
        /// <param name="tableName">Excel中的表名</param>
        /// <param name="dataFilePath">Excel文件路径</param>
        /// <returns></returns>
        static public DataTable getDataSetFromExcelTable(string tableName,string dataFilePath)
        {
            string oleDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataFilePath+ ";Extended Properties='Excel 12.0;IMEX=1';";
            OleDbConnection connct = new OleDbConnection(oleDB);
            try
            {
                connct.Open();
                DataSet dataSet = new DataSet();
                string sql = "select * from " + "[" + tableName + "$]";
                OleDbDataAdapter datpTextdapter = new OleDbDataAdapter(sql, oleDB);

                try
                {
                    datpTextdapter.Fill(dataSet);
                    connct.Close();
                    return dataSet.Tables[0];
                }
                finally
                {
                    dataSet.Dispose();
                    datpTextdapter.Dispose();
                }
            }
            finally
            {
                connct.Dispose();
                //command.Dispose();
            }
        }