.net 開啟Excel文檔並轉為DataTable

標籤：style   blog   class   code   java   tar   

 

        /// <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;        }

View Code

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;        }

View Code