Filter excel file/datatable based on value's font color

Help Activities
, , , , , ,
1

Hello,

I have an excel file that I want to filter based on its font color and not cell color. Basically I want to retain the rows that has a font color and remove rows when all of the line items/ all columns in that row are in black font. What’s the most efficient way to do this? Thanks

2
  • Use this activity to indicate the Excel file you want to work with.

  1. Read Range:

    • Use this activity to read the data from the Excel sheet into a DataTable variable.

  2. For Each Row:

    • Use a “For Each Row” activity to loop through each row in the DataTable.

  3. Get Row Item:

    • Inside the “For Each Row” loop, use the “Get Row Item” activity to get the value of a specific cell in the current row.

  4. If Activity:

    • Use an “If” activity to check if the font color of the cell is not black.

      row(“ColumnName”, DataRowVersion.Current).ToString()
      This condition checks if the font color is not black.

  5. Remove Data Row:

    • Inside the “If” activity, use a “Remove Data Row” activity to remove the entire row if the condition is not met.

  6. Write Range:

    • Finally, use the “Write Range” activity to write the filtered DataTable back to the Excel file.

This approach allows you to iterate through each row, check the font color of the desired column, and remove rows where the font color is black.

Hope this helps
Usha

3

Thank you very much for the reply. I just have a question for the step 5. May I know what should be the output for row(“ColumnName”, DataRowVersion.Current).ToString()?

I tried it on my end and it only gives me the value of the cell and not the color. Also it gives me an error when I put it inside the condition of if statement.