.net 打开Excel文档并转为DataTable

 

        /// <summary>
        /// 打开Excel文档并转为DataTable
        /// </summary>
        /// <returns></returns>
        public static DataTable ExcelWorksheetToDataTable()
        {
            DataTable dtExecl = new DataTable();
            double Ver = GetExcelVer();
            if (Ver <= 0)
            {
                MessageBox.Show("The computer does not have Excel installed.", "prompt");
                return null;
            }
            string ExcelFile = "";
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection conn = null;
                ExcelFile = ofd.FileName;
                if (ExcelFile.Length > 0)
                {
                    try
                    {
                        string StrConn = "Provider={0};" + "Data Source=" + ExcelFile + ";" + "Extended Properties='Excel {1};HDR=YES; IMEX=1'";
                        if ((new System.IO.FileInfo(ExcelFile).Extension).ToLower() == ".xlsx")
                        {
                            StrConn = string.Format(StrConn, "Microsoft.ACE.OLEDB.12.0", "12.0");
                        }
                        else
                        {
                            StrConn = string.Format(StrConn, "Microsoft.Jet.OLEDB.4.0", "8.0");
                        }

                        conn = new OleDbConnection(StrConn);
                        DataSet ds = new DataSet();
                        conn.Open();
                        OleDbDataAdapter myCommand = null;
                        myCommand = new OleDbDataAdapter("select * from [sheet1$]", StrConn);
                        myCommand.Fill(ds, "sheet1");
                        if (ds != null && ds.Tables.Count > 0)
                        {
                            dtExecl = ds.Tables[0];
                            if (!dtExecl.Columns.Contains("IntAUID"))
                            {
                                dtExecl.Columns.Add("IntAUID", typeof(int));
                            }
                            int i = 1;
                            foreach (DataRow dr in dtExecl.Rows)
                            {
                                dr["IntAUID"] = i;
                                i += 1;
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.ToString());
                        return null;
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return dtExecl;
        }

Code by 博客园-曹永思

        /// <summary>
        /// 获取当前计算机安装Excel版本号
        /// </summary>
        /// <returns></returns>
        private static double GetExcelVer()
        {
            Type objExcelType = Type.GetTypeFromProgID("Excel.Application");
            if (objExcelType == null)
            {
                return 0;
            }
            object objApp = Activator.CreateInstance(objExcelType);
            if (objApp == null)
            {
                return 0;
            }
            object objVer = objApp.GetType().InvokeMember("Version", BindingFlags.GetProperty, null, objApp, null);
            double Ver = Convert.ToDouble(objVer.ToString());
            return Ver;
        }