直接从Excel中读取出来,然后对"供应商" select distinct,然后分别对每个供应商做表.
代码如下:
1
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
}
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;";
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["供应商"].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 }
代码如下:
1
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
}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;";17
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT trim(供应商) as 供应商,零件名称,型号,批量,下线数,下线率,不合格原因,考核原因,考核金额 FROM [Sheet1$]", strConn);18
DataSet ExcelDs = new DataSet();19
try20
{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["供应商"].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
//save64
}65
return true;66
}
添加至收藏夹