topic How to read Excel values including color information in Product Forum

How to read Excel values including color information

sumire — Mon, 13 May 2024 08:05:37 GMT

Hello,

I have a Excel table like this:

I would like to get the values of this table as collection, including color information.
Like this:

I'm looking for a suitable method.
If you know of a better way, please let me know.

Re: How to read Excel values including color information

Tejaskumar_Darji — Mon, 13 May 2024 11:02:17 GMT

Hi, we did some different solutions that involved excel with font color. You will have to build a custom excel filter that can apply a filter based on color and pass input color hex code.

Re: How to read Excel values including color information

sumire — Mon, 13 May 2024 23:41:12 GMT

Hello @Tejaskumar_Darji ,

Thank you for your reply.
What I know is that I use "Get Background Color" action inside "MS Excel Booster" VBO to get the color information.
https://digitalexchange.blueprism.com/dx/entry/78038/solution/spgmi--ms-excel-booster
Is there any other way you are suggesting? If you know of a new method please let me know.

I need to process an Excel sheet that uses color to indicate data types, and I'm having a really hard time.
I don't want them to create such an Excel in the first place... 😞

Re: How to read Excel values including color information

faheemsd — Tue, 14 May 2024 10:49:13 GMT

Dear @sumire

Could you please confirm if the color values are fixed in your excel or is it changing randomly?

Re: How to read Excel values including color information

Mohamad_Dakkouri — Tue, 14 May 2024 10:56:13 GMT

Hi,

You can add a new action that can give you the RGB Codes

1. Go to the object studio SGPMI MS Excel Booster

2 Add a new action and name it for example (Get font color)

3 Add this code

Try

dim C as long
dim R as long
dim G as long
dim B as long

C = GetWorksheet(handle, Workbook, Worksheet ).Range(cellref,cellref).DisplayFormat.Font.Color

R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
GetCellRGB = "R=" & R & ", G=" & G & ", B=" & B

Catch ex as exception

exceptionMessage = ex.message

End Try

for Input provide this

For output this :

And you will have an output like this for example if your cell font is in red, you will have <R=255, G=0, B=0>

Hope it will help you

Re: How to read Excel values including color information

sumire — Wed, 15 May 2024 01:34:38 GMT

Hello @faheemsd ,

Thank you for reply.

Color values are fixed.
Each value is color-coded by type. For example, the large size is blue, the medium size is yellow, and the small size is red.

Re: How to read Excel values including color information

sumire — Wed, 15 May 2024 01:44:22 GMT

Hello @Mohamad_Dakkouri ,

Thank you for your reply.

I also wrote a similar article. (Sorry, I wrote this article in Japanese, so please use machine translation to read it)
https://qiita.com/Sumire_Neko/items/e119bbbdaa9c57e9e930
Since there are hundreds of target cells, reading the color information by moving each cell one by one is time-consuming.
Therefore, I'm looking for a faster way to do this.

Re: How to read Excel values including color information

Mohamad_Dakkouri — Wed, 15 May 2024 14:28:38 GMT

Hello @sumire

You need to create a CUSTOM Action Like this for example :

For inputs you put :

Notice :

column_where_color_is_present is the index column. In your case it's column A, so you put 1

column_result is where you stock the result so you can put 2 or 3 depends on where you want to stock your values.

For Ouptut :

And inside the code you put this

Try
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
Dim i As Long

For i = start_row To last_Row

If Not IsDBNull(GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Value) AndAlso Len(GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Value) > 0 Then
C = GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_where_color_is_present).Font.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
Dim GetCellRGB As String = "R=" & R & ", G=" & G & ", B=" & B
GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_result).Value = GetCellRGB
Else
GetWorksheet(handle, Workbook, Worksheet).Cells(i, column_result).Value = ""
End If

Next i

Catch ex As Exception
exceptionMessage = ex.Message
End Try

You need to make a loop to find your wanted results, we make the loop inside the vbo 🙂

Your process should look like this

Your CUSTOM VBO inputs in the process like this :

Ensure that you dont have empty value inside your column A

At the end you will have this results

Please mark as best answer if my code helped you 🙂

regards

Re: How to read Excel values including color information

faheemsd — Wed, 15 May 2024 20:17:47 GMT

Dear @sumire

We can achieve this with the help of macro code.

Create an Excel template with the macro enabled workbook and copy all your original excel data into the Macro Excel template and then Run the Macro with the help of Blue Prism. output will be created in the Sheet2 and you can read the Sheet2 data as a collection in Blue Prism.
My Excel Data

My Macro Name: GetFontColorsInColumnA

After running the above Macro output will be generated in Sheet2

Macro Code:

Function GetColorName(colorValue As Long) As String
' This function converts the font color value to a known color name
' You can add more color mappings if needed

