.net讀取excel檔案

在.net中可以通過oledb讀取,先讀取excel檔案中的所有表的表名,然後遍曆所有表中的資料，將資料添加到Dataset中。

 

Code
        public DataSet dsXls()
        {
            string oledbText;
            string[] tableNames = GetExcelSheetNames();
            OleDbConnection con;
            OleDbDataAdapter oledbDta;
            DataSet dsXls = new DataSet();

            con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Data Source=" + FileSrc + ";Extended Properties=Excel 8.0;");
            foreach (string table in tableNames)
            {
                oledbText = "select * from [" + table + "]";
                oledbDta = new OleDbDataAdapter(oledbText, con);
                oledbDta.Fill(dsXls, table);
            }
            return dsXls;
        }

        /**//// <summary>
        /// This mehtod retrieves the excel sheet names from 
        /// an excel workbook.
        /// </summary>    
        /// <returns>String[]</returns>
        private String[] GetExcelSheetNames()
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel file to the file you
                // will search.
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + FileSrc + ";Extended Properties=Excel 8.0;";
                // Create connection object by using the preceding connection string.
                objConn = new OleDbConnection(connString);
                // Open connection with the database.
                objConn.Open();
                // Get the data table containg the schema guid.
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                // Loop through all of the sheets if you want too
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }

        }