直接C#读取Excel,然后对"供应商" select distinct,然后分别对每个供应商做表.
C#读取Excel的代码如下:
- 2 protected void Page_Load(object
- sender, EventArgs e)
- 3 {
- 4 DataSet ds = ImportExcel
- (Server.MapPath("ExcelFile/供应商违约扣款.xls"));
- 5 GridView1.DataSource =
- ds.Tables["ExcelInfo"].DefaultView;
- 6 GridView1.DataBind();
- 7
- 8 ToDataBase(ds);
- 9 } //完成C#读取Excel
- 10
- 11 private DataSet ImportExcel
- (string strFileName)
- 12 {
- 13 if (strFileName == "") return null;
- 14 string strConn = "Provider=
- Microsoft.Jet.OLEDB.4.0;" +
- 15 "Data Source=" + strFileName + ";" +
- 16 "Extended Properties=Excel
- 8.0;HDR=Yes;IMEX=1";
- 17 OleDbDataAdapter ExcelDA =
- new OleDbDataAdapter("SELECT trim(供应商)
- as 供应商,零件名称,型号,批量,下线数,下线率,
- 不合格原因,考核原因,考核金额 FROM [Sheet1$]",
- strConn);
- 18 DataSet ExcelDs = new DataSet();
- 19 try
- 20 {
- 21 ExcelDA.Fill(ExcelDs, "ExcelInfo");
- 22
- 23 }
- 24 catch (Exception err)
- 25 {
- 26 System.Console.WriteLine
- (err.ToString());
- 27 }
- 28 return ExcelDs;
- 29 }
- 30
- 31
- 32 private bool ToDataBase(DataSet ds)
- 33 {
- 34 DataTable dtSupplier = new DataTable
- ("dtSupplier");
- 35
- 36
- 37 DataView dv = ds.Tables[0].DefaultView;
- 38
- 39 string[] column = { "供应商" };
- 40 dtSupplier = dv.ToTable(true, column);
- 41
- 42 for (int i = 0; i 〈 dtSupplier.Rows.
- Count; i++)
- 43 {
- 44
- 45 DataRow[] r = ds.Tables[0].Select
- ("供应商='" + dtSupplier.Rows[i]["供应商"].
- ToString() + "'");
- 46
- 47 //插父表
- 48
- 49 for (int j = 0; j 〈 r.Length; j++)
- 50 {
- 51 string ItemName = r[j]["零件名称"].ToString();
- 52 string scale = r[j]["型号"].ToString();
- 53 string batch = r[j]["批量"].ToString();
- 54 string downLine = r[j]["下线数"].ToString();
- 55 string downPercent = r[j]["下线率"].ToString();
- 56 string outReason = r[j]["不合格原因"].ToString();
- 57 string reason = r[j]["考核原因"].ToString();
- 58 string amt = r[j]["考核金额"].ToString();
- 59
- 60 //插子表
- 61 }
- 62
- 63 //save
- 64 }
- 65 return true;
- 66 }
【编辑推荐】
