Export to Excel problem

Question

Question

Rochdi Badis · Apr 15, 2022

Hi Guys,

I'm using the below code to export to excel and it's working fine for one client but not for another and both clients are running the same version of IE 11.

for the second client the code generates an empty xlsx file and when I try to open it it says file corupted although data exits in the resultset, so is there a flag or setting needs be done in the client server or IE?

Class MSDS.UI.Serenity.Report.ExcelExport Extends %ZEN.Report.reportPage
{ Property Location As %String(ZENURL = "location"); Property fromDate As %Date(ZENURL = "fromDate"); Property toDate As %Date(ZENURL = "toDate"); Property USR As %String(ZENURL = "USER"); /// 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" OnCreateResultSet="GetItemList" >
<attribute name='fromDate' expression='$S(+..fromDate:##class(MSDS.COM.Loc).GetDisplayDate(##class(MSDS.COM.Loc).%OpenId(..Location), ..fromDate),1:"Beginning")' />
<attribute name='toDate' expression='$S(+..toDate:##class(MSDS.COM.Loc).GetDisplayDate(##class(MSDS.COM.Loc).%OpenId(..Location), ..toDate),1:"End")' /> <parameter expression='..Location'/>
<parameter expression='..fromDate'/>
<parameter expression='..toDate'/>
<parameter expression='..USR'/> <group name="FilmListing" >
<element field="Assembly" name="Assembly1" />
<element field="batchno" name="batchno1" />
<element field="kitname" name="kitname1" />
<element field="code" name="code1" />
<element field="decontamtime" name="decontamtime1" />
<element field="decontamdate" name="decontamdate1" />
<element field="scannedby" name="scannedby1" />
</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>
} ClassMethod GetItemList(ByRef pSC As %Status, ByRef pParameters) As %ResultSet
{
Set LocationId =$get(pParameters(1))
Set fromDate=$get(pParameters(2))
Set toDate=$get(pParameters(3))
Set usr=$get(pParameters(4))
Set sql="Select distinct d.code As Assembly,it.Batch->Number As batchno, d.Kit->Name As kitname ,d.Kit->Code As code , %External(d.Audit_CreatedTime) As decontamtime,%EXTERNAL(d.Audit_CreatedDate) as decontamdate, d.Audit_CreatedBy as scannedby "
Set sql = sql _ " From MSDS_Serenity.Decontam d,MSDS_Serenity.Item it"
Set sql = sql _ " Where it.ID=d.KitLabelassoc and it.ReceivedIndecontam=1 and d.loc = '"_ LocationId _ "' and ( %INTERNAL(d.Audit_MOdifiedDate) between "_fromDate_" and "_toDate_" ) "
I usr'="" Set sql = sql _" and d.Audit_CreatedBy = '"_ usr _ "' "
Set sql = sql_"order by d.Audit_ModifiedDate,d.Audit_ModifiedTime "
Set rs = ##class(%Library.ResultSet).%New()
Set pSC=rs.Prepare(sql)
If $$$ISERR(pSC) Quit rs
Set pSC=rs.Execute()
Quit rs
} }

Thanks

Product version: Caché 2014.1

Discussion (1)1

Log in or sign up to continue

Julie Bolinsky · Apr 15, 2022

Hello,

The first thing I would do is remove excel from the picture (especially since comparing 2 clients). I would first change from excel to XML. That way you can confirm that 2nd client is actually returning data based off the parameters you are passing in. Once you've confirmed that the result set is actually returning data with the parameters you are passing - I would first remove your mixed attribute/element that you currently have in your report definition xdata block that could be causing some confusion. Next instead of going to mode=xlsx - I would initially try the earlier version of excel - mode=excel and see how that goes. I do know that some clients have received the corrupt message, but if you go ahead and ignore you'll see data but mostly that has been in Chrome and not IE.

0 0