hi Knime developers,
Getting an error as Providing input table is not a valid XLS control table, while doing colour to the column headers as below workflow
colur_to_Headers.knwf (14.3 KB)
Colur_to_headers.xlsx (8.8 KB)
one my column has special characters but that data need to be present. and then i need to colur on full headers and column names as broader.
Team please provide solution.
Hi Knime team,
Any update having anyone knows about this?
Does knime team had any idea on thuis?
Hi @IJpin_02 and welcome to the forum.
I think the error message pretty much tells the tale here. I used a String Manipulation node to remove the +? characters and everything seemed to work fine. I think you could replace the +? characters again at the end of the workflow right before your Excel Writer .
thanks ScottF,
So your solution was saying that need to use 2 String Manipulation nodes at one from starting and another from ending before writer node. okay i will try in that way.
need another help @ScottF can you give me another solution where in the given excel sheet has column “Amount” once i load in knime it changes to double type format but again when i retrieve that data into excel writer it is changing into still integer only.
Colur_to_headers.xlsx (8.8 KB)
amount column has “Double” format while it is in knime workflow. but when i tried to deploy the data in excel sheet amount column was showing “Integer” format. let me know the solution.
I don’t understand how that’s possible. The value 0.011 alone should prevent that from happening.
Can you upload an example workflow demonstrating the problem?
Hi ScottF,
Can you suggest if multiple columns having special characters then String Manipulation Node was not possible.
where as for the latest version knime there was node String Manipulation node(multi column) but currently i am using knime version 4.1.
sample excel file:
Colur_to_headers.xlsx (8.7 KB)
can you provide another solution
Hi ScotF,
i tried this workflow from knime where the excel file having a column named as “Amount” the data type was storing as “Integer” but inside knime workflow i changed to “Double Format”, so it is changing the data type.
But while i appending into excel sheet the date of column “Amount” still data type was "integer only.
Workflow_Format.knwf (10.3 KB)
Step 1: while loading the excel file see the below screenshot
step 2: after changing the data type from Integer to Double:
Step 3: while i deploy the data into excel sheet see the output:
see it was still the Amount column as Integer format only.
→ it doesn’t change data type but it changes the data type in knime workflow.
Copy of format_issue.xlsx (9.0 KB)
I think you could try the Column Expressions node for this as well, although you’d have to have multiple expression inside to account for the various columns you want to update.
I believe this is just a formatting issue in Excel, which often doesn’t respect data types in the way we would hope. If you change the formatting in Excel from “General” to “Number” it will display like you want it to.
The actual value of the numbers is not affected.
Thanks Scottf,
Can you provide some guidance on how to change formatting in excel from “General” to “Number”
Sure. Right click on the column to change:
Then:
I just realized that it might also be possible to do this using a XLS Cell Formatter node, but I haven’t tested it yet:
So can we able to change the format for Amount column through XLS Cell Formatter
Hi ScottF,
Is there any option without changing the format from excel file i.e general to number. while i changed it didn’t worked.
still i am facing issue why the column “Amount” data type was not changed. while it loaded in workflow where in knime it is changed to “Double” but still when i deployed it is not changed still it was as Integer format.
Workflow_Format.knwf (10.3 KB)
FOrmat_issue11.xlsx (10.0 KB)
I think we are talking in circles a bit. You likely won’t be able to change the format of how the number appears. From what I can tell, this is an Excel issue, not a KNIME limitation.
The only way I can think of that might be a workaround is what I mentioned above about the XLS Cell Formatter, but I’ve not had time to try it out myself yet.
Hi Team,
Colour on columns are working for single sheet only, can you help me here if it were multiple sheets what was the solution ??
– having 2 excel sheets in a single excel file so need to add colour for the headers on both sheets. provide some help me on this.
Issue_formatt.xlsx (11.1 KB)
colur_to_Headers00.knwf (32.3 KB)
Hi Team, Is there any other option
From Knime workflow where a column as “Amount” data type is showing “Double” but once it appended into excel workbook, data type was changed.(it is not showing as Double ).
→ I am getting a data from email and i directly saved data into one excel sheet and loaded into excel reader node after that i changed the data type of column Amount to Double and then appended into Excel workbook but the same column Amount data type was not be as “Double”.
→ my main concern once a datatype was assigned in knime workflow, why it should be changed the same datatype while it appended back to excel sheet.
below is the just a sample data;
DataType_issue.xlsx (9.0 KB)
please confirm me whether a sample data coming source i.e from mail, while pasting entire data into excel sheet without proper format and save that excel. then load the same excel file into excel reader and changed every column data type in knime, and then once that data was appeneded into excel sheet again datatype was not changed.
→ i.e in knime work flow data type was changed but in excel sheet it didn’t changed.
Hi @IJpin_02,
this workflows shows an example on how to format multiple sheets: 22_XLS_MultipleSheets – KNIME Hub
The key node is the XLS Sheet Selector node.
Cheers
Kathrin
