Hi @kienerj , it can be done but there are a few obstacles in the way.
(1) How to put a line break into the data cell in KNIME
(2) How to make get the link break into Excel
(3) How to make Excel actually display the line break even once it is there!
(1) Putting a line break into the data cell in KNIME
So the first things. I’ll use a Table Creator which I have to say has never been my favourite node!
I don’t think there is a way to place a linebreak manually into a table creator cell (and I think you asked about that about a year and a half ago!) so what I will do is enter a character into the cell that I wish to become a line break.
Here I have typed
Hello¬World
Testing¬Testing¬has this worked?
with the ¬ character which is a character I never normally use, located on the top left key of my keyboard .
I can then use String Manipulation to turn that into a line break, using regexReplace:
regexReplace($column1$,"¬","\n" )
If I look at the output of String Manipulation, that has worked
(2) Getting the data into Excel
The data can be written to Excel using the Excel Writer but on first inspection it appears that it hasn’t worked
However, if we actually inspect the cell in Excel, we can see that the data has been written with line breaks. Looking at the formula panel in Excel it clearly shows that it is multi-line
(3) How to make Excel actually display the line break even once it is there!
The problem is that by default, Word Wrap is not switched on in Excel so on the sheet, it displays as a single line text.
Highlighting the cells and formatting them, setting the Wrap Text option on fixes the display in Excel
That may be sufficient, and you could even create a macro in Excel with a toolbar button that you press to highlight all cells and turn on Wrap Text. Not perfect, but it might suffice. It’s the simpler “cheaper” solution.
Of course, ideally we would want it to be wrapped automatically and this is where it becomes a bit more complicated. It can be done but to do it, you may need to utilise the Continental XLS formatting nodes.
The workflow is now going to go from this:
to this:
I use a few components to make my life easier when using the continental nodes. I’m not going to “redo” my “tutorial” on what this does, but you can find the one I wrote a couple of days back here:
But the “quick summary” is that following the writing to the XLSX, the continental nodes allow us to apply formatting, and to do that we use the XLS Formatter (apply) node which reads in the XLSX file that has just been written by the Excel Writer and writes it to a new file (different name) that contains formatting that has been defined by the XLS Formatter nodes (of which there are many), and those XLS Formatter nodes apply formatting to things called “tags” which identify the cells to which formatting is to be applied.
The XLS Cell Formatter node is configured like this:
And this tells it to turn on Word Wrap on all cells identified by the tag “data”, which is defined by my XLS Control Table Auto Tag Generator component and represents all cells except the table headings.
Here is the demo workflow. I’ll leave it at that for now. I suggest if you want to work with the Continental Nodes that you take a look at the above link to the other post, and then also the links on that post to other documentation and posts. Links for the components used here are also on that other post. You don’t have to use my components, but if you don’t you’ll need to teach yourself more about how the “tags” work.
Line break in Excel cell.knwf (287.8 KB)
Incidentally, if you have already included \n in your text, the String Manipulation for turning that into actual line breaks is
regexReplace($column1$,"\\\\n","\n" )
(note the “double-escaping” of the literal \n so that it is correctly interpreted by String Manipulation)