I’m wondering if anyone has any ideas / experience with needing to write accounting format cells to Excel?
Principally, I need to be able to write the following accounting formats:
3 letter currency code and value (examples: USD 100.00 / GBP 100.00 / AUD 100.00, and so on)
Currency symbol and value (examples. $ 100.00 / £ 100.00, and so on)
I can create the format, but I need the values written in the accounting format for the end consumers of the Excel file.
My challenge is two fold:
(1) Having looked through the Continental formatting nodes and the forum, I can’t see how to write these formats
(2) The currencies will change - cell A1 maybe USD 100.00, cell A2 maybe GBP 100.00, and so on.
Could you please copy & paste here in text format your first data snapshot (using the formatted text option </>) ? It will help us to provide you with a solution from there. Thanks in advance.
I had a look at your data to realize that the format you need is a special “accounting” format in Excel which provides this kind of formatting (Format Numbers as Currency) :
I had a look at the Continental plugin and it seems that this “accounting” format is not among those configurable by its nodes.
I believe the best people to ask are the Continental plugin developers (@Continental_KNIME_Extension_Developer) who implemented this plugin to check whether there is a possible solution to your problem.
Thanks for bringing this up.
Our node (XLS Cell Formatter) is not limited to the preset, but accepts arbitrary cell formats.
You could try the following format:
I missed that this was for Excel. A much easier solution is to format the columns in excel after a write and use this node. It is pretty much my go to for Excel now since it is such a time saver Vs separate commands for every little adjustment. I tend to put a lot of work into clean presentations.
Thanks for reaching out and for your prompt reply. I’m impressed by the solution. I hope it solves @Unlockedata needs or at least gives a first option to proceed further. I believe that the problem is not just the “accounting” format but also the fact that every cell might have a different currency which complicates things. Having said this, a more elaborated solution might eventually come from the formatting you suggest. @Unlockedata, what do you think ?
Thanks too to Continental for your great Excel plugin and for making it available to the KNIME community.
Is NodePit setup as an Available Software Site in your Preferences? If not there are instructions below. Just make sure that the update site matches your KNIME version #. You can also install directly via downloading the ZIP file, but it will not remain linked for version updates.
Usually, you start the installation process with two information available, the update site (e.g. https://download.nodepit.com/4.6) and the product name (e.g. NodePit for KNIME). Both information is listed on each and every node and product page on NodePit.
After starting KNIME, go to File → Preferences → Install/Update → Available Software Sites, click on Add… and paste the update site. Make sure the update site matches your current KNIME version.
Hi @iCFO,
I understand the argument and I think your proposed solution works fine for static tables. I haven’t used it yet, since the industrial use cases I know often include dynamic table layouts (different number of plants, colored groups of rows, etc.). This is the strength of XLS Formatting nodes.
Also in regards to the topic of this forum topic where dynamically the formatting needs to change the currency based on data content – possible with the XLS Cell Formatter, but not with a static Excel template, as far as I know.
That may be true for a basic excel user, the limitations are very much on the other side for power users. Don’t get me wrong here… I love your nodes and use them for basic formatting in some situations. However, the template approach unlocks infinite possibilities for me.
Basic formatting: I conditionally format base global templates to be self adapting complex formatting presentations. (I reference to a separate sheet with additional conditional control settings populated by KNIME to get added flexibility) I can also conditionally hide / shoe columns and rows. This way Excel just self adapts to any changes with a few simple control / override settings.
The more important strengths: I do not issue static excel reports or user tools. Reports are meant to prompt decisions or perform some action for the users. With the template approach you can keep complex interactivity controls, visualizations, macro action controls. It opens up a lot of easily portable dashboard capabilities for scenario tests so that the users can quickly dial in decisions, or just a nice clean presentation and friendly controls that can be used to drive further automation.
Either approach works. Preference really depends on your wheelhouse and presentation complexity. However, if you need “on the fly” conditional formatting that adapts to user interactions then template is the only current solution.
It is also not a one or the other choice thanks to the Excel Cell Updater. In some workflows I have left in place prior setups with your nodes to handle tables, then incorporated the write to template node in order to get conditional formatting in situations such as user interaction changing formula results.
