Crystal Reports Online Training

Learn Online, Anytime, Anywhere

Step-by-step online tutorials.

19.10 Exporting to Excel

Formatting for Excel

Exporting a report to an Excel spreadsheet gives you options to work with the report areas and how the columns are formatted. The columns can be formatted so that they have headers and that they are all the same width. Table 19-8 shows the properties used for setting the Excel formatting.

Table 19-8. ExcelFormatOptions Properties.

Property Description
ExcelAreaGroupNumber The base area group number if the area type is group area.
ExcelAreaType The area type if you aren’t using constant column width.
ExcelConstantColumnWidth The width of each column (if using constant column width).
ExcelTabHasColumnHeadings Boolean that determines if the columns have headings listed.
ExcelUseConstantColumnWidth Boolean that determines if the columns are the same width.

Exporting to Excel is similar to Word. The export process automatically inserts a footer at the bottom of each page that shows “Powered by Crystal” and the Crystal Decisions logo. If you want a continuous flow of data and you don’t want these page footers appearing in your spreadsheet, you have to open the spreadsheet afterwards and clean it up manually. There is no way to keep this from happening prior to the export.
Listing 19-6. Setting the format to be an Excel spreadsheet.
Public Sub SetFormatExcel(ByVal MyReport As CrystalDecisions.CrystalReports.Engine.ReportDocument, ByVal UseConstantColumnWidth As Boolean, ByVal ColumnWidth As Integer, ByVal UseColumnHeadings As Boolean)
MyReport.ExportOptions.ExportFormatType = _
CrystalDecisions.[Shared].ExportFormatType.Excel
Dim Options As CrystalDecisions.Shared.ExcelFormatOptions = _
New CrystalDecisions.Shared.ExcelFormatOptions
Options.ExcelUseConstantColumnWidth = UseConstantColumnWidth
Options.ExcelConstantColumnWidth = ColumnWidth
Options.ExcelTabHasColumnHeadings = UseColumnHeadings
MyReport.ExportOptions.FormatOptions = Options
End Sub

The listing first sets the format type to Excel. After setting the format type, create an options object and set the properties that determine how to format the columns. This procedure sets the properties for the column width and whether the columns should have headings.