HYTOS

Imports GemBox.Spreadsheet

Imports System.Drawing

Imports System.IO

Public Class CGembox

    Public _ExcelFile As ExcelFile

    Dim _Index_Cnt As Integer = 2

    Dim _LicenseKey As String = "EXK0-W4HZ-N518-IMEW"

    Sub New()

        setLicense()

    End Sub

    Private Sub setLicense()

        SpreadsheetInfo.SetLicense(_LicenseKey)

        _ExcelFile = New ExcelFile

        '   _ExcelFile = New ExcelFile

    End Sub

#Region "엑셀 공용 함수"

    Dim EXCEL_HEAD As Integer = 0

    Dim EXCEL_START As Integer = 1

    Function SaveFileFunc(ByVal mWorksheet As String, ByVal mPath As String, ByVal mDT As DataTable) As Boolean

        Try

            SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_HEAD).Cells(0), "HEAD")

            SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START).Cells(0), "START")

            For mColcnt = 0 To mDT.Columns.Count - 1

                SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_HEAD).Cells(mColcnt + 1), mDT.Columns(mColcnt).ColumnName)

            Next

            If mDT IsNot Nothing Then

                For mRowcnt = 0 To mDT.Rows.Count - 1

                    SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START + 1 + mRowcnt).Cells(0), String.Empty)

                    For mColcnt = 0 To mDT.Columns.Count - 1

                        SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(EXCEL_START + mRowcnt).Cells(mColcnt + 1), mDT.Rows(mRowcnt).Item(mColcnt).ToString)

                    Next

                Next

            End If

            SetCell(_ExcelFile.Worksheets(mWorksheet).Rows(mDT.Rows.Count + EXCEL_START).Cells(0), "END")

            SetSaveFile(_ExcelFile, mPath)

        Catch ex As Exception

            Return False

        End Try

        Return True

    End Function

    Function LoadFileFunc() As DataSet

        Dim oDs As New DataSet

        '  Dim oExcelFile As New ExcelFile

        _TempExcel = New ExcelFile

        '    _TempFileName = DateTime.Now.Year & "_" & DateTime.Now.Month & "_" & DateTime.Now.Day & " " & DateTime.Now.Hour & "_" & DateTime.Now.Minute & "_" & DateTime.Now.Second

        For Each oSheet In _ExcelFile.Worksheets

            If oSheet.Name = _Sheet_Equipment Or

                oSheet.Name = _Sheet_EquipmentComponents Or

                oSheet.Name = _Sheet_Instrumentation Or

                oSheet.Name = _Sheet_Piping Then

                _TempExcel.Worksheets.AddCopy(oSheet.Name, oSheet)

                Dim oDt As New DataTable

                oDt.TableName = oSheet.Name

                Dim mFindHeadInt As Integer = 0 ' GetFindRow(_ExcelFile.Worksheets(msheetName), "HEAD")

                Dim mStartInt As Integer = 1 'GetFindRow(_ExcelFile.Worksheets(msheetName), "START")

                Dim mLastColInt As Integer = oSheet.CalculateMaxUsedColumns 'GetFindColumnCount(_ExcelFile.Worksheets(oSheet.Name), mFindHeadInt)

                Dim mInputColInt As Integer = 0

                If mFindHeadInt <> -1 Or mStartInt <> -1 Or mLastColInt <> -1 Then

                    For mCellcnt = 1 To mLastColInt

                        If _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value <> "" Then

                            Dim oCheckColumnName As Boolean = False

                            For i = 0 To oDt.Columns.Count - 1

                                If oDt.Columns(i).ColumnName = _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value Then

                                    oCheckColumnName = True

                                    Exit For

                                End If

                            Next

                            If oCheckColumnName = False Then

                                '/ 컬럼이름을 데이터테이블에 저장. 그리드뷰에 출력하기 위해서

                                Dim oCol As DataColumn = oDt.Columns.Add(_ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellcnt).Value)

                                oCol.Namespace = mCellcnt

                                mInputColInt = mCellcnt

                            End If

                        End If

                    Next

                    Dim oIndexCol As DataColumn = oDt.Columns.Add(_Column_Index)

                    oIndexCol.Namespace = mInputColInt

                    oIndexCol.ColumnMapping = MappingType.Hidden

                    '/END 열을 찾아서 START 부터 END 까지 데이터를 데이터테이블에 입력

                    Dim mFindEndInt As Integer = _ExcelFile.Worksheets(oSheet.Name).Rows.Count 'GetFindRow(_ExcelFile.Worksheets(oSheet.Name), " ")

                    For mRowcnt = 1 To mFindEndInt - 1

                        Try

                            Dim mDataRow As DataRow = oDt.NewRow

                            For mCellCnt = 1 To mLastColInt - 1

                                Try

                                    Dim oColName As String = _ExcelFile.Worksheets(oSheet.Name).Rows(mFindHeadInt).Cells(mCellCnt).Value

                                    If oColName <> Nothing Then

                                        Dim oValve As String = _ExcelFile.Worksheets(oSheet.Name).Rows(mRowcnt).Cells(mCellCnt).Value

                                        If oValve IsNot Nothing Then

                                            If oValve = "" Then

                                                mDataRow(oColName) = ""

                                            Else

                                                Dim sPreValue As String = mDataRow(oColName).ToString()

                                                If sPreValue <> "" Then

                                                    mDataRow(oColName) = sPreValue & "\" & oValve

                                                Else

                                                    mDataRow(oColName) = oValve

                                                End If

                                            End If

                                        Else

                                            If mDataRow(oColName) IsNot Nothing Then

                                                If mDataRow(oColName).ToString() = "" Then

                                                    mDataRow(oColName) = ""

                                                End If

                                            End If

                                        End If

                                    End If

                                Catch ex As Exception

                                End Try

                            Next

                            mDataRow(_Column_Index) = mRowcnt

                            If mDataRow("SYM FILE NAME").ToString() <> "" Then

                                oDt.Rows.Add(mDataRow)

                            End If

                        Catch ex As Exception

                        End Try

                    Next

                End If

                oDs.Tables.Add(oDt)

            End If

        Next

        Return oDs

    End Function

    Function AddImageLibrary(ByVal oDs As DataSet, ByVal oPIDSymbol_Dt As DataTable) As DataTable

        Dim oImageSymbol_Dt As DataTable = ImageSymbol_Dt()

        Dim iColcnt As Integer = 0

        Dim bCheckSheet As Boolean = False

        Dim oSymbolSheet As ExcelWorksheet = Nothing

        For Each oSheet As ExcelWorksheet In _ExcelFile.Worksheets

            If oSheet.Name = _Sheet_ImageSymbol Then

                bCheckSheet = True

                oSymbolSheet = oSheet

                oSymbolSheet.Clear()

                Exit For

            End If

        Next

        If bCheckSheet = False Then

            oSymbolSheet = _ExcelFile.Worksheets.Add(_Sheet_ImageSymbol)

        End If

        If oSymbolSheet IsNot Nothing Then

            For Each oDt As DataTable In oDs.Tables

                oSymbolSheet.Cells(0, iColcnt).Value = oDt.TableName

                oSymbolSheet.Columns(iColcnt).AutoFit()

                Dim oDataRows() As DataRow = oPIDSymbol_Dt.Select("category = '" & oDt.TableName & "'")

                Dim iRowcnt As Integer = 1

                For Each oRow In oDataRows

                    Dim sCategory As String = oRow("name").ToString()

                    oSymbolSheet.Cells(iRowcnt, iColcnt).Value = sCategory

                    iRowcnt = iRowcnt + 1

                Next

                Dim sStartCell As String = ConvertAlphabet(iColcnt) & 2

                Dim sEndCell As String = ConvertAlphabet(iColcnt) & iRowcnt

                Dim oAddRow As DataRow = oImageSymbol_Dt.NewRow()

                oAddRow(_Sheet_ImageSymbol_Type) = oDt.TableName

                oAddRow(_Sheet_ImageSymbol_StartCell) = sStartCell

                oAddRow(_Sheet_ImageSymbol_EndCell) = sEndCell

                oImageSymbol_Dt.Rows.Add(oAddRow)

                iColcnt = iColcnt + 1

            Next

        End If

        Return oImageSymbol_Dt

    End Function

    Function ConvertAlphabet(ByVal iCol As Integer) As String

        If iCol = 0 Then

            Return "A"

        ElseIf iCol = 1 Then

            Return "B"

        ElseIf iCol = 2 Then

            Return "C"

        ElseIf iCol = 3 Then

            Return "D"

        ElseIf iCol = 4 Then

            Return "E"

        ElseIf iCol = 5 Then

            Return "F"

        ElseIf iCol = 6 Then

            Return "G"

        ElseIf iCol = 7 Then

            Return "H"

        ElseIf iCol = 8 Then

            Return "I"

        ElseIf iCol = 9 Then

            Return "J"

        ElseIf iCol = 10 Then

            Return "K"

        ElseIf iCol = 11 Then

            Return "L"

        ElseIf iCol = 12 Then

            Return "M"

        ElseIf iCol = 13 Then

            Return "N"

        ElseIf iCol = 14 Then

            Return "O"

        ElseIf iCol = 15 Then

            Return "P"

        ElseIf iCol = 16 Then

            Return "Q"

        ElseIf iCol = 17 Then

            Return "R"

        ElseIf iCol = 18 Then

            Return "S"

        End If

        Return ""

    End Function

    Function SaveSymbolLibrary(ByVal oDt As DataTable, ByVal oImageSymbol_Dt As DataTable) As Boolean

        Dim oUpdateFlag As Boolean = True

        Dim mColumnCount As Integer = oDt.Columns.Count

        Try

            Dim sSheetName As String = oDt.TableName

            _ExcelFile.Worksheets(sSheetName).DataValidations.Clear()

            For Each oCol As DataColumn In oDt.Columns

                If IsNumeric(oCol.Namespace) Then

                    Dim sColindex As Integer = oCol.Namespace

                    Dim sExcelColName As String = _ExcelFile.Worksheets(sSheetName).Cells(0, sColindex).Value

                    Dim sColName As String = oCol.ColumnName

                    If sExcelColName <> sColName And sColName <> _Column_Index Then

                        _ExcelFile.Worksheets(sSheetName).Cells(0, sColindex).Value = sColName

                    End If

                End If

            Next

            Dim sAlphabet As String = ""

            Dim iLastRow As Integer = 0

            For j = 0 To oDt.Columns.Count - 1

                If oDt.Columns(j).ColumnName = _Column_ImageSymbol Then

                    Dim iColindex As Integer = -1

                    If IsNumeric(oDt.Columns(j - 1).Namespace) Then

                        iColindex = oDt.Columns(j - 1).Namespace + 1

                    End If

                    If iColindex > 0 Then

                        sAlphabet = ConvertAlphabet(iColindex)

                        _ExcelFile.Worksheets(sSheetName).Columns(iColindex).AutoFit()

                        For i = 0 To oDt.Rows.Count - 1

                            If IsNumeric(oDt.Rows(i)(_Column_Index).ToString()) Then

                                Dim iRowindex As Integer = oDt.Rows(i)(_Column_Index).ToString()

                                Dim sImgSymbolName As String = oDt.Rows(i)(_Column_ImageSymbol).ToString()

                                ' .ErrorMessage = "Value should be a date between 2011-01-01 and 2011-12-31."

                                iLastRow = iRowindex

                                _ExcelFile.Worksheets(sSheetName).Cells(iRowindex, iColindex).Value = sImgSymbolName

                                Dim oFindCell As DataRow() = oImageSymbol_Dt.Select("Type = '" & sSheetName & "'")

                                If oFindCell.Length = 1 Then

                                    Dim sStartcell As String = oFindCell(0)(_Sheet_ImageSymbol_StartCell).ToString()

                                    Dim sEndcell As String = oFindCell(0)(_Sheet_ImageSymbol_EndCell).ToString()

                                    _ExcelFile.Worksheets(sSheetName).DataValidations.Add(New DataValidation(_ExcelFile.Worksheets(sSheetName), sAlphabet & iLastRow + 1) With {

                                .Type = DataValidationType.List,

                                .Formula1 = "=" & _Sheet_ImageSymbol & "!" & sStartcell & ":" & sEndcell,

                                .InputMessageTitle = "Image Symbol 선택",

                                .InputMessage = "SPPID Symbol과 일치하는 ImageSymbol을 선택해주세요",

                                .ErrorStyle = DataValidationErrorStyle.Information,

                                .ErrorTitle = "Invalid Symbol"})

                                End If

                            End If

                        Next

                    End If

                End If

            Next

            'Dim oFindCell As DataRow() = oImageSymbol_Dt.Select("Type = '" & sSheetName & "'")

            'If oFindCell.Length = 1 Then

            '    Dim sStartcell As String = oFindCell(0)(_Sheet_ImageSymbol_StartCell).ToString()

            '    Dim sEndcell As String = oFindCell(0)(_Sheet_ImageSymbol_EndCell).ToString()

            '    _ExcelFile.Worksheets(sSheetName).DataValidations.Add(New DataValidation(_ExcelFile.Worksheets(sSheetName).Cells.GetSubrange(sAlphabet & "1", sAlphabet & iLastRow)) With {

            '.Type = DataValidationType.List,

            '.Formula1 = "=" & _Sheet_ImageSymbol & "!" & sStartcell & ":" & sEndcell,

            '.InputMessageTitle = "Image Symbol 선택",

            '.InputMessage = "SPPID Symbol과 일치하는 ImageSymbol을 선택해주세요",

            '.ErrorStyle = DataValidationErrorStyle.Information,

            '.ErrorTitle = "Invalid Symbol"})

            'End If

        Catch ex As Exception

        End Try

    End Function

    Function outputToExcelFile(ByVal mdt As DataTable, ByVal mReportSubject As String, ByVal mSavePath As String) As String

        '    Dim mExcelFile As New ExcelFile

        Try

            Dim mEwSheet As ExcelWorksheet = _ExcelFile.Worksheets.Add(mReportSubject)

            Dim mColumnCount As Integer = mdt.Columns.Count

            ' 제목

            mEwSheet.Cells.GetSubrangeAbsolute(0, 0, 0, mColumnCount - 2).Merged = True

            mEwSheet.Cells(0, 0).Style.Font.Size = 18 * 20

            mEwSheet.Cells(0, 0).Style.Font.Weight = ExcelFont.BoldWeight

            mEwSheet.Cells(0, 0).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

            mEwSheet.Cells(0, 0).Style.VerticalAlignment = VerticalAlignmentStyle.Center

            mEwSheet.Cells(0, 0).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

            mEwSheet.Cells(0, 0).Value = mReportSubject

            Dim mDateTime As Date = DateTime.Now

            ' 날짜

            mEwSheet.Cells(0, mColumnCount - 1).Value = mDateTime.Year.ToString() + "-" + String.Format("{0:d2}", mDateTime.Month) + "-" + String.Format("{0:d2}", mDateTime.Day)

            mEwSheet.Cells(0, mColumnCount - 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Right

            mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Size = 14 * 20

            mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Weight = ExcelFont.BoldWeight

            mEwSheet.Cells(0, mColumnCount - 1).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

            '---------------------------------------------------------------

            ' 헤더 설정

            '--------------------------------------------------------------

            For i = 0 To mColumnCount - 1

                '--------------------------------------------------------------------------------

                '이름.

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Value = mdt.Columns(i).ToString()

                '---------------------------------------------------------------------------------

                ' 색깔.

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Style.FillPattern.SetSolid(Color.CornflowerBlue)

                '---------------------------------------------------------------------------------

                ' 글씨.

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Style.Font.Color = Color.White

                mEwSheet.Cells(1, i).Style.Font.Weight = ExcelFont.BoldWeight

                '---------------------------------------------------------------------------------

                ' 줄.

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

                '---------------------------------------------------------------------------------

                ' 세로 정렬

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center

                '---------------------------------------------------------------------------------

                ' 가로 정렬

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(1, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

            Next

            '---------------------------------------------------------------

            ' 리포트 행 데이터 입력.

            '---------------------------------------------------------------

            Dim mblankRow As Integer = 2

            For i = 0 To mdt.Rows.Count - 1

                For j = 0 To mdt.Columns.Count - 1

                    ' 값 입력

                    mEwSheet.Cells(i + mblankRow, j).Value = mdt.Rows(i)(j).ToString()

                    '---------------------------------------------------------------------------------

                    ' 줄.

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(i + mblankRow, j).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

                    '---------------------------------------------------------------------------------

                    ' 세로 정렬

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(i + mblankRow, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center

                    '---------------------------------------------------------------------------------

                    ' 가로 정렬

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(i + mblankRow, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left

                Next

            Next

            '---------------------------------------------------------------------------------

            ' 핏

            '---------------------------------------------------------------------------------

            For i = 0 To mColumnCount - 1

                mEwSheet.Columns(i).AutoFit()

            Next

            '---------------------------------------------------------------------------------

            ' 저장

            '---------------------------------------------------------------------------------

            SetSaveFile(_ExcelFile, mSavePath)

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return ""

        End Try

        Return mSavePath

    End Function

    'Function OutputToExcelFile(ByVal mUltragrid As UltraGrid, ByVal mReportSubject As String, ByVal mSavePath As String) As String

    '    Dim mExcelFile As New ExcelFile

    '    Try

    '        Dim mEwSheet As ExcelWorksheet = mExcelFile.Worksheets.Add(mReportSubject)

    '        Dim mColumnCount As Integer = mUltragrid.DisplayLayout.Bands(0).Columns.Count

    '        ' 제목 

    '        mEwSheet.Cells.GetSubrangeAbsolute(0, 0, 0, mColumnCount - 2).Merged = True

    '        mEwSheet.Cells(0, 0).Style.Font.Size = 18 * 20

    '        mEwSheet.Cells(0, 0).Style.Font.Weight = ExcelFont.BoldWeight

    '        mEwSheet.Cells(0, 0).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

    '        mEwSheet.Cells(0, 0).Style.VerticalAlignment = VerticalAlignmentStyle.Center

    '        mEwSheet.Cells(0, 0).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

    '        mEwSheet.Cells(0, 0).Value = mReportSubject

    '        Dim mDateTime As Date = DateTime.Now

    '        ' 날짜

    '        mEwSheet.Cells(0, mColumnCount - 1).Value = mDateTime.Year.ToString() + "-" + String.Format("{0:d2}", mDateTime.Month) + "-" + String.Format("{0:d2}", mDateTime.Day)

    '        mEwSheet.Cells(0, mColumnCount - 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Right

    '        mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Size = 14 * 20

    '        mEwSheet.Cells(0, mColumnCount - 1).Style.Font.Weight = ExcelFont.BoldWeight

    '        mEwSheet.Cells(0, mColumnCount - 1).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

    '        '---------------------------------------------------------------

    '        ' 헤더 설정

    '        '--------------------------------------------------------------

    '        For i = 0 To mColumnCount - 1

    '            '--------------------------------------------------------------------------------

    '            '이름.

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).Value = mUltragrid.DisplayLayout.Bands(0).Columns(i).ToString()

    '            '---------------------------------------------------------------------------------

    '            ' 색깔.

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).Style.FillPattern.SetSolid(Color.CornflowerBlue)

    '            '---------------------------------------------------------------------------------

    '            ' 글씨.

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).Style.Font.Color = Color.White

    '            mEwSheet.Cells(1, i).Style.Font.Weight = ExcelFont.BoldWeight

    '            '---------------------------------------------------------------------------------

    '            ' 줄.

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

    '            '---------------------------------------------------------------------------------

    '            ' 세로 정렬

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center

    '            '---------------------------------------------------------------------------------

    '            ' 가로 정렬

    '            '---------------------------------------------------------------------------------

    '            mEwSheet.Cells(1, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

    '        Next

    '        '---------------------------------------------------------------

    '        ' 리포트 행 데이터 입력.

    '        '---------------------------------------------------------------

    '        Dim mGridRow() As UltraGridRow = mUltragrid.Rows.GetFilteredInNonGroupByRows()

    '        Dim mblankRow As Integer = 2

    '        For i = 0 To mGridRow.Length - 1

    '            For j = 0 To mGridRow.Length - 1

    '                ' 값 입력

    '                mEwSheet.Cells(i + mblankRow, j).Value = mGridRow(i).Cells(j).ToString()

    '                '---------------------------------------------------------------------------------

    '                ' 셀 색.

    '                '---------------------------------------------------------------------------------

    '                If (mGridRow(i).Cells(j).Appearance.BackColor = Color.Empty) Then

    '                    mEwSheet.Cells(i + mblankRow, j).Style.FillPattern.SetSolid(Color.Transparent)

    '                Else

    '                    mEwSheet.Cells(i + mblankRow, j).Style.FillPattern.SetSolid(mGridRow(i).Cells(j).Appearance.BackColor)

    '                End If

    '                '---------------------------------------------------------------------------------

    '                ' 줄.

    '                '---------------------------------------------------------------------------------

    '                mEwSheet.Cells(i + mblankRow, j).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

    '                '---------------------------------------------------------------------------------

    '                ' 세로 정렬

    '                '---------------------------------------------------------------------------------

    '                mEwSheet.Cells(i + mblankRow, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center

    '                '---------------------------------------------------------------------------------

    '                ' 가로 정렬

    '                '---------------------------------------------------------------------------------

    '                mEwSheet.Cells(i + mblankRow, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left

    '            Next

    '        Next

    '        '---------------------------------------------------------------------------------

    '        ' 핏

    '        '---------------------------------------------------------------------------------

    '        For i = 0 To mColumnCount - 1

    '            mEwSheet.Columns(i).AutoFit()

    '        Next

    '        '---------------------------------------------------------------------------------

    '        ' 저장

    '        '---------------------------------------------------------------------------------

    '        If (mSavePath.Contains(".xlsx")) Then

    '            mExcelFile.SaveXlsx(mSavePath)

    '        Else

    '            mExcelFile.SaveXls(mSavePath)

    '            System.Diagnostics.Process.Start(mSavePath)

    '        End If

    '    Catch ex As Exception

    '        MsgBox(ex.Message, MsgBoxStyle.Critical)

    '        Return ""

    '    End Try

    '    Return mSavePath

    'End Function

    '/엑셀파일 여러개 로드할때

    Function isLoadFile(ByVal mExcelFileList As List(Of ExcelFile), ByVal mFilepath() As String)

        Try

            mExcelFileList.Clear()

            For mcnt = 0 To mFilepath.Length - 1

                mExcelFileList.Add(New ExcelFile)

                _ExcelFile = GemBox.Spreadsheet.ExcelFile.Load(mFilepath(mcnt))

            Next

            Return True

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return False

        End Try

    End Function

    '/엑셀파일 하나만 로드할때

    Function isLoadFile(ByVal mFilepath As String) As Boolean

        Try

            _ExcelFile = GemBox.Spreadsheet.ExcelFile.Load(mFilepath)

            Return True

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return False

        End Try

    End Function

    '엑셀 파일 저장하기

    Function SetSaveFile(ByVal mExcelFile As ExcelFile, ByVal outputFilepath As String) As Boolean

        Try

            mExcelFile.Save(outputFilepath)

            Return True

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return False

        End Try

    End Function

    Function SetSaveFile(ByVal outputFilepath As String) As Boolean

        Try

            _ExcelFile.Save(outputFilepath)

            Return True

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Critical)

            Return False

        End Try

    End Function

    '엑셀 셀 데이터 가져오기

    Function GetCellValue(ByVal mData As ExcelCell) As String

        If mData.Value Is Nothing Then

            Return String.Empty

        Else

            Return mData.Value.ToString

        End If

    End Function

    '엑셀 셀 데이터 소문자로 가져오기

    Function GetCellValueToLower(ByVal mData As ExcelCell) As String

        '소문자 변환

        Return GetCellValue(mData).ToLower

    End Function

    '검색스트링으로 엑셀파일 A(첫번째컬럼)의 Row찾기

    Function GetFindRow(ByVal mExcel As ExcelWorksheet, ByVal mName As String) As Integer

        For mCnt = 1 To mExcel.Rows.Count - 1

            If GetCellValueToLower(mExcel.Rows(mCnt).Cells(0)) = mName.ToLower Then

                Return mCnt

            End If

        Next

        Return -1

    End Function

    'row열에 해당하는 마지막 Interface(컬럼) 인덱스 가져오기

    Function GetFindColumnCount(ByVal mExcel As ExcelWorksheet, ByVal mRowCnt As Integer) As Integer

        If mRowCnt = -1 Then

            Return -1

        End If

        For mcnt = 1 To mExcel.CalculateMaxUsedColumns

            If GetCellValue(mExcel.Rows(mRowCnt).Cells(mcnt)) = String.Empty Then

                Return mcnt - 1

            End If

        Next

        Return -1

    End Function

    '검색스트링으로 row에 컬럼들중에서 스트링과 같은 문자열인 컬럼 인덱스 가져오기

    Function getFindColumnIndexFromColumnName(ByVal mExcel As ExcelWorksheet, ByVal mrowcnt As Integer, ByVal mData As String, ByVal mColumnCount As Integer) As Integer

        For mcnt = 1 To mColumnCount

            If GetCellValueToLower(mExcel.Rows(mrowcnt).Cells(mcnt)) = mData.ToLower Then

                Return mcnt

            End If

        Next

        Return -1

    End Function

    ' 검색스트링으로 row에 컬럼들중에서 스트링에 포함하는 컬럼 인덱스 가져오기

    '시작부터 돌면 = true, 끝에서 부터 돌면 = false

    Function getFindColumnIndexFormContainColumnName(ByVal mExcel As ExcelWorksheet, ByVal mRowcnt As Integer, ByVal mData As String, ByVal mColumnCount As Integer, ByVal IsStandardSearch As Boolean) As Integer

        If IsStandardSearch Then

            ' true이면 시작점 부터

            For mcnt = 1 To mColumnCount

                If GetCellValueToLower(mExcel.Rows(mRowcnt).Cells(mcnt)).StartsWith(mData.ToLower) Then

                    Return mcnt

                End If

            Next

        Else

            For mcnt = mColumnCount To 1 Step -1

                If GetCellValueToLower(mExcel.Rows(mRowcnt).Cells(mcnt)).StartsWith(mData.ToLower) Then

                    Return mcnt

                End If

            Next

        End If

        Return -1

    End Function

    '해당 Row가 빈행인지 확인

    Function isBlankCheck(ByVal mExcel As ExcelWorksheet, ByVal mrowcnt As Integer, ByVal mColumnCnt As Integer) As Boolean

        Dim mBlanckCheck As Boolean = True

        For mcnt = 0 To mColumnCnt

            If GetCellValue(mExcel.Rows(mrowcnt).Cells(mcnt)) <> String.Empty Then

                mBlanckCheck = False

            End If

        Next

        Return mBlanckCheck

    End Function

    '셀에 값넣기(가로, 세로 중앙 fit)

    Public Sub SetCell(ByVal mExcelCell As ExcelCell, ByVal mValue As String)

        mExcelCell.Value = mValue

        mExcelCell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

        mExcelCell.Style.VerticalAlignment = VerticalAlignmentStyle.Center

    End Sub

    '셀에 값 넣기(가로, 세로 중앙fit + 글자 진하게)

    Public Sub SetCellBold(ByVal mExcelCell As ExcelCell, ByVal mValue As String)

        SetCell(mExcelCell, mValue)

        mExcelCell.Style.Font.Weight = ExcelFont.BoldWeight

    End Sub

    ' 셀에 값 넣기(가로, 세로 중앙fit + 셀 박스)

    Public Sub SetCellBox(ByVal mExcelCell As ExcelCell, ByVal mValue As String)

        SetCell(mExcelCell, mValue)

        mExcelCell.SetBorders(MultipleBorders.Outside, System.Drawing.Color.Black, LineStyle.Thin)

    End Sub

    '셀에 값 넣기(가로, 세로 중앙fit + 글자 진하게 + 셀 박스)

    Public Sub SetCellBoldAndBox(ByVal mExcelCell As ExcelCell, ByVal mValue As String)

        SetCellBold(mExcelCell, mValue)

        mExcelCell.SetBorders(MultipleBorders.Outside, System.Drawing.Color.Black, LineStyle.Thin)

    End Sub

