C#用OleDb读取Excel表格的问题！求指点!

Question

c#oledb

Answer 1

        public ArrayList GetExcelTables(string FilePath)
        {
            Microsoft.Office.Interop.Excel.Application obj = default(Microsoft.Office.Interop.Excel.Application);
            Microsoft.Office.Interop.Excel.Workbook objWB = default(Microsoft.Office.Interop.Excel.Workbook);            string SheetName = string.Empty;
            ArrayList SheetNameList = new ArrayList();            try
            {
                obj = (Microsoft.Office.Interop.Excel.Application)Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
                objWB = obj.Workbooks.Open(FilePath, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                for (int i = 0; i < objWB.Worksheets.Count; i++)
                {
                    SheetName = ((Microsoft.Office.Interop.Excel.Worksheet)objWB.Worksheets[i + 1]).Name;
                    SheetNameList.Add(SheetName);
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message, "工作簿名称读取失败");
            }
            finally
            {
                try
                {
                    objWB.Close(Type.Missing, Type.Missing, Type.Missing);
                    obj.Quit();
                }
                catch (Exception exp1)
                {
                    MessageBox.Show(exp1.Message, "工作簿关闭失败");
                }
            }            return SheetNameList;
        }你应该用excel.dll中的对象去读取excel的工作簿名称,而不是OLEDB
或者你试试换成NPOI

Answer 2

读Excel是用oledb好还是用Microsoft.Office.Interop.Excel.dll 好？

Answer 3

用EXCEL.DLL
public DataTable GetData(string sExcelFilePath, string sSheetName)
        {
            DataSet Ds = new DataSet();
          //Excel 2003
            string sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;Hdr=Yes\"";
            //Excel 2007
                sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES";            sConn = string.Format(sConn, sExcelFilePath.Trim());
            OleDbConnection conn = new OleDbConnection(sConn);
            conn.Open();
            OleDbDataAdapter myCommand = null;
            string sExcel = string.Format("select * from [{0}$]", sSheetName);
            myCommand = new OleDbDataAdapter(sExcel, sConn);
            myCommand.Fill(Ds, sSheetName);
            conn.Close();
            myCommand.Dispose();
            myCommand = null;
            return Ds.Tables[0];
        }