Question How to import Excel worksheet to DataGridView keeping cell colors

rvercesi

Member
Joined
Oct 16, 2008
Messages
10
Programming Experience
5-10
Hi there

I am developing a small app to help manage huge Excel worksheets.
These worksheets have multiple cells colored within.
I am loading the worksheets to a DataGridView as show below but I want to keep the cell colors.
Any ideias on how to achieve this? Would help if I didn't have to cycle all cells as the worksheets have near to on hundred columns and close to four hundred rows.

Regards

VB.NET:
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
 
                Dim connectionString As String = String.Format(connectionStringTemplate, sFileName)
                Dim sqlSelect As String = "SELECT * FROM [Sheet1$];"
                ' Load the Excel worksheet into a DataTable
                Dim workbook As DataSet = New DataSet()
                Dim excelAdapter As System.Data.Common.DataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)
                Try
                    excelAdapter.Fill(workbook)
                    Dim worksheet As DataTable = workbook.Tables(0)
                    dgv.DataSource = worksheet
 
                Catch
'MANAGE EXCEPTION
 
                End Try
 

HairyMike

Member
Joined
Oct 1, 2008
Messages
20
Programming Experience
3-5
100 columns * 400 rows shouldn't take too long..

I think your only option is to loop through the table to colour them in.

Maybe use a background worker thread to do this.
 

IBgemeda

New member
Joined
Dec 24, 2010
Messages
3
Programming Experience
Beginner
Good Day.

rvercasi, I 'm having the same issue as you. would you be so kind as to post the solution if you have found it. The request goes out to anyone else who might have the solution.

Much thanks and Regards,
ibgemeda
 

MattP

Well-known member
Joined
Feb 29, 2008
Messages
1,206
Location
WY, USA
Programming Experience
5-10
I'm not sure how to do this with ADO.NET but you can access the cell's color using Interop.

VB.NET:
        Dim xlApp As New Excel.Application
        Dim xlWB As Excel.Workbook = xlApp.Workbooks.Open("C:\Temp\TheFile.xlsx")
        Dim xlWS As Excel.Worksheet = xlWB.Sheets(1)
        xlWS.Activate()
        Dim xlRange As Excel.Range = xlWS.Range("A1", "B10")

        For Each RowRange As Excel.Range In xlRange.Rows
            For Each CellRange As Excel.Range In RowRange.Columns
                MessageBox.Show(CellRange.Interior.Color)
            Next
        Next
 
Top Bottom