Home / Tech Tips / Crystal Reports / Crystal Reports Export to Excel – Part 2
InfoSol Blog Tech Tip

Crystal Reports Export to Excel – Part 2

It’s been a while since I published part 1 but hey, better late than never.  So here’s Part 2 of my previous blog “Crystal Reports Export to Excel – Part 1”. I have used the same report from Part 1.

The following tips & tricks will help you in providing a perfect/clean export to Excel from Crystal Reports (Refer to the Part 1 blog for Tips 1 through 6)

Tip 7 – Printer Setting: I created a copy of the report which I used for my Part and when I tried to open the report on a different system, I get the warning message below.

1. crystal reports export to Excel - printer message

 

 

 

 

Here’s what you need to do to get rid of this message irrespective of the source system. Navigate to File -> Page Setup and remove the check mark next to “Dissociate Formatting Page size and Printer paper size”. You can also change the orientation and this will be retained in the Excel print orientation as well

2. crystal reports export to Excel - dissociate formatting page options

Tip 8 – Alternate row colors: Most of the users love alternate row colors for their reports. I’m going to show a method that I did first and why it wasn’t an accepted solution by the Business user. I will then discuss the work around solution to accomplish the user’s request

On the Details section navigate to “Section Expert -> Color” Tab and select the check mark next to Background color.

3. crystal reports export to Excel - Section expert background color details

Click on the formula workshop icon and enter the formula shown below. The comments will show the different colors that you can use. You could use RGB codes as well.

4. crystal reports export to Excel - Formula Workshop

Now the report shows up alternate colors.

4. crystal reports export to Excel - Alternate Colors

I though I was done, but when our user exported this to Excel, it was found that all the column in excel had alternate colors too. They wanted alternate colors only for the cells/columns that had data. I had to remove the unwanted column coloring.

6. crystal reports export to Excel - remove color from empty cells

Tip 9 – Alternate colors for selected cells : Select every single object on the report, right click and select Format Field.

7. crystal reports export to Excel - format fields

In the border Tab, enter the same formula that we had created.

8. crystal reports export to Excel - Formula Worshop Format Editor

Also make sure the formula that was added in the Section Expert is removed or commented

9. crystal reports export to Excel - Formula Worshop Format Editor comments

The crystal report preview looks the one below and the excel output looks crisp with alternate colors only for the cells with data.

10. crystal reports export to Excel - Preview with colors only for cells with data

11. crystal reports export to Excel - Excel report

Tip 10 – Grid Lines : to have grid lines on the excel, select File -> Export -> Report Export Options – > Microsoft Excel and select the option “Show gridlines”

12. crystal reports export to Excel - Gridlines

Stay tuned for my next blog post.

About Rajesh Ponnurangam

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

Design and Development Best Practices for Mobile Dashboards

Design and Development Best Practices for Mobile Dashboards

The relevance of BI on mobile devices is not under question any more – we ...

2 comments

  1. What happens when the user, after exporting to Excel, sorts the data? I’m guessing that the color stays with the cell.

  2. Hello Susan,

    When the sorting is done after the export, the alternate coloring does gets messed up and it might look like a mix of adjacent and alternate coloring (Depending on the data that’s sorted). It’s better to do the sorting before the export.

    Thanks,
    Rajesh