Blank columns issue when exporting to Excel (Data Only) from Crystal Reports

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.

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.