#End Region

    Sub Init_Setting_Save_Piping_Material_Spec_Report(ByVal mEwSheet As ExcelWorksheet, ByVal mLastColumn As Integer,

                                                           ByVal mFirstRow As Integer, ByVal mValue As String, ByVal mHeaderName As String, ByVal mFirstColumn As Integer, ByVal mFontSize As Integer,

                                                           ByVal mRelativeCol As Integer)

        mEwSheet.Cells(mFirstRow, mRelativeCol).Style.Font.Size = mFontSize

        mEwSheet.Cells(mFirstRow, mRelativeCol).Style.Font.Weight = ExcelFont.BoldWeight

        mEwSheet.Cells(mFirstRow, mRelativeCol).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

        mEwSheet.Cells(mFirstRow, mRelativeCol).Style.VerticalAlignment = VerticalAlignmentStyle.Center

        mEwSheet.Cells(mFirstRow, mRelativeCol).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

        mEwSheet.Cells(mFirstRow, mRelativeCol).Style.FillPattern.SetSolid(Color.Orange)

        mEwSheet.Cells(mFirstRow, mRelativeCol).Value = mHeaderName

        mEwSheet.Cells.GetSubrangeAbsolute(mFirstRow, mFirstColumn, mFirstRow, mLastColumn).Merged = True

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.Font.Size = mFontSize

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.Font.Weight = ExcelFont.BoldWeight

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.VerticalAlignment = VerticalAlignmentStyle.Center

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Style.FillPattern.SetSolid(Color.Orange)

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

        mEwSheet.Cells(mFirstRow, mRelativeCol + 1).Value = mValue

        'For mcnt = 0 To mEwSheet.Columns.Count - 1

        '    mEwSheet.Columns(mcnt).AutoFit()

        'Next

    End Sub

    Function Index_Input(ByVal mName As String) As Boolean

        If _ExcelFile.Worksheets.Count = 0 Then

            Dim mEwSheet As ExcelWorksheet = _ExcelFile.Worksheets.Add("Index")

            mEwSheet.Cells(0, 0).Value = "!Index"

            Init_Setting_Save_Piping_Material_Spec_Report(mEwSheet, 1, 1, mName, "No", 1, 14 * 16, 1)

            Return True

        End If

        Return False

    End Function

    Sub Init_Main_Column(ByVal mEwSheet As ExcelWorksheet, ByVal mRowInt As Integer, ByVal mColInt As Integer,

                         ByVal mFontSize As Integer, ByVal mBold As Integer,

                         ByVal mHorizontalAlighment As Integer, ByVal mVerticalAlighment As Integer,

                         ByVal mOutsize As Integer, ByVal mOutsideColor As Color, ByVal mOutsideLine As Integer,

                         ByVal mFillpattern As Color, ByVal mValue As String)

        mEwSheet.Cells(mRowInt, mColInt).Style.Font.Size = mFontSize

        mEwSheet.Cells(mRowInt, mColInt).Style.Font.Weight = mBold

        mEwSheet.Cells(mRowInt, mColInt).Style.HorizontalAlignment = mHorizontalAlighment

        mEwSheet.Cells(mRowInt, mColInt).Style.VerticalAlignment = mVerticalAlighment

        mEwSheet.Cells(mRowInt, mColInt).SetBorders(mOutsize, mOutsideColor, mOutsideLine)

        mEwSheet.Cells(mRowInt, mColInt).Style.FillPattern.SetSolid(mFillpattern)

        mEwSheet.Cells(mRowInt, mColInt).Value = mValue

        mEwSheet.Columns(mColInt).AutoFit()

    End Sub

    Sub Excel_Report_GRIDVIEW_Input(ByVal mHeaderInt As Integer, ByVal mUltragrid As DataGridView, ByVal mEwSheet As ExcelWorksheet)

        Dim mRowcnt As Integer = mUltragrid.Rows.Count

        Dim mColCnt As Integer = mUltragrid.Columns.Count

        Dim mMainHeadercnt As Integer = mHeaderInt

        '---------------------------------------------------------------

        ' 헤더 설정

        '--------------------------------------------------------------

        For i = 0 To mColCnt - 1

            '--------------------------------------------------------------------------------

            '이름.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Value = mUltragrid.Columns(i).ToString()

            '---------------------------------------------------------------------------------

            ' 색깔.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.FillPattern.SetSolid(Color.CornflowerBlue)

            '---------------------------------------------------------------------------------

            ' 글씨.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Color = Color.White

            mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Weight = ExcelFont.BoldWeight

            '---------------------------------------------------------------------------------

            ' 줄.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

            '---------------------------------------------------------------------------------

            ' 세로 정렬

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center

            '---------------------------------------------------------------------------------

            ' 가로 정렬

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

        Next

        mMainHeadercnt += 1

        '---------------------------------------------------------------

        ' 리포트 행 데이터 입력.

        '---------------------------------------------------------------

        '   Dim mGridRow() As UltraGridRow = mUltragrid.Rows.GetFilteredInNonGroupByRows()

        ' Dim mblankRow As Integer = 2

        For i = 0 To mRowcnt - 1

            For j = 0 To mColCnt - 1

                Try

                    mEwSheet.Cells(mMainHeadercnt, j).Value = mUltragrid.Rows(i).Cells(j).Value

                    '---------------------------------------------------------------------------------

                    ' 셀 색.

                    '---------------------------------------------------------------------------------

                    If (mUltragrid.Rows(i).Cells(j).Style.BackColor = Color.Empty) Then

                        mEwSheet.Cells(mMainHeadercnt, j).Style.FillPattern.SetSolid(Color.Transparent)

                    Else

                        mEwSheet.Cells(mMainHeadercnt, j).Style.FillPattern.SetSolid(mUltragrid.Rows(i).Cells(j).Style.BackColor)

                    End If

                    '---------------------------------------------------------------------------------

                    ' 줄.

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(mMainHeadercnt, j).Style.Borders.SetBorders(MultipleBorders.None, Color.Black, LineStyle.None)

                    '---------------------------------------------------------------------------------

                    ' 세로 정렬

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(mMainHeadercnt, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center

                    '---------------------------------------------------------------------------------

                    ' 가로 정렬

                    '---------------------------------------------------------------------------------

                    mEwSheet.Cells(mMainHeadercnt, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left

                Catch ex As Exception

                End Try

                ' 값 입력

            Next

            mMainHeadercnt += 1

        Next

        '---------------------------------------------------------------------------------

        ' 핏

        '---------------------------------------------------------------------------------

        For i = 0 To mColCnt - 1

            mEwSheet.Columns(i).AutoFit()

        Next

    End Sub

    Sub Excel_Report_Datatable_Input(ByVal mHeaderInt As Integer, ByVal mdt As DataTable, ByVal mEwSheet As ExcelWorksheet)

        Dim mRowcnt As Integer = mdt.Rows.Count

        Dim mColCnt As Integer = mdt.Columns.Count

        '---------------------------------------------------------------

        '  SpecName 설정

        '--------------------------------------------------------------

        Dim mMainHeadercnt As Integer = mHeaderInt

        '---------------------------------------------------------------

        ' 헤더 설정

        '--------------------------------------------------------------

        For i = 0 To mColCnt - 1

            '--------------------------------------------------------------------------------

            '이름.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Value = mdt.Columns(i).ToString()

            '---------------------------------------------------------------------------------

            ' 색깔.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.FillPattern.SetSolid(Color.Purple)

            '---------------------------------------------------------------------------------

            ' 글씨.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Color = Color.White

            mEwSheet.Cells(mMainHeadercnt, i).Style.Font.Weight = ExcelFont.BoldWeight

            '---------------------------------------------------------------------------------

            ' 줄.

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

            '---------------------------------------------------------------------------------

            ' 세로 정렬

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.VerticalAlignment = VerticalAlignmentStyle.Center

            '---------------------------------------------------------------------------------

            ' 가로 정렬

            '---------------------------------------------------------------------------------

            mEwSheet.Cells(mMainHeadercnt, i).Style.HorizontalAlignment = HorizontalAlignmentStyle.Center

            mEwSheet.Columns(i).AutoFit()

        Next

        mMainHeadercnt += 1

        '---------------------------------------------------------------

        ' 리포트 행 데이터 입력.

        '---------------------------------------------------------------

        '    Dim mblankRow As Integer = 2

        For i = 0 To mdt.Rows.Count - 1

            For j = 0 To mdt.Columns.Count - 1

                'If j = 0 Then

                '    mEwSheet.Cells(mMainHeadercnt, j).Value = i + 1

                'Else

                '    ' 값 입력

                '    mEwSheet.Cells(mMainHeadercnt, j).Value = mdt.Rows(i)(j).ToString()

                '    '---------------------------------------------------------------------------------

                'End If

                ' 값 입력

                mEwSheet.Cells(mMainHeadercnt, j).Value = mdt.Rows(i)(j).ToString()

                ' 줄.

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(mMainHeadercnt, i).Style.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin)

                '---------------------------------------------------------------------------------

                ' 세로 정렬

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(mMainHeadercnt, j).Style.VerticalAlignment = VerticalAlignmentStyle.Center

                '---------------------------------------------------------------------------------

                ' 가로 정렬

                '---------------------------------------------------------------------------------

                mEwSheet.Cells(mMainHeadercnt, j).Style.HorizontalAlignment = HorizontalAlignmentStyle.Left

                mEwSheet.Columns(j).AutoFit()

            Next

            mMainHeadercnt += 1

        Next

        '---------------------------------------------------------------------------------

        ' 핏

        '---------------------------------------------------------------------------------

        'For i = 0 To mColCnt - 1

        '    mEwSheet.Columns(i).AutoFit()

        'Next

    End Sub

    Sub HyperLink(ByVal mEwSheet As ExcelWorksheet)

        '-----------------------------------------

        ' 하이퍼 링크

        '----------------------------------------

        mEwSheet.Cells("A1").Value = "!index"

        mEwSheet.Cells("A1").Style.Font.UnderlineStyle = UnderlineStyle.Single

        mEwSheet.Cells("A1").Style.Font.Color = Color.Blue

        mEwSheet.Cells("A1").Hyperlink.Location = ("Index" & "!A1")

        Init_Setting_Save_Piping_Material_Spec_Report(_ExcelFile.Worksheets(0), 1, _Index_Cnt, mEwSheet.Name, _Index_Cnt - 1, 1, 14 * 16, 1)

        '_ExcelFile.Worksheets(0).Cells("A" & _Index_Cnt).Value = _Index_Cnt - 1

        _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Style.Font.UnderlineStyle = UnderlineStyle.Single

        _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Style.Font.Color = Color.Blue

        _ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt + 1).Hyperlink.Location = (mEwSheet.Name & "!A1")

        _ExcelFile.Worksheets(0).Columns(1).Width = 5000

        '_ExcelFile.Worksheets(0).Cells("B" & _Index_Cnt).Value = mEwSheet.Name

        _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Style.Font.UnderlineStyle = UnderlineStyle.Single

        _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Style.Font.Color = Color.Blue

        _ExcelFile.Worksheets(0).Cells("C" & _Index_Cnt + 1).Hyperlink.Location = (mEwSheet.Name & "!A1")

        _ExcelFile.Worksheets(0).Columns(2).Width = 9000

        '   _ExcelFile.Worksheets(0).Columns(1).AutoFit()

        _Index_Cnt += 1

    End Sub