Select Case colorValue
Case RGB(0, 0, 0)
GetColorName = "Black"
Case RGB(255, 255, 255)
GetColorName = "White"
Case RGB(255, 0, 0)
GetColorName = "Red"
Case RGB(0, 255, 0)
GetColorName = "Green"
Case RGB(0, 0, 255)
GetColorName = "Blue"
Case RGB(255, 255, 0)
GetColorName = "Yellow"
Case RGB(255, 165, 0)
GetColorName = "Orange"
Case RGB(128, 0, 128)
GetColorName = "Purple"
Case RGB(255, 192, 203)
GetColorName = "Pink"
Case RGB(128, 128, 128)
GetColorName = "Gray"
Case RGB(192, 192, 192)
GetColorName = "Silver"
Case RGB(0, 128, 0)
GetColorName = "Dark Green"
Case RGB(0, 128, 128)
GetColorName = "Teal"
Case RGB(128, 0, 0)
GetColorName = "Maroon"
Case RGB(128, 128, 0)
GetColorName = "Olive"
Case RGB(0, 0, 128)
GetColorName = "Navy"
Case RGB(75, 0, 130)
GetColorName = "Indigo"
Case RGB(255, 69, 0)
GetColorName = "Orange Red"
Case RGB(255, 20, 147)
GetColorName = "Deep Pink"
Case RGB(139, 69, 19)
GetColorName = "Saddle Brown"
Case RGB(210, 105, 30)
GetColorName = "Chocolate"
Case RGB(245, 245, 220)
GetColorName = "Beige"
Case RGB(220, 20, 60)
GetColorName = "Crimson"
Case Else
GetColorName = "Other"
End Select
End Function

Sub GetFontColorsInColumnA()
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim cell As Range
Dim fontColorName As String
Dim fontColorValue As Long
Dim lastRow As Long
Dim outputRow As Long

' Set the input and output worksheets
Set wsInput = ThisWorkbook.Sheets("Sheet1")
On Error Resume Next
Set wsOutput = ThisWorkbook.Sheets("Sheet2")
If wsOutput Is Nothing Then
Set wsOutput = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsOutput.Name = "Sheet2"
End If
On Error GoTo 0

' Clear any existing data in the output sheet
wsOutput.Cells.Clear

' Write headers to the output sheet
wsOutput.Cells(1, 1).Value = "Name"
wsOutput.Cells(1, 2).Value = "Font Color"

' Get the last row with data in the input sheet
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

' Iterate through each cell in column A of the input sheet
outputRow = 2 ' Start from row 2 to skip header
For Each cell In wsInput.Range("A2:A" & lastRow)
fontColorValue = cell.Font.Color

' Determine the font color name
fontColorName = GetColorName(fontColorValue)

' Write the name and font color name to the output sheet
wsOutput.Cells(outputRow, 1).Value = cell.Value
wsOutput.Cells(outputRow, 2).Value = fontColorName

outputRow = outputRow + 1
Next cell
End Sub

Please use the above macro code in the excel workbook.

Open the Excel work book with Blue Prism and Run the Macro as shown in below

Please try this and it will give the the response with in seconds and let me know if you have any doubts on this

@sumire wrote:
Hello,
I have a Excel table like this:
I would like to get the values of this table as collection, including color information.
Like this:
I'm looking for a suitable method.
If you know of a better way, please let me know.se add this macro code in the Excel workbook

Re: How to read Excel values including color information

faheemsd — Wed, 15 May 2024 20:40:15 GMT

Dear @sumire

We can achieve this with the help of the macro.

Create a Excel template with Macro Enabled workbook, copy your original excel data into the Macro Enabled workbook and the run the macro which will give the results in Sheet2
Sample Excel Data

Macro name and Macro code: GetFontColorsInColumnA

Sub GetFontColorsInColumnA()
Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim cell As Range
Dim fontColorName As String
Dim fontColorValue As Long
Dim lastRow As Long
Dim outputRow As Long

' Clear any existing data in the output sheet
wsOutput.Cells.Clear

' Write headers to the output sheet
wsOutput.Cells(1, 1).Value = "Name"
wsOutput.Cells(1, 2).Value = "Font Color"

' Get the last row with data in the input sheet
lastRow = wsInput.Cells(wsInput.Rows.Count, "A").End(xlUp).Row

Please add the above code in the Macro Enabled worksheet

My output:

Try with Blue Prism to generate the output in Sheet2 and read as Collection

Please try the above approach and let me know if you need any help.

Re: How to read Excel values including color information

sumire — Thu, 30 May 2024 09:13:52 GMT

Thank you for your help.

The results of considering this issue are as follows:

Add a symbol to identify the color before colored text using an Excel macro
Get an Excel sheet into a collection in a Blue Prism process
Add a column 'color' and write in the 'color' column while removing symbols to identify the color.
Use the Excel macro to delete the color identification symbol added to the Excel sheet.

topic How to read Excel values ​​including color information in Product Forum

How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

Re: How to read Excel values ​​including color information

topic How to read Excel values including color information in Product Forum

How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information

Re: How to read Excel values including color information