Export Zen report to Excel DEFAULTMODE ="xlsx"

Question

Question

Rochdi Badis · Jan 18, 2022

Hi Guys,

I've set DEFAULTMODE ="xlsx" in my report to be exported to Excel but it's exporting as .zip, is there something I need to add or set or maybe MS Excel to be installed in the server?

Thanks

Product version: Ensemble 2014.1

Discussion (8)1

Log in or sign up to continue

Julie Bolinsky · Jan 19, 2022

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>
}

}

0 0

score 1 · Answer 1 · 2022-01-18T15:06:00-05:00

Hello,

I see you selected a product version of Ensemble 2014.1 - which means your Zen reports supports a DEFAULTMODE= excel or xlsx --- where excel mode generates an xml file that can be viewed within excel, and xlsx mode generates excel worksheets (not supported on VMS).

If you are running this from the browser, then depending on the browser it may open directly within Excel or give a default file name to Save As. This allows you to name the file anything you'd like.

If you are using a Generate method then you can control the outputted file name assigned when you call the Generate method.

Thanks Julie

score 0 · Answer 2 · 2022-01-18T16:56:00-05:00

I clicked on save as where only two options to save as .zip or all files, so I picked all files and forced .xls extension but the generated file is all over the place.

the purpose of this project is to all the user to view a spreadsheet when clicking the "View Excel Format" or prompt the user to open the generated spreadsheet in downloads, and using Generate method maybe better in my case, so how can I send the generated file to open up in in the browser?

I've seen a sample that uses GenerateReport() but that save a file in a pre-defined path but then I would like to instead send the generated file to the browser for the user to view it, any suggestions?

Thanks

score 1 · Answer 3 · 2022-01-18T17:44:09-05:00

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

score 0 · Answer 4 · 2022-01-18T19:09:58-05:00

Yes all I need is an output in one sheet.

I've changed the mode "excel" but I got the attached messy output .

Thanks

score 0 · Answer 5 · 2022-01-19T15:03:44-05:00

Thank you very much Julie, your sample is exactly what I was looking for the only thing I need to add is some headings and information that I need to display at the top or the spreadsheet so I guess for that I can use Attributes!? And I also notice that elements aren’t widely supported in other formats ( pdf) and html tags aren’t supported in xlsx.

Thanks

score 0 · Answer 6 · 2022-01-19T16:49:36-05:00

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

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

score 0 · Answer 7 · 2022-01-23T15:31:41-05:00

Rochdi Badis · Jan 23, 2022

Thanks you very mutch Julie you've been very helpfull

Cheers

0 0