What does your input data look like and what is its data type? If you plan to write the data to Excel you can use the Continental formatter nodes. I may be wrong, but I don’t think there’s a way inside KNIME to use a numeric type with a $ sign - strings only.
My Input datatype is round double, as I need to have 2 decimal places.
I need to write this date to a xlsx.
Can you help with example on how to use continental nodes.
MY Input looks like this
Excel templates can be used to address formatting issues without changing the data type of the original data, but a pre defined Excel template is required.
I provide two methods for comparison, one is to add a currency symbol before the number, and the other is to use an Excel template.
Excel Template.knwf (36.2 KB)
Tip: This workflow was written using version 5.2.3 of KNIME, and I am not sure if there will be any issues opening it in other versions.
Hi @chaithuj , if you do want to try the Continental Nodes, there can be a bit of a learning curve, when you want to just “get it done”. For this reason, I wrote a couple of components to assist. I have two different approaches below depending on your use case.
In both cases below you will need to have the Continental Nodes extension installed. In the first case, the Continental Nodes are encapsulated behind the scenes and remain invisible to you:
Here you would use my XLSX Simple Excel Format Apply component
This makes it really simple to set a standard output for ALL doubles:
The downside is that if you have data where only a subset of the Doubles are currency, it would format them all as currency:
The second option is to actually use the Continental nodes for more fine-grained formatting. But the learning curve on this extension, in my view, is in working out how to define the “control table” and “tags”, which is what it uses to define ranges of cells to be formatted. I hope that by using the technique below, this learning curve is significantly reduced.
…Because this is where my other component comes in… and I use this whenever I am working with the Continental Nodes :
This component generates a control table with “tags” for a wide variety of common use cases.
Each column is allocated a tag “c:columnname”
The header row is allocated a tag “header”
Data rows are allocated the tag “data”
and more besides.
For this data table:
It automatically generates this control table without you having to configure anything:
edit: at the risk of being accused of going a bit “over the top” …
If you want some of the ease of the first option, but with the fine-tuning of the second option above, there is a further component I can introduce: XLS Simple Format Controller
Drop that in after the Auto Tag Generator like this…
As usual @takbb has provided elegant solutions. I’m always in awe of his contributions. Here’s a simple workflow which uses a very basic structure to convert your base numbers to dollars.
I always like to throw in the Write to Excel Template node in threads like this as well. It is a lesser known option, but is infinitely easier / quicker to work with and also opens up the doors to some amazing advanced Excel integrations. (Conditional formatting, user interface designs, dashboard style presentations, easy user friendly control settings for complex KNIME components, etc.)
If it is just basic formatting, then I find the quickest solution is to use the Excel Writer to first output your unformatted Excel file. Open the excel file and setup your formatting, then clear the data. Then point the Excel Write to Template node toward that as your template file. Super quick and easy. I also like to keep my template files in the workflow data folder so they remain bundled with the workflow if you export it for sharing.
Thanks @rfeigel and I agree @iCFO that Write to Excel Template opens up many more of Excel’s features.
My outputs to Excel are generally much simpler tabular reports, and I was (kind of) determined to make it easier for me to use the Continental nodes to write quickly formatted data without any manual editing of Excel.
Without a doubt, my components to perform Control Table generation, and to make the formatting “simple”, are (behind the scenes) some of the scariest to look at, that I have written!
My clients are spoiled. Before the Write to Excel Template node and your formatting components came along (Thanks for those by the way!), I would quickly knock out a workflow and then put a full day + into trying to clean up the formatting piece by piece and sheet by sheet with the Continental nodes. Even after that, I was regularly spending hours adjusting them based on the clients ongoing formatting requests.
I could do an entire web series just on the cool tricks and solutions that are opened up with the Template approach. I would go as far as to say the the Template node by @AnotherFraudUser dramatically improves the overall KNIME AP software functionality as a whole by filling the gaps of sharable and portable user interactive settings / reporting with live calculations. You can do amazing things in Component interactive views if you spend an extreme number of hours dialing it in and creating workarounds, but they are complex / require constant manual refreshes / are limited in formatting flexibility & UI customization. Complex interactive components also require a good bit more update maintenance and have limited portability options as they need to be deployed via sharing the entire workflow for local use, Teams Hub or Business Hub. I am not putting interactive view based components down… I certainly use and love them! I just think too many users see KNIME as an “Excel replacement”, when it is also an Excel multiplier (and vice versa). The more heavily I leverage how much KNIME and Excel compliment each other, the more I am convinced that they combine into the ultimate multi-tool!
Many of my more complex in-house components now have the bulk of their UI and Settings in an excel file which can be launched for changes via a manual setting the Component’s Interactive view.