End Class

Public Class Drawing

    Private _DWGNAME As String = ""

    Private _SIZE As String = ""

    Private _UNIT As String = ""

    Private _LINE_NO As List(Of Line_no)

    Private _TRIM_LINE_NO As List(Of Line_no)

    Private _EQP_NO As List(Of Eqp_no)

    Public Property DwgName() As String

        Get

            Return _DWGNAME

        End Get

        Set(value As String)

            _DWGNAME = value

        End Set

    End Property

    Public Property Size() As String

        Get

            Return _SIZE

        End Get

        Set(value As String)

            _SIZE = value

        End Set

    End Property

    Public Property Unit() As String

        Get

            Return _UNIT

        End Get

        Set(value As String)

            _UNIT = value

        End Set

    End Property

    Public Property Line_nos As List(Of Line_no)

        Get

            Return _LINE_NO

        End Get

        Set(value As List(Of Line_no))

            _LINE_NO = value

        End Set

    End Property

    Public Property TrimLine_nos As List(Of Line_no)

        Get

            Return _TRIM_LINE_NO

        End Get

        Set(value As List(Of Line_no))

            _TRIM_LINE_NO = value

        End Set

    End Property

    Public Property Eqp_nos As List(Of Eqp_no)

        Get

            Return _EQP_NO

        End Get

        Set(value As List(Of Eqp_no))

            _EQP_NO = value

        End Set

    End Property

