Excel 作为数据源

1.首先上传Excel 文件，和普通上传方法一样.

2.导入Excel 里面的数据：

 1 private bool ImportExcel() 
 2     {
 3         bool bResult = true;
 4         string filepath = "";
 5         string path = HttpContext.Current.Request.MapPath("~/UploadFiles/");
 6         try
 7         {
 8             DirectoryInfo di = new DirectoryInfo(path);
 9             FileInfo[] fis = di.GetFiles();
10             if (fis.Length <= 0)
11             {
12                 lblMessage.Text = "no avalible file ，please upload it firstly!";
13                 return false;
14        }
15        Array.Sort(fis, new SortFile(SortOption.CreationTime));
16        filepath = Path.Combine(path, fis[0].ToString());
17       //filepath = @"D:\tt.xls";//test
18       DataSet ds = ExcelDataSource(filepath, ExcelSheetName(filepath)[0].ToString());
19             dt = ds.Tables[0];
20             dt.Columns.Add("Status");
21             AllocationManager am = new AllocationManager();
22             am.AddAllocation(dt, this.LoginName);
23 
24         }
25         catch (Exception ex)
26         {
27             bResult = false;
28             lblMessage.Text += ex.Message;
29         }
30         return bResult;
31     }

得到存数据的Excelsheft name:

 1 public ArrayList ExcelSheetName(string filepath)
 2    {
 3        ArrayList al = new ArrayList();
 4        string strConn;
 5        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
 6        
 7        OleDbConnection conn = new OleDbConnection(strConn);
 8        conn.Open();
 9        DataTable sheetNames = conn.GetOleDbSchemaTable
10        (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
11        conn.Close();
12        foreach (DataRow dr in sheetNames.Rows)
13        {
14            al.Add(dr[2]);
15        }
16        return al;
17    }

从此sheft 里面取出用户数据：

 1 public DataSet ExcelDataSource(string filepath, string sheetname)
 2 
 3    {
 4 
 5        string strConn;
 6 
 7        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
 8 
 9        OleDbConnection conn = new OleDbConnection(strConn);
10 
11        OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
12 
13        DataSet ds = new DataSet();
14        oada.Fill(ds);
15        conn.Close();
16 
17        return ds;
18 
19    }

取上传文件中取最新文件时需要用到的操作帮助类：

View Code

 1 public enum SortOption
 2    {
 3        FileName,
 4        Extension,
 5        CreationTime
 6    }
 7 
 8    public enum OrderOption
 9    {
10        UP = 1,
11        Down = -1
12    }
13 实现文件Comparer; 
14    public class SortFile : IComparer
15    {
16        SortOption mso;
17        OrderOption odo;
18        public SortFile(SortOption so)
19        {
20            mso = so;
21            odo = OrderOption.Down;
22        }
23        public SortFile(SortOption so, OrderOption oo)
24        {
25            mso = so;
26            odo = oo;
27        }
28 
29        int IComparer.Compare(object a, object b)
30        {
31            try
32            {
33                FileInfo fa = (FileInfo)a;
34                FileInfo fb = (FileInfo)b;
35 
36                switch (mso)
37                {
38                    case SortOption.FileName:
39                        return ((int)odo) * String.Compare(fa.Name, fb.Name, true);
40                    //break; 
41                    case SortOption.Extension:
42                        return ((int)odo) * String.Compare(Path.GetExtension(fa.Name), Path.GetExtension(fb.Name), true);
43                    //break; 
44                    case SortOption.CreationTime:
45                        return ((int)odo) * DateTime.Compare(fa.CreationTime, fb.CreationTime);
46                    //break; 
47                    default:
48                        break;
49                }
50            }
51            catch { }
52            return 0;
53        }
54    }