Posts:
Julie has not published any posts yet.
Replies:

Hello,

Displaying at the top of the spreadsheet gets a little tricky as the <include> or <call> entries would always be placed at the end of the report (it ignores the placement within the XDATA).

The only way that I have been able to "add" notes above the output (other than column headers) is to embed it in my returning data. So in my example I was doing a sql statement - I would need to modify it to be something like this:

  sql="select 'First Line' as ID, ' ' as Title, 'First Line Details' as Description 
    union select ' ' as ID, ' ' as Title, ' ' as Description 
    union select 'Col1' as ID, 'Col2' as Title, 'Col3' as Description 
    union select ID,Title, Description from cinema.film"

Because I'm now passing text in fields that previously was declared a number (ID column) as well as passing the column headers, I need to also modify the definition of the FilmListing group like this

<group name="FilmListing" >
<element name="Col1" excelName=" " field="ID" />
<element name="Col2" excelName=" " field="Title" />
<element name="Col3" excelName=" " field="Description" />
</group>

If you need to have things declared as excel numbers then you may want to think about having multi-sheets where the first one contains your additional text, and the second worksheet has your data.

Thanks Julie

Can you change the output mode to be XML and make sure that it makes well formed XML output? I ask this because I have experienced when a non-supportable XML character gets into my data set my output is garbage/messy.

I have included a very simple excel report below that should run in the SAMPLES database using the Cinema.Film table that is traditionally included in our products. Entries from the film table will be displayed when selecting xml, excel or xlsx whereas html or pdf will display a simple message saying excel output. This may be able to confirm that overall setup is correct, and the issue is with the data potentially. Please note that with recent browser security settings, you may find that the user is given a message that the file may be corrupt - if you ignore that message and produce onwards you will see the data (but again I've seen Chrome, Firefox, IE and Edge all function differently depending on my version/browser settings). Also in order to view excel successfully in the browser, you'd need to make sure that appropriate support is there as well (e.g. plugin, etc.)

/// Film report.
Class ZENReports.JulieTestExcel Extends %ZEN.Report.reportPage
{

/// Class name of application this report belongs to.
Parameter APPLICATION;

/// This is the optional XML namespace used for the report.
Parameter REPORTXMLNAMESPACE;

/// This is the default display mode for this report.
Parameter DEFAULTMODE = "xlsx";

/// Parameter DEFAULTMODE = "xml";
/// Parameter DEFAULTMODE = "pdf";
/// Parameter DEFAULTMODE = "html";
/// Parameter DEFAULTMODE = "excel";
 

/// Switches between element-oriented and attribute-oriented parsing of structure for Excel output.
Parameter EXCELMODE As STRING [ Constraint = ",element,attribute", Flags = ENUM ] = "element";

/// Used to tag aggregates in EXCEL mode
Parameter AGGREGATETAG As String;

/// This XML defines the logical contents of this report.
XData ReportDefinition [ XMLNamespace = "http://www.intersystems.com/zen/report/definition]
{
<report xmlns="http://www.intersystems.com/zen/report/definition"
 name="report" 
  sql="select ID,Title, Description from cinema.film"> <group name="FilmListing" >
<element name="ExcelDisplayColumnName" field="ID" isExcelNumber="true" excelNumberFormat='#%' />
<element field="Title" />
<element field="Description" />
</group>

</report>
}

/// This XML defines the display for this report.
/// This is used to generate the XSLT stylesheets for both HTML and XSL-FO.
XData ReportDisplay [ XMLNamespace = "http://www.intersystems.com/zen/report/display]
{
<report xmlns="http://www.intersystems.com/zen/report/display"
 name="report">
<body>
<p>This report should be used for EXCEL output.</p>
</body>
</report>
}

}

Hello,

Did you save it as an .xls extension or a .xlsx extension? It needs to be the .xlsx extension.

If this isn't a multi-sheet excel report, and the report output wouldn't exceed xls file size (~65,000 rows) - you may want to change your mode to excel - this will instead produce an xml file which should open without issues if given a .xls extension.

Any way you could show the error message that you are receiving?

Thanks Julie

Followers:
Julie has no followers yet.
Following:
Julie has not followed anybody yet.
Global Masters badges:
Julie has no Global Masters badges yet.