Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

How to highlight the first row in a different color without knowing how many columns beforehand in vb.net

Hi all.

I have a Visual Studio project that displays a datagridview control, I created a button that when clicked, it will export the datagridview control results to Excel. I would like to highlight the first row which is the column headers a different color, but the problem is that the number of columns can vary each time the datagridview control is populated with data. Sometimes there will be 7 columns other times there may be 10 etc.

Below is the code I have so far for exporting to Excel. Thank you in advance for any help!

Private Sub btnExcelExport_Click(sender As Object, e As EventArgs) Handles btnExcelExport.Click

        Dim oXLApp As Excel.Application       'Declare the object variables
        Dim oXLBook As Excel.Workbook
        Dim oXLSheet As Excel.Worksheet

        oXLApp = New Excel.Application  'Create a new instance of Excel
        oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
        oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet

        oXLApp.Visible = True               'Show it to the user

        With oXLApp
            Dim Col As DataGridViewColumn
            Dim i As Integer = 1
            For Each Col In DataGridView1.Columns
                .Cells(1, i).Value = Col.HeaderText
                i += 1
            Next
            i = 2
            Dim RowItem As DataGridViewRow
            Dim Cell As DataGridViewCell
            For Each RowItem In DataGridView1.Rows
                Dim j As Integer = 1
                For Each Cell In RowItem.Cells
                    .Cells(i, j).Value = Cell.Value
                    j += 1
                Next
                i += 1
            Next

        End With

        'oXLSheet.Range("P:P").EntireColumn.Delete()

        oXLSheet.UsedRange.Borders.LineStyle = 1
        oXLApp.Columns.AutoFit()

        oXLSheet.Range("A:A").Font.Bold = True

        oXLBook = Nothing       'Disconnect from Excel (let the user take over)
        oXLApp = Nothing
        oXLSheet = Nothing

    End Sub

Open in new window

0
printmedia
Asked:
printmedia
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
HI,

pls try

For Each Col In DataGridView1.Columns
    .Cells(1, i).Value = Col.HeaderText
    .Cells(1, i).Interior.ColorIndex = 6
    i+= 1
Next

Open in new window

Regards
0
 
printmediaAuthor Commented:
I get an error message stating that "Leading . can only appear inside a With statement"
0
 
printmediaAuthor Commented:
When I put your code within the "With" statement it created new columns after the ones with data.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieData ProcessorCommented:
Do you have working code for formatting the headers?
0
 
printmediaAuthor Commented:
The code I have is the one I provided in my initial question
0
 
Rgonzo1971Commented:
was the new code
Private Sub btnExcelExport_Click(sender As Object, e As EventArgs) Handles btnExcelExport.Click

        Dim oXLApp As Excel.Application       'Declare the object variables
        Dim oXLBook As Excel.Workbook
        Dim oXLSheet As Excel.Worksheet

        oXLApp = New Excel.Application  'Create a new instance of Excel
        oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
        oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet

        oXLApp.Visible = True               'Show it to the user

        With oXLApp
            Dim Col As DataGridViewColumn
            Dim i As Integer = 1
            For Each Col In DataGridView1.Columns
                .Cells(1, i).Value = Col.HeaderText
                .Cells(1, i).Interior.ColorIndex = 6
                i += 1
            Next
            i = 2
            Dim RowItem As DataGridViewRow
            Dim Cell As DataGridViewCell
            For Each RowItem In DataGridView1.Rows
                Dim j As Integer = 1
                For Each Cell In RowItem.Cells
                    .Cells(i, j).Value = Cell.Value
                    j += 1
                Next
                i += 1
            Next

        End With

        'oXLSheet.Range("P:P").EntireColumn.Delete()

        oXLSheet.UsedRange.Borders.LineStyle = 1
        oXLApp.Columns.AutoFit()

        oXLSheet.Range("A:A").Font.Bold = True

        oXLBook = Nothing       'Disconnect from Excel (let the user take over)
        oXLApp = Nothing
        oXLSheet = Nothing

    End Sub

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now