excel读取问题

Question

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" +
                                "data source=" + FilePath;
// 查询语句
string sql = "SELECT * FROM [sheet1$]";ds = new DataSet();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, connStr);
da.Fill(ds);    // 填充DataSet采用如上的方法读取excel文件，问题是，当我不知道工作表的名称sheet1时能不能读取，该怎么读取？

Answer 1

不会 EXCEL 只会MSsqlServer

Answer 2

DataTable tabelNames = OleDbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

Answer 3

OleDbConnection   excon=new   OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data   Source="   +pth   +   ";Extended   Properties=Excel   8.0");     
  excon.Open();   
  DataTable   schemaTable   =   excon.GetOleDbSchemaTableSystem.Data.OleDb.OleDbSchemaGuid.Tables,null);   
  string   tblname=schemaTable.Rows[0][2].ToString().Trim();

Answer 4

private String[] GetExcelSheetNames(string excelFile)
{
  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=" + excelFile + ";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();
    }
  }
}

Answer 5

运行结果是：找不到可安装的 ISAM。这是什么原因呢？是缺少引用什么文件吗？

Answer 6

Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;' 

Answer 7

既然你已经知道了表名，那就再创建一个查询的 SQL 嘛// 查询语句
string sql = "SELECT * FROM [sheet1$]"; 
改成
string sql = string.Fromat("SELECT * FROM [{0}]", "获取到的表名" );

Answer 8

问题是只知道excel文件名，不知道工作表的名称

Answer 9

DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 
                    //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素 
                    string tableName = dataTable.Rows[0][2].ToString().Trim(); 
                    tableName = "[" + tableName.Replace("'", "") + "]"; 

Answer 10

public void InsetDataFromExcel() 
        { 
            openFileDialog = new OpenFileDialog(); 
            openFileDialog.Filter = "Excel files(*.xls)|*.xls"; 
            if (openFileDialog.ShowDialog() == DialogResult.OK) 
            { 
                FileInfo fileInfo = new FileInfo(openFileDialog.FileName); 
                string filePath = fileInfo.FullName; 
                string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";                 try 
                { 
                    OleDbConnection oleDbConnection = new OleDbConnection(connExcel); 
                    oleDbConnection.Open();                     SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnString"].ToString());                     //获取excel表 
                    DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 
                    //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素 
                    string tableName = dataTable.Rows[0][2].ToString().Trim(); 
                    tableName = "[" + tableName.Replace("'", "") + "]"; 
                    //利用SQL语句从Excel文件里获取数据 
                    //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName; 
                    string query = "SELECT 日期,开课城市,讲师,课程名称,持续时间 FROM " + tableName; 
                    dataSet = new DataSet(); 
                    //OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection); 
                    //OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand); 
                    OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, connExcel);                     oleAdapter.Fill(dataSet, "gch_Class_Info"); 
                    //dataGrid1.DataSource = dataSet; 
                    //dataGrid1.DataMember = tableName; 
                    dataGrid1.SetDataBinding(dataSet, "gch_Class_Info"); 
                    //从excel文件获得数据后，插入记录到SQL Server的数据表 
                    DataTable dataTable1 = new DataTable();                     SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate, 
classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info", sqlConnection1);                     SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);                     sqlDA1.Fill(dataTable1); 
                    foreach (DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows) 
                    { 
                        DataRow dataRow1 = dataTable1.NewRow();                         dataRow1["classDate"] = dataRow["日期"]; 
                        dataRow1["classPlace"] = dataRow["开课城市"]; 
                        dataRow1["classTeacher"] = dataRow["讲师"]; 
                        dataRow1["classTitle"] = dataRow["课程名称"]; 
                        dataRow1["durativeDate"] = dataRow["持续时间"]; 
                        dataTable1.Rows.Add(dataRow1); 
                    } 
                    Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录"); 
                    sqlDA1.Update(dataTable1);                     oleDbConnection.Close(); 
                } 
                catch (Exception ex) 
                { 
                    Console.WriteLine(ex.ToString()); 
                } 
            } 
        }