Click here to Skip to main content
14,920,381 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am not able to hide column in an excel sheet...i tried the below code..please help me out

What I have tried:

VB
Public Function setHeaderColumns(ByVal pck As ExcelPackage, ByVal columns As IEnumerable(Of ExcelEntity), ByVal WorksheetName As String) As ExcelPackage
            'create worksheet object
            Dim ws As ExcelWorksheet
            If (pck.Workbook.Worksheets.Count > 0) Then
                ws = pck.Workbook.Worksheets(WorksheetName)
            Else
                ws = pck.Workbook.Worksheets.Add(WorksheetName)
            End If
            Dim ColumnIndex As Integer = 1
            'set the name, color or hide the columns in excel
            For Each col As ExcelEntity In columns
                Dim excelColumn As ExcelColumn = ws.Column(ColumnIndex)
                ws.Column(ColumnIndex).Hidden = Not col.isActive

                'excelColumn.Hidden = Not col.isActive
                ws.Cells(1, ColumnIndex).Value = col.fieldText
                ws.Cells(1, ColumnIndex).Style.Font.Bold = True
                ws.Cells(1, ColumnIndex).Style.Font.Color.SetColor(Color.Black)
                If col.isRequired Then
                    ws.Cells(1, ColumnIndex).Style.Font.Color.SetColor(Color.Red)
                End If
                ws.Column(ColumnIndex).Width = 35
                ColumnIndex += 1
            Next
            Return pck
        End Function
Posted
Updated 1-Dec-16 20:38pm
v2

1 solution

Try:

VB
ws.Column(ColumnIndex).EntireColumn.Hidden = Not col.isActive


If this does not work directly you might have to create a selection first and apply the .EntireColumn.Hidden to the selection, one of the odd behaviours of VBA.

Easiest way to find code for tasks in Excel is to record a macro executing the task then look at the code generated and apply it in your code.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900