1 /// <summary>
2 /// 导入
3 /// </summary>
4 private void btn_In_Click(object sender, EventArgs e)
5 {
6 int i = DataTableToDB();
7 MessageBox.Show("成功导入" + i + "条商品信息！");
8 }
9
10
11
12
13 /// <summary>
14 /// 获取后缀名为*.xlsx的文件
15 /// </summary>
16 public void GetFile()
17 {
18 System.IO.DirectoryInfo dir = new DirectoryInfo(VPath);
19 if (dir.Exists)//判读是否存在改文件
20 {
21 fiList = dir.GetFiles("*.xlsx"); //获取后缀名为*.xlsx的文件
22 }
23 }
24
25
26 /// <summary>
27 /// Excel数据转化为DataTable
28 /// </summary>
29 /// <param name="strSheetName"></param>
30 /// <param name="strExcelFileName">文件路径</param>
31 /// <returns>返回DataTable</returns>
32 public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
33 {
34 string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'", strExcelFileName);
35 string strExcel = string.Format("select * from [{0}$]", strSheetName);
36 DataSet ds = new DataSet();
37
38 using (OleDbConnection conn = new OleDbConnection(strConn))
39 {
40 conn.Open();
41 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
42 adapter.Fill(ds, strSheetName);
43 conn.Close();
44 }
45
46 return ds.Tables[strSheetName];
47 }
48
49
50
51 public int DataTableToDB()
52 {
53 GetFile();
54 int count = 0;
55 string _strExcelFileName = "";
56 for (int i = 0; i < fiList.Length; i++)
57 {
58 _strExcelFileName = dir + "\\" + fiList[i];
59
60 DataTable dtExcel = Global.g_objDb.ExcelToDataTable(_strExcelFileName, "Sheet1");
61 for (int j = 0; j < dtExcel.Rows.Count; j++)
62 {
63 if ((ReturnSqlResultCount("select * from A where a1='" + dtExcel.Rows[j][0].ToString() + "'")) > 0)
64 {
65 continue;
66 }
67 else
68 {
69 Global.g_objDb.InsertDataToAccess(dtExcel.Rows[j][0].ToString(), dtExcel.Rows[j][1].ToString(), dtExcel.Rows[j][2].ToString(), dtExcel.Rows[j][3].ToString(), dtExcel.Rows[j][4].ToString(), dtExcel.Rows[j][5].ToString(), dtExcel.Rows[j][6].ToString(), dtExcel.Rows[j][7].ToString());
70
71 count++;
72 }
73 }
74 }
75
76 return count;
77 }
78
79 String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Access_DataBase.mdb;Jet OLEDB:Database Password=123456"";
80
81 OleDbConnection Connection = new OleDbConnection(connectionString);
82
83 /// <summary>
84 /// 执行一查询语句语句，同时返回bool值
85 /// </summary>
86 public bool InsertDataToAccess(string col1, string col2, string col3, string col4, string col5, string col6, string col7, string col8)
87 {
88 bool resultState = false;
89
90 Connection.Open();
91 string strSQL = "insert into spdm(a,b,c,d,e,f,g,h) values('" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "')";
92 OleDbTransaction myTrans = Connection.BeginTransaction();
93 OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans);
94
95 try
96 {
97 command.ExecuteNonQuery();
98 myTrans.Commit();
99 resultState = true;
100 }
101 catch
102 {
103 myTrans.Rollback();
104 resultState = false;
105 }
106 finally
107 {
108 Connection.Close();
109 }
110 return resultState;
111 }
112
113 /// <summary>
114 /// 执行一查询语句，同时返回查询结果数目
115 /// </summary>
116 /// <param name="strSQL"></param>
117 /// <returns></returns>
118 public int ReturnSqlResultCount(string strSQL)
119 {
120 int sqlResultCount = 0;
121
122 try
123 {
124 Connection.Open();
125 OleDbCommand command = new OleDbCommand(strSQL, Connection);
126 OleDbDataReader dataReader = command.ExecuteReader();
127
128 while (dataReader.Read())
129 {
130 sqlResultCount++;
131 }
132 dataReader.Close();
133 }
134 catch
135 {
136 sqlResultCount = 0;
137 }
138 finally
139 {
140 Connection.Close();
141 }
142 return sqlResultCount;
143 }
