链接excle的语句是:myConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file_path + ";Extended Properties=Excel 8.0;");
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + table_name + "]", myConnection);
DataSet ds = new DataSet();
结构ds中除了获得的excel的正常数据外,把excle的200多列空白的也查出来了,怎样可以有多少列数据就查询多少列呢?
------解决方案--------------------------------------------------------
- C# code
#region 读取Excel文件内容到DataSet中 public static DataSet ReadExcel(string xlsPath) { // 读取Excel数据,填充DataSet // 连接字符串 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002),并且第一行作为数据返回,且以文本方式读取 "data source=" + xlsPath + ";"; string sql_F = "SELECT * FROM [{0}]"; System.Data.OleDb.OleDbConnection conn = null; System.Data.OleDb.OleDbDataAdapter da = null; System.Data.DataTable tblSchema = null; IList<string> tblNames = null; // 初始化连接,并打开 conn = new System.Data.OleDb.OleDbConnection(connStr); try { conn.Open(); } catch (Exception ex) { throw ex; } // 获取数据源的表定义元数据 //tblSchema = conn.GetSchema("Tables"); tblSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); //关闭连接 conn.Close(); tblNames = new List<string>(); foreach (DataRow row in tblSchema.Rows) { tblNames.Add((string)row["TABLE_NAME"]); // 读取表名 } // 初始化适配器 da = new System.Data.OleDb.OleDbDataAdapter(); // 准备数据,导入DataSet DataSet ds = new DataSet(); foreach (string tblName in tblNames) { da.SelectCommand = new System.Data.OleDb.OleDbCommand(String.Format(sql_F, tblName), conn); try { da.Fill(ds, tblName); } catch { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); } throw; } } // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); } return ds; } #endregion
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
那我想问你,你不用建数据库吗,你数据库字段也没确定?你筛选数据总有个条件吧,根据这些还不能确定?总之问题就这,你都写了select * 还不让空白列出来不是搞笑么,再看看吧