Imports System
Imports System.Diagnostics
Imports System.ComponentModel

Imports System.Management
Imports System.IO
Imports System.Collections
Imports System.Drawing
Imports System.Threading
Imports System.Windows.Forms

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports Microsoft.Win32


Public Class Form1


    ' Will only use these if for some reason the default template files cannot be found
    Dim sThreaded_Header As String = "Thread Type - Standard(1). Straight(2) or Tapered(3),Sub Type,Thread Family,Size,Nominal Diameter,Tap Drill Diameter,Internal Minor,External Minor,Outside Pipe Diameter,Length For External Threads,Inside Pipe Diameter,Length For Internal Threads,Thread Height,Thread Type External,Thread Type Internal,Start Chamfer Offset,Start Chamfer Angle,End Chamfer Offset,End Chamfer Angle,Hole Depth,V Bottom Angle"
    Dim sSimple_Header As String = "Sub Type,Size,Fit,Hole Diameter,Start Chamfer Offset,Start Chamfer Angle,End Chamfer Offset,End Chamfer Angle,Hole Depth,V Bottom Angle"
    Dim sCounterbore As String = "Sub Type,Size,Fit,Hole Diameter,Counterbore Diameter,Counterbore Depth,Start Chamfer Offset,Start Chamfer Angle,Neck Chamfer Offset,Neck Chamfer Angle,End Chamfer Offset,End Chamfer Angle,Hole Depth,V Bottom Angle"
    Dim sCountersink As String = "Sub Type,Size,Fit,Hole Diameter,Countersink Diameter,Countersink Angle,Countersink Head Clearance,Start Chamfer Offset,Start Chamfer Angle,End Chamfer Offset,End Chamfer Angle,Hole Depth,V Bottom Angle"



    Dim sOutputLog As String = Nothing
    Dim sLocalizedTemplate As String = Nothing
    Dim sEXEPath As String = Nothing
    Dim sDirectoryLocation As String = Nothing
    Dim sSolidEdgePref As String = Nothing


    Enum Header
        BeginInch = 1
        EndInch = 2
        BeginMetric = 3
        EndMetric = 4
        BeginStraightPipe = 5
        EndStraightPipe = 6
        BeginTaperPipe = 7
        EndTaperPipe = 8
        NotDefined = 9
    End Enum

    ' Keep track of the active header
    Dim eCurrentHeader As Header

    ' The Sheet order of the xlsx files should not be changed.
    Enum SheetName
        Units = 1
        Simple = 2
        Threaded = 3
    End Enum


    Dim bHoleTemplate_Found As Boolean = False
    Dim bFileCreated As Boolean = False

    Dim sUnit As String = Nothing

    Dim bInchFile As Boolean = False
    Dim bMetricFile As Boolean = False

    Dim nTotal_Files_To_Convert As Integer = 0
    Dim nPassed As Integer = 0
    Dim nFailed As Integer = 0

    Dim aFailed_LogInfo As New ArrayList
    Dim aPassed_LogInfo As New ArrayList

    Dim aMetricSimpleDia As New ArrayList
    Dim aStandardSimpleDia As New ArrayList

    Private Sub Process_Click(sender As Object, e As EventArgs) Handles ProcessBtn.Click
        Dim SEInstallData As Object = Nothing
        Dim sSolidEdgeInstallPath As String = Nothing
        Dim DirInfo As DirectoryInfo = Nothing
        Dim ParentDir As DirectoryInfo = Nothing
        Dim SubDirs As DirectoryInfo() = Nothing
        Dim dri As DirectoryInfo = Nothing
        Dim i As Integer = 0
        Dim j As Integer = 0


        eCurrentHeader = Header.NotDefined


        Dim aData As New ArrayList                                  ' Array that contains the data from the file.
        aData.Clear()

        Dim bFailure As Boolean = False                             ' Flag - Used to determine if there was a failure reading or writing out the data
        Dim bValid As Boolean = False                               ' Flag - Line valid or not.
        Dim sMsg As String = Nothing                                ' String to keep track of the error messages.



        ' Make sure they select at least one file to be converted
        If (ListBox1.Items.Count = 0) Then
            MsgBox("You need to provide file(s) to be converted.")
            Exit Sub
        End If

        ' They didn't provide a output directory so warn them and exit.
        If (Output_Directory_TB.Text.Trim = "") Then
            MsgBox("You need to provide an output dirctory.")
            Exit Sub
        End If

        ' Show a wait cursor 
        Windows.Forms.Cursor.Current = Cursors.WaitCursor


        Dim bFileValid As Boolean = True                ' Flag used to determine if the file has a valid format.


        ' Clear out the Arrays that contain the list of files that Fail and Pass.
        aFailed_LogInfo.Clear()
        aPassed_LogInfo.Clear()


        ' Clear out the previous runs log if it exist.  Want to put it in the directory where the exe is instead of where the output directory so we don't it the text file in the Preferences 
        ' in case that is where the customer put the output.
        File.Delete(sOutputLog)

        ' Get the number of files to be converted.
        nTotal_Files_To_Convert = ListBox1.Items.Count


        Try
            ' Loop through the files that they selected and process them.
            For i = 0 To ListBox1.Items.Count - 1

                bFileCreated = False    ' reset variable.

                ' Show them which file is being converted so they know things are progressing.
                ' Check to see if the file exists, maybe they deleted it after selecting it?  Not likely but might as well check
                If File.Exists(ListBox1.Items.Item(i)) Then
                    Dim fl As New FileInfo(ListBox1.Items.Item(i))
                    resultLabel.Text = "Converting: " + fl.Name
                    Me.Update()
                End If


                bFileValid = True                       ' Reset the Flag that determines if the File has a valid format or not.
                aData.Clear()                           ' Clear the data array so it can be populated with the inforamtion in the next file.


                ' Read in the file
                ReadInFile(ListBox1.Items.Item(i), aData, False, bFailure)

                ' Loop through the data and check to see if the file is valid
                For j = 0 To aData.Count - 1
                    bValid = True
                    sMsg = Nothing
                    Check_Line_Format(aData(j), bValid, sMsg)

                    ' If not valid then put it in the log and continue to the next file.
                    If (bValid = False) Then
                        Add_Msg_To_Array(aFailed_LogInfo, ListBox1.Items.Item(i).ToString + ": File Invalid - " + sMsg)
                        bFileValid = False
                        Exit For

                    End If
                Next

                ' If the file was valid up to now, but doesn't end with a EndInch or EndMetric then its invalid.  Continue to the next file.
                If (bValid) And (eCurrentHeader <> Header.EndInch) And (eCurrentHeader <> Header.EndMetric) Then
                    Add_Msg_To_Array(aFailed_LogInfo, ListBox1.Items.Item(i).ToString + ": File Invalid - It doesn't end with either EndInch or EndMetric.")
                    bFileValid = False
                    Continue For
                End If


                ' Valid file write it out.
                If (bFileValid) Then

                    bInchFile = False
                    bMetricFile = False

                    ' Write out the data to the xlsx file.
                    Write_Out_File_Data(ListBox1.Items.Item(i), aData, bFailure)
                    If (bFailure) Then
                        Add_Msg_To_Array(aFailed_LogInfo, ListBox1.Items.Item(i).ToString + ": Problem writing out the modified file.")
                    End If
                End If

            Next

        Catch ex As Exception

        End Try


        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Write out the results.
        nFailed = aFailed_LogInfo.Count
        nPassed = aPassed_LogInfo.Count

        aPassed_LogInfo.Insert(0, "Total Files: " + nTotal_Files_To_Convert.ToString)
        aPassed_LogInfo.Insert(1, "Converted Files: " + nPassed.ToString)
        aPassed_LogInfo.Insert(2, "Failed Files: " + nFailed.ToString)

        If (nPassed > 0) Then
            aPassed_LogInfo.Insert(3, "")
            aPassed_LogInfo.Insert(4, "Converted Files:")
        End If
        Write_Out_Array(sOutputLog, aPassed_LogInfo, False)

        If (nFailed > 0) Then
            aFailed_LogInfo.Insert(0, "")
            aFailed_LogInfo.Insert(1, "Failed Files:")
            Write_Out_Array(sOutputLog, aFailed_LogInfo, True)
        End If

        ' Enable the View Log button so they can view the results
        Check_For_OutputLog(sOutputLog)


        resultLabel.Text = ""
        Me.Update()


        sMsg = "Passed: " + nPassed.ToString + Environment.NewLine + "Failed: " + nFailed.ToString
        MsgBox(sMsg)

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Check to see if there is a output log and enable the button
    Private Sub Check_For_OutputLog(ByVal sOutputLog As String)
        If File.Exists(sOutputLog) Then
            ViewLogBtn.Enabled = True
        Else
            ViewLogBtn.Enabled = False
        End If
    End Sub

    ' Write out an array to a file
    Public Sub Write_Out_Array(ByVal sFilename As String, ByVal aOutputArray As ArrayList, ByVal bAppend As Boolean)
        Dim i As Integer = 0

        ' Check to make sure the directory where the file is to be writen exists.  If it doesn't then create it.
        Try
            Dim fl As FileInfo = New FileInfo(sFilename)

            If Not (fl.Directory.Exists) Then
                fl.Directory.Create()
            End If

        Catch ex As Exception

        End Try

        Try
            Dim sw As StreamWriter = New StreamWriter(sFilename, bAppend, System.Text.Encoding.Default) ', False, System.Text.Encoding.UTF8)
            For i = 0 To aOutputArray.Count - 1
                sw.WriteLine(aOutputArray(i))
            Next
            sw.Close()
        Catch ex As Exception

        End Try

    End Sub

    ' Write out a string to a file
    Public Sub Write_To_Output_No_Quote(ByVal sFilename As String, ByVal sMsg As String, ByVal bAppend As Boolean)
        'bFormatOutput = True

        ' Check to make sure the directory where the file is to be writen exists.  If it doesn't then create it.
        Try
            Dim fl As FileInfo = New FileInfo(sFilename)

            If Not (fl.Directory.Exists) Then
                fl.Directory.Create()
            End If

        Catch ex As Exception

        End Try



        Try
            Dim sw As StreamWriter = New StreamWriter(sFilename, bAppend, System.Text.Encoding.Default) ', False, System.Text.Encoding.UTF8)
            sw.WriteLine(sMsg)
            sw.Close()
        Catch ex As Exception

        End Try

    End Sub

    Private Sub Write_Out_File_Data(ByVal sOriginalFile_Name As String, ByVal aData As ArrayList, ByRef bFailure As Boolean)
        Dim sMsg As String = Nothing

        Dim aPipeThreads As New ArrayList
        aPipeThreads.Clear()
        Dim i As Integer = 0
        Dim j As Integer = 0

        Dim stemp As String = Nothing

        Dim bBeginInch As Boolean = False
        Dim bBeginMetric As Boolean = False

        Dim bStraightPipe As Boolean = True
        Dim bTaperPipe As Boolean = False

        Dim sLine() As String = Nothing

        Dim aOutside_PipelDia As New ArrayList
        Dim aInternal_MinorDia As New ArrayList
        Dim aExternal_MinorDia As New ArrayList
        Dim aThread_Type As New ArrayList
        Dim aThread_Family As New ArrayList
        Dim aThreadType As New ArrayList
        Dim aSubType As New ArrayList
        Dim aFit As New ArrayList

        aOutside_PipelDia.Clear()
        aInternal_MinorDia.Clear()
        aExternal_MinorDia.Clear()
        aThread_Type.Clear()
        aThread_Family.Clear()
        aThreadType.Clear()
        aSubType.Clear()
        aFit.Clear()

        Dim aEmpty As New ArrayList
        aEmpty.Clear()

        Dim aErrorLines As New ArrayList
        aErrorLines.Clear()


        'length for external threads 
        Dim aLength_Ext_Threads As New ArrayList
        aLength_Ext_Threads.Clear()

        'inside pipe diameter 
        Dim aInside_PipeDia As New ArrayList
        aInside_PipeDia.Clear()

        'length for internal threads 
        Dim aLength_Int_Threads As New ArrayList
        aLength_Int_Threads.Clear()

        'thread height 
        Dim aThread_Height As New ArrayList
        aThread_Height.Clear()

        'thread type external
        Dim aThread_Type_External As New ArrayList
        aThread_Type_External.Clear()

        'thread type internal
        Dim aThread_Type_Internal As New ArrayList
        aThread_Type_Internal.Clear()

        ' Used to hold the sizes for the Simple holes.
        aMetricSimpleDia.Clear()
        aStandardSimpleDia.Clear()


        Dim sNew_FileName As String = Nothing
        Dim sBaseFileName As String = Nothing

        Dim sExtension As String = Nothing

        Try

            ' Need to get the filename without the extension to create the new file(s) from.
            Dim fl As New FileInfo(sOriginalFile_Name)
            sNew_FileName = fl.Name
            sExtension = fl.Extension
            sNew_FileName = sNew_FileName.Replace(sExtension, "")


            ' Will add the _Metric.xlsx or _Inch.xlsx
            sNew_FileName = Output_Directory_TB.Text + "\" + sNew_FileName
            sBaseFileName = sNew_FileName

        Catch ex As Exception
            bFailure = True
            Exit Sub
        End Try




        Dim bClear As Boolean = False
        Dim nSheetIndex As Integer = SheetName.Threaded


        For i = 0 To aData.Count - 1

            stemp = aData(i).ToString.Trim

            ' Skip comment lines, lines with \\ at the beginning.
            If (stemp.StartsWith("\\")) Then
                Continue For
            End If


            ' BeginStraightPipe
            If (stemp.ToLower = "beginstraightpipe") Then
                bStraightPipe = True
                Continue For
            End If

            If (stemp.ToLower = "endstraightpipe") Then
                bStraightPipe = False
                nSheetIndex = SheetName.Threaded

                aThreadType.Clear()
                aSubType.Clear()

                ' Thread Type = 2
                ' Sub Type = Straight Pipe Thread 

                For j = 0 To aThread_Family.Count - 1
                    aThreadType.Add("2")
                Next

                For j = 0 To aThread_Family.Count - 1
                    aSubType.Add("Straight Pipe Thread")
                Next

                Write_Excel_XLSX(4, sUnit, aThreadType, aSubType, aThread_Family, aThread_Type, aOutside_PipelDia, aInternal_MinorDia, aExternal_MinorDia, aEmpty, aLength_Ext_Threads, aInside_PipeDia, aLength_Int_Threads, aThread_Height, aThread_Type_External, aThread_Type_Internal, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)


                If Not (sMsg Is Nothing) Then
                    Add_Msg_To_Array(aFailed_LogInfo, sOriginalFile_Name + " - failed to be converted.  " + sMsg)
                    sMsg = Nothing
                Else
                    Add_Msg_To_Array(aPassed_LogInfo, sOriginalFile_Name + " has been converted to " + sNew_FileName)
                End If

                ' Reset to the base file name just incase the original file has both Metric and Standard values which will cause it to create two files.
                sNew_FileName = sBaseFileName


                ' Clear out the arrays
                aOutside_PipelDia.Clear()
                aInternal_MinorDia.Clear()
                aExternal_MinorDia.Clear()
                aThread_Type.Clear()
                aThread_Family.Clear()

                aLength_Ext_Threads.Clear()
                aInside_PipeDia.Clear()
                aLength_Int_Threads.Clear()
                aThread_Height.Clear()
                aThread_Type_External.Clear()
                aThread_Type_Internal.Clear()
                Continue For
            End If



            ' BeginTaperPipe
            If (stemp.ToLower = "begintaperpipe") Then
                bTaperPipe = True
                Continue For
            End If

            If (stemp.ToLower = "endtaperpipe") Then
                bTaperPipe = False
                nSheetIndex = SheetName.Threaded

                aThreadType.Clear()
                aSubType.Clear()

                ' Thread Type = 3
                ' Sub Type = Tapered Pipe Thread

                For j = 0 To aThread_Family.Count - 1
                    aThreadType.Add("3")
                Next

                For j = 0 To aThread_Family.Count - 1
                    aSubType.Add("Tapered Pipe Thread")
                Next


                Write_Excel_XLSX(4, sUnit, aThreadType, aSubType, aThread_Family, aEmpty, aEmpty, aEmpty, aEmpty, aOutside_PipelDia, aLength_Ext_Threads, aInside_PipeDia, aLength_Int_Threads, aThread_Height, aThread_Type_External, aThread_Type_Internal, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)

                If Not (sMsg Is Nothing) Then
                    Add_Msg_To_Array(aFailed_LogInfo, sOriginalFile_Name + " - failed to be converted.  " + sMsg)
                    sMsg = Nothing
                Else
                    Add_Msg_To_Array(aPassed_LogInfo, sOriginalFile_Name + " has been converted to " + sNew_FileName)
                End If

                ' Reset to the base file name just incase the original file has both Metric and Standard values which will cause it to create two files.
                sNew_FileName = sBaseFileName

                ' Clear out the arrays
                aOutside_PipelDia.Clear()
                aInternal_MinorDia.Clear()
                aExternal_MinorDia.Clear()
                aThread_Type.Clear()
                aThread_Family.Clear()

                aLength_Ext_Threads.Clear()
                aInside_PipeDia.Clear()
                aLength_Int_Threads.Clear()
                aThread_Height.Clear()
                aThread_Type_External.Clear()
                aThread_Type_Internal.Clear()
                Continue For
            End If


            If (stemp.ToLower = "begininch") Then
                bBeginInch = True
                sUnit = "inch"

                aOutside_PipelDia.Clear()
                aInternal_MinorDia.Clear()
                aExternal_MinorDia.Clear()
                aThread_Type.Clear()
                aThread_Family.Clear()

                aLength_Ext_Threads.Clear()
                aInside_PipeDia.Clear()
                aLength_Int_Threads.Clear()
                aThread_Height.Clear()
                aThread_Type_External.Clear()
                aThread_Type_Internal.Clear()
                Continue For
            End If

            If (stemp.ToLower = "endinch") Then

                ' Check to see if there is anything in the arrays, if so then write it out.  If not then assume there was a EndTaperPipe or EndStraightPipe before it and the arrays were written out then.
                If (aOutside_PipelDia.Count > 0) And (aOutside_PipelDia.Count = aInternal_MinorDia.Count) And (aOutside_PipelDia.Count = aExternal_MinorDia.Count) And (aOutside_PipelDia.Count = aThread_Type.Count) And (aOutside_PipelDia.Count = aThread_Family.Count) Then
                    nSheetIndex = SheetName.Threaded

                    aThreadType.Clear()
                    aSubType.Clear()

                    ' Thread Type = 1
                    ' Sub Type = "Standard Thread"

                    For j = 0 To aThread_Family.Count - 1
                        aThreadType.Add("1")
                    Next

                    For j = 0 To aThread_Family.Count - 1
                        aSubType.Add("Standard Thread")
                    Next

                    Write_Excel_XLSX(4, sUnit, aThreadType, aSubType, aThread_Family, aThread_Type, aOutside_PipelDia, aInternal_MinorDia, aExternal_MinorDia, aEmpty, aLength_Ext_Threads, aInside_PipeDia, aLength_Int_Threads, aThread_Height, aThread_Type_External, aThread_Type_Internal, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)


                    If Not (sMsg Is Nothing) Then
                        Add_Msg_To_Array(aFailed_LogInfo, sOriginalFile_Name + " - failed to be converted.  " + sMsg)
                        sMsg = Nothing
                    Else
                        Add_Msg_To_Array(aPassed_LogInfo, sOriginalFile_Name + " has been converted to " + sNew_FileName)
                    End If

                    ' Reset to the base file name just incase the original file has both Metric and Standard values which will cause it to create two files.
                    sNew_FileName = sBaseFileName
                End If


                ' Not sure this is needed with the code that checks format.
                If (bBeginInch) Then
                    bBeginInch = False
                End If
            End If


            If (stemp.ToLower = "endmetric") Then
                ' Check to see if there is anything in the arrays, if so then write it out.  If not then assume there was a EndTaperPipe or EndStraightPipe before it and the arrays were written out then.
                If (aOutside_PipelDia.Count > 0) And (aOutside_PipelDia.Count = aInternal_MinorDia.Count) And (aOutside_PipelDia.Count = aExternal_MinorDia.Count) And (aOutside_PipelDia.Count = aThread_Type.Count) And (aOutside_PipelDia.Count = aThread_Family.Count) Then
                    nSheetIndex = SheetName.Threaded

                    aThreadType.Clear()
                    aSubType.Clear()

                    ' Thread Type = 1
                    ' Sub Type = "Standard Thread"

                    For j = 0 To aThread_Family.Count - 1
                        aThreadType.Add("1")
                    Next

                    For j = 0 To aThread_Family.Count - 1
                        aSubType.Add("Standard Thread")
                    Next

                    Write_Excel_XLSX(4, sUnit, aThreadType, aSubType, aThread_Family, aThread_Type, aOutside_PipelDia, aInternal_MinorDia, aExternal_MinorDia, aEmpty, aLength_Ext_Threads, aInside_PipeDia, aLength_Int_Threads, aThread_Height, aThread_Type_External, aThread_Type_Internal, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)


                    If Not (sMsg Is Nothing) Then
                        Add_Msg_To_Array(aFailed_LogInfo, sOriginalFile_Name + " - failed to be converted.  " + sMsg)
                        sMsg = Nothing
                    Else
                        Add_Msg_To_Array(aPassed_LogInfo, sOriginalFile_Name + " has been converted to " + sNew_FileName)
                    End If

                    ' Reset to the base file name just incase the original file has both Metric and Standard values which will cause it to create two files.
                    sNew_FileName = sBaseFileName
                End If

                If (bBeginMetric) Then
                    bBeginMetric = False
                End If
            End If

            If (stemp.ToLower = "beginmetric") Then
                bBeginMetric = True
                sUnit = "mm"

                aOutside_PipelDia.Clear()
                aInternal_MinorDia.Clear()
                aExternal_MinorDia.Clear()
                aThread_Type.Clear()
                aThread_Family.Clear()

                aLength_Ext_Threads.Clear()
                aInside_PipeDia.Clear()
                aLength_Int_Threads.Clear()
                aThread_Height.Clear()
                aThread_Type_External.Clear()
                aThread_Type_Internal.Clear()

                Continue For
            End If


            sLine = Split(stemp, ";")

            Try

                If (bStraightPipe) Then
                    If (sLine.Count <> 5) Then
                        ' Keep track of the lines that don't seem to be formated correctly.
                        aErrorLines.Add(sLine)
                    Else
                        aOutside_PipelDia.Add(sLine(0).Trim)
                        aInternal_MinorDia.Add(sLine(1).Trim)
                        aExternal_MinorDia.Add(sLine(2).Trim)
                        aThread_Type.Add(sLine(3).Trim)
                        aThread_Family.Add(sLine(4).Trim)
                    End If
                ElseIf (bTaperPipe) Then
                    If (sLine.Count <> 8) Then
                        ' Keep track of the lines that don't seem to be formated correctly.
                        aErrorLines.Add(sLine)
                    Else
                        aOutside_PipelDia.Add(sLine(0).Trim)
                        aLength_Ext_Threads.Add(sLine(1).Trim)
                        aInside_PipeDia.Add(sLine(2).Trim)
                        aLength_Int_Threads.Add(sLine(3).Trim)
                        aThread_Height.Add(sLine(4).Trim)
                        aThread_Type_External.Add(sLine(5).Trim)
                        aThread_Type_Internal.Add(sLine(6).Trim)
                        aThread_Family.Add(sLine(7).Trim)
                    End If
                End If


            Catch ex As Exception

            End Try

        Next



        ' Write out the Simple Hole Dia.
        If (aMetricSimpleDia.Count > 0) Then
            sUnit = "mm"
            nSheetIndex = SheetName.Simple
            sNew_FileName = sBaseFileName

            aFit.Clear()
            aSubType.Clear()

            ' Sub Type = "Nominal Thread dia."
            For j = 0 To aMetricSimpleDia.Count - 1
                aSubType.Add("Nominal Thread dia.")
            Next

            For j = 0 To aMetricSimpleDia.Count - 1
                aFit.Add("Exact")
            Next

            Write_Excel_XLSX(3, sUnit, aSubType, aMetricSimpleDia, aFit, aMetricSimpleDia, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)
        End If

        If (aStandardSimpleDia.Count > 0) Then
            sUnit = "inch"
            nSheetIndex = SheetName.Simple
            sNew_FileName = sBaseFileName

            aFit.Clear()
            aSubType.Clear()

            ' Sub Type = "Nominal Thread dia."
            For j = 0 To aStandardSimpleDia.Count - 1
                aSubType.Add("Nominal Thread dia.")
            Next

            For j = 0 To aStandardSimpleDia.Count - 1
                aFit.Add("Exact")
            Next

            Write_Excel_XLSX(3, sUnit, aSubType, aStandardSimpleDia, aFit, aStandardSimpleDia, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, aEmpty, sNew_FileName, nSheetIndex, bInchFile, bMetricFile, sMsg)
        End If




    End Sub

    ' Add the message to the log, check if its already in there first so we don't have duplicates.  
    ' Duplicates can happen in the case of converting Pipe were there are Straight and Tapered information going to the same file.
    ' Doing it this way keeps the information in the proper order.
    Private Sub Add_Msg_To_Array(ByRef aLogInfo As ArrayList, ByVal sMessage As String)
        Dim i As Integer = 0
        Dim bFound As Boolean = False

        For i = 0 To aLogInfo.Count - 1
            If (aLogInfo(i) = sMessage) Then
                bFound = True
                Exit For
            End If
        Next

        If (bFound = False) Then
            aLogInfo.Add(sMessage)
        End If
    End Sub

    ' Given a column index get its name.
    Public Shared Function ColumnNameFromIndex(columnIndex As UInt32) As String
        Dim remainder As UInt32
        Dim columnName As String = ""

        While (columnIndex > 0)
            remainder = (columnIndex - 1) Mod 26
            columnName = System.Convert.ToChar(65 + remainder).ToString() + columnName
            columnIndex = ((columnIndex - remainder) / 26)
        End While

        Return columnName
    End Function

    ' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    ' If the cell already exists, return it. 
    Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart, ByVal worksheet As Worksheet, ByVal sheetData As SheetData) As Cell

        Dim cellReference As String = (columnName + rowIndex.ToString())


        ' If the worksheet does not contain a row with the specified row index, insert one.
        Dim row As Row = Nothing
        Dim newCell As Cell = Nothing

        If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
            row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
        Else
            row = New Row()
            row.RowIndex = rowIndex
            sheetData.Append(row)
        End If

        ' If there is not a cell with the specified column name, insert one.  
        If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
            Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
        Else
            ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Dim refCell As Cell = Nothing
            For Each cell As Cell In row.Elements(Of Cell)()
                If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                    refCell = cell
                    Exit For
                End If
            Next


            newCell = New Cell()
            newCell.CellReference = cellReference

            row.InsertBefore(newCell, refCell)

            Return newCell
        End If
    End Function


    ' Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
    ' and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
    Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
        ' If the part does not contain a SharedStringTable, create one.
        If (shareStringPart.SharedStringTable Is Nothing) Then
            shareStringPart.SharedStringTable = New SharedStringTable
        End If

        Dim i As Integer = 0

        ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If (item.InnerText = text) Then
                Return i
            End If
            i = (i + 1)
        Next

        ' The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
        shareStringPart.SharedStringTable.Save()

        Return i
    End Function
    Private Sub SetCellValue(ByVal loc As String, ByVal Val As String)
        Dim cell As Cell = New Cell


        cell.CellReference = loc
        cell.DataType = CellValues.String
        cell.CellValue = New CellValue(Val)

    End Sub
    Public Function InsertValue_Column(ByVal docName As String, ByVal nSheet As Integer, ByVal nColumn As Integer, ByVal nValueType As Integer, ByVal oValue As Object, ByVal nCount As Integer, ByRef nInitalRow As Integer)

        Dim i As Integer = 0
        Dim nRow As Integer = 0
        Dim nSheetCount As Integer = 1
        Dim spreadSheet As SpreadsheetDocument = Nothing

        ' Open the document for editing.
        Try
            spreadSheet = SpreadsheetDocument.Open(docName, True)
        Catch ex As Exception
            i = 0
        End Try


        Using (spreadSheet)


            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart = Nothing
            Dim index As Integer = 0

            ' Get the WorkBook
            Dim workbookpart As WorkbookPart = Nothing
            workbookpart = spreadSheet.WorkbookPart()


            Dim WorkbookSheets As Sheets
            Dim strSheetName As String
            Dim wsp As WorksheetPart = Nothing


            ' The sheet ids don't always in order so just counting as we loop through them.
            WorkbookSheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
            For Each childSheet As Sheet In WorkbookSheets
                strSheetName = childSheet.Name

                If (nSheetCount = nSheet) Then
                    wsp = spreadSheet.WorkbookPart.GetPartById(childSheet.Id)
                    Exit For
                Else
                    nSheetCount = nSheetCount + 1
                End If
            Next

            Dim worksheet As Worksheet = wsp.Worksheet

            Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

            ' Trying this so I don't use the "A" to reference a column, want to use the index
            Dim sColumnName As String = ColumnNameFromIndex(nColumn)

            If (nValueType = 0) Then
                If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
                Else
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                End If
            End If

            ' Loop through the array and add the values.
            For i = 0 To nCount - 1

                ' Check the SharedStringTable to see if the string is already in it.
                If (nValueType = 0) Then
                    ' Insert the text into the SharedStringTablePart.
                    index = InsertSharedStringItem(oValue.ToString, shareStringPart)
                End If

                ' Get the Last Row Index
                Try
                    If (nInitalRow = -1) Then
                        Dim lastRow As Row = sheetData.Elements(Of Row)().LastOrDefault()
                        If (lastRow Is Nothing) Then
                            nInitalRow = i + 2
                        Else
                            Dim nLastRow As DocumentFormat.OpenXml.UInt32Value = lastRow.RowIndex
                            If (nInitalRow = -1) Then
                                nInitalRow = nLastRow.Value + 1
                            End If
                        End If

                        nRow = nInitalRow
                    Else
                        If (nRow = 0) Then
                            nRow = nInitalRow
                        Else
                            nRow = nRow + 1
                        End If

                    End If

                Catch ex As Exception

                End Try

                Dim cell As Cell = InsertCellInWorksheet(sColumnName, nRow, wsp, worksheet, sheetData)


                ' Set the value of cell A1.
                If (nValueType = 0) Then
                    cell.StyleIndex = 0
                    cell.CellValue = New CellValue(index.ToString)
                    cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
                Else
                    If (nValueType = 1) Then
                        cell.DataType = New EnumValue(Of CellValues)(CellValues.Number)
                        cell.StyleIndex = 1
                        cell.CellValue = New CellValue(DoubleValue.FromDouble(CDbl(oValue)))
                    End If
                End If

            Next


            ' Save the new worksheet.
            spreadSheet.WorkbookPart.Workbook.Save()
            spreadSheet.Close()

        End Using

        Return 0

    End Function

    Public Function InsertArray_Column(ByVal docName As String, ByVal nSheet As Integer, ByVal nColumn As Integer, ByVal nValueType As Integer, ByVal aValues As ArrayList, ByRef nInitalRow As Integer)

        Dim i As Integer = 0
        Dim nRow As Integer = 0
        Dim nSheetCount As Integer = 1
        Dim spreadSheet As SpreadsheetDocument = Nothing

        ' Open the document for editing.
        Try
            spreadSheet = SpreadsheetDocument.Open(docName, True)
        Catch ex As Exception

        End Try


        Using (spreadSheet)


            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart = Nothing
            Dim index As Integer = 0

            ' Get the WorkBook
            Dim workbookpart As WorkbookPart = Nothing
            workbookpart = spreadSheet.WorkbookPart()

            Dim WorkbookSheets As Sheets
            Dim strSheetName As String
            Dim wsp As WorksheetPart = Nothing

            WorkbookSheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
            For Each childSheet As Sheet In WorkbookSheets
                strSheetName = childSheet.Name

                If (nSheetCount = nSheet) Then
                    wsp = spreadSheet.WorkbookPart.GetPartById(childSheet.Id)
                    Exit For
                Else
                    nSheetCount = nSheetCount + 1
                End If
            Next

            Dim worksheet As Worksheet = wsp.Worksheet

            Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

            ' Trying this so I don't use the "A" to reference a column, want to use the index
            Dim sColumnName As String = ColumnNameFromIndex(nColumn)


            If (nValueType = 0) Then
                If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
                Else
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                End If
            End If


            ' Loop through the array and add the values.
            For i = 0 To aValues.Count - 1

                ' Check the SharedStringTable to see if the string is already in it.
                If (nValueType = 0) Then
                    ' Insert the text into the SharedStringTablePart.
                    index = InsertSharedStringItem(aValues(i), shareStringPart)
                End If

                ' Get the Last Row Index
                Try
                    If (nInitalRow = -1) Then
                        Dim lastRow As Row = sheetData.Elements(Of Row)().LastOrDefault()
                        If (lastRow Is Nothing) Then
                            nInitalRow = i + 2
                        Else
                            Dim nLastRow As DocumentFormat.OpenXml.UInt32Value = lastRow.RowIndex
                            If (nInitalRow = -1) Then
                                nInitalRow = nLastRow.Value + 1
                            End If
                        End If

                        nRow = nInitalRow
                    Else
                        If (nRow = 0) Then
                            nRow = nInitalRow
                        Else
                            nRow = nRow + 1
                        End If

                    End If

                Catch ex As Exception

                End Try

                Dim cell As Cell = InsertCellInWorksheet(sColumnName, nRow, wsp, worksheet, sheetData)

                ' Set the value of cell A1.
                If (nValueType = 0) Then

                    '  Original Code
                    cell.StyleIndex = 0
                    cell.CellValue = New CellValue(index.ToString)
                    cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

                Else
                    If (nValueType = 1) Then                        
                        cell.CellValue = New CellValue(DoubleValue.FromDouble(CDbl(aValues(i))))
                        cell.DataType = New EnumValue(Of CellValues)(CellValues.Number)
                    End If

                End If

            Next

            ' Save the new worksheet.
            spreadSheet.WorkbookPart.Workbook.Save()
            spreadSheet.Close()

        End Using

        Return 0

    End Function
    Public Shared Sub InsertRow(ByVal worksheetPart As WorksheetPart, ByVal LastRowofSection As Integer)
        Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)()
        Dim lastRow As Row = sheetData.Elements(Of Row)().LastOrDefault()
        sheetData.InsertAfter(New Row() With {.RowIndex = (LastRowofSection)}, lastRow)
    End Sub

    Public Function InsertValue(ByVal docName As String, ByVal nSheet As Integer, ByVal nColumn As Integer, ByVal nRow As Integer, ByVal nValueType As Integer, ByVal sValue As String, ByVal nValue As Integer, ByVal dValue As Double)
        Dim i As Integer = 0
        Dim nSheetCount As Integer = 1
        Dim spreadSheet As SpreadsheetDocument = Nothing

        ' Open the document for editing.
        Try
            spreadSheet = SpreadsheetDocument.Open(docName, True)
        Catch ex As Exception
            i = 0
        End Try


        Using (spreadSheet)

            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart
            Dim index As Integer = 0

            If (nValueType = 0) Then
                If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
                Else
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
                End If

                ' Insert the text into the SharedStringTablePart.
                index = InsertSharedStringItem(sValue, shareStringPart)
            End If



            ' Get the WorkBook
            Dim workbookpart As WorkbookPart = Nothing
            workbookpart = spreadSheet.WorkbookPart()


            Dim WorkbookSheets As Sheets
            Dim strSheetName As String
            Dim wsp As WorksheetPart = Nothing


            ' The sheet ids don't always in order so just counting as we loop through them.
            WorkbookSheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
            For Each childSheet As Sheet In WorkbookSheets
                strSheetName = childSheet.Name

                If (nSheetCount = nSheet) Then
                    wsp = spreadSheet.WorkbookPart.GetPartById(childSheet.Id)
                    Exit For
                Else
                    nSheetCount = nSheetCount + 1
                End If


            Next
            Dim worksheet As Worksheet = wsp.Worksheet


            Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()

            ' Get the Last Row Index
            Try
                If (nRow = -1) Then
                    Dim lastRow As Row = sheetData.Elements(Of Row)().LastOrDefault()
                    If Not (lastRow Is Nothing) Then
                        Dim nLastRow As DocumentFormat.OpenXml.UInt32Value = lastRow.RowIndex
                        nRow = nLastRow.Value
                    End If
                End If


            Catch ex As Exception

            End Try


            ' Trying this so I don't use the "A" to reference a column, want to use the index
            Dim sColumnName As String = ColumnNameFromIndex(nColumn)

            Dim cell As Cell = InsertCellInWorksheet(sColumnName, nRow, wsp, worksheet, sheetData)

            ' Set the value of cell A1.
            If (nValueType = 0) Then
                cell.CellValue = New CellValue(index.ToString)
                cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
            Else
                If (nValueType = 1) Then
                    cell.CellValue = New CellValue(nValue)
                Else
                    cell.CellValue = New CellValue(dValue)
                End If
                cell.DataType = New EnumValue(Of CellValues)(CellValues.Number)
            End If


            ' Save the new worksheet.
            spreadSheet.WorkbookPart.Workbook.Save()
            spreadSheet.Close()

        End Using

        Return 0

    End Function

    Public Sub Add_Sheet_To_Workbook(ByVal sFileName As String, ByVal sSheetName As String)

        Try
            ' Open the document for editing.
            Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(sFileName, True)

            Using (spreadSheet)
                ' Add a blank WorksheetPart.
                Dim newWorksheetPart As WorksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
                newWorksheetPart.Worksheet = New Worksheet(New SheetData())

                Dim sheets As Sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
                Dim relationshipId As String = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart)

                ' Get a unique ID for the new worksheet.
                Dim sheetId As UInteger = 1
                If (sheets.Elements(Of Sheet).Count > 0) Then
                    sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1
                End If

                ' Give the new worksheet a name.
                Dim sheetName As String = (sSheetName)

                ' Append the new worksheet and associate it with the workbook.
                Dim sheet As Sheet = New Sheet
                sheet.Id = relationshipId
                sheet.SheetId = sheetId
                sheet.Name = sheetName
                sheets.Append(sheet)
            End Using


        Catch ex As Exception

        End Try


    End Sub


    Public Shared Function AddBasicStyles(spreadsheet As DocumentFormat.OpenXml.Packaging.SpreadsheetDocument) As Boolean
        Try
            Dim stylesheet As DocumentFormat.OpenXml.Spreadsheet.Stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet

            ' Numbering formats (x:numFmts)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.NumberingFormats)(New DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0)

            ' Currency
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.NumberingFormats)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.NumberingFormat)(
               New DocumentFormat.OpenXml.Spreadsheet.NumberingFormat() With {
                  .NumberFormatId = 164,
                  .FormatCode = "#,##0.00" _
                  & " """ & System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol & "  """
               }, 0)

            ' Fonts (x:fonts)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Fonts)(New DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1)
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Fonts)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Font)(
               New DocumentFormat.OpenXml.Spreadsheet.Font() With {
                  .FontSize = New DocumentFormat.OpenXml.Spreadsheet.FontSize() With {
                     .Val = 11
                  },
                  .FontName = New DocumentFormat.OpenXml.Spreadsheet.FontName() With {
                     .Val = "Calibri"
                  }
               }, 0)

            ' Fills (x:fills)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Fills)(New DocumentFormat.OpenXml.Spreadsheet.Fills(), 2)
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Fills)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Fill)(
               New DocumentFormat.OpenXml.Spreadsheet.Fill() With {
                  .PatternFill = New DocumentFormat.OpenXml.Spreadsheet.PatternFill() With {
                     .PatternType = New DocumentFormat.OpenXml.EnumValue(Of DocumentFormat.OpenXml.Spreadsheet.PatternValues)() With {
                         .Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None
                     }
                  }
               }, 0)

            ' Borders (x:borders)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Borders)(New DocumentFormat.OpenXml.Spreadsheet.Borders(), 3)
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Borders)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.Border)(
               New DocumentFormat.OpenXml.Spreadsheet.Border() With {
                  .LeftBorder = New DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
                  .RightBorder = New DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
                  .TopBorder = New DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
                  .BottomBorder = New DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
                  .DiagonalBorder = New DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder()
               }, 0)

            ' Cell style formats (x:CellStyleXfs)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats)(New DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4)
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.CellFormat)(
               New DocumentFormat.OpenXml.Spreadsheet.CellFormat() With {
                  .NumberFormatId = 0,
                  .FontId = 0,
                  .FillId = 0,
                  .BorderId = 0
               }, 0)

            ' Cell formats (x:CellXfs)
            stylesheet.InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.CellFormats)(New DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5)
            ' General text
            stylesheet.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.CellFormats)().InsertAt(Of DocumentFormat.OpenXml.Spreadsheet.CellFormat)(
               New DocumentFormat.OpenXml.Spreadsheet.CellFormat() With {
                  .FormatId = 0,
                  .NumberFormatId = 0
               }, 0)


            stylesheet.Save()
        Catch ex As Exception

        End Try
        

        Return True
    End Function


    Public Sub CreateSpreadsheetWorkbook(ByVal sFileName As String, ByVal sSheetName As String)

        Dim spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Create(sFileName, SpreadsheetDocumentType.Workbook)
        Dim workbookStylesPart As DocumentFormat.OpenXml.Packaging.WorkbookStylesPart


        ' Add a WorkbookPart to the document.
        Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
        workbookpart.Workbook = New Workbook
        workbookpart.Workbook.Save()

        ' Add a WorksheetPart to the WorkbookPart.
        Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
        worksheetPart.Worksheet = New Worksheet(New SheetData())
        worksheetPart.Worksheet.Save()


        ' Add Sheets to the Workbook.
        Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

        ' Append a new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
        sheet.SheetId = 1
        sheet.Name = sSheetName

        sheets.Append(sheet)


        workbookpart.Workbook.Save()

        ' Stylesheet
        WorkbookStylesPart = spreadsheetDocument.WorkbookPart.AddNewPart(Of DocumentFormat.OpenXml.Packaging.WorkbookStylesPart)()
        WorkbookStylesPart.Stylesheet = New DocumentFormat.OpenXml.Spreadsheet.Stylesheet()
        WorkbookStylesPart.Stylesheet.Save()


        AddBasicStyles(spreadsheetDocument)

        ' Close the document.
        spreadsheetDocument.Close()
    End Sub

    Public Sub GetSheetInfo(ByVal fileName As String)

        Dim aTable As New ArrayList


        ' Open file as read-only.
        Dim nSheetCount As Integer = 0

        Using mySpreadsheet As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, True)
            Dim sheets As DocumentFormat.OpenXml.Spreadsheet.Sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets

            ' For each sheet, display the sheet information.
            For Each sheet As DocumentFormat.OpenXml.OpenXmlElement In sheets
                nSheetCount = nSheetCount + 1

                For Each attr As DocumentFormat.OpenXml.OpenXmlAttribute In sheet.GetAttributes()
                    aTable.Add("LocalName: " + attr.LocalName + ",Value: " + attr.Value)
                Next
            Next

        End Using

        'Write_Out_Array(sEXEPath + "\ex.csv", aTable, False)

    End Sub
    Private Sub CreateFile_From_Template(ByRef sFileName As String, ByRef bFailed As Boolean, ByRef sMsg As String)
        Dim fl As FileInfo = Nothing

        Try
            Directory.CreateDirectory(sEXEPath + "\Temp")
            fl = New FileInfo(sFileName)

            File.Copy(sLocalizedTemplate, sEXEPath + "\Temp\Holes.xlsx")
            My.Computer.FileSystem.RenameFile(sEXEPath + "\Temp\Holes.xlsx", fl.Name)
            File.Move(sEXEPath + "\Temp\" + fl.Name, sFileName)
            'File.Delete(sEXEPath + "\Temp\Holes.xlsx")
            bFileCreated = True

        Catch ex As Exception
            bFailed = True
            sMsg = ex.Message
        End Try
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Create the xlsx file from scratch.
    Private Sub CreateFile_From_Scratch(ByVal sFileName As String, ByRef bFailed As Boolean, ByRef sMsg As String)

        Dim nSheet As Integer = 1   ' Units
        Dim nColumn As Integer = 1
        Dim nRow As Integer = 1
        Dim sValue As String = ""
        Dim nValue As Integer = 0
        Dim dValue As Double = 0.0

        Dim nValueType As Integer = 0
        ' 0 - String
        ' 1 - Integer
        ' 2 - Float

        Dim i As Integer = 0
        Dim sTemp() As String = Nothing

        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' If we are trying to write out a Simple Hole and the Hole template could not be found then create an English version of it and fill it in.

        ' Check to see if the file exists already.
        If Not (File.Exists(sFileName)) Then
            Try
                CreateSpreadsheetWorkbook(sFileName, "Information")
                Add_Sheet_To_Workbook(sFileName, "Units")
                Add_Sheet_To_Workbook(sFileName, "Simple")
                Add_Sheet_To_Workbook(sFileName, "Threaded")
                Add_Sheet_To_Workbook(sFileName, "Counterbore")
                Add_Sheet_To_Workbook(sFileName, "Countersink")

                ' Write out the Headers for the Simple sheet.
                sTemp = Split(sSimple_Header, ",")
                nSheet = 3
                nRow = 1
                For i = 1 To sTemp.Count
                    nColumn = i
                    sValue = sTemp(i - 1)
                    InsertValue(sFileName, nSheet, nColumn, nRow, nValueType, sValue, 0, 0.0)
                Next

                ' Threaded Sheet
                sTemp = Split(sThreaded_Header, ",")
                nSheet = 4
                nRow = 1
                For i = 1 To sTemp.Count
                    nColumn = i
                    sValue = sTemp(i - 1)
                    InsertValue(sFileName, nSheet, nColumn, nRow, nValueType, sValue, 0, 0.0)
                Next

                ' Counterbore  
                sTemp = Split(sCounterbore, ",")
                nSheet = 5
                nRow = 1
                For i = 1 To sTemp.Count
                    nColumn = i
                    sValue = sTemp(i - 1)
                    InsertValue(sFileName, nSheet, nColumn, nRow, nValueType, sValue, 0, 0.0)
                Next

                ' Countersink
                sTemp = Split(sCountersink, ",")
                nSheet = 6
                nRow = 1
                For i = 1 To sTemp.Count
                    nColumn = i
                    sValue = sTemp(i - 1)
                    InsertValue(sFileName, nSheet, nColumn, nRow, nValueType, sValue, 0, 0.0)
                Next

            Catch ex As Exception
                bFailed = True
                sMsg = ex.Message
            End Try

        End If


    End Sub
    Private Sub Write_Excel_XLSX(ByVal nSheet As Integer, ByVal sUnit As String, ByVal aColumn1 As ArrayList, ByVal aColumn2 As ArrayList, ByVal aColumn3 As ArrayList, ByVal aColumn4 As ArrayList, ByVal aColumn5 As ArrayList, ByVal aColumn7 As ArrayList, ByVal aColumn8 As ArrayList, ByVal aColumn9 As ArrayList, ByVal aColumn10 As ArrayList, ByVal aColumn11 As ArrayList, ByVal aColumn12 As ArrayList, ByVal aColumn13 As ArrayList, ByVal aColumn14 As ArrayList, ByVal aColumn15 As ArrayList, ByRef sFileName As String, ByVal nSheetName As Integer, ByRef bInchFile As Boolean, ByRef bMetricFile As Boolean, ByRef sMsg As String)

        Dim bFailed As Boolean = False

        Dim bInchCreated As Boolean = False         ' Flags to keep track if a Inch or Metric file was created
        Dim bMetricCreated As Boolean = False


        Dim nColumn As Integer = 1
        Dim nRow As Integer = 1
        Dim sValue As String = ""
        Dim nValue As Integer = 0
        Dim dValue As Double = 0.0

        Dim nValueType As Integer = 0
        ' 0 - String
        ' 1 - Integer
        ' 2 - Float

        Dim i As Integer = 0
        Dim sTemp() As String = Nothing

        ' Full Path - filename
        If (sUnit.ToLower = "mm") Then
            sFileName = sFileName + "_Metric.xlsx"
            If (bMetricFile = False) Then
                If (File.Exists(sFileName)) Then
                    sMsg = sFileName + ": File already exists."
                    Exit Sub
                Else
                    ' Create the file

                    ' If the Localized hole template is there then copy
                    If (bHoleTemplate_Found) Then
                        CreateFile_From_Template(sFileName, bFailed, sMsg)
                        If (bFailed) Then
                            sMsg = "Problem trying to create database file template.  " + sMsg
                            Exit Sub
                        End If
                    Else
                        CreateFile_From_Scratch(sFileName, bFailed, sMsg)
                        If (bFailed) Then
                            sMsg = "Problem trying to create database file from scratch.  " + sMsg
                            Exit Sub
                        End If
                    End If

                End If

            End If
            bMetricCreated = True

        ElseIf (sUnit.ToLower = "inch") Then
            sFileName = sFileName + "_Inch.xlsx"

            If (bInchFile = False) Then
                If (File.Exists(sFileName)) Then
                    sMsg = sFileName + ": File already exists."
                    Exit Sub
                Else
                    ' Create the file

                    ' If the Localized hole template is there then copy
                    If (bHoleTemplate_Found) Then
                        CreateFile_From_Template(sFileName, bFailed, sMsg)
                        If (bFailed) Then
                            sMsg = "Problem trying to create database file template.  " + sMsg
                            Exit Sub
                        End If
                    Else
                        CreateFile_From_Scratch(sFileName, bFailed, sMsg)
                        If (bFailed) Then
                            sMsg = "Problem trying to create database file from scratch.  " + sMsg
                            Exit Sub
                        End If
                    End If

                End If
            End If

            bInchCreated = True
        End If


        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Insert the unit type on the Unit Sheet.  Either inch or mm.        
        nColumn = 1
        nRow = 1
        Try
            sValue = sUnit.ToLower
            InsertValue(sFileName, 2, nColumn, nRow, nValueType, sValue, 0, 0.0)
        Catch ex As Exception

        End Try



        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Write out the converted information
        Try

            nRow = -1

            ' Column 1
            Try
                If (aColumn1.Count > 0) Then
                    nColumn = 1
                    nValueType = 0              ' String - Both Simple and Threaded column 1 items should be strings
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn1, nRow)
                End If
            Catch ex As Exception

            End Try


            ' Column 2
            Try
                If (aColumn2.Count > 0) Then
                    nColumn = 2
                    nValueType = 0              ' String - Both Simple and Threaded column 2 items should be strings
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn2, nRow)
                End If
            Catch ex As Exception

            End Try


            ' Column 3
            Try
                If (aColumn3.Count > 0) Then
                    nColumn = 3
                    nValueType = 0          ' String - Both Simple and Threaded column 3 items should be strings
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn3, nRow)
                End If
            Catch ex As Exception

            End Try


            ' Column 4
            Try
                If (aColumn4.Count > 0) Then
                    nColumn = 4

                    If (nSheet = 3) Then        ' Simple sheet
                        nValueType = 1                  ' Hole Dim - Number
                    ElseIf (nSheet = 4) Then    ' Threaded sheet
                        nValueType = 0                  ' Size - String
                    End If

                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn4, nRow)
                End If
            Catch ex As Exception

            End Try


            ' Column 5     
            Try
                If (aColumn5.Count > 0) Then

                    ' Threaded Sheet
                    ' Nominal Dia -     (Outside_PipelDia)
                    nColumn = 5
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn5, nRow)


                    ' Use these values to populate the Simple Sheet.
                    For i = 0 To aColumn5.Count - 1

                        ' Make sure there aren't any duplicates.
                        If (sUnit.ToLower = "mm") Then
                            If Not (aMetricSimpleDia.Contains(aColumn5(i))) Then
                                aMetricSimpleDia.Add(aColumn5(i))
                            End If
                        ElseIf (sUnit.ToLower = "inch") Then
                            If Not (aStandardSimpleDia.Contains(aColumn5(i))) Then
                                aStandardSimpleDia.Add(aColumn5(i))
                            End If
                        End If

                    Next
                End If

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try


            ' InternalMinor
            Try
                If (aColumn7.Count > 0) Then
                    nColumn = 7
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn7, nRow)
                End If
            Catch ex As Exception

            End Try


            ' ExternalMinor
            Try
                If (aColumn8.Count > 0) Then
                    nColumn = 8
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn8, nRow)
                End If
            Catch ex As Exception

            End Try

            ' 
            Try
                If (aColumn9.Count > 0) Then
                    nColumn = 9
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn9, nRow)
                End If
            Catch ex As Exception

            End Try



            ' LengthForExternalThreads 
            Try
                If (aColumn10.Count > 0) Then
                    nColumn = 10
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn10, nRow)
                End If
            Catch ex As Exception

            End Try

            'InsidePipeDiameter 
            Try
                If (aColumn11.Count > 0) Then
                    nColumn = 11
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn11, nRow)
                End If
            Catch ex As Exception

            End Try

            'LengthForInternalThreads 
            Try
                If (aColumn12.Count > 0) Then
                    nColumn = 12
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn12, nRow)
                End If
            Catch ex As Exception

            End Try

            'ThreadHeight 
            Try
                If (aColumn13.Count > 0) Then
                    nColumn = 13
                    nValueType = 1
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn13, nRow)
                End If
            Catch ex As Exception

            End Try

            'ThreadTypeExternal 
            Try
                If (aColumn14.Count > 0) Then
                    nColumn = 14
                    nValueType = 0
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn14, nRow)
                End If
            Catch ex As Exception

            End Try

            'ThreadTypeInternal
            Try
                If (aColumn15.Count > 0) Then
                    nColumn = 15
                    nValueType = 0
                    InsertArray_Column(sFileName, nSheet, nColumn, nValueType, aColumn15, nRow)
                End If
            Catch ex As Exception

            End Try

        Catch ex As Exception

        End Try


        CleanupObjects()

        ' Keep track if we created the files or if they were already there.
        If (bInchCreated) Then
            bInchFile = True
        ElseIf (bMetricCreated) Then
            bMetricFile = True
        End If
    End Sub

    Public Sub CleanupObjects()
        Try
            GC.Collect(GC.MaxGeneration())
            GC.WaitForPendingFinalizers()
            GC.Collect(GC.MaxGeneration())
            GC.WaitForPendingFinalizers()
        Catch ex As Exception

        End Try
    End Sub


    Public Sub ReadInFile(ByVal strFilename As String, ByRef aReport As ArrayList, ByVal bParseInput As Boolean, ByRef bFailure As Boolean)

        ' Read in the files.
        If File.Exists(strFilename) Then

            ' The StreamReader must be defined outside of the Try-Catch block
            '   in order to reference it in the Finally block.
            Dim myStreamReader As StreamReader
            Dim myInputString As String
            Dim rowCount As Integer = 0

            myStreamReader = Nothing

            Try
                ' Create a StreamReader using a Shared (static) File class.
                myStreamReader = New StreamReader(strFilename, System.Text.Encoding.Default)

                ' Begin by reading a single line
                Dim MyPos As Integer = 0
                Dim singlequote As String = Nothing
                myInputString = myStreamReader.ReadLine()
                While Not myInputString Is Nothing

                    If (bParseInput) Then
                        myInputString = myInputString.Replace("""", "")         ' Get rid of any quotes.
                        myInputString = myInputString.Trim()
                    End If


                    If (myInputString.Length <> 0) Then
                        aReport.Add(myInputString)
                    End If

                    myInputString = Nothing
                    myInputString = myStreamReader.ReadLine()

                End While

            Catch exc As Exception
                bFailure = True
            Finally
                ' Close the object if it has been created.
                If Not myStreamReader Is Nothing Then
                    myStreamReader.Close()
                End If
            End Try
            ' Close the object if it has been created.
            If Not myStreamReader Is Nothing Then
                myStreamReader.Close()
            End If
        Else
            bFailure = True
        End If

    End Sub

    Private Sub BrowseBtn_Click(sender As Object, e As EventArgs) Handles BrowseBtn.Click
        Dim fl As FileInfo = Nothing
        Dim sFilterString As String = "Text File (*.txt)|*.txt"
        Dim sFilename As String = Nothing
        Dim sFullPath_FileName As String = Nothing
        Dim bAddFile As Boolean = True

        Dim aFileList As New ArrayList
        aFileList.Clear()

        Dim aFailedToAdd As New ArrayList
        aFailedToAdd.Clear()

        Dim sTemp As String = Nothing


        Try
            OpenFileDialog1.FileName = Nothing
            OpenFileDialog1.Multiselect = True
            OpenFileDialog1.Filter = sFilterString
            OpenFileDialog1.InitialDirectory = sSolidEdgePref
        Catch ex As Exception

        End Try

        If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            ListBox1.BeginUpdate()

            ' Get the current list of filenames
            For i = 0 To ListBox1.Items.Count - 1
                fl = New FileInfo(ListBox1.Items(i))
                aFileList.Add(fl.Name)
            Next


            For Each sFullPath_FileName In OpenFileDialog1.FileNames
                bAddFile = True
                fl = New FileInfo(sFullPath_FileName)
                sFilename = fl.Name

                ' Check to make sure it isn't already in the collection of items.
                If ((sFullPath_FileName Is Nothing) Or (ListBox1.Items.Contains(fl.FullName) = True)) Then
                    bAddFile = False
                End If

                ' Check to see if any of the files have the same name, if they do then second file will fail.  We don't overwrite an existing file.
                If (aFileList.BinarySearch(sFilename) >= 0) Then
                    bAddFile = False
                    aFailedToAdd.Add(sFullPath_FileName)
                Else
                    aFileList.Add(sFilename)
                End If

                ' If it met the conditions above (same file not already in the list and there isn't a file with the same name then add it to the list
                If (bAddFile) Then
                    ListBox1.Items.Add(fl.FullName)
                End If
            Next

            ListBox1.EndUpdate()
        End If

        ' If there were files that could not be added then let the user know.
        If (aFailedToAdd.Count > 0) Then
            For i = 0 To aFailedToAdd.Count - 1
                sTemp = aFailedToAdd(i) + Environment.NewLine
            Next

            MsgBox("The following files could not be added because they are already in the list or have the same name as a file already in the list." + Environment.NewLine + sTemp)
        End If



    End Sub

    Private Sub ListBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles ListBox1.KeyUp
        Dim nCount As Integer
        Dim x As Integer

        ' Allow Delete to remove selected items.
        If e.KeyCode = Keys.Delete Then

            nCount = ListBox1.SelectedIndices.Count
            If (nCount > 0) Then
                'Windows.Forms.Cursor.Current = Cursors.WaitCursor

                ListBox1.BeginUpdate()
                For x = nCount - 1 To 0 Step -1
                    ListBox1.Items.RemoveAt(ListBox1.SelectedIndices(x))
                Next
                ListBox1.EndUpdate()
            End If

        End If

    End Sub

    Private Sub Check_Line_Format(ByVal sInputLine As String, ByRef bValid As Boolean, ByRef sMsg As String)
        Dim stemp As String = Nothing                    ' Temp location for the string.
        Dim eInputHeader As Header = Header.NotDefined   ' Initialize it.
        Dim sLine() As String = Nothing

        stemp = sInputLine.ToString.Trim                 ' Just in case they put a space in front.

        ' Skip comment lines, lines with \\
        If (stemp.StartsWith("\\")) Then
            Exit Sub
        End If

        ' Skip the empty lines
        If (stemp.Length = 0) Then
            Exit Sub
        End If

        Try
            sLine = Split(stemp, ";")

        Catch ex As Exception
            ' Somehow got an exception doing a split.
            sMsg = "Exception: trying to split - " + stemp
            bValid = False
            Exit Sub
        End Try



        Select Case (stemp.ToLower)
            Case ("begininch")
                eInputHeader = Header.BeginInch
            Case ("endinch")
                eInputHeader = Header.EndInch
            Case ("beginmetric")
                eInputHeader = Header.BeginMetric
            Case ("endmetric")
                eInputHeader = Header.EndMetric
            Case ("beginstraightpipe")
                eInputHeader = Header.BeginStraightPipe
            Case ("endstraightpipe")
                eInputHeader = Header.EndStraightPipe
            Case ("begintaperpipe")
                eInputHeader = Header.BeginTaperPipe
            Case ("endtaperpipe")
                eInputHeader = Header.EndTaperPipe
            Case Else
                If (sLine.Count = 1) Then
                    bValid = False
                    sMsg = "The following line is invalid: " + stemp
                    Exit Sub
                End If
        End Select



        Select Case (eInputHeader)

            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ' BeginInch
            Case (Header.BeginInch)
                ' Make sure they don't have two BeginInch headers in a row - invalid file format.
                If (eCurrentHeader = Header.BeginInch) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a BeginInch.  This isn't a valid format for this file."

                    ' They have a EndInch then BeginInch, I'm going to allow this, doesn't make sense but as long as they have another EndInch I guess its ok.
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginMetric then a BeginInch without the EndMetric
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndMetric then a BeginInch which should be fine.
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginStraightPipe then BeginInch without the EndStraightPipe
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then BeginInch without the EndStraightPipe
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a EndStraightPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then BeginInch without the EndTaperPipe
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then BeginInch without the EndTaperPipe
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a EndTaperPipe.  This isn't a valid format for this file."

                    ' First Header Found set the CurrentHeader
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    eCurrentHeader = eInputHeader
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' EndInch
            Case (Header.EndInch)
                ' They have a BeginInch then a EndInch, which is ok.
                If (eCurrentHeader = Header.BeginInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndInch then another EndInch
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There are two EndInch Headers in a row.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a EndInch without the EndMetric
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    bValid = False
                    sMsg = "There is a EndInch Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndMetric then a EndInch, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a EndInch Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then EndInch, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a EndInch Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then EndInch which is ok.
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginTaperPipe then EndInch, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a EndInch Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then EndInch which is ok.
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    eCurrentHeader = eInputHeader

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a EndInch at the begining of the file."
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' BeginMetric
            Case (Header.BeginMetric)
                ' They have a BeginInch then a BeginMetric, not valid.
                If (eCurrentHeader = Header.BeginInch) Then
                    bValid = False
                    sMsg = "There is a BeginInch Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndInch then BeginMetric, which is ok.
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginMetric then another BeginMetric 
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    bValid = False
                    sMsg = "There is a BeginMetric Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndMetric then a BeginMetric, I'm going to allow this they could have multiple Metric areas?
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginStraightPipe then BeginMetric, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginMetric Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then BeginMetric, not valid
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginMetric Header after a EndStraightPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then BeginMetric, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginMetric Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a EndTaperPipe then BeginMetric, not valid
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginMetric Header after a EndTaperPipe.  This isn't a valid format for this file."

                    ' First Header Found set the CurrentHeader
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    eCurrentHeader = eInputHeader
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' EndMetric
            Case (Header.EndMetric)
                ' They have a BeginInch then a EndMetric, which is ok
                If (eCurrentHeader = Header.BeginInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndInch then a EndMetric, not valid
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There is a EndMetric Header after a EndInch.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a EndMetric, which is ok
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndMetric then a EndInch, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a EndMetric Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then EndMetric, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a EndMetric Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then EndMetric which is ok.
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginTaperPipe then EndMetric, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a EndMetric Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a EndTaperPipe then EndMetric, which is ok.
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    eCurrentHeader = eInputHeader

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a EndInch at the begining of the file."
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' BeginStraightPipe
            Case (Header.BeginStraightPipe)
                ' They have a BeginInch then a BeginStraightPipe, which is ok
                If (eCurrentHeader = Header.BeginInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndInch then a BeginStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There is a BeginStraightPipe Header after a EndInch.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a BeginStraightPipe, which is ok
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndMetric then a BeginStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a BeginStraightPipe Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then BeginStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginStraightPipe Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then BeginStraightPipe which is ok.  Going to allow this, they could have two StraightPipe sections?
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginTaperPipe then BeginStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginStraightPipe Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a EndTaperPipe then BeginStraightPipe, which is ok.
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    eCurrentHeader = eInputHeader

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a BeginStraightPipe at the begining of the file."
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' EndStraightPipe
            Case (Header.EndStraightPipe)
                ' They have a BeginInch then a EndStraightPipe, not valid
                If (eCurrentHeader = Header.BeginInch) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a BeginInch.  This isn't a valid format for this file."

                    ' They have a EndInch then a EndStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a EndInch.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a EndStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndMetric then a EndStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then EndStraightPipe, which is ok.  Going to allow this, they could have two StraightPipe sections?
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndStraightPipe then EndStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a EndStraightPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then EndStraightPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a EndTaperPipe then EndStraightPipe,  not valid
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe Header after a EndTaperPipe.  This isn't a valid format for this file."

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a EndStraightPipe at the begining of the file."
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' BeginTaperPipe
            Case (Header.BeginTaperPipe)
                ' They have a BeginInch then a BeginTaperPipe, which is ok.
                If (eCurrentHeader = Header.BeginInch) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndInch then a BeginTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There is a BeginTaperPipe Header after a EndInch.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a BeginTaperPipe, which is ok.
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndMetric then a BeginTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a BeginTaperPipe Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then BeginTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a BeginTaperPipe Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then BeginTaperPipe, which is ok.
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a BeginTaperPipe then BeginTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    bValid = False
                    sMsg = "There is a BeginTaperPipe Header after a BeginTaperPipe.  This isn't a valid format for this file."

                    ' They have a EndTaperPipe then BeginTaperPipe,  which is ok.  Going to allow this, they could have two TaperPipe sections?
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    eCurrentHeader = eInputHeader

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a BeginTaperPipe at the begining of the file."
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' EndTaperPipe
            Case (Header.EndTaperPipe)
                ' They have a BeginInch then a EndTaperPipe, not valid
                If (eCurrentHeader = Header.BeginInch) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a BeginInch.  This isn't a valid format for this file."

                    ' They have a EndInch then a EndTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.EndInch) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a EndInch.  This isn't a valid format for this file."

                    ' They have a BeginMetric then a EndTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginMetric) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a BeginMetric.  This isn't a valid format for this file."

                    ' They have a EndMetric then a EndTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.EndMetric) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a EndMetric.  This isn't a valid format for this file."

                    ' They have a BeginStraightPipe then EndTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.BeginStraightPipe) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a BeginStraightPipe.  This isn't a valid format for this file."

                    ' They have a EndStraightPipe then EndTaperPipe, not valid
                ElseIf (eCurrentHeader = Header.EndStraightPipe) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a EndStraightPipe.  This isn't a valid format for this file."

                    ' They have a BeginTaperPipe then EndTaperPipe, which is ok
                ElseIf (eCurrentHeader = Header.BeginTaperPipe) Then
                    eCurrentHeader = eInputHeader

                    ' They have a EndTaperPipe then EndTaperPipe,  not valid
                ElseIf (eCurrentHeader = Header.EndTaperPipe) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe Header after a EndTaperPipe.  This isn't a valid format for this file."

                    ' First Header Found = not valid
                ElseIf (eCurrentHeader = Header.NotDefined) Then
                    bValid = False
                    sMsg = "There is a EndTaperPipe at the begining of the file."
                End If

        End Select

    End Sub

    Private Sub RemoveBtn_Click(sender As Object, e As EventArgs) Handles RemoveBtn.Click
        Dim nCount As Integer = 0

        nCount = ListBox1.SelectedIndices.Count
        If (nCount > 0) Then
            ListBox1.BeginUpdate()
            For x = nCount - 1 To 0 Step -1
                ListBox1.Items.RemoveAt(ListBox1.SelectedIndices(x))
            Next
            ListBox1.EndUpdate()
        End If

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged

        ' Check that they have something selected if not then the remove button is disabled.
        Dim nCount As Integer = 0

        nCount = ListBox1.SelectedIndices.Count
        If (nCount > 0) Then
            RemoveBtn.Enabled = True
        Else
            RemoveBtn.Enabled = False
        End If
    End Sub

    Private Sub SaveToBrowseBtn_Click(sender As Object, e As EventArgs) Handles SaveToBrowseBtn.Click
        FolderBrowserDialog1.SelectedPath = sDirectoryLocation

        If (FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            sDirectoryLocation = FolderBrowserDialog1.SelectedPath()

            If Not (sDirectoryLocation Is Nothing) Then
                Output_Directory_TB.Text = sDirectoryLocation
            End If
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim SEInstallData As Object = Nothing
        Dim sSolidEdgeInstallPath As String = Nothing
        Dim DirInfo As DirectoryInfo = Nothing
        Dim ParentDir As DirectoryInfo = Nothing
        Dim SubDirs As DirectoryInfo() = Nothing
        Dim dri As DirectoryInfo = Nothing
        Dim i As Integer = 0


        ' Try and get the location of the Preferences directory.
        Try
            ' Get the path to Solid Edge
            SEInstallData = CreateObject("SolidEdge.InstallData")
            sSolidEdgeInstallPath = SEInstallData.GetInstalledPath()
            DirInfo = New DirectoryInfo(sSolidEdgeInstallPath)
            ParentDir = DirInfo.Parent

            ' Search for the SELicense.dat file.  Could just use Preferences but not sure if that is localized so this should cover it.
            SubDirs = ParentDir.GetDirectories()

            For Each dri In SubDirs
                ' PR#1977801
                ' Changed the location instead of going to the Preferences folder it now goes to Holes directory under Preferences.
                If Directory.Exists(dri.FullName + "\Holes") Then
                    sSolidEdgePref = dri.FullName + "\Holes"
                    Exit For
                End If
            Next
        Catch ex As Exception

        End Try

        Try

            ' Read in the output directory if they have defined one.  If they haven't then use the Solid Edge Preferences as the default.
            If (My.Settings.sOutputLoc = "") Then
                sDirectoryLocation = sSolidEdgePref
            Else
                sDirectoryLocation = My.Settings.sOutputLoc
            End If

            Output_Directory_TB.Text = sDirectoryLocation

        Catch ex As Exception

        End Try


        ' Get the location of the executable.
        Try
            Dim fl2 As New FileInfo(Application.ExecutablePath)
            sEXEPath = fl2.DirectoryName

            sOutputLog = sEXEPath + "\Output_Log.txt"
            sLocalizedTemplate = sEXEPath + "\Holes.xlsx"



            ' Check to make sure there are template .xlsx files in the same directory as the executable.  These should be the localized versions.  If not then we will write out an English version.
            If File.Exists(sLocalizedTemplate) Then

                ' Check to see if the template is the correct format.  if not then create the new file from scratch.
                If (TemplateFile_Format_Correct(sLocalizedTemplate)) Then
                    bHoleTemplate_Found = True
                Else
                    bHoleTemplate_Found = False
                End If

            Else
                bHoleTemplate_Found = False
            End If

            If (bHoleTemplate_Found = False) Then
                ' The Template could not be found, warn them and create it using English.
                MsgBox("The localized template file: " + sLocalizedTemplate + " could not be found or the format isn't correct." + Environment.NewLine + "The utility will create a holes database file using the English language.")
            End If

        Catch ex As Exception

        End Try

        ' Enable the View Log button so they can view the results
        Check_For_OutputLog(sOutputLog)

    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Check to make sure the template file provided has 6 sheets.
    Private Function TemplateFile_Format_Correct(ByVal sFilename As String) As Boolean

        Dim nSheetCount As Integer = 1
        Dim spreadSheet As SpreadsheetDocument = Nothing

        ' Open the document for editing.
        Try
            spreadSheet = SpreadsheetDocument.Open(sFilename, True)
        Catch ex As Exception

        End Try


        Using (spreadSheet)

            Try
                ' Get the SharedStringTablePart. If it does not exist, create a new one.
                Dim shareStringPart As SharedStringTablePart = Nothing

                ' Get the WorkBook
                Dim workbookpart As WorkbookPart = Nothing
                workbookpart = spreadSheet.WorkbookPart()

                Dim WorkbookSheets As Sheets = Nothing
                Dim wsp As WorksheetPart = Nothing

                WorkbookSheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()

                nSheetCount = WorkbookSheets.Count

                ' There should be 6 sheets, if not then we will create the file from scratch.
                If (nSheetCount <> 6) Then
                    Return False
                End If
            Catch ex As Exception

            End Try

        End Using

        Return True

    End Function

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing

        ' When they are exiting the dlg save of the Save To location so the won't have to select it again.
        If (My.Settings.sOutputLoc <> Output_Directory_TB.Text.Trim) Then
            My.Settings.sOutputLoc = Output_Directory_TB.Text.Trim
            ' Save the settings
            My.Settings.Save()
        End If
    End Sub

    Private Sub ViewLogBtn_Click(sender As Object, e As EventArgs) Handles ViewLogBtn.Click

        ' Open the Log file
        Try
            If File.Exists(sOutputLog) Then
                System.Diagnostics.Process.Start("notepad", sOutputLog)
            Else
                MsgBox("Could not find the file: " + sOutputLog)
            End If
        Catch ex As Exception
            MsgBox("Error trying to open the file: " + sOutputLog)
        End Try

    End Sub
End Class