End Class

Public Class Line_no

    Private _UID As String = ""

    Private _TEXT As String = ""

    Private _LOCATION As String = ""

    Private _ANGLE As String = ""

    Private _SYSTEMPATH As String = ""

    Private _Line_Dt As DataTable

    Private _Symbol_Dt As DataTable

    Private _Attribute_Dt As DataTable

    Private _Line As List(Of Line)

    Private _Symbol As List(Of Symbol)

    Private _Attribute As List(Of Attribute)

    Public Property Uid() As String

        Get

            Return _UID

        End Get

        Set(value As String)

            _UID = value

        End Set

    End Property

    Public Property Text() As String

        Get

            Return _TEXT

        End Get

        Set(value As String)

            _TEXT = value

        End Set

    End Property

    Public Property Location() As String

        Get

            Return _LOCATION

        End Get

        Set(value As String)

            _LOCATION = value

        End Set

    End Property

    Public Property Angle() As String

        Get

            Return _ANGLE

        End Get

        Set(value As String)

            _ANGLE = value

        End Set

    End Property

    Public Property SystemPath() As String

        Get

            Return _SYSTEMPATH

        End Get

        Set(value As String)

            _SYSTEMPATH = value

        End Set

    End Property

    Public Property Dt_Line As DataTable

        Get

            Return _Line_Dt

        End Get

        Set(value As DataTable)

            _Line_Dt = value

        End Set

    End Property

    Public Property Dt_Symbol As DataTable

        Get

            Return _Symbol_Dt

        End Get

        Set(value As DataTable)

            _Symbol_Dt = value

        End Set

    End Property

    Public Property Dt_Attribute As DataTable

        Get

            Return _Attribute_Dt

        End Get

        Set(value As DataTable)

            _Attribute_Dt = value

        End Set

    End Property

    Public Property Line As List(Of Line)

        Get

            Return _Line

        End Get

        Set(value As List(Of Line))

            _Line = value

        End Set

    End Property

    Public Property Symbol As List(Of Symbol)

        Get

            Return _Symbol

        End Get

        Set(value As List(Of Symbol))

            _Symbol = value

        End Set

    End Property

    Public Property Attribute As List(Of Attribute)

        Get

            Return _Attribute

        End Get

        Set(value As List(Of Attribute))

            _Attribute = value

        End Set

    End Property

