Filter rows by color

I have an excel file with multiple sheets having same column names. But i need to extract some rows from each sheet that are already colored by the user.
Is there any way to extract those rows just be identifying the cell color?

1 Like

Hi @r_jain,

It’s not the most elegant solution, but you could first filter the Excel file by color. When reading in the Excel file, ensure β€œSkip hidden rows” is selected in the Advanced Settings section of the Excel Reader node.

Kind regards

Hello @r_jain
Coding the data gather with R or Py seems the way to go

xlsx package can handle this task with R :

Another approach from Py coding is by using xlrd :

BR

I Installed python extension and got the code

for row in ws[column_letter]:
color_table.append(row.fill.start_color.index)
print(color_table)
df = pd.DataFrame(color_table)

However df is a dataframe but its not allowing me to work with knime as an knime table output.

I tried finding out solution to convert dataframe into output_tables[0] but it is failing.
knio.output_tables[0] = knio.Table.from_pandas(df)

The error:-

β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, 4, 4, 4, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’, β€˜00000000’]
Executing the Python script failed: Traceback (most recent call last):
File β€œβ€, line 21, in
File β€œC:\Program Files\KNIME\plugins\org.knime.python3_4.7.0.v202211291350\src\main\python\knime\api\table.py”, line 341, in from_pandas
return _backend.create_table_from_pandas(data, sentinel, row_ids=row_ids)
File β€œC:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_table.py”, line 136, in create_table_from_pandas
return _create_table_from_pandas(data, sentinel, row_ids)
File β€œC:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_table.py”, line 121, in create_table_from_pandas
data = kap.pandas_df_to_arrow(data, row_ids=pandas_row_ids)
File β€œC:\Program Files\KNIME\plugins\org.knime.python3.arrow_4.7.0.v202211291117\src\main\python\knime_arrow_pandas.py”, line 107, in pandas_df_to_arrow
return pa.Table.from_pandas(df)
File β€œpyarrow\table.pxi”, line 3480, in pyarrow.lib.Table.from_pandas
File β€œC:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 609, in dataframe_to_arrays
arrays = [convert_column(c, f)
File β€œC:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 609, in
arrays = [convert_column(c, f)
File β€œC:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 596, in convert_column
raise e
File β€œC:\Program Files\KNIME\plugins\org.knime.pythonscripting.channel.v1.bin.win32.x86_64_4.7.0.v202211160931\env\lib\site-packages\pyarrow\pandas_compat.py”, line 590, in convert_column
result = pa.array(col, type=type
, from_pandas=True, safe=safe)
File β€œpyarrow\array.pxi”, line 313, in pyarrow.lib.array
File β€œpyarrow\array.pxi”, line 83, in pyarrow.lib._ndarray_to_array
File β€œpyarrow\error.pxi”, line 123, in pyarrow.lib.check_status
pyarrow.lib.ArrowTypeError: (β€œExpected bytes, got a β€˜int’ object”, β€˜Conversion failed for column 0 with type object’)