|
|
|
ถามเรื่อง import excel to database หน่อยครับทำตามบทเรียนแล้วแต่ |
|
|
|
|
|
|
|
นี่เลยครับโค้ต
Code (VB.NET)
Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
If (UploadFile.ShowDialog = DialogResult.OK) Then
txtFile.Text = UploadFile.FileName
Else
txtFile.Text = ""
End If
End Sub
Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
If (rdoReportImport.Checked) = True Then
Dim UlFileName As String
Dim i As Integer
UlFileName = txtFile.Text
'*** Create Excel.Application ***'
'สร้าง Excel.application
Dim xlsApp As New Excel.Application
Dim xlsSheet1 As Excel.Worksheet
Dim xlsBook As Excel.Workbook
xlsBook = xlsApp.Workbooks.Open(UlFileName)
xlsBook.Application.Visible = False
xlsSheet1 = xlsBook.Worksheets(1)
'*** Create DataTable ***'
Dim dt As New System.Data.DataTable
Dim dr As System.Data.DataRow
'*** Column ***'
dt.Columns.Add("Jobref")
dt.Columns.Add("ReqtoPlant")
dt.Columns.Add("Mode")
dt.Columns.Add("ShipperName")
dt.Columns.Add("PO")
dt.Columns.Add("INV")
dt.Columns.Add("ETD")
dt.Columns.Add("ATD")
dt.Columns.Add("ETA")
dt.Columns.Add("ATA")
dt.Columns.Add("FWDR")
dt.Columns.Add("GrossWeight")
dt.Columns.Add("Quantity")
dt.Columns.Add("Unit")
dt.Columns.Add("ICLCBM")
dt.Columns.Add("1x20")
dt.Columns.Add("1x40")
dt.Columns.Add("1x40HQ")
dt.Columns.Add("ContrnCBM")
dt.Columns.Add("Port")
dt.Columns.Add("Country")
dt.Columns.Add("BL_no")
dt.Columns.Add("DO")
dt.Columns.Add("Rcvd_Doc")
dt.Columns.Add("Starting")
dt.Columns.Add("Released")
dt.Columns.Add("PlanDelivery")
dt.Columns.Add("Delivery")
dt.Columns.Add("Delivery_Place")
dt.Columns.Add("Remark")
dt.Columns.Add("BG_Date")
dt.Columns.Add("BG_Amount")
dt.Columns.Add("ImportCustomer")
dt.Columns.Add("TypeOfEntry")
dt.Columns.Add("SKUNumber")
dt.Columns.Add("DetailOfGoods")
dt.Columns.Add("CIF")
dt.Columns.Add("HSCode")
dt.Columns.Add("TarrifRate")
dt.Columns.Add("Duty")
dt.Columns.Add("Vat")
dt.Columns.Add("Eprivilege")
dt.Columns.Add("Eduty")
dt.Columns.Add("Vat2")
dt.Columns.Add("CostSaving")
dt.Columns.Add("KPI_LeadTime")
dt.Columns.Add("Billing")
dt.Columns.Add("KPI_Billing")
dt.Columns.Add("Duty2")
dt.Columns.Add("DutyExcludedVat")
dt.Columns.Add("DueDateDuty")
dt.Columns.Add("Freight")
dt.Columns.Add("Exwork")
dt.Columns.Add("DO1")
dt.Columns.Add("OtherCharge")
dt.Columns.Add("Total")
dt.Columns.Add("ConsolBill")
dt.Columns.Add("FrtBillNo")
dt.Columns.Add("FrtBillDate")
dt.Columns.Add("TranSportation")
dt.Columns.Add("TotalAmount")
dt.Columns.Add("BillingMonth")
dt.Columns.Add("FrtFromUS")
dt.Columns.Add("SavingFrtCost")
dt.Columns.Add("IsTypeImport")
dt.Columns.Add("IstypeExport")
i = 2
Do While Not Trim(xlsSheet1.Cells.Item(i, 1).Value) = ""
'*** Rows ***'
dr = dt.NewRow
dr("Jobref") = xlsSheet1.Cells.Item(i, 1).Value
dr("ReqtoPlant") = xlsSheet1.Cells.Item(i, 2).Value
dr("Mode") = xlsSheet1.Cells.Item(i, 3).Value
dr("ShipperName") = xlsSheet1.Cells.Item(i, 4).Value
dr("PO") = xlsSheet1.Cells.Item(i, 5).Value
dr("INV") = xlsSheet1.Cells.Item(i, 6).Value
dr("ETD") = xlsSheet1.Cells.Item(i, 7).Value
dr("ATD") = xlsSheet1.Cells.Item(i, 8).Value
dr("ETA") = xlsSheet1.Cells.Item(i, 9).Value
dr("ATA") = xlsSheet1.Cells.Item(i, 10).Value
dr("FWDR") = xlsSheet1.Cells.Item(i, 11).Value
dr("GrossWeight") = xlsSheet1.Cells.Item(i, 12).Value
dr("Quantity") = xlsSheet1.Cells.Item(i, 13).Value
dr("Unit") = xlsSheet1.Cells.Item(i, 14).Value
dr("ICLCBM") = xlsSheet1.Cells.Item(i, 15).Value
dr("1x20") = xlsSheet1.Cells.Item(i, 16).Value
dr("1x40") = xlsSheet1.Cells.Item(i, 17).Value
dr("1x40HQ") = xlsSheet1.Cells.Item(i, 18).Value
dr("ContrnCBM") = xlsSheet1.Cells.Item(i, 19).Value
dr("Port") = xlsSheet1.Cells.Item(i, 20).Value
dr("Country") = xlsSheet1.Cells.Item(i, 21).Value
dr("BL_no") = xlsSheet1.Cells.Item(i, 22).Value
dr("DO") = xlsSheet1.Cells.Item(i, 23).Value
dr("Rcvd_Doc") = xlsSheet1.Cells.Item(i, 24).Value
dr("Starting") = xlsSheet1.Cells.Item(i, 25).Value
dr("Released") = xlsSheet1.Cells.Item(i, 26).Value
dr("PlanDelivery") = xlsSheet1.Cells.Item(i, 27).Value
dr("Delivery") = xlsSheet1.Cells.Item(i, 28).Value
dr("Delivery_Place") = xlsSheet1.Cells.Item(i, 29).Value
dr("Remark") = xlsSheet1.Cells.Item(i, 30).Value
dr("BG_Date") = xlsSheet1.Cells.Item(i, 31).Value
dr("BG_Amount") = xlsSheet1.Cells.Item(i, 32).Value
dr("ImportCustomer") = xlsSheet1.Cells.Item(i, 33).Value
dr("TypeOfEntry") = xlsSheet1.Cells.Item(i, 34).Value
dr("SKUNumber") = xlsSheet1.Cells.Item(i, 35).Value
dr("DetailOfGoods") = xlsSheet1.Cells.Item(i, 36).Value
dr("CIF") = xlsSheet1.Cells.Item(i, 37).Value
dr("HSCode") = xlsSheet1.Cells.Item(i, 38).Value
dr("TarrifRate") = xlsSheet1.Cells.Item(i, 39).Value
dr("Duty") = xlsSheet1.Cells.Item(i, 40).Value
dr("Vat") = xlsSheet1.Cells.Item(i, 41).Value
dr("Eprivilege") = xlsSheet1.Cells.Item(i, 42).Value
dr("Eduty") = xlsSheet1.Cells.Item(i, 43).Value
dr("Vat2") = xlsSheet1.Cells.Item(i, 44).Value
dr("CostSaving") = xlsSheet1.Cells.Item(i, 45).Value
dr("KPI_LeadTime") = xlsSheet1.Cells.Item(i, 46).Value
dr("Billing") = xlsSheet1.Cells.Item(i, 47).Value
dr("KPI_Billing") = xlsSheet1.Cells.Item(i, 48).Value
dr("Duty2") = xlsSheet1.Cells.Item(i, 49).Value
dr("DutyExcludedVat") = xlsSheet1.Cells.Item(i, 50).Value
dr("DueDateDuty") = xlsSheet1.Cells.Item(i, 51).Value
dr("Freight") = xlsSheet1.Cells.Item(i, 52).Value
dr("Exwork") = xlsSheet1.Cells.Item(i, 53).Value
dr("DO1") = xlsSheet1.Cells.Item(i, 54).Value
dr("OtherCharge") = xlsSheet1.Cells.Item(i, 55).Value
dr("Total") = xlsSheet1.Cells.Item(i, 56).Value
dr("ConsolBill") = xlsSheet1.Cells.Item(i, 57).Value
dr("FrtBillNo") = xlsSheet1.Cells.Item(i, 58).Value
dr("FrtBillDate") = xlsSheet1.Cells.Item(i, 59).Value
dr("TranSportation") = xlsSheet1.Cells.Item(i, 60).Value
dr("TotalAmount") = xlsSheet1.Cells.Item(i, 61).Value
dr("BillingMonth") = xlsSheet1.Cells.Item(i, 62).Value
dr("FrtFromUS") = xlsSheet1.Cells.Item(i, 63).Value
dr("SavingFrtCost") = xlsSheet1.Cells.Item(i, 64).Value
dr("IsTypeImport") = 1
dr("IstypeExport") = 0
dt.Rows.Add(dr)
i = i + 1
Loop
'*** End DataTable ***'
'*** Insert to Database ***'
Dim sqlInsert As String = ""
Dim comInsert As New OleDbCommand
Try
ConToData()
For i = 0 To dt.Rows.Count - 1
'*** Insert Record ***'
sqlInsert = "INSERT INTO Detail (Jobref,ReqtoPlant,Mode,ShipperName,PO,INV,ETD,ATD,ETA,ATA,FWDR,GrossWeight,Quantity,Unit,"
sqlInsert &= "ICLCBM,1x20,1x40,1x40HQ,ContrnCBM,Port,Country,BL_no,DO,Rcvd_Doc,Starting,Released,PlanDelivery,Delivery,Delivery_Place,Remark," '///finish Tab Data1
sqlInsert &= "BG_Date,BG_Amount,ImportCustomer,TypeOfEntry,SKUNumber,DetailOfGoods,CIF,HSCode,TarrifRate,Duty,Vat,Eprivilege,Eduty,Vat2,CostSaving,"
sqlInsert &= "KPI_LeadTime,Billing,KPI_Billing,Duty2,DutyExcludedVat,DueDateDuty," '///finish Tab Data2
sqlInsert &= "Freight,Exwork,DO1,OtherCharge,Total,ConsolBill,FrtBillNo,FrtBillDate,TranSportation,TotalAmount,BillingMonth,FrtFromUS,SavingFrtCost,IsTypeImport,IsTypeExport)"
sqlInsert &= " VALUES ('" & dt.Rows(i)("Jobref") & "','" & dt.Rows(i)("ReqtoPlant") & "','" & dt.Rows(i)("Mode") & "','" & dt.Rows(i)("ShipperName") & "','" & dt.Rows(i)("PO") & "',"
sqlInsert &= "'" & dt.Rows(i)("INV") & "','" & dt.Rows(i)("ETD") & "','" & dt.Rows(i)("ATD") & "','" & dt.Rows(i)("ETA") & "','" & dt.Rows(i)("ATA") & "','" & dt.Rows(i)("FWDR") & "',"
sqlInsert &= "'" & dt.Rows(i)("GrossWeight") & "','" & dt.Rows(i)("Quantity") & "','" & dt.Rows(i)("Unit") & "','" & dt.Rows(i)("ICLCBM") & "','" & dt.Rows(i)("1x20") & "','" & dt.Rows(i)("1x40") & "',"
sqlInsert &= "'" & dt.Rows(i)("1x40HQ") & "','" & dt.Rows(i)("ContrnCBM") & "','" & dt.Rows(i)("Port") & "','" & dt.Rows(i)("Country") & "','" & dt.Rows(i)("BL_no") & "',"
sqlInsert &= "'" & dt.Rows(i)("DO") & "','" & dt.Rows(i)("Rcvd_Doc") & "','" & dt.Rows(i)("Starting") & "','" & dt.Rows(i)("Released") & "','" & dt.Rows(i)("PlanDelivery") & "',"
sqlInsert &= "'" & dt.Rows(i)("Delivery") & "','" & dt.Rows(i)("Delivery_Place") & "','" & dt.Rows(i)("Remark") & "'," '///finish Tab Data1
sqlInsert &= "'" & dt.Rows(i)("BG_Date") & "','" & dt.Rows(i)("BG_Amount") & "','" & dt.Rows(i)("ImportCustomer") & "','" & dt.Rows(i)("TypeOfEntry") & "','" & dt.Rows(i)("SKUNumber") & "','" & dt.Rows(i)("DetailOfGoods") & "',"
sqlInsert &= "'" & dt.Rows(i)("CIF") & "','" & dt.Rows(i)("HSCode") & "','" & dt.Rows(i)("TarrifRate") & "','" & dt.Rows(i)("Duty") & "','" & dt.Rows(i)("Vat") & "','" & dt.Rows(i)("Eprivilege") & "','" & dt.Rows(i)("Eduty") & "',"
sqlInsert &= "'" & dt.Rows(i)("Vat2") & "','" & dt.Rows(i)("CostSaving") & "','" & dt.Rows(i)("KPI_LeadTime") & "','" & dt.Rows(i)("Billing") & "','" & dt.Rows(i)("KPI_Billing") & "','" & dt.Rows(i)("Duty2") & "',"
sqlInsert &= "'" & dt.Rows(i)("DutyExcludedVat") & "','" & dt.Rows(i)("DueDateDuty") & "'," '///finish Tab Data2
sqlInsert &= "'" & dt.Rows(i)("Freight") & "','" & dt.Rows(i)("Exwork") & "','" & dt.Rows(i)("DO1") & "','" & dt.Rows(i)("OtherCharge") & "','" & dt.Rows(i)("Total") & "','" & dt.Rows(i)("ConsolBill") & "','" & dt.Rows(i)("FrtBillNo") & "',"
sqlInsert &= "'" & dt.Rows(i)("FrtBillDate") & "','" & dt.Rows(i)("TranSportation") & "','" & dt.Rows(i)("TotalAmount") & "','" & dt.Rows(i)("BillingMonth") & "','" & dt.Rows(i)("FrtFromUS") & "',"
sqlInsert &= "'" & dt.Rows(i)("SavingFrtCost") & "','" & dt.Rows(i)("IsTypeImport") & "','" & dt.Rows(i)("IstypeExport") & "'"
comInsert = New OleDbCommand(sqlInsert, ConnectAccess)
comInsert.ExecuteNonQuery()
Next
Catch ex As Exception
MessageBox.Show("Cannot insert data because, " & ex.Message, "Error", MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End Try
End If
End Sub
ไม่มี error ไรเลยครับแต่ที่ผมสงสัยตรงบรรทัดนี้ครับ
xlsBook = xlsApp.Workbooks.Open(UlFileName) ของผม
แต่ของบทเรียนจะใช้
xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile)) ผมลองแล้วครับไม่มี properties Server.MapPath อ่ะครับจะใช้คำสั่งไรหรอครับ
VB2010 ครับ ขอบคุณครับ
Tag : .NET, VS 2010 (.NET 4.x)
|
|
|
|
|
|
Date :
2010-10-11 16:37:25 |
By :
GhostLocal |
View :
1112 |
Reply :
5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ถ้าบน Windows App ใช้ระบุ Path ได้เลยครับ พอดีอันนี้บน ASP.NET เลยจะต้องใช้ Server.MapPath ครับ
|
|
|
|
|
Date :
2010-10-11 20:55:24 |
By :
webmaster |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
พี่วิน ของผมเขียนถูกป่าวครับ ถ้าถูกแล้วทำไมตอนรันไม่มีไรเกิดขึ้นเลยครับและผมก็ระบุ path แล้วน่ะครับ
|
|
|
|
|
Date :
2010-10-12 08:09:28 |
By :
GhostLocal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
สร้าง connection กับ excel แล้ว query ออกมาแบบ database จะง่ายกว่ามั้ย
|
|
|
|
|
Date :
2010-10-14 13:43:24 |
By :
tungman |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ขอบคุณจอมยุทธทุกท่าน ตอนนี้ผมได้แล้วครับ
|
|
|
|
|
Date :
2010-10-14 14:10:00 |
By :
GhostLocal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load balance : Server 05
|