Eexcel数据妆化成到Dataset

判断 Excel字段是否合法
判断 Excel字段是否合法
 2  publicbool IsExcel(string filename,string[] field)
 3         {
 4 #region 判断是否是 Excel 文件
 5 string connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ filename +";Extended Properties=Excel 8.0;";
 6             OleDbConnection conn =new OleDbConnection(connstr);
 7 try
 8             {
 9                 conn.Open();
10                 DataTable table=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[] {null, null, null, "TABLE"});
11 if(table.Rows.Count>0)
12                 {
13 string TableName=table.Rows[0]["TABLE_NAME"].ToString();
14 string sql="select top 1 * from ["+ TableName +"]";
15                     System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(sql,conn);
16                     DataSet ds=new DataSet();
17                     oda.Fill(ds);
18 if(field!=null&& field.Length>0)
19                     {
20 for(int i=0;i<field.Length;i++)
21                         {
22 if(!ds.Tables[0].Columns.Contains(field[i]))
23                             {
24 thrownew Exception("未找到字段："+ field[i]);
25                             }
26                         }
27                     }
28                 }
29 else
30                 {
31 thrownew Exception("没有找到任何表");
32                 }
33             }
34 catch
35             {
36 returnfalse;
37             }
38 finally
39             {
40                 conn.Close();
41             }
42 returntrue;
43 #endregion
44         }

 

///<summary>
 2 /// 将 Excel 转化为 DataSet
 3 ///</summary>
 4 ///<param name="filename">Excel 文件的完整路径</param>
 5 ///<returns></returns>
 6  publicstatic DataSet ExcelDataSet(string filename)
 7         {
 8             DataSet ds=new DataSet();
 9 string connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ filename +";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
10             OleDbConnection conn =new OleDbConnection(connstr);
11 try
12             {
13                 conn.Open();
14                 DataTable table=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[] {null, null, null, "TABLE"});
15 if(table.Rows.Count>0)
16                 {
17 string TableName=table.Rows[0]["TABLE_NAME"].ToString();
18 string sql="select * from ["+ TableName +"]";
19                     System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(sql,conn);
20                     oda.Fill(ds);
21 
22 
23 //检查是否是空行，是则删除该行
24 int ColumnCount=ds.Tables[0].Columns.Count;
25 for(int i=ds.Tables[0].Rows.Count-1;i>=0;i--)
26                     {
27 bool NullRow=true;
28 for(int j=0;j<ColumnCount;j++)
29                         {
30 string ddd=ds.Tables[0].Rows[i][j].ToString();
31 if(ds.Tables[0].Rows[i][j]!=System.DBNull.Value)
32                             {
33                                 NullRow=false;
34 break;
35                             }
36                         }
37 if(NullRow)
38                         {
39                             ds.Tables[0].Rows.RemoveAt(i);
40                         }
41                     }
42                 }
43 else
44                 {
45 thrownew Exception("没有找到任何表");
46                 }
47             }
48 catch
49             {
50 return ds;
51             }
52 finally
53             {
54                 conn.Close();
55             }
56 return ds;
57         }
58