RE: Excel formatting issues when using Crystal Reports SDK for VS .NET
We are using Crystal Reports SDK for VS .NET in our windows application development in VS2010 .net. The Crystal Report Viewer is embeded in the WinForm and tied up to the ReportDocument to display the Crystal Report developed in Crstal Report 2011.
The Export button in the Crystal Report Viewer , which is part of the Crystal Reports SDK for VS .NET doesn’t give us the options to select “show gridlines” as in CR XI and to open the exported Export Report in Excel (Application option missing) .The only option is to store to a Disk file location and the Excel file stored doesn’t show grid lines.
So we started using the CrystalReports.Engine Api(s) with ExcelFormatOptions, and passed it as parameter to the ReportDocument.Export method.
When we do that we are getting formatting issues. the format of the Excel output is not matching the format of the Orginal Report in CR2011(i.e when it is exported using the Crystal Designer IDE)
Please suggest ideas to match the formats as our users are using the Excel outputs to feed other systems and hence the formatting becomes atmost importance for us to use Crystal Reports. I give below the code we use to achieve this so far
strRepFileName = Report_ID.Trim() + “.xls”;
dfDestinationOptions.DiskFileName = strRepFileName.Trim();
exOptions.ExportDestinationType = ExportDestinationType.DiskFile;
exOptions.DestinationOptions = dfDestinationOptions;
ExcelFormatOptions exopt = new ExcelFormatOptions();
exopt.ShowGridLines = true;
exopt.ConvertDateValuesToString = true;
exopt.ExcelAreaGroupNumber = 1;
exopt.ExcelAreaType = AreaSectionKind.Detail;
exopt.ExcelTabHasColumnHeadings = true;
exopt.ExcelUseConstantColumnWidth = true;
exopt.ExportPageBreaksForEachPage = true;
exopt.ExportPageHeadersAndFooters = ExportPageAreaKind.OnEachPage;
exopt.UsePageRange = true;
ExportOptions exOptions = new ExportOptions();
exOptions.ExportFormatType = ExportFormatType.Excel;
exOptions.ExportFormatOptions = exopt;
Well, re. missing export options. That would be considered an enhancement and the best place for those is the the SAP Idea Place. Other than that, below is a copy of best Excel export practices. I got this from Dell who often posts to these forums – I can’t remember if I got this from Dell’s post to one of the forum threads or website. In any case see the suggestions below. Dell, if you read this, perhaps you can let us know where the eck I got it from(?). I’m so bad for not saving the link…
Crystal Reports enables the export of data to MS Excel in two different ways – with formatting and “data only” without formatting. One of the major frustrations I’ve had is that columns don’t line up after the export. The other is that when exporting with formatting, there are lots of merged cells making it impossible to manipulate the data without first doing some major reformatting. So, I’ve developed a set of simple formatting guidelines that I use to make sure my reports export to Excel cleanly.
- If at all possible, use a Cross-Tab. They’re guaranteed to export well without any special formatting.
- Make sure the data and column header in each column line up exactly on the left and that each of the objects is the same width. Set up guides on the ruler above the report to help with this.
- Make sure that all of the objects in a row line up exactly. Set up guides on the ruler to the left of the report to help with this.
- ALL of the objects in a row MUST have data in them. If any object is blank, the objects to the right of it will move to the left to fill up the column. I’ve done this a number of ways:
- Set the default values on numeric fields so that they always show up as zeros.
- Account for nulls in any formula used on the report to make sure they return either a zero or a space.
- Sometimes I’ve had to put a tilde (~) or other meaningless character in a text block that is the same size and location as the object that may be blank. Set the font to the same color as the background so that it doesn’t show up when viewing the report and suppress the text block when the object it’s replacing is not null.
- If your column header is more than one row high, ALL of the column headers have to be the same height. Just adding carriage-returns, doesn’t solve the issue, there has to be at least a blank space on the line.
- When exporting with formatting, the best way to help prevent merged cells is to do the following:
- Use guide in the ruler at the top of the report to mark the left AND right of the first column.
- Using the guide, start the next column at the exact same spot where the previous column ended.
- Place a guide to mark the right side of this new column.
- Repeat until all columns are in place.
- For rows, make sure that the objects are placed at the very top of the section –right click on an object; select “Size and Position”; set the Y coordinate to 0.0. Then align all of the other objects in the row with the top of the one just set.
- Move the bottom edge of the section up to the bottom of the objects in the row.
Designing a Crystal Report so that it exports well to MS Excel requires a little bit of planning and a lot of precision. Some of the techniques I’ve outlined will cause a report to not look as clean when viewing it in Crystal, but will make it export in a clean, usable fashion.