293 lines
10 KiB
VB.net
293 lines
10 KiB
VB.net
|
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
|