Asp.Net 导入Excel自动获取表名

时间:2021-11-02 09:30:51
public static DataSet ReadExcel(string Path, string fileType)
    {
        //服务器需要安装驱动
        //http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
        //try
        //{
            //string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\"";

            string connectionString = "";
            if (fileType == "xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            else
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
            }
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            string selectCommandText = "";
            OleDbDataAdapter adapter = null;
            DataSet dataSet = null;

            System.Data.DataTable sTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string tableName = sTable.Rows[0][2].ToString().Trim();//默认表名Sheet1,如果修改了则无法导入,所以需要动态获取表面
            //selectCommandText = "select * from [Sheet1$]";//默认表名Sheet1
            selectCommandText = "select * from [" + tableName + "]";
            adapter = new OleDbDataAdapter(selectCommandText, connectionString);
            dataSet = new DataSet();
            adapter.Fill(dataSet, "table1");
            connection.Close();
            connection.Dispose();
            return dataSet;
        //}
        //catch
        //{
        //    return null;
        //}
    }