Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)
Dim conStr As String = ""
Select Case Extension
Case ".xls"
'Excel 97-03
conStr = ConfigurationManager.ConnectionStrings("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'") _
.ConnectionString()
Exit Select
Case ".xlsx"
'Excel 07
conStr = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FilePath & "';Extended Properties= 'Excel 12.0 Xml;HDR=No'") _
.ConnectionString()
Exit Select
End Select
conStr = String.Format(conStr, FilePath, isHDR)
Dim connExcel As New OLEDBConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
'connExcel.Open()
'Dim ExcelTable As DataTable
'Dim SheetName As String
'ExcelTable = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing})
'SheetName = ExcelTable.Rows(0).Item(2).ToString
'MsgBox(SheetName)
'SheetName = ExcelTable.Rows(1).Item(2).ToString
'MsgBox(SheetName)
'SheetName = ExcelTable.Rows(2).Item(2).ToString
'MsgBox(SheetName)
'connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
Dim Con As New SqlConnection(ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString)
Select Case MsgBox("Are you sure ?", MsgBoxStyle.OkOnly, "ADD DIE")
Case MsgBoxResult.Ok
If conStr <> "Galley" Then
MsgBox("please choose new file")
Response.Redirect("priorityfold.aspx")
Response.Close()
connExcel.Close()
End If
End Select
connExcel.Open()
Con.Open()
Dim sqlUserName As String = "SELECT * FROM groupdie"
cmdExcel.CommandText = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5] as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] WHERE [" & "Galley$" & "].[f9] = 'Galley PartsFold' OR [" & "Galley$" & "].[f19] IS NOT NULL AND [" & "Galley$" & "].[f5] IS NOT NULL "
Dim com As New SqlCommand(sqlUserName, Con)
Dim dr As DataRow
Dim ex As String
ex = "SELECT * FROM [" & "Galley$" & "]"
Dim conn As New OleDbCommand(ex, connExcel)
'เลือกไฟล์ผิดจะหา galley ไม่เจอแต่ถ้าเลือกถูกข้อมูลขึ้น ???
'เตือนเมื่อเลือกไฟล์ผิด()
'Dim tbn As String
'tbn = "Galley" ' ชื่อsheet ที่ถูก
'MsgBox("Start")
''MsgBox()
'Dim msg = "please choose new file"
'Dim Currentname As String
'Currentname = Convert.ToString(conn.ExecuteScalar)
'If Currentname = "tbn" Then
' MsgBox(msg)
' เพิ่มcolumn
oda.SelectCommand = cmdExcel
oda.Fill(dt)
dt.Columns.Add("die r 1", Type.GetType("System.String"))
dt.Columns.Add("die v 1", Type.GetType("System.String"))
dt.Columns.Add("die r 2", Type.GetType("System.String"))
dt.Columns.Add("die v 2", Type.GetType("System.String"))
dt.Columns.Add("die r 3", Type.GetType("System.String"))
dt.Columns.Add("die v 3", Type.GetType("System.String"))
dt.Columns.Add("die r 4", Type.GetType("System.String"))
dt.Columns.Add("die v 4", Type.GetType("System.String"))
dt.Columns.Add("die r 5", Type.GetType("System.String"))
dt.Columns.Add("die v 5", Type.GetType("System.String"))
dt.Columns.Add("die r 6", Type.GetType("System.String"))
dt.Columns.Add("die v 6", Type.GetType("System.String"))
dt.Columns.Add("die r 7", Type.GetType("System.String"))
dt.Columns.Add("die v 7", Type.GetType("System.String"))
dt.Columns.Add("die r 8", Type.GetType("System.String"))
dt.Columns.Add("die v 8", Type.GetType("System.String"))
dt.Columns.Add("die r 9", Type.GetType("System.String"))
dt.Columns.Add("die v 9", Type.GetType("System.String"))
dt.Columns.Add("die r 10", Type.GetType("System.String"))
dt.Columns.Add("die v 10", Type.GetType("System.String"))
dt.Columns.Add("rev", Type.GetType("System.String"))
dt.Columns.Add("remark", Type.GetType("System.String"))
'จับคู่ฐานข้อมูล()
For Each dr In dt.Rows
com = New SqlCommand("select groupdie.dier1 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 1") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev1 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 1") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier2 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 2") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev2 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 2") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier3 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 3") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev3 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 3") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier4 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 4") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev4 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 4") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier5 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 5") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev5 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 5") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier6 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 6") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev6 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 6") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier7 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 7") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev7 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 7") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier8 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 8") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev8 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 8") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier9 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 9") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev9 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 9") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.dier10 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die r 10") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.diev10 from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("die v 10") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.revdie from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("rev") = Convert.ToString(com.ExecuteScalar)
com = New SqlCommand("select groupdie.remark from groupdie where '" & dr("item") & "' = groupdie.id_die ", Con)
dr("remark") = Convert.ToString(com.ExecuteScalar)
Next
'การ Grouping die เรียงตาม Plan week
Dim dra As String
dra = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5] as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] from [" & "Galley$" & "] ORDER BY(f13) in (select [f3] from [" & "Galley$" & "] group by [f13] having count(f13) >='1')"
'Dim pri As String = "SELECT [" & "Galley$" & "].[f3] as Project, [" & "Galley$" & "].[f4] as Item, [" & "Galley$" & "].[f5] as PARTNAME, [" & "Galley$" & "].[f10] as ShipWK, [" & "Galley$" & "].[f13] as Planwk, [f41] as Status From [" & "Galley$" & "] from [" & "Galley$" & "] ORDER BY(f13) in (select [f3] from [" & "Galley$" & "] group by [f13] having count(f13) >='1')"
connExcel.Close()
oda.SelectCommand = cmdExcel
oda.Fill(dt)
Con.Close()
' Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath)
GridView1.DataSource = dt
GridView1.DataBind()
connExcel.Close()
End Sub
Load balance : Server 04
