how to merge cells dynamically in JXLS API in transformed template

Question

I am using Jxls API

i can now create , pass data lists to the template which creates excel output sheet as desired

but now i have to merge the column cells which carry same value

this is my for each tag for repeating cells

<jx:forEach items="${dummyData}" var="dummy" groupBy="col1">
<jx:forEach items="${group.items}" var="myList123">
${myList123.col1} ${myList123.col2} ${myList123.col3} ${myList123.col4} ${myList123.col5} ${myList123.col6} ${myList123.col7}
</jx:forEach>
</jx:forEach>

Answer 1

I make a custom jx:each-merge command to do the auto merge things. But need to pay attention that I am using org.jxls:jxls:2.4.2, org.jxls:jxls-poi:1.0.13 and having a main-sub data structure (means the data has been grouped by before). You can see the template and the rendered result from the bellow picture:

code example:

public void xls() throws Exception {
  // from template
  InputStream template = getClass().getClassLoader().getResourceAsStream("templates/each-merge.xls");

  // output to
  File out = new File("target/each-merge-result.xls");
  if (out.exists()) out.delete();
  OutputStream output = new FileOutputStream(out);

  // template data
  Map<String, Object> data = generateData();

  // render
  JxlsUtils.renderTemplate(template, data, output);

  // verify
  assertThat(out.exists(), is(true));
  assertThat(out.getTotalSpace() > 0, is(true));
}

You can get the source code from the github project simter-jxls-ext. The test class is EachMergeCommandTest.java.

The data structure is:

[
  {
    sn: 1, 
    name: 'row1',
    subs: [
      {sn: '1-1', name: 'row1sub1'},
      ...
    ]
  },
  ...
]

Hope this is helpful.

Answer 2

I am not sure if this will help. But here is my idea

1. Generate the file like you showed above and then save the file
2. Reload the file using apache POI and then implement your logic to merge the required cells. POI has below function that I have used

You can use sheet.addMergedRegion(rowFrom,rowTo,colFrom,colTo); from POI library

I never worked on Jxlx, but looks like they do not have this feature.