Exporting to 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-5 shows the properties used for setting the Excel formatting.

Table 19-5. ExcelFormatOptions Properties.

The following listing is very similar to the previous listing. It first creates an ExportOptions object variable, myExportOptions, and tells it that the export file will be saved to a disk file using the the DiskFileDestinationOptions object. The difference is that the format options object is specific to Excel and sets the properties listed in Table 19-5. After setting those properties, it calls the Export() method as the previous listing did.

Listing 19-2. Setting the format to be an Excel spreadsheet.
[VB.NET]
Public Sub ExportToExcel(ByVal Filename As String, ByRef myReport As CrystalDecisions.CrystalReports.Engine.ReportDocument, ByVal UseConstantColumnWidth As Boolean, ByVal ColumnWidth As Integer, ByVal UseColumnHeadings As Boolean)
Dim myExportOptions As New CrystalDecisions.Shared.ExportOptions
'Set the export to a disk file
myExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
Dim myDestinationOptions As CrystalDecisions.Shared.DiskFileDestinationOptions
myDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions
myDestinationOptions.DiskFileName = Filename
myExportOptions.ExportDestinationOptions = myDestinationOptions
'Set the Excel formatting properties
myExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.ExcelRecord
Dim myFormatOptions As CrystalDecisions.Shared.ExcelFormatOptions
myFormatOptions = CrystalDecisions.Shared.ExportOptions.CreateExcelFormatOptions()
myFormatOptions.ExcelConstantColumnWidth = ColumnWidth
myFormatOptions.ExcelUseConstantColumnWidth = UseConstantColumnWidth
myFormatOptions.ExcelTabHasColumnHeadings = UseColumnHeadings
'Assign the formatting properties to the report and export it
myExportOptions.FormatOptions = myFormatOptions
myReport.Export(myExportOptions)
End Sub
[C#]
public void ExportToExcel(string Filename, ref CrystalDecisions.CrystalReports.Engine.ReportDocument myReport, bool UseConstantColumnWidth, int ColumnWidth, bool UseColumnHeadings)
{
CrystalDecisions.Shared.ExportOptions myExportOptions = new CrystalDecisions.Shared.ExportOptions();
//Set the export to a disk file
myExportOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
CrystalDecisions.Shared.DiskFileDestinationOptions myDestinationOptions;
myDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions;
myDestinationOptions.DiskFileName = Filename;
myExportOptions.ExportDestinationOptions = myDestinationOptions;
//Set the Excel formatting properties
myExportOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.ExcelRecord;
CrystalDecisions.Shared.ExcelFormatOptions myFormatOptions;
myFormatOptions = CrystalDecisions.Shared.ExportOptions.CreateExcelFormatOptions();
myFormatOptions.ExcelConstantColumnWidth = ColumnWidth;
myFormatOptions.ExcelUseConstantColumnWidth = UseConstantColumnWidth;
myFormatOptions.ExcelTabHasColumnHeadings = UseColumnHeadings;
//Assign the formatting properties to the report and export it
myExportOptions.FormatOptions = myFormatOptions;
myReport.Export(myExportOptions);
}

Question: I need to export 64 columns of data to an Excel spreadsheet. But Crystal won’t let me because this is bigger than what can fit on a page. How do I send all my data to Excel?

Answer: You need to trick Crystal Reports into thinking that you have a very large printer installed on your computer. This lets you have a wide page format and export it to Excel. To do this, open the Control Panel and add a new printer. Select Local Printer and uncheck the option “Automatically detect and install”. Click the Next button. Select the option “Create a New Port” and leave it at Local Port. Click the Next buton. Enter a dummy name and click the OK button. Select the printer “Hewlett-Packard HP-GL/2 Plotter” and click Next.

After installing the new printer, go into your report and select the menu options Crystal Reports > Design > Printer Setup. Select the HP Plotter printer and select the paper size “DIN CO 917 x 1297 mm”. Click the OK button to save your changes. You can now export a large number of columns to Excel