C# Open Excel File

        public static object OpenFile(string fileName)
        {
            if (!File.Exists(fileName))
            {
                MessageBox.Show("File not found");
                return null;
            }
            string connectionString = string.Empty;
            const string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
            const string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
            switch (Path.GetExtension(fileName))
            {
                case ".xls": //Excel 97-03
                    connectionString = string.Format(Excel03ConString, fileName);
                    break;
                case ".xlsx": //Excel 07
                    connectionString = string.Format(Excel07ConString, fileName);
                    break;
            }

            OleDbConnection con = new OleDbConnection(connectionString);
            con.Open();
            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"].ToString()), connectionString);
            var ds = new DataSet();
            const string tableName = "excelData";
            adapter.Fill(ds, tableName);
            DataTable data = ds.Tables[tableName];
            return data;
        }
    }

Leave a comment