Excel Data Export | ActiveReports 18 .NET Edition

Excel Data Export

In This Topic

Excel Data is one of the formats to which you can render your report using ExcelTransformationDevice. You can export excel files in two formats, Xlsx and Csv.

Excel Data exports only data from Tablix, Table, and Matrix data regions, preserving the data region structure and ignoring layout-related features (page break, cumulative total, etc). Other controls and data regions of the original report are ignored at this export. The FrozenRows and FrozenColumns settings of Table and Tablix are handled at the report export and can be applied in the exported file.

Note: If a report does not contain any data region (Table or Tablix), a Csv file is not generated.

For the Xlsx format, when a report has multiple data regions, each data region is exported to a separate excel sheet.

For the Csv format, a separate CSV file is created for each data region, available in the report.

The following steps provide an example of rendering a report in the Microsoft Excel format.

Create a new Visual Studio project.
In the New Project dialog that appears, select ActiveReports 18 Page report Application and specify a name for the project in the Name field.
Click OK to create a new ActiveReports 18 Page report Application. By default a Page Report is added to the project.
Add MESCIUS.ActiveReports.Export.Excel package in the project.
On the Form.cs or Form.vb that opens, double-click the title bar to create the Form_Load event.

Add the following code inside the Form_Load event.

Csv Data

Visual Basic.NET code. Paste INSIDE the Form Load event.	Copy Code
' Provide the Page Report you want to render. Dim rptPath As System.IO.FileInfo = New System.IO.FileInfo("..\..\PageReport1.rdlx") Dim pageReport As GrapeCity.ActiveReports.PageReport = New GrapeCity.ActiveReports.PageReport(rptPath) ' Create an output directory. Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyCsvData") outputDirectory.Create() ' Provide settings for your rendering output. Dim settings = New GrapeCity.ActiveReports.Export.Excel.Page.Settings() settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Csv settings.Csv.ColumnsDelimiter = "," settings.Csv.Encoding = System.Text.Encoding.UTF8 settings.Csv.NoHeader = False settings.Csv.QuotationSymbol = """"c settings.Csv.RowsDelimiter = vbCrLf ' Set the rendering extension and render the report. Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)) ' Overwrite output file if it already exists. outputProvider.OverwriteOutputFile = True pageReport.Document.Render(New GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings)

Visual Basic.NET code. Paste INSIDE the Form Load event.

Copy Code

' Provide the Page Report you want to render.
Dim rptPath As System.IO.FileInfo = New System.IO.FileInfo("..\..\PageReport1.rdlx")

Dim pageReport As GrapeCity.ActiveReports.PageReport = New GrapeCity.ActiveReports.PageReport(rptPath)

' Create an output directory.
Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyCsvData")
outputDirectory.Create()

' Provide settings for your rendering output.
Dim settings = New GrapeCity.ActiveReports.Export.Excel.Page.Settings() 

settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Csv
settings.Csv.ColumnsDelimiter = ","
settings.Csv.Encoding = System.Text.Encoding.UTF8
settings.Csv.NoHeader = False
settings.Csv.QuotationSymbol = """"c
settings.Csv.RowsDelimiter = vbCrLf

' Set the rendering extension and render the report.
Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name))

' Overwrite output file if it already exists.
outputProvider.OverwriteOutputFile = True
pageReport.Document.Render(New GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings)

C# code. Paste INSIDE the Form Load event.	Copy Code
// Provide the Page Report you want to render. System.IO.FileInfo rptPath = new System.IO.FileInfo(@"..\..\PageReport1.rdlx"); GrapeCity.ActiveReports.PageReport pageReport = new GrapeCity.ActiveReports.PageReport(rptPath); // Create an output directory. System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyCsvData"); outputDirectory.Create(); // Provide settings for your rendering output. var settings = new GrapeCity.ActiveReports.Export.Excel.Page.Settings(); settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Csv; settings.Csv.ColumnsDelimiter = ","; settings.Csv.Encoding = Encoding.UTF8; settings.Csv.NoHeader = false; settings.Csv.QuotationSymbol = '"'; settings.Csv.RowsDelimiter = "\r\n"; // Set the rendering extension and render the report. GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)); // Overwrite output file if it already exists. outputProvider.OverwriteOutputFile = true; pageReport.Document.Render(new GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings);

C# code. Paste INSIDE the Form Load event.

Copy Code

// Provide the Page Report you want to render.
System.IO.FileInfo rptPath = new System.IO.FileInfo(@"..\..\PageReport1.rdlx");

GrapeCity.ActiveReports.PageReport pageReport = new GrapeCity.ActiveReports.PageReport(rptPath);

// Create an output directory.
System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyCsvData");
outputDirectory.Create();

// Provide settings for your rendering output.
var settings = new GrapeCity.ActiveReports.Export.Excel.Page.Settings();

settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Csv;
settings.Csv.ColumnsDelimiter = ",";
settings.Csv.Encoding = Encoding.UTF8;
settings.Csv.NoHeader = false;
settings.Csv.QuotationSymbol = '"';
settings.Csv.RowsDelimiter = "\r\n";

// Set the rendering extension and render the report.
GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name));

// Overwrite output file if it already exists.
outputProvider.OverwriteOutputFile = true;
pageReport.Document.Render(new GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings);

Excel Data

