'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