Following up on my article on correcting disappearing headers, a further issue with our web-report is that even an export to Excel (Data Only) from Crystal Reports 9, created extra columns.  This issue could not be replicated on the client machine (ie. from my workstation).  I would have to upload the report to the server, configure the web application to use the newly uploaded report, export to a Crystal Report, which in turn we export to Excel:

Note that as this is from within a web application, there are no extra options or dialogs to select.  The export had to work from this point on.  I can't take any credit for the below as it was copied from http://hosteddocs.ittoolbox.com/ST030504.pdf.  A "Crystal Decisions" documentation.

 

Blank columns appear when exporting to Excel.

 
There are four main causes for extra blank columns to appear in a worksheet.
 
Cause 1:  Fields in the same column are not aligned with guidelines or snap-togrid
To prevent fields from appearing in different rows, horizontally align fields with guidelines:
  1. In Design view, click the vertical ruler to create a vertical guideline.
  2. Click one of the fields in the section, and drag it to the vertical guideline.
  3. The field should ‘snap’ to the guideline. When the corners are highlighted in red, this indicates the field is snapped to the guideline.
  4. Repeat step 2 until all the fields in the section are snapped to the vertical guideline(s).
  5. Set the object’s size and position by right-clicking the field, selecting Object Size and Position and then setting the XY coordinates.
  6. Use the horizontal and vertical rulers as a reference when specifying height, width, and XY coordinates.
  7. Export the report to Excel and all the fields in the section appear in the same column. Extra blank columns will not appear.
 
Cause 2: Objects in the same column have different widths
To ensure all the objects in the same column have the same width:
  1. Select all the objects (such as database fields, column header text objects, and summaries or subtotals) in the column by holding down the Shift key while clicking each field, until all the fields are highlighted.
  2. Right-click any of the fields, click Size, and then select Same Width. This widens all the fields in the column until they are the same width as the widest object in the column.
  3. Export the report to Excel and on extra blank columns will appear in the worksheet.
NOTE:  Fields formatted so they are much wider than the actual size of the data can cause blank columns. Make sure the field width is just wide enough to display the longest data value.
 
Cause 3: Fields and objects in the same column do not have the same text alignment
For example, the column heading text object is aligned to the right, while the database field is aligned to center.
To ensure all objects have the same alignment:
  1. Select all the objects (such as database fields, column header text objects, and summary or subtotals) in the column by holding down the Shift key while clicking each field, until all the fields are highlighted.
  2. Right-click any of the fields, then click Format Objects. The Format Editor dialog box appears.
  3. From the Common tab, specify an alignment in the Horizontal Alignment box.
  4. Click OK to return to the report. All the objects in the same column now have the same alignment.
  5. Export the report to Excel and there will be no extra blank columns in the worksheet.
 
Cause 4: Fields or objects with different font size and formatting (bold, italics) can cause blank columns
To ensure the same font formatting for all objects in a column:
  1. Select all the objects (such as database fields, column header text objects, and summary or subtotals) in the column by holding down the Shift key while clicking each field, until all the fields are highlighted.
  2. Right-click any of the fields, then click Format Objects. The Format Editor dialog box opens.
  3. From the Font tab, specify a size and format for the object’s box.
  4. Click OK to return to the report.

Add comment


Send

Comments   

+2 SAnthosh Sunday, 2nd February 2014, 5:00 pm
Hi,

I'm using Crystal report to get the reports in both PDF and Excel output format.
I'm using this is peoplesoft to fetch the data from the table into report.
PDF is looking fine and in excel there is blank columns in the middle of the report.
I did all the changes as you told but still its showing the blanks.Please suggest.

Thanks & Regards,
Santhosh
0 Martin D Wednesday, 13th February 2013, 7:31 pm
Hi!

Can anyone help me please? I've done a crystal report with just a cross-tab table which has one column and 2 field rows.

Whe I run it in PeopleSoft application to XLS output, The columns are not fitted with columns in Excel. I mean, the only column I've in the report fits in 5 or 6 columns in excel, and I need it to fit in one. How can I do that? Is it possible?