Visual Basic.NET code. Paste INSIDE the Form Load event.	Copy Code
' Provide the Page Report you want to render. Dim rptPath As System.IO.FileInfo = New System.IO.FileInfo("..\..\PageReport1.rdlx") Dim pageReport As GrapeCity.ActiveReports.PageReport = New GrapeCity.ActiveReports.PageReport(rptPath) ' Create an output directory. Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyExcelData") outputDirectory.Create() ' Provide settings for your rendering output. Dim settings = New GrapeCity.ActiveReports.Export.Excel.Page.Settings() settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Xlsx settings.Xlsx.AllowImages = True settings.Xlsx.UseCompression = True settings.Xlsx.OpenXmlStandard = GrapeCity.ActiveReports.Export.Excel.Page.OpenXmlStandard.Transitional settings.Xlsx.Security.ReadOnlyRecommended = True ' Set the rendering extension and render the report. Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)) ' Overwrite output file if it already exists. outputProvider.OverwriteOutputFile = True pageReport.Document.Render(New GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings)

Visual Basic.NET code. Paste INSIDE the Form Load event.

Copy Code

' Provide the Page Report you want to render.
Dim rptPath As System.IO.FileInfo = New System.IO.FileInfo("..\..\PageReport1.rdlx")

Dim pageReport As GrapeCity.ActiveReports.PageReport = New GrapeCity.ActiveReports.PageReport(rptPath)

' Create an output directory.
Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyExcelData")
outputDirectory.Create()

' Provide settings for your rendering output.

Dim settings = New GrapeCity.ActiveReports.Export.Excel.Page.Settings()

settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Xlsx
settings.Xlsx.AllowImages = True
settings.Xlsx.UseCompression = True
settings.Xlsx.OpenXmlStandard = GrapeCity.ActiveReports.Export.Excel.Page.OpenXmlStandard.Transitional
settings.Xlsx.Security.ReadOnlyRecommended = True

' Set the rendering extension and render the report.
Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name))

' Overwrite output file if it already exists.
outputProvider.OverwriteOutputFile = True
pageReport.Document.Render(New GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings)

C# code. Paste INSIDE the Form Load event.	Copy Code
// Provide the Page Report you want to render. System.IO.FileInfo rptPath = new System.IO.FileInfo(@"..\..\PageReport1.rdlx"); GrapeCity.ActiveReports.PageReport pageReport = new GrapeCity.ActiveReports.PageReport(rptPath); // Create an output directory. System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyExcelData"); outputDirectory.Create(); // Provide settings for your renderidng output. var settings = new GrapeCity.ActiveReports.Export.Excel.Page.Settings(); settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Xlsx; settings.Xlsx.AllowImages = true; settings.Xlsx.UseCompression = true; settings.Xlsx.OpenXmlStandard = GrapeCity.ActiveReports.Export.Excel.Page.OpenXmlStandard.Transitional; settings.Xlsx.Security.ReadOnlyRecommended = true; // Set the rendering extension and render the report. GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)); // Overwrite output file if it already exists. outputProvider.OverwriteOutputFile = true; pageReport.Document.Render(new GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings);

C# code. Paste INSIDE the Form Load event.

Copy Code

// Provide the Page Report you want to render.
System.IO.FileInfo rptPath = new System.IO.FileInfo(@"..\..\PageReport1.rdlx");

GrapeCity.ActiveReports.PageReport pageReport = new GrapeCity.ActiveReports.PageReport(rptPath);

// Create an output directory.
System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyExcelData");
outputDirectory.Create();

// Provide settings for your renderidng output.
var settings = new GrapeCity.ActiveReports.Export.Excel.Page.Settings();

settings.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.ExcelDataFileFormat.Xlsx;
settings.Xlsx.AllowImages = true;
settings.Xlsx.UseCompression = true;   
settings.Xlsx.OpenXmlStandard = GrapeCity.ActiveReports.Export.Excel.Page.OpenXmlStandard.Transitional;
settings.Xlsx.Security.ReadOnlyRecommended = true;

// Set the rendering extension and render the report.
GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name));

// Overwrite output file if it already exists.
outputProvider.OverwriteOutputFile = true;
pageReport.Document.Render(new GrapeCity.ActiveReports.Export.Excel.Page.ExcelTransformationDevice(), outputProvider, settings);

Excel Data Rendering Properties

ActiveReports offers several options to control how reports render to Excel Data.

Property	Description
Csv	Csv related properties. See Csv Rendering Properties below.
FileFormat	Indicates whether to use Csv or OpenXml format for the output file.
Xlsx	OpenXml related properties. See Xlsx Rendering Properties below.

Csv Rendering Properties

Property	Description
ColumnsDelimiter	Sets or returns the text inserted between columns.
DateTimeFormat	Specifies the default format for date values, for example, 'yyyy-MM-dd'.
Encoding	Specifies the encoding schema for output.
NoHeader	Specifies whether to omit the CSV Header.
NumericFormat	Specifies the format for numeric values, for example, '0.####'.
QuotationSymbol	Sets or returns the qualifier character to put around results.
RowsDelimiter	Sets or returns the text inserted between rows.

Xlsx Rendering Properties

Property	Description
AllowImages	Indicates whether to allow images or just plain data content.
Author	Sets the name of the author that appears in the Author field in the Properties of the exported Excel document.
AutoRowsHeight	Indicates whether to export rows height or specify auto height.
Categories	Sets the name of the categories that appears in the Categories field in the Properties of the exported Excel document.
OpenXmlStandard	Specifies the level of Open XML document conformance on exporting in Xlsx file format. You can choose from the following values: Transitional: The default value. Strict: The Excel file generated using Strict cannot be viewed on iOS devices.
RightToLeft	Shows direction of sheets from right to left.
Security	Initializes the document security.
Title	Sets the name of the title for a document that appears in the Title field in the Properties of the exported Excel document.
UseCompression	Indicates whether to use compression on exporting to an Xlsx file.