?
Solved

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

Posted on 2016-11-25
6
Medium Priority
?
57 Views
Last Modified: 2016-11-29
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
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41901803
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
 

Author Comment

by:printmedia
ID: 41901812
I get an error message stating that "Leading . can only appear inside a With statement"
0
 

Author Comment

by:printmedia
ID: 41901815
When I put your code within the "With" statement it created new columns after the ones with data.
0
Industry Leaders: 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!

 
LVL 34

Expert Comment

by:Norie
ID: 41901941
Do you have working code for formatting the headers?
0
 

Author Comment

by:printmedia
ID: 41902308
The code I have is the one I provided in my initial question
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41902569
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question