从Excel表格读数据导数据的几种方法,兼容office2007和2003格式

 

下面是调用

DataSet DS = new DataSet();
                    if (ExcelDBA.ExcelToDataSet(SaveAsPath, DS))
                    {
                        DataTable DT = DS.Tables[0];

                    }

 下面是方法

/// <summary>
        /// 方法一(OLEDB):导出Excel表格第一个工作到DataSet(excel2007,兼容2003)
        /// </summary>
        /// <param name="filePath">文件绝对路经(Server.MapPath过的)</param>
        /// <param name="myDataSet">返回的DataSet变量</param>
        /// <returns>是否成功</returns>
        public static bool ExcelToDataSet(string filePath, DataSet myDataSet)
        {
            bool Back = false;
            try
            {
                //excel2007,兼容2003   
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
                //excel2003   
                // string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";   
                System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
                myConn.Open();
                //获取excel第一标签名 
                System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim();//标签名   
                string strCom = "SELECT *  FROM [" + tableName + "]";//查询语句   
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
                //创建一个DataSet对象      
                //DataSet myDataSet = new DataSet();
                //得到自己的DataSet对象      
                myCommand.Fill(myDataSet);
                //关闭此数据链接      
                myConn.Close();
                Back = true;
            }
            catch (Exception ex)
            { 
                
            }
            return Back;
        }

        /// <summary>
        /// 方法二(OLEDB):导出Excel表格第一个工作到DataSet(excel2007,兼容2003)
        /// </summary>
        /// <param name="filePath">文件绝对路经(Server.MapPath过的)</param>
        /// <returns>返回的DataSet</returns>
        public static DataSet ExcelToDataSet(string filePath)
        {
            //创建一个DataSet对象      
            DataSet myDataSet = new DataSet();
            try
            {
                //excel2007,兼容2003   
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
                //excel2003   
                // string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";   
                System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
                myConn.Open();
                //获取excel第一标签名 
                System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0][2].ToString().Trim();//标签名   
                string strCom = "SELECT *  FROM [" + tableName + "]";//查询语句   
                System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
                
                //得到自己的DataSet对象      
                myCommand.Fill(myDataSet);
                //关闭此数据链接      
                myConn.Close();
            }
            catch (Exception ex)
            {
                myDataSet = null;
            }
            return myDataSet;
        }

        /// <summary>
        /// 方法三(Excel.dll)：导出Excel表格第一个工作到DataSet(excel2007,兼容2003)必须先引用Excel.dll
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSetA(string filepath)
        {
            string excelfilePath = filepath;
            Excel.Application myExcel = new Excel.ApplicationClass();
            Excel.Workbooks myBooks = myExcel.Application.Workbooks;
            object oMissing = System.Reflection.Missing.Value;

            Excel.Workbook myBook = myBooks.Open(excelfilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            //Excel.Workbook myBook = myExcel.Workbooks[1];
            int sheetint = myBook.Worksheets.Count;//能得到sheet的数量

            Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];
            int rowsint = mySheet.UsedRange.Cells.Rows.Count; //得到行数
            int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数
            string[] colName=new string[columnsint+1];//例名
            System.Data.DataTable dt = new System.Data.DataTable("table1");
            for (int j = 1; j <= columnsint; j++)//添加表头
            {
                Excel.Range r = (Excel.Range)mySheet.Cells[1, j];
                string strValue = r.Text.ToString().Trim();
                if (strValue == "")
                {
                    colName[j]="F" + j.ToString();
                }
                else 
                { 
                    colName[j]=strValue;
                }
                dt.Columns.Add(colName[j], System.Type.GetType("System.String"));
            }
            DataSet myDs = new DataSet();
            myDs.Tables.Add(dt);
            DataRow myRow;
            myDs.Clear();

            for (int i = 2; i <= rowsint; i++) //第一行为标题，不读取
            {
                myRow = myDs.Tables["table1"].NewRow();
                for (int j = 1; j <= columnsint; j++)
                {

                    Excel.Range r = (Excel.Range)mySheet.Cells[i, j];
                    string strValue = r.Text.ToString();
                    myRow[colName[j]] = strValue;
                }
                myDs.Tables["table1"].Rows.Add(myRow);
            }
            myExcel.Quit();
            GC.Collect();
            return myDs;
        }

 这里附上Excel.dll文件件点击下载Excel.dll