End Class

Public Class Eqp_no

    Private _UID As String = ""

    Private _TEXT As String = ""

    Private _LOCATION As String = ""

    Private _ANGLE As String = ""

    Private _SYSTEMPATH As String = ""

    Private _Eqp_Dt As DataTable

    Private _Attribute_Dt As DataTable

    Public Property Uid() As String

        Get

            Return _UID

        End Get

        Set(value As String)

            _UID = value

        End Set

    End Property

    Public Property Text() As String

        Get

            Return _TEXT

        End Get

        Set(value As String)

            _TEXT = value

        End Set

    End Property

    Public Property Location() As String

        Get

            Return _LOCATION

        End Get

        Set(value As String)

            _LOCATION = value

        End Set

    End Property

    Public Property Angle() As String

        Get

            Return _ANGLE

        End Get

        Set(value As String)

            _ANGLE = value

        End Set

    End Property

    Public Property SystemPath() As String

        Get

            Return _SYSTEMPATH

        End Get

        Set(value As String)

            _SYSTEMPATH = value

        End Set

    End Property

    Public Property Dt_Equipment As DataTable

        Get

            Return _Eqp_Dt

        End Get

        Set(value As DataTable)

            _Eqp_Dt = value

        End Set

    End Property

    Public Property Dt_Attribute As DataTable

        Get

            Return _Attribute_Dt

        End Get

        Set(value As DataTable)

            _Attribute_Dt = value

        End Set

    End Property

