Imports Excel = Microsoft.Office.Interop.Excel Module ExcelModule Private xlApp As New Excel.Application '엑셀 응용프로그램의 변수. Private xlworkbook As Excel.Workbook Private xlworksheet As Excel.Worksheet Private xlRange As Excel.Range Public xlReadData(,) As Object 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 Function OpenDBFile() As String Dim filePath As String With frmMySqlTest.OpenFileDB .Title = "[JION MEDITECH] Select T/D DB File!" .InitialDirectory = "d:\" .Filter = "T/D DB Files | *.xlsx;*.xls;*.csv;" .Multiselect = False If .ShowDialog() = 0 Then .ShowDialog() End If End With filePath = frmMySqlTest.OpenFileDB.FileName Return filePath End Function Public Function loadExcelDB() As Boolean Dim xlRowCnt, xlCollumnCnt As Integer Dim xlEndPosition As String Dim currRowCnt As Integer Dim filePath As String Dim dbcmd As String Try filePath = OpenDBFile() If filePath = Nothing Then Return False End If '엑셀 창을 띄울것인지 여부 체크 xlApp = DirectCast(CreateObject("Excel.Application"), Excel.Application) xlApp.Visible = False xlworkbook = xlApp.Workbooks.Open(filePath) xlworksheet = xlworkbook.Sheets(1) 'xlRange = xlworksheet.Range("A1", Type.Missing) xlRowCnt = xlworksheet.Range("A1").CurrentRegion.Rows.Count xlCollumnCnt = xlworksheet.Range("A1").CurrentRegion.Columns.Count xlEndPosition = xlworksheet.Range("A1").CurrentRegion.Address xlRange = xlworksheet.Range(xlEndPosition) 'Retrieve the data from the range. ReDim xlReadData(xlRowCnt, xlCollumnCnt) xlReadData = xlRange.Value xlworkbook.Close() xlApp.Quit() currRowCnt = frmMySqlTest.DataGridView.Rows.Count If currRowCnt > 1 Then For cnt = 1 To currRowCnt - 1 frmMySqlTest.DataGridView.Rows.RemoveAt(0) Next End If For cnt = 2 To xlRowCnt 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 = '" & xlReadData(cnt, 1) & "'" 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) 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" End If frmMySqlTest.DataGridView.Rows.Add(xlReadData(cnt, 1), 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)) Next Return True Catch ex As Exception MsgBox("MySqlCommand Error:" + vbCrLf + Err.Description) xlworkbook.Close() xlApp.Quit() Return False End Try End Function Public Sub showDataGridViewContent(ByVal data_grid As DataGridView) Dim columnIndex As Integer = 1 Dim rowIndex As Integer = 1 Dim colIndex As Integer = 1 Try xlworkbook = xlApp.Workbooks.Add xlworksheet = xlworkbook.Worksheets(1) xlApp.Visible = False xlApp.Visible = True xlworksheet.Cells.Clear() For Each col As DataGridViewColumn In data_grid.Columns xlworksheet.Cells(1, columnIndex) = col.HeaderText columnIndex += 1 Next For Each row As DataGridViewRow In data_grid.Rows rowIndex += 1 colIndex = 1 For Each cell As DataGridViewCell In row.Cells xlworksheet.Cells(rowIndex, colIndex) = cell.Value colIndex += 1 Next Next Catch ex As Exception MsgBox(ex.Message & ex.ToString, MsgBoxStyle.Critical, "오류") End Try End Sub Public Function loadIQCExcelDB() As Boolean Dim xlRowCnt, xlCollumnCnt As Integer Dim xlEndPosition As String Dim currRowCnt As Integer Dim filePath As String Try filePath = OpenDBFile() 'filePath = "d:\Excel_Spl.xlsx" '엑셀 창을 띄울것인지 여부 체크 xlApp = DirectCast(CreateObject("Excel.Application"), Excel.Application) xlApp.Visible = False xlworkbook = xlApp.Workbooks.Open(filePath) xlworksheet = xlworkbook.Sheets(1) 'xlRange = xlworksheet.Range("A1", Type.Missing) xlRowCnt = xlworksheet.Range("A1").CurrentRegion.Rows.Count xlCollumnCnt = xlworksheet.Range("A1").CurrentRegion.Columns.Count xlEndPosition = xlworksheet.Range("A1").CurrentRegion.Address xlRange = xlworksheet.Range(xlEndPosition) 'Retrieve the data from the range. ReDim xlReadData(xlRowCnt, xlCollumnCnt) xlReadData = xlRange.Value xlworkbook.Close() xlApp.Quit() currRowCnt = frmMySqlTest.DataGridView.Rows.Count If currRowCnt > 1 Then For cnt = 1 To currRowCnt - 1 frmMySqlTest.DataGridView.Rows.RemoveAt(0) Next End If For cnt = 2 To xlRowCnt frmMySqlTest.DataGridView.Rows.Add(xlReadData(cnt, 1), xlReadData(cnt, 2), xlReadData(cnt, 3), xlReadData(cnt, 4), xlReadData(cnt, 5), xlReadData(cnt, 6), Format(xlReadData(cnt, 7), "0.000"), Format(xlReadData(cnt, 8), "0.000"), Format(xlReadData(cnt, 9), "0.000"), Format(xlReadData(cnt, 10), "0.000"), Format(xlReadData(cnt, 11), "0.000"), Format(xlReadData(cnt, 12), "0.000")) Next ''frmMySqlTest.txbPVdate.Text = xlReadData(2, 2) ''For cnt = 4 To xlRowCnt '' frmMySqlTest.DataGridView.Rows.Add(xlReadData(cnt, 1), xlReadData(cnt, 2), Val(xlReadData(cnt, 3)) * 1000, xlReadData(cnt, 4), xlReadData(cnt, 5), xlReadData(cnt, 6), xlReadData(cnt, 7)) ''Next ''frmMySqlTest.DataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect ''frmMySqlTest.DataGridView.Columns(0).Width = 200 ''frmMySqlTest.DataGridView.Columns(0).Frozen = True ' 틀 고정 ''frmMySqlTest.DataGridView.Columns(0).DividerWidth = 2 ' 틀고정 나누기 bar ''frmMySqlTest.DataGridView.MultiSelect = False ' multiselect 불가 ''frmMySqlTest.DataGridView.EnableHeadersVisualStyles = False ' 컬럼 header 세팅 ''Dim colHeader As New DataGridViewCellStyle ''colHeader.BackColor = Color.FromArgb(176, 251, 164) ''colHeader.Font = New Font("Gulim", 9, FontStyle.Bold) ''frmMySqlTest.DataGridView.ColumnHeadersDefaultCellStyle = colHeader ' 컬럼헤더는 다르게 세팅 '' '' 컬럼 클릭해서 소팅안되게 설정 '' For Int() i = 0; i < this.PLA.Columns.Count; i++) 'DBGrid --> DataGridView ''{ '' this.PLA.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; ''} ''frmMySqlTest.DataGridView ''frmMySqlTest.DataGridView.Rows.Insert("1", "1", "1", "1", "1", "1") ''frmMySqlTest.DataGridView.Columns.Add("1", "1") ''frmMySqlTest.DataGridView.Columns.Add("2", "2") ''frmMySqlTest.DataGridView.Columns.Add("3", "3") ''frmMySqlTest.DataGridView.Columns.Add("4", "4") ''frmMySqlTest.DataGridView.Columns.Add("5", "5") Return True Catch ex As Exception MsgBox("MySqlCommand Error:" + vbCrLf + Err.Description) xlworkbook.Close() xlApp.Quit() Return False End Try End Function End Module