大体思路是:将EXCEL的数据提出放在数据集中,在过循环将主表数据插入,在通过循环将从表插入:
代码如下:
Code
1
private void button1_Click(object sender, System.EventArgs e)
2![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
3
//选择文件
4
ofdSelectExcel.Filter = "Excel Files(*.xls)|*.xls";
5
ofdSelectExcel.RestoreDirectory = true;
6
if( ofdSelectExcel.ShowDialog() == DialogResult.OK )
7![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
8
if ( ofdSelectExcel.FileName.Trim().Length == 0)
9![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
10
MessageBox.Show(this,"Please select a excel file first!");
11
return;
12
}
13
else
14![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15
ImportExcelToSqlServer(ofdSelectExcel.FileName.Trim());
16
}
17
18
}
19
}
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
********************************************************
22
提取数据
23
public void ImportExcelToSqlServer(string fileName)
24![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
25
if (fileName == null)
26![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
27
throw new ArgumentNullException("filename string is null!");
28
}
29![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
if (fileName.Length == 0)
31![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
32
throw new ArgumentException("filename string is empty!");
33
}
34![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
string oleDBConnString = String.Empty;
36
oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
37
oleDBConnString += "Data Source=";
38
oleDBConnString += fileName;
39
oleDBConnString += ";Extended Properties=Excel 8.0;";
40![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
42
OleDbConnection oleDBConn = null;
43
OleDbDataAdapter oleAdMaster = null;
44
DataTable m_tableName=new DataTable();;
45
DataSet ds=new DataSet();
46
try
47![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
48
oleDBConn = new OleDbConnection(oleDBConnString);
49
oleDBConn.Open();
50
m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
51
52
if (m_tableName != null && m_tableName.Rows.Count > 0)
53![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
54![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString();
56![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
}
58
string sqlMaster;
59
sqlMaster=" SELECT * FROM ["+m_tableName+"]";
60
oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn);
61
oleAdMaster.Fill(ds,"m_tableName");
62
63
MailRebateManager manger=new MailRebateManager();
64
bool isSucess=manger.AddExceLGmailRebate(ds.Tables["m_tableName"],ApplicationVariable.HomeCompanyID);
65
if(isSucess)
66![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
67
MessageBox.Show("Manipulate Succs!");
68
}
69
else
70![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
71
MessageBox.Show("Manipulate Failed");
72
}
73
}
74
catch(Exception ex)
75![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
76
MessageBox.Show(ex.Message);
77
SimpleLogger.Log(ex);
78
try
79![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
80![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81
}
82
catch (OleDbException e)
83![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
84
SimpleLogger.Log(e);
85
MessageBox.Show("An exception of type " + e.GetType() +");
86
}
87
}
88![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
89
90
}
91
*****************************************
92
将数据进行处理分别插入主表和从表
93
public bool AddExceLGmailRebate(DataTable tb,string homeCompanyID)
94![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
95
bool ret=false;
96
SqlConnection con=null;
97
98
DataTable table=new DataTable();
99
table=tb;
100
101
string sConn = PublicManager.GetDBConnectionString(homeCompanyID);
102
con=new SqlConnection();
103
con.ConnectionString=sConn;
104
105
SqlTransaction tran=null;
106
SqlCommand com=null;
107
SqlCommand comm=null;
108
109
try
110![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
{
111
con.Open();
112
tran = con.BeginTransaction();
113
114
if (table != null && table.Rows.Count > 0)
115![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
116
117
for(int i=1;i<table.Rows.Count;i++)
118![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
119
120
string m_PromoCode=Convert.ToString(table.Rows[i][0]);
121
if(m_PromoCode=="")
122![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
123
m_PromoCode=Convert.ToString(table.Rows[i-1][0]);
124
}
125
if(m_PromoCode.Length>50 )
126![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
127
m_PromoCode=m_PromoCode.Substring(0,50);
128
}
129![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
130![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
131
string m_ItemDescription=Convert.ToString(table.DefaultView[i][1]);
132
if(m_ItemDescription.IndexOf("(")>0)
133![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
134
int num=m_ItemDescription.IndexOf("(");
135
m_ItemDescription=m_ItemDescription.Substring(0,num);
136
if(m_ItemDescription.Length>50)
137![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
138
m_ItemDescription=m_ItemDescription.Substring(0,50);
139
}
140
141
}
142
if(m_ItemDescription.Length>50)
143![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
144
m_ItemDescription=m_ItemDescription.Substring(0,50);
145
}
146![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
147![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
148
string begin=Convert.ToString(table.DefaultView[i][2]);
149
string m_BeginPromoPeriodDate;
150
string m_EndPromoPeriodEndDate;
151
if(begin=="")
152![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
153
continue;
154
}
155
else
156![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
157
string beginTime=begin.Substring(0,8);
158
beginTime=beginTime.Replace("/","-");
159
m_BeginPromoPeriodDate=beginTime;
160
string endTime=begin.Substring(begin.Length-8);
161
endTime=endTime.Replace("/","-");
162
m_EndPromoPeriodEndDate=endTime;
163
}
164![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
165![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
166
string m_RebateAmountStr=Convert.ToString(table.DefaultView[i][3]);
167
if(m_RebateAmountStr.Length >9)
168![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
169
m_RebateAmountStr=m_RebateAmountStr.Substring(0,9);
170
}
171
decimal m_RebateAmount;
172
if(m_RebateAmountStr=="")
173![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
174
m_RebateAmount=0;
175
}
176
else
177![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
178
m_RebateAmount= Convert.ToDecimal(m_RebateAmountStr);
179
}
180![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
181![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
182
string m_TotalSoldStr=Convert.ToString(table.DefaultView[i][7]);
183
if(m_TotalSoldStr.Length >4)
184![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
185
m_TotalSoldStr=m_TotalSoldStr.Substring(0,4);
186
}
187
int m_TotalSold;
188
if(m_TotalSoldStr=="")
189![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
190
m_TotalSold=0;
191
}
192
else
193![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
194
m_TotalSold=Convert.ToInt32(m_TotalSoldStr);
195
}
196![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
197
string m_RebateReserveStr=Convert.ToString(table.DefaultView[i][8]);
198
if(m_RebateReserveStr.Length>9)
199![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
200
m_RebateReserveStr=m_RebateReserveStr.Substring(0,9);
201
}
202
decimal m_RebateReserve;
203
if(m_RebateReserveStr=="")
204![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
205
m_RebateReserve=0;
206
}
207
else
208![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
209
m_RebateReserve=Convert.ToDecimal(m_RebateReserveStr);
210
211
}
212![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
213
string m_RedeemedStr=Convert.ToString(table.DefaultView[i][17]);
214
if(m_RedeemedStr.Length >8)
215![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
216
m_RedeemedStr=m_RedeemedStr.Substring(0,8);
217
}
218
decimal m_Redeemed;
219
if(m_RedeemedStr=="")
220![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
221
m_Redeemed=0;
222
}
223
else
224![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
225
m_Redeemed=Convert.ToDecimal(m_RedeemedStr);
226
}
227
string m_PromoItem=Convert.ToString(table.DefaultView[i][23]);
228
if(m_PromoItem.Length >50)
229![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
230
m_PromoItem=m_PromoItem.Substring(0,50);
231
}
232![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
233
DateTime m_InDate;
234
m_InDate=DateTime.Now;
235
236
string m_sqlMaster="INSERT INTO act.dbo.Newegg_GMailRebate(PromoCode,PromoItem,RebateAmount,ItemDescription,BeginPromoPeriodDate,
237
EndPromoPeriodEndDate,PostMaskDate,TotalSold,RebateReserve,Redeemed,InDate)VALUES(’"+m_PromoCode+"’,’"+m_PromoItem+"’,’"+m_RebateAmount+"’,
238
’"+m_ItemDescription+"’,’"+m_BeginPromoPeriodDate+"’,’"+m_EndPromoPeriodEndDate+"’,
239
’"+m_InDate+"’,’"+m_TotalSold+"’,’"+m_RebateReserve+"’,’"+m_Redeemed+"’,’"+m_InDate+"’)";
240
comm=new SqlCommand(m_sqlMaster,con);
241
comm.Transaction =tran;
242
comm.ExecuteNonQuery ();
243
comm.Dispose();
244
245
}
246
247
248
for(int i=1;i<table.Rows.Count;i++)
249![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
250
251
string m_PromoCode=Convert.ToString(table.Rows[i][0]);
252
if(m_PromoCode=="")
253![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
254
continue;
255
}
256
int m_PromoCodeTransactionID=GetMasterTransactionID(m_PromoCode,homeCompanyID);
257
258
string m_InvoiceNumber=Convert.ToString(table.Rows[i][4]);
259
if(m_InvoiceNumber.Length >30)
260![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
261
m_InvoiceNumber=m_InvoiceNumber.Substring(0,30);
262
}
263
if(m_InvoiceNumber=="")
264![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
265
m_InvoiceNumber="0";
266
}
267![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
268
string m_InvoiceDate=Convert.ToString(table.DefaultView[i][6]);
269
if(m_InvoiceDate=="")
270![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
271
DateTime m_InDate;
272
m_InDate=DateTime.Now;
273
m_InvoiceDate=Convert.ToString(m_InDate);
274
}
275![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
276
string m_serialNumberStr=Convert.ToString(table.Rows[i][5]);
277
if(m_serialNumberStr.Length >4)
278![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
279
m_serialNumberStr=m_serialNumberStr.Substring(0,4);
280
}
281
if(m_serialNumberStr=="")
282![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
283
m_serialNumberStr="0";
284
}
285
int m_serialNumber=Convert.ToInt32(m_serialNumberStr);
286
287
288
string m_TotalValidStr=Convert.ToString( table.DefaultView[i][9]);
289
if(m_TotalValidStr.Length >4)
290![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
291
m_TotalValidStr=m_TotalValidStr.Substring(0,4);
292
293
}
294
if(m_TotalValidStr=="")
295![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
296
m_TotalValidStr="0";
297
}
298
int m_TotalValid=Convert.ToInt32 (m_TotalValidStr);
299![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
300![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
301
string m_TotalInValidStr=Convert.ToString( table.DefaultView[i][10]);
302
if(m_TotalInValidStr.Length >4)
303![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
304
m_TotalInValidStr=m_TotalInValidStr.Substring(0,4);
305
}
306
if(m_TotalInValidStr=="")
307![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
308
m_TotalInValidStr="0";
309
}
310
int m_TotalInValid=Convert.ToInt32(m_TotalInValidStr);
311
312![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
313
string m_ProcessFeeStr=Convert.ToString(table.DefaultView[i][13]);
314
if(m_ProcessFeeStr.Length >9)
315![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
316
m_ProcessFeeStr=m_ProcessFeeStr.Substring(0,9);
317
}
318
if(m_ProcessFeeStr=="")
319![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
320
m_ProcessFeeStr="0";
321
}
322![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
323
decimal m_ProcessFee=Convert.ToDecimal(m_ProcessFeeStr);
324![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
325![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
326
string m_sqlDetails="INSERT INTO act.dbo.Newegg_GMailExcelMaster(PromoCodeTransactionID,InvoiceNumber,InvoiceDate,SerialNumber,
327
TotalValid,TotalInvalid,ProcessFee)VALUES(’"+m_PromoCodeTransactionID+"’,’"+m_InvoiceNumber+"’,’"+m_InvoiceDate+"’,
328
’"+m_serialNumber+"’,’"+m_TotalValid+"’,’"+m_TotalInValid+"’,’"+m_ProcessFee+"’)";
329
com=new SqlCommand(m_sqlDetails,con);
330
com.CommandTimeout=60;
331
com.Transaction =tran;
332
com.ExecuteNonQuery();
333
com.Dispose();
334
m++;
335
}
336
tran.Commit();
337![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
338
ret=true;
339![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
340
}
341
342
}
343
catch(Exception ex)
344![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
345
346
try
347![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
348
tran.Rollback();
349
}
350
catch (SqlException e)
351![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
352
if (tran.Connection != null)
353![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
354
MessageBox.Show("An exception of type " + e.GetType() +");
355
}
356
}
357
MessageBox.Show("Error come up row number:"+m.ToString());
358
MessageBox.Show("Error details:"+ex.Message);
359
SimpleLogger.Log(ex);
360
ret=false;
361
}
362
finally
363![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
364
con.Close();
365
con.Dispose();
366
367
}
368
return ret;
369![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
370
}
371![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
372