Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Imports System.IO
- Imports System.Runtime.InteropServices
- Imports System.Text
- Imports System.Windows.Forms
- Imports Excel = Microsoft.Office.Interop.Excel
- Public Class Form1
- Private savedFilePaths As New Dictionary(Of String, String)()
- Private folderPaths As New Dictionary(Of String, String)()
- Private fileDict As New Dictionary(Of String, String)()
- Private excelDataCache As New Dictionary(Of String, List(Of List(Of String)))()
- Private WithEvents excelApp As Excel.Application
- Private openExcelInstances As New List(Of Excel.Application)()
- Private Sub SaveFilePathsToFile()
- Dim filePath As String = Path.Combine(Application.StartupPath, "selected_files.txt")
- Using sw As New StreamWriter(filePath)
- For Each item As String In ListBox1.Items
- Dim filePathForItem As String = savedFilePaths.FirstOrDefault(Function(kvp) kvp.Key = item).Value
- If filePathForItem IsNot Nothing Then
- Dim line As New StringBuilder()
- line.Append(item).Append("|").Append(filePathForItem)
- sw.WriteLine(line.ToString())
- End If
- Next
- End Using
- End Sub
- Private Function GetFormattedRowData(row As Excel.Range, columnIndices As Dictionary(Of String, Integer), maxColumnWidths As Dictionary(Of String, Integer)) As String
- Dim rowData As String = ""
- For Each header As String In maxColumnWidths.Keys
- If columnIndices.ContainsKey(header) Then
- Dim cell As Excel.Range = row.Cells(1, columnIndices(header))
- Dim cellValue As String = If(cell.Value IsNot Nothing, cell.Value.ToString(), "")
- rowData += cellValue.PadRight(maxColumnWidths(header) + 2)
- End If
- Next
- Return rowData
- End Function
- Private Function PadCellValue(value As String, width As Integer) As String
- Return value.PadRight(width)
- End Function
- Private Sub DisplayExcelData(excelData As List(Of List(Of String)), dataGridView As DataGridView)
- dataGridView.Rows.Clear()
- For Each row As List(Of String) In excelData
- If row.Count >= 4 Then
- dataGridView.Rows.Add(row(0), row(1), row(2), row(3))
- End If
- Next
- End Sub
- Private Function ReadAndCacheExcelData(filePath As String) As List(Of List(Of String))
- Dim app As Excel.Application = New Excel.Application()
- Dim wb As Excel.Workbook = app.Workbooks.Open(filePath)
- Dim ws As Excel.Worksheet = Nothing
- For Each sheet As Excel.Worksheet In wb.Sheets
- If sheet.Name = "Kabelliste" Then
- ws = sheet
- Exit For
- End If
- Next
- If ws Is Nothing Then
- MessageBox.Show("Sheet 'data' not found in the selected Excel file.")
- Return New List(Of List(Of String))()
- End If
- Dim columnLetters As String() = {"K", "C", "A", "D"}
- Dim cachedData As New List(Of List(Of String))()
- For i As Integer = 2 To ws.UsedRange.Rows.Count
- Dim rowData As New List(Of String)()
- For Each columnLetter As String In columnLetters
- Dim cell As Excel.Range = ws.Range(columnLetter & i.ToString())
- Dim cellValue As String = If(cell.Value IsNot Nothing, cell.Value.ToString(), "")
- rowData.Add(cellValue)
- Next
- cachedData.Add(rowData)
- Next
- wb.Close(False)
- app.Quit()
- ReleaseObject(ws)
- ReleaseObject(wb)
- ReleaseObject(app)
- Return cachedData
- End Function
- Private Sub SaveExcelPaths()
- Dim lines As New List(Of String)
- For Each key As String In fileDict.Keys
- lines.Add($"{key}={fileDict(key)}")
- Next
- File.WriteAllLines("excel.txt", lines)
- End Sub
- Private Sub LoadExcelPaths()
- If Not File.Exists("excel.txt") Then
- Return
- End If
- Dim lines As String() = File.ReadAllLines("excel.txt")
- For Each line As String In lines
- Dim parts As String() = line.Split("=")
- If parts.Length = 2 Then
- fileDict(parts(0)) = parts(1)
- End If
- Next
- End Sub
- Private Sub SaveExcelFilePath(key As String, filePath As String)
- fileDict(key) = filePath
- SaveExcelPaths()
- End Sub
- Private selectedItem As String = ""
- Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
- If ListBox1.SelectedIndex <> -1 Then
- selectedItem = ListBox1.SelectedItem.ToString()
- End If
- End Sub
- Private Sub ListBox1_DoubleClick(sender As Object, e As EventArgs) Handles ListBox1.DoubleClick
- If ListBox1.SelectedIndex <> -1 Then
- OpenExcelFileDialog(ListBox1.SelectedItem.ToString())
- End If
- End Sub
- Private Sub OpenExcelFileDialog(selectedItem As String)
- Using openFileDialog As New OpenFileDialog()
- openFileDialog.Filter = "Excel Files|*.xlsx;*.xls"
- openFileDialog.Title = "Select an Excel File"
- If openFileDialog.ShowDialog() = DialogResult.OK Then
- Dim filePath As String = openFileDialog.FileName
- SaveExcelFilePath(selectedItem, filePath)
- excelDataCache(selectedItem) = ReadAndCacheExcelData(filePath)
- DisplayExcelData(excelDataCache(selectedItem), DataGridView1)
- End If
- End Using
- End Sub
- Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
- LoadPaths()
- LoadFiles()
- LoadExcelPaths()
- Label1.Text = "Select harness from the list to show KAB."
- For Each column As DataGridViewColumn In DataGridView1.Columns
- column.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
- Next
- DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
- ProgressBar1.Visible = False
- End Sub
- Private Sub RadioButton1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton1.Click
- LoadFile("C:\Users\filip\Desktop\Harnesses\c2.txt")
- End Sub
- Private Sub RadioButton2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton2.Click
- LoadFile("C:\Users\filip\Desktop\Harnesses\PF.txt")
- End Sub
- Private Sub RadioButton3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton3.Click
- LoadFile("C:\Users\filip\Desktop\Harnesses\COMPONENTS.txt")
- End Sub
- Private Sub radioButton1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton1.CheckedChanged
- If RadioButton1.Checked Then
- RadioButton1_Click(sender, e)
- End If
- End Sub
- Private Sub radioButton2_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton2.CheckedChanged
- If RadioButton2.Checked Then
- RadioButton2_Click(sender, e)
- End If
- End Sub
- Private Sub radioButton3_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton3.CheckedChanged
- If RadioButton3.Checked Then
- RadioButton3_Click(sender, e)
- End If
- End Sub
- Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyDown
- If e.KeyCode = Keys.Enter Then
- Dim searchText As String = TextBox1.Text.ToLower()
- For i As Integer = 0 To ListBox1.Items.Count - 1
- Dim currentItem As String = ListBox1.Items(i).ToString().ToLower()
- If currentItem.Contains(searchText) Then
- ListBox1.SelectedIndex = i
- Exit For
- End If
- Next
- End If
- End Sub
- Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- ' Check if folder path is already set
- If folderPaths.ContainsKey(selectedItem) Then
- ' Ask user if they want to change the default folder location
- Dim result As DialogResult = MessageBox.Show($"Are you sure you want to change the default folder location for {selectedItem}?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
- If result = DialogResult.Yes Then
- ' Prompt user to select a new folder location
- Using folderBrowser As New FolderBrowserDialog()
- folderBrowser.Description = $"Select a folder for {selectedItem}"
- If folderBrowser.ShowDialog() = DialogResult.OK Then
- ' Update folder path and save to file
- folderPaths(selectedItem) = folderBrowser.SelectedPath
- SavePaths()
- MessageBox.Show($"Default folder location updated for {selectedItem}")
- End If
- End Using
- End If
- Else
- ' If folder path is not set, inform user to double-click on the item
- MessageBox.Show($"Please double-click on {selectedItem} to set the default folder location.")
- End If
- End Sub
- Private Sub OpenFolder(ByVal folderPath As String)
- If folderPath <> "" Then
- Try
- Process.Start("explorer.exe", folderPath)
- Catch ex As Exception
- MessageBox.Show($"Error opening folder: {ex.Message}")
- End Try
- End If
- End Sub
- Private Sub SavePaths()
- Dim lines As New List(Of String)
- For Each key As String In folderPaths.Keys
- lines.Add($"{key}={folderPaths(key)}")
- Next
- File.WriteAllLines("paths.txt", lines)
- End Sub
- Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- ' Check if folder path is already set
- If folderPaths.ContainsKey(selectedItem) Then
- ' Open folder in File Explorer
- Try
- Process.Start("explorer.exe", folderPaths(selectedItem))
- Catch ex As Exception
- MessageBox.Show($"Error opening folder: {ex.Message}")
- End Try
- Return
- End If
- ' If folder path is not set, ask user to select folder
- Using folderBrowser As New FolderBrowserDialog()
- folderBrowser.Description = $"Select a folder for {selectedItem}"
- If folderBrowser.ShowDialog() = DialogResult.OK Then
- ' Set folder path and open folder in File Explorer
- folderPaths(selectedItem) = folderBrowser.SelectedPath
- SavePaths()
- Try
- Process.Start("explorer.exe", folderBrowser.SelectedPath)
- Catch ex As Exception
- MessageBox.Show($"Error opening folder: {ex.Message}")
- End Try
- End If
- End Using
- End Sub
- Private Function GetSelectedRadioButton() As RadioButton
- If RadioButton1.Checked Then
- Return RadioButton1
- ElseIf RadioButton2.Checked Then
- Return RadioButton2
- ElseIf RadioButton3.Checked Then
- Return RadioButton3
- Else
- Return Nothing
- End If
- End Function
- Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
- Dim selectedRadioButton As RadioButton = GetSelectedRadioButton()
- If selectedRadioButton Is Nothing Then
- MessageBox.Show("Please select a radio button")
- Return
- End If
- Dim filePath As String = Path.Combine("C:\Users\filip\Desktop\Harnesses", selectedRadioButton.Text & ".txt")
- Dim textToWrite As String = TextBox1.Text & vbCrLf
- File.AppendAllText(filePath, textToWrite)
- MessageBox.Show("Harness added to " & selectedRadioButton.Text)
- ListBox1.Items.Clear()
- LoadFile(filePath)
- End Sub
- Private Sub SaveFiles()
- ' Write the contents of the dictionary to the files.txt file
- Dim lines As New List(Of String)()
- For Each kvp As KeyValuePair(Of String, String) In fileDict
- lines.Add(kvp.Key & "," & kvp.Value)
- Next
- File.WriteAllLines("files.txt", lines)
- End Sub
- Private Sub LoadFiles()
- ' Check if the files.txt file exists
- If Not File.Exists("files.txt") Then
- Return
- End If
- ' Read the contents of the files.txt file and add the entries to the dictionary
- Dim lines As String() = File.ReadAllLines("files.txt")
- For Each line As String In lines
- Dim parts As String() = line.Split(",")
- If parts.Length = 2 Then
- fileDict(parts(0)) = parts(1)
- End If
- Next
- End Sub
- Private Sub LoadFile(filePath As String)
- If Not File.Exists(filePath) Then
- MessageBox.Show("File not found: " & filePath)
- Return
- End If
- Dim lines As String() = File.ReadAllLines(filePath)
- ListBox1.Items.Clear()
- ListBox1.Items.AddRange(lines)
- End Sub
- Private Sub LoadPaths()
- If File.Exists("paths.txt") Then
- Dim lines() As String = File.ReadAllLines("paths.txt")
- For Each line As String In lines
- Dim parts() As String = line.Split("=")
- If parts.Length = 2 Then
- folderPaths(parts(0)) = parts(1)
- End If
- Next
- End If
- End Sub
- Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
- ' Get the selected item from ListBox1
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- ' Check if file location is already set
- If fileDict.ContainsKey(selectedItem) Then
- ' Open file using stored location
- Try
- Dim startInfo As New ProcessStartInfo()
- startInfo.FileName = fileDict(selectedItem)
- startInfo.UseShellExecute = True
- Process.Start(startInfo)
- Catch ex As Exception
- MessageBox.Show("Error opening file: " & ex.Message)
- End Try
- Return
- End If
- ' If file location is not set, ask user to select file
- Dim selectedFile As String = ""
- Using openFileDialog As New OpenFileDialog()
- openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
- openFileDialog.Filter = "All Files (*.*)|*.*"
- openFileDialog.FilterIndex = 1
- If openFileDialog.ShowDialog() = DialogResult.OK Then
- selectedFile = openFileDialog.FileName
- End If
- End Using
- ' Check if a file was selected
- If String.IsNullOrEmpty(selectedFile) Then
- MessageBox.Show("No file selected.")
- Return
- End If
- ' Set file location and save to file
- fileDict(selectedItem) = selectedFile
- SaveFiles()
- ' Open the file using the default application
- Try
- Dim startInfo As New ProcessStartInfo()
- startInfo.FileName = selectedFile
- startInfo.UseShellExecute = True
- Process.Start(startInfo)
- Catch ex As Exception
- MessageBox.Show("Error opening file: " & ex.Message)
- End Try
- End Sub
- Private Sub ListBox3_DragEnter(sender As Object, e As DragEventArgs) Handles ListBox3.DragEnter
- If e.Data.GetDataPresent(DataFormats.FileDrop) Then
- e.Effect = DragDropEffects.Copy
- Else
- e.Effect = DragDropEffects.None
- End If
- End Sub
- Private Sub ListBox3_DragDrop(sender As Object, e As DragEventArgs) Handles ListBox3.DragDrop
- Dim files() As String = e.Data.GetData(DataFormats.FileDrop)
- For Each file In files
- If Path.GetExtension(file) = ".xls" Or Path.GetExtension(file) = ".xlsx" Then
- ListBox3.Items.Add(file)
- End If
- Next
- End Sub
- Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
- If ListBox3.Items.Count < 1 Then
- MessageBox.Show("Ubaci MACH liste!")
- Return
- End If
- ProgressBar1.Value = 0 ' Reset the progress bar
- ProgressBar1.Maximum = ListBox3.Items.Count
- ProgressBar1.Step = 1
- ProgressBar1.Visible = True
- Dim app As Excel.Application = New Excel.Application()
- app.Visible = False ' Make the Excel application visible
- Dim wb As Excel.Workbook = app.Workbooks.Add()
- Dim ws As Excel.Worksheet = wb.Worksheets(1)
- ' Copy header row
- Dim headerRange As Excel.Range = ws.Range("A1:CD1")
- headerRange.Value = "Header"
- Dim rowCount As Integer = 2 ' Start copying data from second row
- Dim headerCopied As Boolean = False
- For Each file As String In ListBox3.Items
- If Path.GetFileName(file) <> "Import-Export.xls" Then
- If Not headerCopied Then
- Dim wbHeader As Excel.Workbook = app.Workbooks.Open(file)
- Dim wsHeader As Excel.Worksheet = wbHeader.Worksheets("Data")
- Dim headerToCopy As Excel.Range = wsHeader.Range("A1", "CD1")
- headerToCopy.Copy()
- headerRange.PasteSpecial(Excel.XlPasteType.xlPasteAll)
- wbHeader.Close(False)
- headerCopied = True
- End If
- Try
- Dim wbCopy As Excel.Workbook = app.Workbooks.Open(file)
- Dim wsCopy As Excel.Worksheet = wbCopy.Worksheets("Data")
- Dim rangeToCopy As Excel.Range = wsCopy.Range("A2", "CD" & wsCopy.Range("A2").End(Excel.XlDirection.xlDown).Row)
- rangeToCopy.Copy()
- Dim nextRow As Excel.Range = ws.Range("A" & rowCount)
- nextRow.PasteSpecial(Excel.XlPasteType.xlPasteAll)
- rowCount += rangeToCopy.Rows.Count
- ' Copy a blank cell
- wsCopy.Range("A1").Copy()
- wbCopy.Close(False)
- ' Clear the clipboard
- app.CutCopyMode = False
- Catch comEx As System.Runtime.InteropServices.COMException
- MessageBox.Show("An error occurred while processing the file: " & file & vbCrLf & "Error: The 'Data' worksheet was not found in the file.")
- Return
- Catch ex As Exception
- MessageBox.Show("An error occurred while processing the file: " & file & vbCrLf & "Error message: " & ex.Message)
- Return
- End Try
- End If
- ProgressBar1.PerformStep()
- Application.DoEvents()
- Next
- Dim columnRange As Excel.Range = ws.UsedRange.Columns
- columnRange.AutoFit()
- app.Visible = True
- openExcelInstances.Add(app)
- ExcelCheckTimer.Enabled = True
- ExcelCheckTimer.Start()
- AddHandler app.WorkbookBeforeClose, AddressOf WorkbookBeforeClose
- excelApp = app
- ' Release objects
- ReleaseObject(ws)
- ReleaseObject(wb)
- ProgressBar1.Visible = False
- ProgressBar1.Value = 0
- ' Do not release the app object here since it will be released in the excelApp_WorkbookBeforeClose method
- End Sub
- Private Sub ExcelCheckTimer_Tick(sender As Object, e As EventArgs) Handles ExcelCheckTimer.Tick
- If excelApp IsNot Nothing AndAlso IsNothing(excelApp.Workbooks) Then
- ExcelCheckTimer.Stop()
- ExcelCheckTimer.Enabled = False
- Else
- Return
- End If
- ' If no Excel workbooks are open, quit the Excel application and release its resources
- If excelApp IsNot Nothing Then
- excelApp.Quit()
- Marshal.ReleaseComObject(excelApp)
- excelApp = Nothing
- End If
- End Sub
- Private Sub WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
- Dim app As Excel.Application = Wb.Application
- Wb.Saved = True ' Mark the workbook as saved to prevent any save prompts
- If app IsNot Nothing Then
- app.Quit()
- Marshal.ReleaseComObject(app)
- End If
- End Sub
- Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
- Dim app As Excel.Application = Wb.Application
- If app IsNot Nothing Then
- app.Quit()
- Marshal.ReleaseComObject(app)
- End If
- End Sub
- Private Sub excelApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Handles excelApp.WorkbookBeforeClose
- If excelApp IsNot Nothing Then
- excelApp.Quit()
- Marshal.ReleaseComObject(excelApp)
- excelApp = Nothing
- End If
- End Sub
- Private Sub ReleaseObject(ByVal obj As Object)
- Try
- System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
- Catch ex As Exception
- Console.WriteLine("Error releasing object: " & ex.Message)
- Finally
- obj = Nothing
- GC.Collect()
- End Try
- End Sub
- Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
- ListBox3.Items.Clear()
- ProgressBar1.Value = 0
- End Sub
- Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
- If ListBox1.SelectedIndex <> -1 Then
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- If fileDict.ContainsKey(selectedItem) Then
- ListBox3.Items.Add(fileDict(selectedItem))
- Else
- MessageBox.Show("Selected item does not have a file location.")
- End If
- Else
- MessageBox.Show("Please select an item from the list.")
- End If
- End Sub
- Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
- If ListBox1.SelectedIndex <> -1 Then
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- If fileDict.ContainsKey(selectedItem) Then
- Dim filePath As String = fileDict(selectedItem)
- If File.Exists(filePath) Then
- If Not excelDataCache.ContainsKey(selectedItem) Then
- excelDataCache(selectedItem) = ReadAndCacheExcelData(filePath)
- End If
- DisplayExcelData(excelDataCache(selectedItem), DataGridView1)
- Else
- MessageBox.Show("File has been removed or moved! Double-click on the harness to select a new KAB list.")
- End If
- Label1.Visible = True
- Label1.Text = $"KAB lista za: {ListBox1.SelectedItem}"
- Else
- MessageBox.Show("Ovaj harness nema KAB listu! Double-click on the harness to select a KAB list.")
- End If
- Else
- MessageBox.Show("Please select an item from the list.")
- End If
- End Sub
- Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
- If ListBox1.SelectedIndex <> -1 Then
- Dim selectedItem As String = ListBox1.SelectedItem.ToString()
- Dim openFileDialog As OpenFileDialog = New OpenFileDialog()
- openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
- openFileDialog.Title = "Select a KAB List Excel File"
- If openFileDialog.ShowDialog() = DialogResult.OK Then
- Dim newFilePath As String = openFileDialog.FileName
- If fileDict.ContainsKey(selectedItem) Then
- fileDict(selectedItem) = newFilePath
- Else
- fileDict.Add(selectedItem, newFilePath)
- End If
- MessageBox.Show("Location for " & selectedItem & " changed.")
- End If
- Else
- MessageBox.Show("Please select a harness from the list.")
- End If
- End Sub
- End Class
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement