334 lines
13 KiB
VB.net
334 lines
13 KiB
VB.net
'Imports System.Data.OleDb
|
|
'Imports System.Reflection
|
|
Imports System.ComponentModel
|
|
Imports Excel = Microsoft.Office.Interop.Excel
|
|
''Imports Microsoft.Office.Interop
|
|
|
|
|
|
Public Class frmMySqlTest
|
|
|
|
Private Enum data_set
|
|
fre
|
|
imp
|
|
cap
|
|
res
|
|
cur
|
|
cva
|
|
gra
|
|
r1
|
|
r2
|
|
r3
|
|
r4
|
|
r5
|
|
r6
|
|
r7
|
|
r8
|
|
r9
|
|
r10
|
|
|
|
max
|
|
End Enum
|
|
|
|
Private data_select(data_set.max) As String
|
|
|
|
Private cmd As String
|
|
Private dbcmd As String
|
|
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
|
|
|
|
'''DataGridView를 선택하면 전체 한줄이 선택되게 됩니다.
|
|
DataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect
|
|
|
|
'컬럼 2개를 생성하고 각각의 이름을 정해줍니다. DataGridView1의 상단에 위치하게 됩니다.
|
|
DataGridView.ColumnCount = 18
|
|
|
|
DataGridView.Columns(0).Name = "시리얼번호"
|
|
DataGridView.Columns(1).Name = "주파수"
|
|
DataGridView.Columns(2).Name = "임피던스"
|
|
DataGridView.Columns(3).Name = "캐패시턴스"
|
|
DataGridView.Columns(4).Name = "레지스턴스"
|
|
DataGridView.Columns(5).Name = "전류값"
|
|
DataGridView.Columns(6).Name = "C값"
|
|
DataGridView.Columns(7).Name = "등급"
|
|
DataGridView.Columns(8).Name = "0P"
|
|
DataGridView.Columns(9).Name = "22P"
|
|
DataGridView.Columns(10).Name = "56P"
|
|
DataGridView.Columns(11).Name = "82P"
|
|
DataGridView.Columns(12).Name = "100P"
|
|
DataGridView.Columns(13).Name = "120P"
|
|
DataGridView.Columns(14).Name = "150P"
|
|
DataGridView.Columns(15).Name = "180P"
|
|
DataGridView.Columns(16).Name = "220P"
|
|
DataGridView.Columns(17).Name = "270P"
|
|
|
|
size_est()
|
|
End Sub
|
|
|
|
'Keep the application object and the workbook object global, so you can
|
|
'retrieve the data in Button2_Click that was set in Button1_Click.
|
|
Dim objApp As Excel.Application
|
|
Dim objBook As Excel._Workbook
|
|
|
|
Private Sub size_est()
|
|
Dim width, height As Integer
|
|
|
|
Me.Hide()
|
|
Me.WindowState = FormWindowState.Maximized
|
|
|
|
width = Val(Me.Width)
|
|
height = Val(Me.Height)
|
|
|
|
Me.MaximumSize = New Size(width, height)
|
|
Me.Width = width
|
|
Me.Height = height
|
|
|
|
Me.Show()
|
|
Me.Refresh()
|
|
|
|
End Sub
|
|
|
|
Private Sub DisplayQuarterlySales(oWS As Excel.Worksheet)
|
|
Dim oResizeRange As Excel.Range
|
|
Dim oChart As Excel.Chart
|
|
Dim iNumQtrs As Integer
|
|
Dim sMsg As String
|
|
Dim iRet As Integer
|
|
|
|
' Determine how many quarters to display data for.
|
|
For iNumQtrs = 4 To 2 Step -1
|
|
sMsg = "Enter sales data for" & Str(iNumQtrs) & " quarter(s)?"
|
|
iRet = MsgBox(sMsg, vbYesNo Or vbQuestion _
|
|
Or vbMsgBoxSetForeground, "Quarterly Sales")
|
|
If iRet = vbYes Then Exit For
|
|
Next iNumQtrs
|
|
|
|
sMsg = "Displaying data for" & Str(iNumQtrs) & " quarter(s)."
|
|
MsgBox(sMsg, vbMsgBoxSetForeground, "Quarterly Sales")
|
|
|
|
' Starting at E1, fill headers for the number of columns selected.
|
|
oResizeRange = oWS.Range("E1", "E1").Resize(ColumnSize:=iNumQtrs)
|
|
|
|
oResizeRange.Formula = "=""Q"" & COLUMN()-4 & CHAR(10) & ""Sales"""
|
|
|
|
' Change the Orientation and WrapText properties for the headers.
|
|
oResizeRange.Orientation = 38
|
|
oResizeRange.WrapText = True
|
|
|
|
' Fill the interior color of the headers.
|
|
oResizeRange.Interior.ColorIndex = 36
|
|
|
|
' Fill the columns with a formula and apply a number format.
|
|
oResizeRange = oWS.Range("E2", "E6").Resize(ColumnSize:=iNumQtrs)
|
|
oResizeRange.Formula = "=RAND()*100"
|
|
oResizeRange.NumberFormat = "$0.00"
|
|
|
|
' Apply borders to the Sales data and headers.
|
|
oResizeRange = oWS.Range("E1", "E6").Resize(ColumnSize:=iNumQtrs)
|
|
oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin
|
|
|
|
' Add a Totals formula for the sales data and apply a border.
|
|
oResizeRange = oWS.Range("E8", "E8").Resize(ColumnSize:=iNumQtrs)
|
|
oResizeRange.Formula = "=SUM(E2:E6)"
|
|
With oResizeRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
|
|
.LineStyle = Excel.XlLineStyle.xlDouble
|
|
.Weight = Excel.XlBorderWeight.xlThick
|
|
|
|
End With
|
|
|
|
' Add a Chart for the selected data
|
|
oResizeRange = oWS.Range("E2:E6").Resize(ColumnSize:=iNumQtrs)
|
|
oChart = oWS.Parent.Charts.Add
|
|
With oChart
|
|
.ChartWizard(oResizeRange, Excel.XlChartType.xl3DColumn, , Excel.XlChartType.xl3DColumn)
|
|
.SeriesCollection(1).XValues = oWS.Range("A2", "A6")
|
|
For iRet = 1 To iNumQtrs
|
|
.SeriesCollection(iRet).Name = "=""Q" & Str(iRet) & """"
|
|
Next iRet
|
|
.Location(Excel.XlLocationInTable.xlColumnHeader, oWS.Name)
|
|
End With
|
|
|
|
' Move the chart so as not to cover your data.
|
|
With oWS.Shapes("Chart 1")
|
|
.Top = oWS.Rows(10).Top
|
|
.Left = oWS.Columns(2).Left
|
|
|
|
End With
|
|
|
|
' Free any references.
|
|
oChart = Nothing
|
|
oResizeRange = Nothing
|
|
|
|
End Sub
|
|
|
|
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
|
|
loadExcelDB()
|
|
End Sub
|
|
|
|
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
|
|
showDataGridViewContent(DataGridView)
|
|
|
|
End Sub
|
|
|
|
Private Sub Button1_Click(sender As Object, e As EventArgs)
|
|
|
|
Dim te1, te2, te3, te4, te5, te6, te7, te8, te9, te10, te11, te12, te13, te14, te15, te16, te17, te18, te19, te20, te21, te22, te23, te24, te25, te26, te27, te28, te29, te30, te31, te32, te33, te34, te35, te36, te37, te38, te39, te40, te41, te42 As String
|
|
|
|
For cnt = DataGridView.Rows.Count - 2 To 0 Step -1
|
|
te1 = DataGridView.Rows(cnt).Cells(0).Value.ToString
|
|
te2 = DataGridView.Rows(cnt).Cells(1).Value.ToString
|
|
te3 = DataGridView.Rows(cnt).Cells(2).Value.ToString
|
|
te4 = DataGridView.Rows(cnt).Cells(3).Value.ToString
|
|
te5 = DataGridView.Rows(cnt).Cells(4).Value.ToString
|
|
te6 = DataGridView.Rows(cnt).Cells(5).Value.ToString
|
|
te7 = DataGridView.Rows(cnt).Cells(6).Value.ToString
|
|
te8 = DataGridView.Rows(cnt).Cells(7).Value.ToString
|
|
te9 = DataGridView.Rows(cnt).Cells(8).Value.ToString
|
|
te10 = DataGridView.Rows(cnt).Cells(9).Value.ToString
|
|
te11 = DataGridView.Rows(cnt).Cells(10).Value.ToString
|
|
te12 = DataGridView.Rows(cnt).Cells(11).Value.ToString
|
|
te13 = DataGridView.Rows(cnt).Cells(12).Value.ToString
|
|
te14 = DataGridView.Rows(cnt).Cells(13).Value.ToString
|
|
te15 = DataGridView.Rows(cnt).Cells(14).Value.ToString
|
|
te16 = DataGridView.Rows(cnt).Cells(15).Value.ToString
|
|
te17 = DataGridView.Rows(cnt).Cells(16).Value.ToString
|
|
te18 = DataGridView.Rows(cnt).Cells(17).Value.ToString
|
|
te19 = DataGridView.Rows(cnt).Cells(18).Value.ToString
|
|
te20 = DataGridView.Rows(cnt).Cells(19).Value.ToString
|
|
te21 = DataGridView.Rows(cnt).Cells(20).Value.ToString
|
|
te22 = DataGridView.Rows(cnt).Cells(21).Value.ToString
|
|
te23 = DataGridView.Rows(cnt).Cells(22).Value.ToString
|
|
te24 = DataGridView.Rows(cnt).Cells(23).Value.ToString
|
|
te25 = DataGridView.Rows(cnt).Cells(24).Value.ToString
|
|
te26 = DataGridView.Rows(cnt).Cells(25).Value.ToString
|
|
te27 = DataGridView.Rows(cnt).Cells(26).Value.ToString
|
|
te28 = DataGridView.Rows(cnt).Cells(27).Value.ToString
|
|
te29 = DataGridView.Rows(cnt).Cells(28).Value.ToString
|
|
te30 = DataGridView.Rows(cnt).Cells(29).Value.ToString
|
|
te31 = DataGridView.Rows(cnt).Cells(30).Value.ToString
|
|
te32 = DataGridView.Rows(cnt).Cells(31).Value.ToString
|
|
te33 = DataGridView.Rows(cnt).Cells(32).Value.ToString
|
|
te34 = DataGridView.Rows(cnt).Cells(33).Value.ToString
|
|
te35 = DataGridView.Rows(cnt).Cells(34).Value.ToString
|
|
te36 = DataGridView.Rows(cnt).Cells(35).Value.ToString
|
|
te37 = DataGridView.Rows(cnt).Cells(36).Value.ToString
|
|
te38 = DataGridView.Rows(cnt).Cells(37).Value.ToString
|
|
te39 = DataGridView.Rows(cnt).Cells(38).Value.ToString
|
|
te40 = DataGridView.Rows(cnt).Cells(39).Value.ToString
|
|
te41 = DataGridView.Rows(cnt).Cells(40).Value.ToString
|
|
Next
|
|
|
|
End Sub
|
|
|
|
Private Sub btnReadDB_Click(sender As Object, e As EventArgs) Handles btnReadDB.Click
|
|
Dim td_sn As String
|
|
Dim switch As Boolean
|
|
|
|
If ckb_clear.Checked = True Then
|
|
DataGridView.Rows.Clear()
|
|
End If
|
|
|
|
If ckb_fail_delete.Checked = True Then
|
|
For i = DataGridView.Rows.Count - 1 To 0 Step -1
|
|
If DataGridView.Rows(i).DefaultCellStyle.BackColor = Color.Red Then
|
|
DataGridView.Rows.RemoveAt(i)
|
|
End If
|
|
Next
|
|
End If
|
|
|
|
td_sn = tdsn_parsing()
|
|
|
|
dbcmd = "SELECT PV_Frequency,PV_Impedance,PV_Capacitance,PV_Resistance,QC_Current,QC_Capacitor,QC_Grade,QC_R1Value,QC_R2Value,QC_R3Value,QC_R4Value,QC_R5Value,QC_R6Value,QC_R7Value,QC_R8Value,QC_R9Value,QC_R10Value FROM jomtTesterDB.jomtCartridgeTbl WHERE PV_SN = '" & td_sn & "'"
|
|
|
|
If DBQueryReader(dbcmd) Then
|
|
data_select(data_set.fre) = sqlDataQuery(0)
|
|
data_select(data_set.imp) = sqlDataQuery(1)
|
|
data_select(data_set.cap) = sqlDataQuery(2)
|
|
data_select(data_set.res) = sqlDataQuery(3)
|
|
data_select(data_set.cur) = sqlDataQuery(4)
|
|
data_select(data_set.cva) = sqlDataQuery(5)
|
|
data_select(data_set.gra) = sqlDataQuery(6)
|
|
data_select(data_set.r1) = sqlDataQuery(7)
|
|
data_select(data_set.r2) = sqlDataQuery(8)
|
|
data_select(data_set.r3) = sqlDataQuery(9)
|
|
data_select(data_set.r4) = sqlDataQuery(10)
|
|
data_select(data_set.r5) = sqlDataQuery(11)
|
|
data_select(data_set.r6) = sqlDataQuery(12)
|
|
data_select(data_set.r7) = sqlDataQuery(13)
|
|
data_select(data_set.r8) = sqlDataQuery(14)
|
|
data_select(data_set.r9) = sqlDataQuery(15)
|
|
data_select(data_set.r10) = sqlDataQuery(16)
|
|
|
|
switch = True
|
|
|
|
Else
|
|
data_select(data_set.fre) = "0"
|
|
data_select(data_set.imp) = "0"
|
|
data_select(data_set.cap) = "0"
|
|
data_select(data_set.res) = "0"
|
|
data_select(data_set.cur) = "0"
|
|
data_select(data_set.cva) = "0"
|
|
data_select(data_set.gra) = "0"
|
|
data_select(data_set.r1) = "0"
|
|
data_select(data_set.r2) = "0"
|
|
data_select(data_set.r3) = "0"
|
|
data_select(data_set.r4) = "0"
|
|
data_select(data_set.r5) = "0"
|
|
data_select(data_set.r6) = "0"
|
|
data_select(data_set.r7) = "0"
|
|
data_select(data_set.r8) = "0"
|
|
data_select(data_set.r9) = "0"
|
|
data_select(data_set.r10) = "0"
|
|
|
|
switch = False
|
|
End If
|
|
|
|
DataGridView.Rows.Add(td_sn, data_select(data_set.fre), data_select(data_set.imp), data_select(data_set.cap), data_select(data_set.res), data_select(data_set.cur), data_select(data_set.cva), data_select(data_set.gra), data_select(data_set.r1), data_select(data_set.r2), data_select(data_set.r3), data_select(data_set.r4), data_select(data_set.r5), data_select(data_set.r6), data_select(data_set.r7), data_select(data_set.r8), data_select(data_set.r9), data_select(data_set.r10))
|
|
|
|
If switch = True Then
|
|
DataGridView.Rows(DataGridView.Rows.Count - 1).DefaultCellStyle.BackColor = Color.White
|
|
txbPV_SN.Text = Nothing
|
|
Else
|
|
DataGridView.Rows(DataGridView.Rows.Count - 1).DefaultCellStyle.BackColor = Color.Red
|
|
txbPV_SN.Text = Nothing
|
|
MsgBox("데이터가 존재하지 않습니다.", vbCritical)
|
|
End If
|
|
DataGridView.Refresh()
|
|
End Sub
|
|
|
|
Private Function tdsn_parsing() As String
|
|
Dim td_sn As String = Nothing
|
|
|
|
If rdb_dit.Checked Then
|
|
td_sn = rdb_dit.Text
|
|
ElseIf rdb_dith.Checked Then
|
|
td_sn = rdb_dith.Text
|
|
ElseIf rdb_diti.Checked Then
|
|
td_sn = rdb_diti.Text
|
|
End If
|
|
|
|
td_sn = td_sn & txbPV_SN.Text
|
|
|
|
If ckb_upper.Checked = True Then
|
|
Return td_sn.ToUpper
|
|
Else
|
|
Return td_sn
|
|
End If
|
|
|
|
|
|
|
|
End Function
|
|
|
|
Private Sub txbPV_SN_Click(sender As Object, e As EventArgs) Handles txbPV_SN.Click
|
|
txbPV_SN.Text = ""
|
|
End Sub
|
|
|
|
Private Sub txbPV_SN_KeyDown(ByVal sender As Object, ByVal e As KeyEventArgs) Handles txbPV_SN.KeyDown
|
|
If e.KeyCode = Keys.Return Then
|
|
btnReadDB_Click(sender, e)
|
|
End If
|
|
End Sub
|
|
|
|
Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
|
|
DataGridView.Rows.Clear()
|
|
End Sub
|
|
End Class
|