Tips to Export Crystal Report (.rpt) to Excel
Tips to achieve the same report structure when you export into Excel:
Following points will help to avoid the unwanted cell spaces and formatting issue while exporting .rpt file to Excel files.
- Remove unwanted sections in the report. If the sections are not used either suppress or delete it.
- Avoid more spaces between each object. For example if you have more space between each objects then when you are export to excel, the space will be considered as each cells & the data will be shifted randomly. To avoid the cells shifting, insert the blank text objects in between the objects.
- Even for the small space try to insert the blank text objects.
- For conditional objects try to create in the different sections.
– For example I have condition to display two fields i.e. Grade A and Grade B based on revenue value.
– If revenue is more than 60000 then we need to display Grade A field else Grade B. This condition we need to give at 2 objects suppression formula.
– In this case the .rpt file will display only one field object at report. But when you export to excel it will take 2 cells for both field objects. To avoid that just create 2 different sections for example you just create detail 1 for Grade A and Detail 2 for Grade B with above suppression logic.
5. We can’t export lines and tables when you are export to Excel data only.
6. After you drag & drop fields into your designer, don’t resize or overlap them. Make sure that you have selected options snap to grid.
7. Select the Can Grow option for all the fields in the report by default. This will avoid the overlapping.
These are main point’s needs to be remember, when you are exporting the .rpt file to .xls file.
Great pointers. For more ideas, see the file attached in the SCN Discussion thread Why export format Excel is not usable as excel?
Also see:
http://scn.sap.com/docs/DOC-6687
- Ludek
Is there any similar Excel-Export Best Practices document for CRYSTAL REPORTS for ENTERPRISE 4.1...?
Totally missing my CR-2013 Horizontal and Vertical GUIDELINES - and Field-Binding options today....because the "smart guidelines, smart default formatting" in CR4E doesn't seem very SMART right now...?
Mark, I agree completely. Why have VERY useful features been removed, or at best hidden? I'm having to spend way too much time trying to get fields lined up right next to each other so there's no space between them, because all the reports I create get exported to Excel for the users.
However, since you've yet to get an answer, I'm not optimistic anyone will answer, even if the answer is "sorry, we took that away, and oh yeah, smart guidelines are really just dumb and of no help at all." (I really hope I'm wrong and we'll get some help... please?)
Sorry, but I forgot about some footer fields. Therefore, I must apologize for my frustration showing through, and my forgetting about the footer; it's been awhile since I've created new reports from scratch (and now in CR 2013, whereas before it was in CR XI). Once I aligned the footer fields with the detail fields the "mysterious" extra columns disappeared. (It'd still be nice not to have to resort to manual calculations and setting of values.)
How can I prevent the export to excel merging rows & leaving blank rows?
eg: when detail section objects/fields wrap to more than 1 line please?
eg: name may appear on 1 line, but address may wrap over 2 or more lines for some (not all), any fields/objects that have more than 1 line export as merged rows in excel but all the 1 line values are not merged, leaving blank cells underneath their values.
This is the case when 'can grow' is used OR when the height of the crystal field/object is enough to fit 2 or more lines of text in it.
If the purpose of the report is to be exported and not viewed, you could turn "Can Grow" off for the address fields. This will cause all of the address data to appear in a single cell instead of merging into multiple rows.
-Dell