To specify how a document is exported to Excel, our reporting tools provide the property XlsxExportOptions.ExportMode. Here are the three possible values of that property:
| Name |
Description |
| SingleFile |
The document is exported to a single file. In this mode, page headers and footers are added to the resulting XLSX file just once, at the beginning and the end of the document. |
| DifferentFiles |
The document is exported to multiple files, one report page per file. |
| SingleFilePageByPage |
The document is exported to a single file, but an individual sheet is created for each page. |
As you can see, if the report document is split into parts during the export process, this always happens along page boundaries. If you wanted to split the report into individual files or sheets per group instead of page, the only solution in the past was to use the Spreadsheet Document API from the Office File API product suite as described in the article How to export reports to different sheets in an XLSX file by using Spreadsheet Document Server.
In v19.1.4 we made this possible without any additional components. Read on to find out the details.
The RollPaper Property
The name of this property might sound old-fashioned, but it can be used to solve the problem described above. In v19.1.4 we fixed an issue for this feature, so that page breaks are now recognized correctly when RollPaper is true.
Here is a report with one group level:
The following image shows the same report with a group footer band inserted. The height of the band is zero and its PageBreak property is set to AfterBand:
The property XtraReport.RollPaper has now been enabled. In the Preview tab you see a document with several pages of different lengths. Each page represents one group.
When this report is exported to Excel using the export mode SingleFilePageByPage, each report group occupies its own sheet in the resulting file:
To download a complete sample project, simply point your browser to the following GitHub repository: DevExpress Reports - How to Export Different Report Groups to Different Sheets in an XLSX file.
Your Feedback Counts
We are interested in your thoughts about the suggested solution described in this post. For the future, we will consider a special export mode SingleFileGroupPerPage. Using this mode, the preview could show “normal” page sizes, while the export would still create the same result you see above. Please submit your responses to this short survey to let us know your priorities:
Free DevExpress Products - Get Your Copy Today
The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the
DevExpress Support Center at your convenience. We'll be happy to follow-up.