Attachment Template - Excel Template

Post date: Oct 19, 2011 4:17:45 PM

This post outlines how to configure Excel templates using the Attachment module.

• • Ensure the Microsoft Access Database Engine 2010 Redistributable is installed on server(s) generating the document. The default install is for 32 bit applications. The EXE can be found at:

    • http://www.microsoft.com/download/en/details.aspx?id=13255

  • Download latest qbo.Attachment.Template project and ensure ExcelTemplate.cs is included in project. Build and and deploy the dll. Default deployment location is:

    • C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/

  • Navigate to Configuration -> AttachmentTemplates. Add a new entry:

    • Name: ExcelTemplate

    • Assembly: C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/qbo.Attachment.Template.dll

    • Assembly Namespace: qbo.Attachment.Template

    • Upon completion, AttachmentTemplate.config should contain an entry:

   <AttachmentTemplateAssembly>

        <Name>ExcelTemplate</Name>

        <AssemblyFile>C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/qbo.Attachment.Template.dll</AssemblyFile>

        <NameSpace>qbo.Attachment.Template</NameSpace>

      </AttachmentTemplateAssembly>

• • Create Attachment Template:

    • Ensure MimeType: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. You can optionally add an ObjectType record:

      • ObjectType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

      • Object: 'AttachmentTemplate.MimeType'

    • AttachmentService: ExcelTemplate

    • Report: <Assoicate Custom Report>

    • Transform: <See Below>

  • Create Report

    • This attachment template supports accepting a XmlDocument (which represents the report output) or executing a stored procedure inside the AttachmentTemplate code. This feature is available as a workaround to IAttachmentTempalte not supporting DbDataReaders. This pattern is used for large recordsets that could generate memory exceptions. Note:

      • Executing a stored procedure inside AttachmentTemplate - in this case, the report only needs to select a stored procedure name and parameter values as these will be passed to the AttachmentTemplate to execute.  The AttachmentTemplate's Transform Xslt will format the parameters so they can be passed to qbo.Data.StoredProcedure

      • Executing a Report with Report Queries - This is default behavior for a report and the Xml dataset will be passed to the AttachmentTemplate

  • AttachmentTemplate Transform - Used to transform existing DataSet or format a Stored Procedure call inside AttachmentTemplate.

    • Making a Stored Procedure call inside AttachmentTemplate. Xslt needs to render a StoredProcedure with parameters. With the example below it looks for values returned from a report:

<xsl:stylesheet version="1.0"

                xmlns:qboWeb="http://qbo.web.quandis.com/"

                xmlns:qbo="http://services.quandis.com/QDS/"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

>

  <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>

  <xsl:param name="ApplicationRoot"/>

  <xsl:template match="*">

    <ExcelTemplate>

      <StoredProcedureCollection>

        <StoredProcedure>

          <xsl:attribute name="name">

            <xsl:choose>

              <xsl:when test="//StoredProcedureName != ''">

                <xsl:value-of select="//StoredProcedureName"/>

              </xsl:when>

              <xsl:otherwise>

                <xsl:text>rShortSaleDataExtractAll</xsl:text>

              </xsl:otherwise>

            </xsl:choose>

          </xsl:attribute>

          <OrganizationID>

            <xsl:value-of select="//OrganizationID"/>

          </OrganizationID>

        </StoredProcedure>

      </StoredProcedureCollection>

      <!--

        Workbook (optional)

        Name - (optional) If supplied sheets will have naming pattern {Name}{SheetIndex} where SheetIndex starts at 0

      -->

      <Workbook Name="Export">

        <!--

        Sheet - (optional) Overrides global behavior. Sequence of Sheet must match sequence of dataset. Eg. If SP returns 3 tables, first table returned will match first Sheet

        Name - (optional) If supplied sheets will have naming pattern {Sheet}. Otherwise sheet naming will cascade to Workbook naming pattern

      -->

        <Sheet Name="ShortSale"/>

        <Sheet Name="Process"/>

        <Sheet Name="Loan"/>

        <Sheet Name="Property"/>

        <Sheet Name="Borrower"/>

        <Sheet Name="Foreclosure"/>

        <Sheet Name="Contact"/>

        <Sheet Name="ContactMethod"/>

        <Sheet Name="Person"/>

      </Workbook>

    </ExcelTemplate>

  </xsl:template>

</xsl:stylesheet>

• • Xslt can also transform an existing DataSet. Note the transformed output must be able to convert into a DataSet

Fixing Issues With Large File Generation for Ace Driver

Depending on size of Excel document, the driver may write to a temp folder. If this folder does not exist the Excel document will not generate corrrectly.

The following fix was found from http://social.msdn.microsoft.com/Forums/en-AU/adodotnetdataproviders/thread/4d1eeb6d-436d-4595-8645-fde90b2f9b18

• • Windows 2003 - Ensure Path C:\Documents and Settings\Default User\Local Settings\Temporary Internet Files\Content.MSO exists with adequate security

  • Windows 2008 - Ensure Path C:\Users\Default User\Local Settings\Temporary Internet Files\Content.MSO exists with adequate security

Adding Registry Types on IIS Servers

Add Mime Type to enable correct file naming. This should be performed on all servers that generate attachment:

• • Start -> Run -> Regedit

  • HKEY_CLASSES_ROOT -> MIME -> Database -> Content Type

  • Ensure/Add Key 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

  • Right Click on Key -> New -> String Value:

    • Name = 'Extension'

    • Value data = '.xlsx'

Add Mime Type to enable browser rendering. This should be performed on all IIS Servers:

• • Start -> Run -> Regedit

  • HKEY_CLASSES_ROOT

  • Ensure/Add Key '.xlsx'

  • Right Click on Key -> New -> String Value:

    • Name = 'Content Type'

    • Value data = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

IIS and Event Service must be restarted for settings to take place.