End Class

Public Class Symbol

End Class

Public Class Line

End Class

Public Class Attribute

End Class

Public Class FindOpc

    'Dialog Caption : Set Stockpile Location of Partner OPC

    'Dialog Class : ThunderRT6FormDC

    Private Declare Auto Function FindWindow Lib "user32.dll" (

ByVal lpClassName As String,

ByVal lpWindowName As String

) As IntPtr

    Private Declare Auto Function FindWindowEx Lib "user32.dll" (

ByVal hwndParent As IntPtr,

ByVal hwndChildAfter As IntPtr,

ByVal lpszClass As String,

ByVal lpszWindow As String

) As IntPtr

    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long,

ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

    Private Declare Auto Function SendMessage Lib "user32.dll" (

hWnd As IntPtr, uMsg As Int32, WParam As IntPtr, LParam As IntPtr

) As IntPtr

    Private Const WM_CLOSE As Long = &H10

    Private Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long

    Public Sub RemoveOPCDlg()

        Try

            Dim iHandle As Integer = FindWindow("ThunderRT6FormDC", "Set Stockpile Location of Partner OPC")

            If iHandle > 0 Then

                System.Threading.Thread.Sleep(100)

                SendMessage(iHandle, WM_CLOSE, 0, 0) 'Press Ctrl Key

                System.Threading.Thread.Sleep(100)

                iHandle = 0

            End If

        Catch ex As Exception

        End Try

    End Sub

