Results 1 to 6 of 6

Thread: How to import Excel worksheet to DataGridView keeping cell colors

  1. #1
    rvercesi is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Oct 2008
    Posts
    10
    Reputation
    77

    Question How to import Excel worksheet to DataGridView keeping cell colors

    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

    Code:
    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

  2. #2
    HairyMike is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Oct 2008
    Posts
    20
    Reputation
    77
    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.

  3. #3
    rvercesi is offline VB.NET Forum Newbie
    .NET Framework
    .NET 2.0 (VS 2005)
    Join Date
    Oct 2008
    Posts
    10
    Reputation
    77
    Thank You Very Much

  4. #4
    IBgemeda is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.0
    Join Date
    Dec 2010
    Posts
    3
    Reputation
    0
    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

  5. #5
    MattP is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Feb 2008
    Location
    WY, USA
    Posts
    1,206
    Reputation
    590
    I'm not sure how to do this with ADO.NET but you can access the cell's color using Interop.

    Code:
            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

  6. #6
    Budius is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5
    Join Date
    Aug 2010
    Location
    UK
    Posts
    137
    Reputation
    110
    Quote Originally Posted by rvercesi View Post
    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.
    that's the way through....

    there isn't any 'adater' to do it faster.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Harvest time tracking