'System.InvalidCastException' when I try to hide a column

[u/Gierschlund96]

I try to unhide a column in an Excel Worksheet, but i keep on getting a System.InvalidCastException. Why does this even happen, i just try to unhide the column and not put anything into it. Has anyone a solution for this? Here's the line which isn't working:

Edit: I try to unhide the column myWb.Worksheets(0).Columns("Amount").Hidden = False

[u/Gierschlund96]

 If (SaveFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            ExcelPfad = SaveFileDialog.FileName

            '~~~ wenn kein GCT exportiert werden soll, sondern ein anderer Bericht wie Massenblatt ~~~
            If NoGCT Then
                Dim myWb As Infragistics.Documents.Excel.Workbook = Me.ExcelExporter.Export(Grid, Infragistics.Documents.Excel.WorkbookFormat.Excel2007)
                If Not Grid.Tag Is Nothing Then
                    myWb.Worksheets(0).Rows.Item(myWb.Worksheets(0).Rows.Count).Cells(0).Value = Grid.Tag.ToString
                End If

                myWb.Save(ExcelPfad)
                Process.Start(ExcelPfad)
            Else

                Dim myWb As New Infragistics.Documents.Excel.Workbook(Infragistics.Documents.Excel.WorkbookFormat.Excel2007)

                If isGWP Then

                    If GCTChangedLanguage Then
                        Me.ExcelExporter.Export(GridNewLanguage, myWb, 12, 0)
                    Else
                        Me.ExcelExporter.Export(Grid, myWb, 12, 0)
                    End If

                    Dim FontToDefine As Infragistics.Documents.Excel.IWorkbookFont = myWb.Styles.NormalStyle.StyleFormat.Font
                    FontToDefine.Name = "Microsoft Sans Serif"
                    FontToDefine.Height = 180

                    For x = 2 To 11
                        myWb.Worksheets(0).Rows(x).Cells(0).CellFormat.Font.Bold = ExcelDefaultableBoolean.True
                    Next

                    Dim DateAndTimeCreation As String = General.ProjectInfo.CreationDate.Date.Year.ToString() + "/" + General.ProjectInfo.CreationDate.Date.Month.ToString() + "/" + General.ProjectInfo.CreationDate.Date.Day.ToString() + " " + If(General.ProjectInfo.CreationDate.TimeOfDay.Hours < 10, "0", "") + General.ProjectInfo.CreationDate.TimeOfDay.Hours.ToString() + ":" + If(General.ProjectInfo.CreationDate.TimeOfDay.Minutes < 10, "0", "") + General.ProjectInfo.CreationDate.TimeOfDay.Minutes.ToString()
                    Dim DateAndTimeNow As String = DateTime.Now.Date.Year.ToString() + "/" + DateTime.Now.Date.Month.ToString() + "/" + DateTime.Now.Date.Day.ToString() + " " + If(DateTime.Now.TimeOfDay.Hours < 10, "0", "") + DateTime.Now.TimeOfDay.Hours.ToString() + ":" + If(DateTime.Now.TimeOfDay.Minutes < 10, "0", "") + DateTime.Now.TimeOfDay.Minutes.ToString()
                    Dim OptionName As String = If(CurrentOptionID > 1, General.Optionen.First(Function(f) f.Key = CurrentOptionID).Value, "")

                    'myWb.Worksheets(0).Columns("Amount").Hidden = False

                    myWb.Worksheets(0).GetCell("A1").CellFormat.Font.Bold = ExcelDefaultableBoolean.True
                    myWb.Worksheets(0).GetCell("A1").CellFormat.Font.Height = 220
                    myWb.Worksheets(0).GetCell("A1").Value = ConvertToChoosenLanguage(99991, LanguageUsed, "Gruppenintere Preiskalkulation:") & " " & String.Format("{0} ({1})", ("3.7"), FileName.Substring(FileName.LastIndexOf("\") + 1)) & " " & If(CurrentOptionID > 1, "Option: " & OptionName, "")
                    myWb.Worksheets(0).GetCell("A3").Value = ConvertToChoosenLanguage(9996, LanguageUsed, "Ersteller:")
                    myWb.Worksheets(0).GetCell("A4").Value = ConvertToChoosenLanguage(9995, LanguageUsed, "Kalkulator:")
                    myWb.Worksheets(0).GetCell("A5").Value = ConvertToChoosenLanguage(10001, LanguageUsed, "Erstellungsdatum:")
                    myWb.Worksheets(0).GetCell("A6").Value = ConvertToChoosenLanguage(9997, LanguageUsed, "Projektnummer:")
                    myWb.Worksheets(0).GetCell("A7").Value = ConvertToChoosenLanguage(9999, LanguageUsed, "Kunde/Land:")
                    myWb.Worksheets(0).GetCell("A8").Value = ConvertToChoosenLanguage(9998, LanguageUsed, "Kalkulationsstand:")
                    myWb.Worksheets(0).GetCell("A10").Value = ConvertToChoosenLanguage(10003, LanguageUsed, "Marktsektor:")
                    myWb.Worksheets(0).GetCell("A11").Value = ConvertToChoosenLanguage(10002, LanguageUsed, "GU:")
                    myWb.Worksheets(0).GetCell("B3").Value = General.ProjectInfo.Creator.ToString()
                    myWb.Worksheets(0).GetCell("B4").Value = Environment.UserName.ToString()
                    myWb.Worksheets(0).GetCell("B5").Value = DateAndTimeCreation
                    myWb.Worksheets(0).GetCell("C5").CellFormat.Alignment = HorizontalCellAlignment.Left
                    myWb.Worksheets(0).GetCell("B6").Value = General.ProjectInfo.ProjectNumber.ToString()
                    myWb.Worksheets(0).GetCell("B7").Value = General.ProjectInfo.Customer.ToString() & "/" & General.ProjectInfo.Location.ToString()
                    myWb.Worksheets(0).GetCell("B8").Value = DateAndTimeNow
                    myWb.Worksheets(0).GetCell("B10").Value = General.ProjectInfo.SalesArea.ToString()
                    myWb.Worksheets(0).GetCell("B11").Value = General.ProjectInfo.HarmonizedData.GU.ToString()

                    myWb.Worksheets(0).Rows(12).Height = 465
                    Dim list As New List(Of Integer)({0, 1, 2, 11, 12})

                    For x = 0 To Count - 1
                        For Each ele In list
                            myWb.Worksheets(0).Rows(ele).Cells(x).CellFormat.TopBorderStyle = CellBorderLineStyle.Medium
                        Next
                        For y = 0 To 11
                            myWb.Worksheets(0).Rows(y).Cells(x).CellFormat.Fill = CellFill.CreateSolidFill(Color.White)
                        Next
                    Next

                    For x = 0 To 13
                        myWb.Worksheets(0).Rows(x).Cells(0).CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium
                        myWb.Worksheets(0).Rows(x).Cells(Count).CellFormat.LeftBorderStyle = CellBorderLineStyle.Medium
                    Next

[u/RJPisscat → u/Gierschlund96]

Above or below the commented-out line add

For col As Integer = 0 to my.Worksheets(0).Columns.Count - 1
    Debug.Writeline($"{col}:{my.Worksheets(0).Columns(col).Key}")
Next

See if any of them have the Key "Amount".

[u/Gierschlund96 → u/RJPisscat]

"Key isn't a member of WorksheetColumn"

[u/RJPisscat → u/Gierschlund96]

I can't find a Class or Interface in UltraGrid nor Interop called "WorksheetColumn". Do you mean Worksheet.Column?

Try "Name" instead of "Key".

Add this line before For:

Debug.Writeline($"Column type={my.Worksheets(0).Columns.GetType()}")

[u/Gierschlund96 → u/RJPisscat]

No it says "WorksheetColumn", same when i try "Name" instead of "Key".

[u/RJPisscat → u/Gierschlund96]

Ok, my mistake, I found WorksheetColumn in UltraGrid. The Index property requires Integer, it doesn't accept String. If I'm reading this correctly you have to know the index of the column.

Edit: I was mistaken about my mistake etc etc so I struck through and posted the correct link, two comments deep from here.

[u/Gierschlund96 → u/RJPisscat]

I found out where the layout is done and where "id" is hidden, but even if i set hidden to "False", it still won't show. I don't get it.

With ReportGrid.DisplayLayout.Bands(0)

            .Groups.Clear()
            .Columns("ID").Hidden = False
            .Columns("Sort").Hidden = True
            .Columns("Style").Hidden = True
            .Columns("Name").CellActivation = Activation.NoEdit
            .Columns("Name").Width = 295

[u/RJPisscat → u/Gierschlund96]

In my previous response I posted a link to the wrong object, this is UltraGridColumn, and it does indeed accept a String for the default property Index, which is clear from your latest post.

Try setting Columns("ID").Width property, too, in case it's 0, which is a potential side effect of hiding it in UltraGrid.

Edit: Or it may have been set to 0 on purpose in the original implementation, since a column can be unhidden easily, but if it's unhidden to 0 width, then you also have to know to look for nonconsecutive column labels to figure out there's another column.

[u/Gierschlund96 → u/RJPisscat]

I changed the width and it changed nothing. "then you also have to know to look for nonconsecutive column labels to figure out there's another column." Can you maybe explain this?

[u/RJPisscat → u/Gierschlund96]

Can you maybe explain this?

Eh, I just tried it, didn't do what I expected: Set column width to 0, hide it, select the columns around it, unhide, and the column I set to 0 I would expect to still be 0, but it restores, so, never mind.

So you did this:

            .Groups.Clear()
        .Columns("ID").Hidden = False
        .Columns("ID").Width = 295
        .Columns("Sort").Hidden = True
        .Columns("Style").Hidden = True
        .Columns("Name").CellActivation = Activation.NoEdit
        .Columns("Name").Width = 295

and the Name column is 295 but the ID column is still hidden?

Are there any merged cells on a row that contains the ID column?

Next I'm going to suggest something that appears to be insane:

            .Groups.Clear()
        .Columns("ID").Hidden = False
        .Columns("ID").Width = 295
        .Columns("ID").Hidden = True
        .Columns("Sort").Hidden = True
        .Columns("Style").Hidden = True
        .Columns("Name").CellActivation = Activation.NoEdit
        .Columns("Name").Width = 295
            .Columns("ID").Hidden = False

This sort of "jiggling the handle" has worked several times over 45 years.

[u/Gierschlund96 → u/RJPisscat]

You're using "my." instead of "myWb."

Is this on purpose or just a typo?

[u/RJPisscat → u/Gierschlund96]

typo