I have written several times over the years about exporting reports to Excel. My first post was in 2005 and follow-ups in 2008, 2012 and 2015. It took much trial and error but I eventually found solutions to most of the challenges.
So I was a bit surprised to find a 15-page document dedicated to exporting to Excel on the SAP website. It was originally published years ago, but exactly when is hard to say. The web page that hosts the document says it was created in 2008 and updated in 2012. But the page footer inside the PDF has a time-stamp from 2006 right next to a copyright of 2004. Obviously it isn’t new and was available when I was struggling with these exports. It would have been nice to know about the document. In some ways it goes into more depth, like explaining how all of the export options work.
But my posts cover several points that are not covered or not covered clearly in the SAP document. For instance, they don’t provide any help when it comes to subreport data. The also document points out 3 main reasons you will get extra rows:
1) Objects not horizontally aligned in a section
2) Objects in a section have different heights
3) Complex conditional suppression formulas.
It also mentions that extra space at the bottom of a section can cause extra rows. But the document never clearly explains that extra space at the TOP of a section does the same thing. It took me several years to figure that one out. In other words, the objects could be all the same height, be perfectly aligned, have no conditional suppression and all be touching the bottom of the section. Despite all that you would still get extra rows if there was a sliver of space between the top of the objects and the top of the section. To fix this you can use the “Size and Position” property to set the Y position to zero, but this can only be done one object at a time. Or you can just push the objects against the top of the section. One trick I use is to select all the objects in a section and align them, and then hit [CTRL-up arrow] until the row jumps to the next section. Then hit [CTRL-down arrow] once. They should jump right back into the original section and should end up at position zero.
So you should certainly download this document. I have, and have added it to my Useful Links page. But you should also note the links above to get the full picture.