Excel export creates unwanted cell merges, especially in subreports

[drummer54]

I have reports that must be included in sets, thus the use of subreports, and that must be exported to Excel so that our CEO and clients can sort the data to their liking.  I've been able to mostly remove unwanted cell merging using the net.sf.jasperreports.export.xls.column.width property, but only when exporting the report as the main report.  When the report is included as a subreport and exported the numerous unwanted cell merges occur.  Is there any way to rectify this?

Image of report exported as subreport:

Image of report exported as main report, with manageable cell merges:

[butlerc]

I couldn't see a problem on the files you attached - although it's hard to paste files on here.

Mergin of cells is mostly down to bad report design.

If your using subreports, try to plan ahead and use the same size column widths and make sure you allign the subreports in sync in your master.  Excel will create a merge of cells if any of cells in your reports overlap.

For example if in main report column 1 has width of 100, col2 width 100 and in subreport 1 column 1 has width of 50, col 1 width 100 this will cause a merge to occur in excel because your reports are not alligned.

 

Hope this helps

[drummer54]

So it affects subreports in Excel even if the subreports are saved to different worksheets?  What if the data being presented in each report doesn't lend itself to identical column widths?  SQL Server Reporting Services doesn't have this problem.  Not that I think it's an especially good tool, because it has a lot of faults, but it does handle creation of Excel columns well.

The only way I have figured out how to mitigate this effect is to move all of my column headings into the title bar, make a single field with the date/time and place it over a data column that is the same width as the date/time field, and make certain that the report title is the same width as the data column under it.  All of this seems to be an unnecessary restriction - again, SSRS handles this quite well.  Our standard is that the report title is placed at the top left and the date/time is placed at the top right of the report.  If I have a long report title (and we have many that are rather lengthy) or a right most data column that is very narrow (narrower than required by the date/time stamp), it fouls up the cells when exporting to Excel.  Or we define cell widths just to accommodate this "bad programming" (vs bad report design) whether it fits the data or not.

[drummer54]

I have to say it is really ridiculous that the subreports have to have the same number of columns and that the columns all have to be the same width to get this to work properly.  I've also tried using tables to achieve this and the behavior is the same - if the tables either have a different number of columns or if the column widths differ then you get merged cells that prevent the user from sorting, even though each table, as is the case with the subreports, is being saved to a different worksheet.  SSRS can handle this so I don't see where it's a fault of Excel, it's something in JasperReports.