Icon

Merging Cells with Continental Nodes

There has been no title set for this workflow's metadata.

Demo workflow for merging cells in columns where the String values of the columns have been repeated

Create "control table" based on location of empty cells which follow non-empty cells in each column Add empty row as first row of control table, and then pass the whole table to the "Control TableGenerator" Get the distinct set of "merge tags" that we have generated. I have been lazy and generated a merge tag for every cell, even ifit is a single cell Create the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the file name,and then apply formatting to that file using the XLS Formatter (apply) node Apply the XLS Continental formatting to the merge tags. State that the merge tag represents a cell to be merged, and also specify that on mergeing, the verticalalignment is to be "centred". Iterate through the list of merge tags and appy this to each ALTERNATIVECreate the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the filename, and then apply formatting to that file using the XLS Formatter (apply) node Alternative with Components ALTERNATIVECreate the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the filename, and then apply formatting to that file using the XLS Formatter (apply) node Alternative with Components Combining with auto tag generator and a pre-definedXLS Control Format Template component sample datablank all repeated cells(takbb component)if the cell isempty, set it to 0otherwise set it to 1only going tomergeString columns(bottom branch)cumulative sum the1s and 0sdownwardsThis means blankcells below a non-blank cellwill be allocated the same number as the non-blank cell abovepopulate each cell with a unique tag name for the blockof cells with the samemerge number.This incorporates the loop iterationnumber so different columns will get different merge tagsAdd merge tagto the modelgenerate the controltable from the suppliedtag namesloop through allcolumnsto find where cells should be mergedend the splititerate each of the merge tagswrite datato xlsxapply formatting and open formatted versionin Excelcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)add blank rowwhcih we want to add to top of table (hence the pair of sorters)This provides detail that thereare no merge tags forthe heading rowrepeatfor eachmerge tagCollect modelfrom current iteration.This component allowsus to build a modelwiithin a loop and collect it after the loopcompletes(takbb component)retrieve xls format model collected within theloop(takbb component)repeat for all columnsproduce singlecolumns list of all tagsdistinct listof tags in "ColumnValues"columnaddverticle alignmentto middlefor merge tagcreate merge control table(takbb component)make the "merge" tags(takbb component)apply formatting and open formatted versionin Excelcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)write datato xlsxsample datacreate merge control table(takbb component)apply formatting and open formatted versionin Excelmake the "merge" tags(takbb component)write datato xlsxcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)sample datagenerate tagssets headings to black/yellowno banding of rows Table Creator Blank Down Strings String Manipulation(Multi Column) Column Splitter Moving Aggregator String Manipulation(Multi Column) XLS Cell Merger XLS ControlTable Generator Column ListLoop Start Column Appender Table Row toVariable Loop Start Excel Writer XLS Formatter(apply) Edit PathVariable Filename Add Empty Rows Sorter Sorter Variable Loop End XLS FormatCollector XLS FormatRetriever Loop End (ColumnAppend) Unpivot DuplicateRow Filter XLS Cell Formatter XLS Format Cell MergeAuto Tag Generator XLS Format CreateCell Merge tags XLS Formatter(apply) Edit PathVariable Filename Excel Writer Table Creator XLS Format Cell MergeAuto Tag Generator XLS Formatter(apply) XLS Format CreateCell Merge tags Excel Writer Edit PathVariable Filename Table Creator XLS Control TableAuto Tag Generator XLS ControlFormat Template 0 Create "control table" based on location of empty cells which follow non-empty cells in each column Add empty row as first row of control table, and then pass the whole table to the "Control TableGenerator" Get the distinct set of "merge tags" that we have generated. I have been lazy and generated a merge tag for every cell, even ifit is a single cell Create the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the file name,and then apply formatting to that file using the XLS Formatter (apply) node Apply the XLS Continental formatting to the merge tags. State that the merge tag represents a cell to be merged, and also specify that on mergeing, the verticalalignment is to be "centred". Iterate through the list of merge tags and appy this to each ALTERNATIVECreate the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the filename, and then apply formatting to that file using the XLS Formatter (apply) node Alternative with Components ALTERNATIVECreate the XLSX file and write the data to it. Modify the file path with the addition of a suffix to the filename, and then apply formatting to that file using the XLS Formatter (apply) node Alternative with Components Combining with auto tag generator and a pre-definedXLS Control Format Template component sample datablank all repeated cells(takbb component)if the cell isempty, set it to 0otherwise set it to 1only going tomergeString columns(bottom branch)cumulative sum the1s and 0sdownwardsThis means blankcells below a non-blank cellwill be allocated the same number as the non-blank cell abovepopulate each cell with a unique tag name for the blockof cells with the samemerge number.This incorporates the loop iterationnumber so different columns will get different merge tagsAdd merge tagto the modelgenerate the controltable from the suppliedtag namesloop through allcolumnsto find where cells should be mergedend the splititerate each of the merge tagswrite datato xlsxapply formatting and open formatted versionin Excelcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)add blank rowwhcih we want to add to top of table (hence the pair of sorters)This provides detail that thereare no merge tags forthe heading rowrepeatfor eachmerge tagCollect modelfrom current iteration.This component allowsus to build a modelwiithin a loop and collect it after the loopcompletes(takbb component)retrieve xls format model collected within theloop(takbb component)repeat for all columnsproduce singlecolumns list of all tagsdistinct listof tags in "ColumnValues"columnaddverticle alignmentto middlefor merge tagcreate merge control table(takbb component)make the "merge" tags(takbb component)apply formatting and open formatted versionin Excelcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)write datato xlsxsample datacreate merge control table(takbb component)apply formatting and open formatted versionin Excelmake the "merge" tags(takbb component)write datato xlsxcomponent used to easily adda suffix to a path variable so thata second path var can besupplied to the XLS Formatter (apply)node(takbb component)sample datagenerate tagssets headings to black/yellowno banding of rowsTable Creator Blank Down Strings String Manipulation(Multi Column) Column Splitter Moving Aggregator String Manipulation(Multi Column) XLS Cell Merger XLS ControlTable Generator Column ListLoop Start Column Appender Table Row toVariable Loop Start Excel Writer XLS Formatter(apply) Edit PathVariable Filename Add Empty Rows Sorter Sorter Variable Loop End XLS FormatCollector XLS FormatRetriever Loop End (ColumnAppend) Unpivot DuplicateRow Filter XLS Cell Formatter XLS Format Cell MergeAuto Tag Generator XLS Format CreateCell Merge tags XLS Formatter(apply) Edit PathVariable Filename Excel Writer Table Creator XLS Format Cell MergeAuto Tag Generator XLS Formatter(apply) XLS Format CreateCell Merge tags Excel Writer Edit PathVariable Filename Table Creator XLS Control TableAuto Tag Generator XLS ControlFormat Template 0

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: BATEB031
Created at: 2023-12-21
On NodePit since: 2023-12-21
Last update: 2024-12-24
Created with KNIME version: v5.2.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!