End Class

Public Class GenerateRelation

    Dim d라인오차범위 As Double = 100

    Dim d심블오차범위 As Double = 100

    Public Function CalcLineConnectionItem_Equal(ByRef oLinelist_Dt_Line As DataTable, ByRef oLineLists As List(Of Line_no)) As Boolean

        Try

            For Each oLineRow In oLinelist_Dt_Line.Rows

                Dim sLineUid As String = oLineRow(_XML_LINE_UID)

                Dim dLineStart_x As Double = 0.0

                Dim dLineStart_y As Double = 0.0

                Dim dLineEnd_x As Double = 0.0

                Dim dLineEnd_y As Double = 0.0

                Dim sStartpoint As String = oLineRow(_XML_LINE_STARTPOINT).ToString()

                Dim sEndpoint As String = oLineRow(_XML_LINE_ENDPOINT).ToString()

                ConvertPointBystring(sStartpoint, dLineStart_x, dLineStart_y)

                ConvertPointBystring(sEndpoint, dLineEnd_x, dLineEnd_y)

                Dim sConn1_Uid As String = oLineRow(_XML_LINE_Conn1_Uid).ToString()

                Dim sConn2_Uid As String = oLineRow(_XML_LINE_Conn2_Uid).ToString()

                Dim sConn1_Flag As Boolean = False

                Dim sConn2_Flag As Boolean = False

                For Each oLinelist In oLineLists

                    For Each oConnLineRow In oLinelist.Dt_Line.Rows

                        Dim sConnLineUid As String = oConnLineRow(_XML_LINE_UID)

                        If sLineUid <> sConnLineUid Then

                            If sConn1_Uid <> "" And sConn2_Uid <> "" Then

                                Exit For

                            End If

                            Dim dConnLineStart_x As Double = 0.0

                            Dim dConnLineStart_y As Double = 0.0

                            Dim dConnLineEnd_x As Double = 0.0

                            Dim dConnLineEnd_y As Double = 0.0

                            Dim sConnStartpoint As String = oConnLineRow(_XML_LINE_STARTPOINT).ToString()

                            Dim sConnEndpoint As String = oConnLineRow(_XML_LINE_ENDPOINT).ToString()

                            ConvertPointBystring(sConnStartpoint, dConnLineStart_x, dConnLineStart_y)

                            ConvertPointBystring(sConnEndpoint, dConnLineEnd_x, dConnLineEnd_y)

                            If CheckDuplicatePoint(dLineStart_x, dLineStart_y, dLineEnd_x, dLineEnd_y,

                                                   dConnLineStart_x, dConnLineStart_y, dConnLineEnd_x, dConnLineEnd_y) Then

                                If sConn1_Uid = "" Then

                                    If dLineStart_x = dConnLineStart_x And dLineStart_y = dConnLineStart_y Then

                                        sConn1_Uid = sConnLineUid

                                        oConnLineRow(_XML_LINE_Conn1_Uid) = sLineUid

                                        oLineRow(_XML_LINE_Conn1_Uid) = sConnLineUid

                                    ElseIf dLineStart_x = dConnLineEnd_x And dLineStart_y = dConnLineEnd_y Then

                                        sConn1_Uid = sConnLineUid

                                        oConnLineRow(_XML_LINE_Conn2_Uid) = sLineUid

                                        oLineRow(_XML_LINE_Conn1_Uid) = sConnLineUid

                                    End If

                                End If

                                If sConn2_Uid = "" Then

                                    If dLineEnd_x = dConnLineStart_x And dLineEnd_y = dConnLineStart_y Then

                                        sConn2_Uid = sConnLineUid

                                        oConnLineRow(_XML_LINE_Conn1_Uid) = sLineUid

                                        oLineRow(_XML_LINE_Conn2_Uid) = sConnLineUid

                                    ElseIf dLineEnd_x = dConnLineEnd_x And dLineEnd_y = dConnLineEnd_y Then

                                        sConn2_Uid = sConnLineUid

                                        oConnLineRow(_XML_LINE_Conn2_Uid) = sLineUid

                                        oLineRow(_XML_LINE_Conn2_Uid) = sConnLineUid

                                    End If

                                End If

                            End If

                        End If

                    Next

                Next

                For Each oLinelist In oLineLists

                    For Each oConnSymbolRow In oLinelist.Dt_Symbol.Rows

                        If sConn1_Uid <> "" And sConn2_Uid <> "" Then

                            Exit For

                        End If

                        Dim sSymbolUid As String = oConnSymbolRow(_XML_SYMBOL_UID).ToString()

                        Dim sSymbolName As String = oConnSymbolRow(_XML_SYMBOL_NAME).ToString()

                        Dim sSymbolConnPoint As String = oConnSymbolRow(_XML_SYMBOL_CONNECTIONPOINT).ToString()

                        Dim sConnSplit As String() = sSymbolConnPoint.Split("/")

                        If sLineUid = "3c90fe63-b88e-4bb3-a774-a0c4ae8c4ebf" And sSymbolUid = "9ebcb62e-156e-4d3c-aeb2-3d9e5089915e" Then

                        End If

                        If sSymbolConnPoint <> "" Then

                            Dim dSymbolConn1_x As Double = 0.0

                            Dim dSymbolConn1_y As Double = 0.0

                            Dim dSymbolConn2_x As Double = 0.0

                            Dim dSymbolConn2_y As Double = 0.0

                            Dim dSymbolConn3_x As Double = 0.0

                            Dim dSymbolConn3_y As Double = 0.0

                            Dim dSymbolConn4_x As Double = 0.0

                            Dim dSymbolConn4_y As Double = 0.0

                            Dim sConn1Point As String = ""

                            Dim sConn2Point As String = ""

                            Dim sConn3Point As String = ""

                            Dim sConn4Point As String = ""

                            sConn1Point = sConnSplit(0)

                            If sConnSplit.Length > 1 Then

                                sConn2Point = sConnSplit(1)

                                If sConnSplit.Length > 2 Then

                                    sConn3Point = sConnSplit(2)

                                    If sConnSplit.Length > 3 Then

                                        sConn4Point = sConnSplit(3)

                                    End If

                                End If

                            End If

                            ConvertPointBystring(sConn1Point, dSymbolConn1_x, dSymbolConn1_y)

                            If sConn2Point <> "" Then

                                ConvertPointBystring(sConn2Point, dSymbolConn2_x, dSymbolConn2_y)

                            End If

                            If sConn3Point <> "" Then

                                ConvertPointBystring(sConn3Point, dSymbolConn3_x, dSymbolConn3_y)

                            End If

                            If sConn4Point <> "" Then

                                ConvertPointBystring(sConn4Point, dSymbolConn4_x, dSymbolConn4_y)

                            End If

                            '라인 끝에 연결되어 있는 심블

                            If sConn1_Uid = "" Then

                                If dLineStart_x = dSymbolConn1_x And dLineStart_y = dSymbolConn1_y Then

                                    sConn1_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn1_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn1_Uid) = sConn1_Uid

                                ElseIf dLineStart_x = dSymbolConn2_x And dLineStart_y = dSymbolConn2_y Then

                                    sConn1_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn2_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn1_Uid) = sConn1_Uid

                                ElseIf dLineStart_x = dSymbolConn3_x And dLineStart_y = dSymbolConn3_y Then

                                    sConn1_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn3_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn1_Uid) = sConn1_Uid

                                ElseIf dLineStart_x = dSymbolConn4_x And dLineStart_y = dSymbolConn4_y Then

                                    sConn1_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn4_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn1_Uid) = sConn1_Uid

                                End If

                            End If

                            If sConn2_Uid = "" Then

                                If dLineEnd_x = dSymbolConn1_x And dLineEnd_y = dSymbolConn1_y Then

                                    sConn2_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn1_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn2_Uid) = sConn2_Uid

                                ElseIf dLineEnd_x = dSymbolConn2_x And dLineEnd_y = dSymbolConn2_y Then

                                    sConn2_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn2_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn2_Uid) = sConn2_Uid

                                ElseIf dLineEnd_x = dSymbolConn3_x And dLineEnd_y = dSymbolConn3_y Then

                                    sConn2_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn3_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn2_Uid) = sConn2_Uid

                                ElseIf dLineEnd_x = dSymbolConn4_x And dLineEnd_y = dSymbolConn4_y Then

                                    sConn2_Uid = sSymbolUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn4_Uid) = sLineUid

                                    oLineRow(_XML_LINE_Conn2_Uid) = sConn2_Uid

                                End If

                            End If

                            '라인 안에 포함되어 있는 심블

                            If sConn1_Uid = "" And sConn2_Uid = "" Then

                                If (dLineStart_x <= dSymbolConn1_x And dLineStart_y <= dSymbolConn1_y And

                                dLineEnd_x >= dSymbolConn2_x And dLineEnd_y >= dSymbolConn2_y) Or

                                (dLineStart_x >= dSymbolConn1_x And dLineStart_y >= dSymbolConn1_y And

                                dLineEnd_x <= dSymbolConn2_x And dLineEnd_y <= dSymbolConn2_y) Then

                                    oConnSymbolRow(_XML_SYMBOL_Conn1_Uid) = sLineUid

                                    oConnSymbolRow(_XML_SYMBOL_Conn2_Uid) = sLineUid

                                Else

                                End If

                            End If

                        End If

                    Next

                Next

                oLineRow(_XML_LINE_Conn1_Uid) = sConn1_Uid

                oLineRow(_XML_LINE_Conn2_Uid) = sConn2_Uid

            Next

        Catch ex As Exception

            Return False

        End Try

        Return True

    End Function

    Public Function CalcLineConnectionItem_NonEqual(ByRef oLinelist_Dt_Line As DataTable, ByRef oLineLists As List(Of Line_no)) As Boolean

        Try

            For Each oLineRow In oLinelist_Dt_Line.Rows

                Dim sLineUid As String = oLineRow(_XML_LINE_UID).ToString()

                If sLineUid = "c5e85037-c9a6-4b8b-aff8-240d4dd89876" Then

                    ' MessageBox.Show("")

                End If

                Dim dLineStart_x As Double = 0.0

                Dim dLineStart_y As Double = 0.0

                Dim dLineEnd_x As Double = 0.0

                Dim dLineEnd_y As Double = 0.0

                Dim sStartpoint As String = oLineRow(_XML_LINE_STARTPOINT).ToString()

                Dim sEndpoint As String = oLineRow(_XML_LINE_ENDPOINT).ToString()

                ConvertPointBystring(sStartpoint, dLineStart_x, dLineStart_y)

                ConvertPointBystring(sEndpoint, dLineEnd_x, dLineEnd_y)

                Dim dPlusMinusValue = 0

                If Math.Abs(dLineStart_x - dLineEnd_x) < Math.Abs(dLineStart_y - dLineEnd_y) Then

                    '세로라인

                    dPlusMinusValue = Math.Abs(dLineStart_y - dLineEnd_y) / d라인오차범위

                Else

                    '가로라인

                    dPlusMinusValue = Math.Abs(dLineStart_x - dLineEnd_x) / d라인오차범위

                End If

                Dim sConn1_Uid As String = oLineRow(_XML_LINE_Conn1_Uid).ToString()

                Dim sConn2_Uid As String = oLineRow(_XML_LINE_Conn2_Uid).ToString()

                For Each oConnLineRow In oLinelist_Dt_Line.Rows

                    Dim sConnLineUid As String = oConnLineRow(_XML_LINE_UID).ToString()

                    If sLineUid <> sConnLineUid Then

                        If sConn1_Uid <> "" And sConn2_Uid <> "" Then

                            Exit For

                        End If

                        Dim dConnLineStart_x As Double = 0.0

                        Dim dConnLineStart_y As Double = 0.0

                        Dim dConnLineEnd_x As Double = 0.0

                        Dim dConnLineEnd_y As Double = 0.0

                        Dim sConnStartpoint As String = oConnLineRow(_XML_LINE_STARTPOINT).ToString()

                        Dim sConnEndpoint As String = oConnLineRow(_XML_LINE_ENDPOINT).ToString()

                        ConvertPointBystring(sConnStartpoint, dConnLineStart_x, dConnLineStart_y)

                        ConvertPointBystring(sConnEndpoint, dConnLineEnd_x, dConnLineEnd_y)