I have continued to develop the program I am working on and was able to populate a DataGridView with my Excel Spreadsheet data successfully, all of this works perfectly (currently).
However one this I did notice, was that since adding Imports DocumentFormat.OpenXml.Spreadsheet any line of code that changes the Font of my Label or the BackColor will not work, specifying the error for example with BackColor: 'FromArgb' is not a member of 'Color' or for instance in terms of Font: Value of type 'FontFamily' cannot be converted to OpenXmlElement'.
Now from what I can gather, my Imports DocumentFormat.OpenXml.Spreadsheet is simply for spreadsheet stuff, I do not entirely know though, like I said my DataGridView is working perfectly. However I have lost the BackColor and Font functionality. What I was attempting to achieve (and had done previously before Imports DocumentFormat.OpenXml.Spreadsheet was added, however I need this line for my DataGridView to work properly) was when my mouse went over my "Add Data" or "Contact" label for example, the font would increase slightly creating a pop effect and the back colour would change to Cyan so that it would stand out. Any help would be greatly appreciated. The code is shown below:
Imports System.ComponentModel
Imports System.Runtime.InteropServices
Imports DocumentFormat.OpenXml.EMMA
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Public Class frmDDEF
Private Sub LoadExcelDataToDataGridView()
If Not bckLoader.IsBusy Then
bckLoader.RunWorkerAsync()
End If
End Sub
Private Sub bckLoader_DoWork(sender As Object, e As DoWorkEventArgs) Handles bckLoader.DoWork
Dim filePath As String = "*spreadsheet path*"
' Define the row where your data starts in the Excel file
Dim startingRow As Integer = 1 ' Start from Row 1
Try
Using spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
Dim workbookPart As WorkbookPart = spreadsheetDocument.WorkbookPart
Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.First()
Dim worksheet As SheetData = worksheetPart.Worksheet.Elements(Of SheetData)().First()
' Clear existing data in the DataGridView
DataGridView1.Invoke(Sub() DataGridView1.Rows.Clear())
Dim rows = worksheet.Elements(Of Row)().Skip(startingRow - 1)
For Each row In rows
Dim values = row.Elements(Of Cell)().Take(5).Select(Function(cell, colIndex)
Dim cellValue = GetCellValue(workbookPart, cell)
If colIndex = 1 Then ' Assuming Time column is at index 1
Dim timeValue As Double
If Double.TryParse(cellValue, timeValue) Then
Dim totalSeconds As Integer = CInt(timeValue * 24 * 60 * 60)
Dim hours As Integer = totalSeconds \ 3600
Dim minutes As Integer = (totalSeconds \ 60) Mod 60
Dim seconds As Integer = totalSeconds Mod 60
cellValue = $"{hours:D2}:{minutes:D2}:{seconds:D2}"
End If
End If
Return cellValue
End Function).ToList()
Dim hasData = values.Any(Function(value) Not String.IsNullOrEmpty(value))
If hasData Then
DataGridView1.Invoke(Sub() DataGridView1.Rows.Add(values.ToArray()))
End If
Next
End Using
Catch ex As Exception
MsgBox("An error occurred: " & ex.Message)
End Try
End Sub
Private Function GetCellValue(workbookPart As WorkbookPart, cell As Cell) As String
Dim cellValue As String = cell.InnerText
If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
Dim sharedStringTablePart As SharedStringTablePart = workbookPart.GetPartsOfType(Of SharedStringTablePart).First()
If sharedStringTablePart.SharedStringTable.Elements().Count > 0 Then
Dim sharedStringItem As SharedStringItem = sharedStringTablePart.SharedStringTable.Elements().ElementAt(Integer.Parse(cellValue))
cellValue = sharedStringItem.Text.Text
End If
ElseIf cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.Date Then
' Handle Time format "hh:mm:ss"
Dim excelTimeValue As Double
If Double.TryParse(cellValue, excelTimeValue) Then
Dim totalSeconds As Integer = CInt(excelTimeValue * 24 * 60 * 60)
Dim hours As Integer = totalSeconds \ 3600
Dim minutes As Integer = (totalSeconds \ 60) Mod 60
Dim seconds As Integer = totalSeconds Mod 60
cellValue = $"{hours:D2}:{minutes:D2}:{seconds:D2}"
End If
End If
Return cellValue
End Function
Private Function ConvertToExcelColumnName(columnIndex As Integer) As String
Dim dividend As Integer = columnIndex + 1
Dim columnName As String = String.Empty
While dividend > 0
Dim modulo As Integer = (dividend - 1) Mod 26
columnName = Convert.ToChar(65 + modulo) & columnName
dividend = CInt((dividend - modulo) / 26)
End While
Return columnName
End Function
Private Sub tmrIconLoad_Tick(sender As System.Object, e As System.EventArgs) Handles tmrIconLoad.Tick
Me.ShowIcon = False ' Disables icon once loaded
tmrIconLoad.Enabled = False ' Disables timer
End Sub
Private Sub frmDDEF_Load(sender As Object, e As EventArgs) Handles MyBase.Load
LoadExcelDataToDataGridView()
tmrIconLoad.Enabled = True ' Triggers Timer to disable Icon once loaded
grpPANELMENU.BackColor = Color.FromArgb(2, 7, 26) ' Sets default colour of the Menu Panel
grpPANELTOP.BackColor = Color.FromArgb(2, 7, 26) ' Sets default colour of the Top Panel (has image on it anyway)
End Sub
' CLICK CODE
Private Sub lblAddData_Click(sender As Object, e As EventArgs) Handles lblAddData.Click
MessageBox.Show("This will allow you to Add Data")
End Sub
Private Sub lblPhoneLog_Click(sender As Object, e As EventArgs) Handles lblPhoneLog.Click
MessageBox.Show("This will allow you to view the Phone Logs")
End Sub
Private Sub lblContact_Click(sender As Object, e As EventArgs) Handles lblContact.Click
frmContact.LoadForm("loading")
End Sub
' HIGHLIGHT CODE
Private Sub lblAddData_MouseMove(sender As Object, e As MouseEventArgs) Handles lblAddData.MouseMove
lblAddData.BackColor = Color.FromArgb(3, 182, 252) ' Changes the Add Data Label to Cyan Highlight on the Menu Panel
End Sub
Private Sub lblPhoneLog_MouseMove(sender As Object, e As MouseEventArgs) Handles lblPhoneLog.MouseMove
' lblPhoneLog.BackColor = Color.FromArgb(3, 182, 252) ' Changes the Phone Log Label to Cyan Highlight on the Menu Panel
End Sub
Private Sub lblContact_MouseMove(sender As Object, e As MouseEventArgs) Handles lblContact.MouseMove
' lblContact.BackColor = Color.FromArgb(3, 182, 252) ' Changes the Contact Label to Cyan Highlight on the Menu Panel
End Sub
Private Sub frmDDEF_MouseMove(sender As Object, e As MouseEventArgs) Handles MyBase.MouseMove
' lblAddData.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Add Data Label when the mouse goes over the Form
'lblPhoneLog.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Phone Log Label when the mouse goes over the Form
' lblContact.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Contact Label when the mouse goes over the Form
End Sub
Private Sub grpPANELMENU_MouseMove(sender As Object, e As MouseEventArgs) Handles grpPANELMENU.MouseMove
' lblAddData.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Add Data Label when the mouse goes over the Panel Menu
' lblPhoneLog.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Phone Log Label when the mouse goes over the Panel Menu
' lblContact.BackColor = Color.FromArgb(2, 7, 26) ' Removes the Cyan Highlight from the Contact Label when the mouse goes over the Panel Menu
End Sub
' SELECTED LABEL EFFECT
Private Sub lblAddData_MouseEnter(sender As Object, e As EventArgs) Handles lblAddData.MouseEnter
lblAddData.Font = New Font(lblAddData.Font.FontFamily, lblAddData.Font.Size + 2)
End Sub
Private Sub lblAddData_MouseLeave(sender As Object, e As EventArgs) Handles lblAddData.MouseLeave
' lblAddData.Font = New Font(lblAddData.Font.FontFamily, lblAddData.Font.Size - 2)
End Sub
Private Sub lblPhoneLog_MouseEnter(sender As Object, e As EventArgs) Handles lblPhoneLog.MouseEnter
' lblPhoneLog.Font = New Font(lblPhoneLog.Font.FontFamily, lblPhoneLog.Font.Size + 2)
End Sub
Private Sub lblPhoneLog_MouseLeave(sender As Object, e As EventArgs) Handles lblPhoneLog.MouseLeave
' lblPhoneLog.Font = New Font(lblPhoneLog.Font.FontFamily, lblPhoneLog.Font.Size - 2)
End Sub
Private Sub lblContact_MouseEnter(sender As Object, e As EventArgs) Handles lblContact.MouseEnter
' lblContact.Font = New Font(lblContact.Font.FontFamily, lblContact.Font.Size + 2)
End Sub
Private Sub lblContact_MouseLeave(sender As Object, e As EventArgs) Handles lblContact.MouseLeave
' lblContact.Font = New Font(lblContact.Font.FontFamily, lblContact.Font.Size - 2)
End Sub
End Class
I commented out the sections of the code that wasn't working to stop the errors so I could still work on the project. These fixes aren't crucial but would be really nice to have :)
After doing research and a bunch of testing, Imports DocumentFormat.OpenXml.Spreadsheet is the problem, however a work around is instead of using Color.FromArgb use System.Drawing.Color.FromArgb due to a clash with the Xml. The same can be used with font, instead of New Font use System.Drawing.Font.
Examples shown below:
Private Sub lblAddData_MouseMove(sender As Object, e As MouseEventArgs) Handles lblAddData.MouseMove
lblAddData.BackColor = System.Drawing.Color.FromArgb(3, 182, 252) ' Changes the Add Data Label to Cyan Highlight on the Menu Panel
End Sub
Private Sub lblAddData_MouseEnter(sender As Object, e As EventArgs) Handles lblAddData.MouseEnter
lblAddData.Font = New System.Drawing.Font(lblAddData.Font.FontFamily, lblAddData.Font.Size + 2)
End Sub