Attachment Generator: ExcelGenerator

Summary

The Excel Generator plug-in has been moved from qbo.Attachment and qbo.Application classes into a stand-alone plug-in qbo.Attachment.OpenXml. The plug-in has been updated with:

• • Performance fixes - Excel generation can now generate Excel spreadsheets supporting hundreds of thousands of rows without any significant memory consumption / memory leaks

  • Formatting enhancements - column data types are more accurately inferred based off IDataReader column types. Explicit column data types and formatting patterns can be specified as well

  • NuGet Package reference for DocumentFormat.OpenXml - Eliminates "aged" references

  • Supports IDataReader and DataSet. XmlReader is not supported

  • Dedicated handler for emitting Excel Exports

Performance Fixes

• • Row and cell writing now utilize OpenXmlWriter for SAX like writing approach. This eliminates the need to store sheet content in memory before being written to disk

  • Iteration through IDataReader has been enhanced to cache reference of column locations to eliminate field lookup every time a cell is rendered

  • Under IGenerator mode, IDataReader is now executed asynchronously to remove unnecessary database contention

Dedicated Handler

Report/Excel.ashx is part of plug-in and renders Excel files from a browser

Usage:

• • Report/Excel.ashx/{TableName}/{Statement}?{Parameters}

  • Eg. Report/Excel.ashx/Contact/Search?LastName=Smith

Scripts/qbo.Excel.Extension.js appends qbo3.AbstractObject to allow ajax calls Excel.ashx

Usage:

qbo3.getObject(this).invokeExcel()

Samples can be found in ApplicationWeb > Theme.Core.xslt

Formatting Enhancements

An Excel document can be rendered two ways:

• • Dynamic column rendering based off query/statement

  • Explicit column rendering using spec file (optional using IGenerator)

Dynamic Column Rendering

Excel Generator now supports recognition of numerical, date and bit data types from IDataReader and sets column types in Excel accordingly based off data types. This is commonly used when a query is "Exported to Excel" and the Excel Generator creates a spreadsheet with dynamic columns.

Explicit Column Rendering

When the Excel Generator plug-in is used by an AttachmentTemplate, a specification file can be leveraged to further control the settings of the spreadsheet. The specification file is formatted as Xml. The following settings can be specified:

• • Workbook

    • Font - Global Spreadsheet Font. default: 'Calibri'

    • FontSize - Global Spreadsheet Font. default: '11'

    • DefaultFormat

      • 'general' - formats everything as general formatting unless field formats (FormatPattern or FormatType) are explicitly set

      • 'dynamic' - formats fields based on what the datatype detected by IDataReader (schema table) unless field formats are explicitly set

    • FieldRender

      • 'Explicit' - only renders defined fields under a given sheet

      • 'Implicit' (default) - renders all fields in a given sheet returned by query

  • Sheets

    • IncludeHeader

      • 'true' (default) - renders header

      • 'false' - omits header

    • InverseHeader

      • 'true' - inverts header color format. By setting to true will also set IncludeHeader to true

      • 'false' (default) - does not alter header format

    • DefaultFormat (see above) - can be overridden at sheet level

    • FieldRender (see above) - can be overridden at sheet level

    • Field - Allows custom formatting to be specified per field. Matches based on SQL field name maching 'name' or 'Mapping' attribute. 

      • Mapping - DB column field is mapped to. If omitted, column value will map to 'name'

      • FormatType - used to specify datatype of field. Defaults to general. Possible values are:

        • None (defaults to General)

        • Date

        • Money

        • Number

        • Boolean

      • FormatPattern - string value that contains format pattern. If a FormatPattern is specified you must specify a FormatType. Patterns can be found here.

      • DefaultValue - Sets a default value for a field if the field cannot be located or the located field is null. Only used in FieldRender.Explicit

For example, a specification file that would render a workbook with two sheets expects a query with two separate select statements. The select statements work "top down" indicating the first query corresponds to the first sheet in the specification, the second select statement corresponds to the second sheet so on an so forth.

<Workbook Name="Export" Font="Verdana" FontSize="8">

<Sheet Name="ProductSummary" IncludeHeader="true" InverseHeader="true" DefaultFormat="dynamic" FieldRender="explicit">

<Field name="ContactID"/>

<Field name="LoanNumber"/>

<Field name="USSSN4"/>

<Field name="BirthDate" FormatType="Date" FormatPattern="yyyy-mm-dd"/>

<Field name="FirstName"/>

<Field name="LastName"/>

<Field name="Address"/>

<Field name="City"/>

<Field name="State"/>

<Field name="PostalCode"/>

<Field name="ContactType"/>

<Field name="Status"/>

<Field name="DateOfInterest" FormatType="Date" FormatPattern="yyyy-mm-dd"/>

<Field name="Branch"/>

<Field name="Effective" FormatType="Date" FormatPattern="yyyy-mm-dd"/>

<Field name="Expires" FormatType="Date" FormatPattern="yyyy-mm-dd"/>

<Field name="VerifiedDate" FormatType="Date" FormatPattern="yyyy-mm-dd"/>

<Field name="DODReportID"/>

<Field name="Error"/>

<Field name="MatchType"/>

</Sheet>

<Sheet Name="Invalid" DefaultFormat="general"/>

</Workbook>

The first sheet contains has explicit rendering defined. The second sheet will dynamically render the columns in the second statement.

Installation

The plug-in is managed by a separate solution located at \qbo.3\qbo.Core\Plugins\OpenXml\qbo.Attachment.OpenXml.sln.

Installation is performed using qbo3.AttachmentPlugins.Targets:

• Individual: OpenXml

• Combined: AttachmentPlugins