Export and customize a crystal report in excel

I am having an issue is that while exporting a report to excel sheet, there are lots of spaces and empty cells between the data, as well as, the cells are merged. Is there is a way to export the report and each field will be in a cell or to control that exportation, suppose my report looks like this:

No Trans_No 1 123 2 333
In my excel sheet, I would like 
A B No Trans_No 1 123 2 333

, But currently it is showing a merging of the cells and spaces , so instead of Trans_No will be in CELL B, it is in D. So, is there is a way to control o export that?

32.5k 32 32 gold badges 115 115 silver badges 136 136 bronze badges asked Sep 20, 2010 at 21:08 43 1 1 gold badge 2 2 silver badges 4 4 bronze badges

3 Answers 3

mohs, welcome to StackOverflow.

Crystal Reports and Excel have very different methods and data structures. When exporting a .rpt into .xls format, Crystal has to make many compromises and judgement calls. Here are some suggestions:

  1. Do you absolutely need to use Crystal in this process? A. You can import data directly from your data source into Excel (without using Crystal) using Data->Import External Data.
    B. You can export from Crystal into CSV format. If the Excel file is being made just for a machine to read it, CSV is a better option.
  2. Keep your Crystal Report very simple. A. After you drag & drop fields onto your design, do not resize or overlap them.
    B. Make sure in your options, you have snap to grid checked.
    C. Are your fields horizontally aligned? If not, they will probably be put on different rows.
    D. If you are grouping data, you may want to suppress the group headers & footers.
answered Sep 21, 2010 at 13:26 11.7k 20 20 gold badges 65 65 silver badges 102 102 bronze badges

Hello PowerUser, yes I will export the report to excel and then a machine will read the values. The problem is that my report is very complex and lots of formulas and subreports which can not be done directly. While exporting to CSV file the name of the formulas and subreport formula name will be at the beginning and then the data. So what do you suggest?

Commented Sep 21, 2010 at 20:16

Make 2 versions of this Crystal Report: 1 for human eyes and 1 for machine reading. Then you can simplify the machine read-version and strip it down to just the fields you want. This can probably be done, you just need to put some work into revising your format.

Commented Sep 21, 2010 at 20:36

If you are finding empty rows between your data, you can filter these out in Excel:

I don't use Crystal Reports, but could you export to a CSV file, then import into Excel. The import will allow you to specify the delimiters and should format your data better.

answered Sep 20, 2010 at 21:48 Edward Leno Edward Leno 6,317 3 3 gold badges 35 35 silver badges 50 50 bronze badges

Edward, Crystal Reports lets you export your report to Excel. But it's an imperfect conversion that leaves alot of gaps and such.

Commented Sep 21, 2010 at 13:02 The problem is also that there are some merged cells which can't easily be figured out. Commented Sep 21, 2010 at 20:19

The merged cells are probably due to fields which aren't perfectly aligned with each other and the same size.

Commented Sep 22, 2010 at 13:00

From experience with exporting from older versions of Crystal to Excel, a couple of options:

(1) Export to CSV and open the CSV file in Excel.

This had the disadvantage that instead of appearing at the top of the report above the data values, the column headings would appear on every line of the output before the column values - like so:

No Trans_No 1 123 No Trans_No 2 333

This issue may have been resolved in CR XI - if not, the workround we used for this was to suppress column headings (so that only the values were included in the output), then copy and paste a standard spreadsheet heading for the report into the output in Excel.

(2) Consistently format all fields to the same, minimum size (typically, two grid widths), with columns aligned by snapping the left edge of fields to guidelines.

This produces output which is almost unreadable in the standard report viewer, but which should align correctly in Excel.