Thnx in advice!
+1 Joel L Thursday, 14th February 2013, 11:45 am
Hi Martin,

Would you be able to get away with concatenating the values of a row into a single string? Then it would fit in one column (albeit a big column).
+2 Reem Wednesday, 18th April 2012, 11:17 am
:sad: :sad: :sad:
after I export my report to excel format data only or all data I cann`t beable to count the subtotal in excel it dose not work at all .Plz any one can help
0 Joel L Thursday, 14th February 2013, 11:43 am
Hi Reem,

Sounds like you should get the report to count the subtotal and then get it to export.
+1 Mastered S Wednesday, 7th March 2012, 12:35 am
Thanks for the great tips, this really helped us a bunch at work, we've had this problem with all our reports and using the guidelines (and being very careful designing the report) works like a charm!
+3 trocoso Monday, 30th January 2012, 1:52 pm
Sometimes, completely unaligned headers does export right... as I said, CR is not logical. Please, CR, do a favor to the world and quit from this business.
+2 trocoso Monday, 30th January 2012, 1:49 pm
Arghhh... Crystal is not a logical tool. The fixes are working in most of the cases, but there are still 2 reports (out of 42) that doesnt show headers.
My customer is very upset for this... he wants a logical explanation that I can not give. As a developer, I like to have all my products under control, with CR this is impossible... next time I'll not choose CR.
+1 rajendra Thursday, 29th September 2011, 7:59 pm
I have placed two database fields under one column header , one below the other. when I export to excel, it is not appearing in one cell. The bottom field is appearing at different place and the top field is appearing after two rows under same column.
+1 Joel L Friday, 30th September 2011, 1:56 pm
Hi rajendra,

I think I've tried that for a table where the headings were on the left (per row) and data in the second column. The only time that worked was when there would only be one row returned per data. In all other cases, datasets are returning the rows and not columns.

If you are asking about alignment or missing headers, please refer to the first part of this article at joellipman.com/.../...

Cheers!

Joe
+1 Nisha P Wednesday, 14th September 2011, 6:54 am
Hey Guys,

Can anyone help me out. I am facing an issue like after exporting the report data in excel data only format my header elements are missing. Tried each and every suggestion mentioned above, but couldnt found the solution.

Please help me out :o ..Really need this to be done
0 Joel L Wednesday, 14th September 2011, 12:27 pm
Hi Nisha,

Thanks for the comment. Did you first read my article on "Crystal Reports: Exporting to Excel omits column headers"? The article above was an expansion but the previous article is what fixed this problem for me. www.joellipman.com/.../

For us, the solution was simply ensuring that all Header elements were the same width and height and all properly aligned.

Hope that helps!

Joe
+1 Anirban Wednesday, 20th April 2011, 1:18 pm
Hey man good solution but I think the best and easy solution is check the width of your table in the report and then for all the header elements either set the same width. Ie: Say your table width is 15 then set all header elements width 15. it is that simple man.
+2 Nick Monday, 13th June 2011, 2:04 pm
Anirban:
What do you mean by "table width"? There is no "table" in an Excel report. Do you mean Report width? Or what?
Thank you.
+1 Joel L Monday, 13th June 2011, 2:58 pm
Yes that kinda makes two of us who don't know what you mean by table width. Following the logic wouldn't you say that if the column width is 15 then header elements should be 15...?

No you're right Nick. I don't know what that means. But I did fix this issue by making each data cell fit correctly in the columns. When they were all various sizes, the report behaved erratically.

Though I also found that what other people put on the web wasn't necessarily right. It was just what worked for them. Your report has to be looked at in terms of design. I'm not sure how much of a margin/padding there should be for each textbox so I would suggest getting just the first column working and then getting the next one working (based on settings of the first?) and so on.
+1 Nick Monday, 13th June 2011, 4:32 pm
Yeah Joe, I could do one column at a time, make sure it works, then go to the next. But what I was hoping for, though, was a solution with less trial & error. Because if you have say, 8, 9 or 10 columns, well, doing one column at a time could take a while.