01. try
02. {
03. int x = 1;
04. FileInfo excel = new FileInfo((@"D:\Application\Billpayment\FileTest\COOP_180531_012.xls"));
05. using (var package = new ExcelPackage(excel))
06. {
07. var workbook = package.Workbook;
08.
09.
10. var worksheet = workbook.Worksheets.First();
11.
12. string strConnString = "Server=xxxxxxxxxx;UID=sa;PASSWORD=xxxxxx;database=mybase";
13. var objConn = new SqlConnection(strConnString);
14. objConn.Open();
15.
16.
17. int totalRows = worksheet.Dimension.End.Row;
18. for (int i = 10; i <= totalRows; i++)
19. {
20. MessageBox.Show(worksheet.Cells[i, 1].ToString());
21. string strSQL = "INSERT INTO tbl_billpayment_confirm (billpayment_date,order_no,ref_no_1,ref_no_2,billpayment_name,amount,status_flag,user_id) "
22. + " VALUES ("
23. + "GETDATE()" + ", "
24. + " '" + x + "', "
25. + " '" + worksheet.Cells[i, 1].Text.ToString() + "', "
26. + " '" + worksheet.Cells[i, 2].Text.ToString() + "', "
27. + " '" + worksheet.Cells[i, 3].Text.ToString() + "', "
28. + " '" + worksheet.Cells[i, 4].Text.ToString() + "', "
29. + " '" + null + "', "
30. + " '" + null + "' "
31. + ")";
32. var objCmd = new SqlCommand(strSQL, objConn);
33. objCmd.ExecuteNonQuery();
34.
35. }
36.
37.
38. MessageBox.Show("เสร็จ");
39. x++;
40. }
41. }
42. catch (Exception ex)
43. {
44. MessageBox.Show(ex.Message);
45. }
46.}
