Write Excel: string and number within the same column

[nealwzz]

I’d like to export my data to an Excel file. My table contains columns (string type) with numbers (treated as strings) and string data types. After using the Write Excel node, I end up with an xlsx file that displays warnings in the cells stating “numbers are stored as text.” I’m trying to prevent this issue but haven’t been successful so far. I’ve attempted to create a new column with an unspecified data type in the Excel Write node, but that didn’t work. Any assistance in resolving this would be greatly appreciated in advance!

[yogesh_nawale]

Hi @nealwzz,

You can try using Column Auto Type Cast node before the Excel Writer node to change the column type automatically

Column Auto Type Cast

Regards,
Yogesh

[mlauber71]

@nealwzz If you need this you will not be able to just export the data but might have to use something like openpyxl to fill each cell in Excel and change the format.

One option could be to write all cells as string from knime and then have a code converting ‘suitable’ cells into numbers without loosing the content.

Do you know which cells will be numbers or do you have a list that can be translated into Excel cell logic like A1, B19 and so on?

[nealwzz]

Thank you for your response. However, the Column Auto Type casting has determined the type as a string. Despite the fact that my column contains three rows with the value “inactive,” the majority of the other rows are numeric.

[nealwzz]

Thank you for your response. My table is structured with one row of strings serving as titles, followed by rows of numeric data. Subsequently, there is another row of titles and more numeric data. I wanted to ask if the “code” you mentioned is still available within the KNIME environment?

[mlauber71]

@nealwzz what you can do is use a Python script to try to convert each cell to a numeric value and then export to Excel from within the Python node. That way you can have mixed cells in one column.

# Iterate over the dataframe and apply numeric conversion where applicable
df_numeric = df.applymap(lambda x: pd.to_numeric(x, errors='ignore'))

# Save to Excel
file_name = 'output_mixed_format.xlsx'
df_numeric.to_excel(file_name, index=False)

You can expand this by also formatting date cells:

Excel - convert dataframe to mixed cells – mlauber71

[nealwzz]

Thank you very much. Your advice was indeed helpful. I was able to successfully convert my data types. I have another question, if you don’t mind. In my table, there’s a column named ‘curve’, which is derived from the Dose Response (R) node followed by an Image to Table conversion. However, when I used the Python script for Excel output, the ‘curve’ column did not display as expected images but rather as a string similar to ‘<PIL.PngImagePlugin.PngImageFile image mode=RGBA size=800x600 at 0x127C28A10>’. Currently, the only way I can resolve this is by saving the images locally first and then reading them back in, but this significantly reduces the efficiency of my analysis. I would greatly appreciate any further assistance you can provide.