Use Cache to Write Excel Files
Currently, when we want to write data to a file that will be viewed in Excel, we parse the data in tab deliminated format to the file and name it with .xls at the end. That is sent to end users via email. They get a warning that the data is not formatted properly (it's not really an Excel file after all) but it does display somewhat correctly as the tabs are understood (this does not work if we deliminated with commas however).
How can we format the file for Excel? If it helps to know, our Cache is on Unix so the files are sent to a Unix directory before a batch job pulls them and emails them to the correct people.
Ideas I can think of:
- Develop an Excel workbook that has an import macro that takes in the raw data (in the form of Cache globals e.g. ^("DATA") = "other | data") or flat file (CSV or tab?) and formats it, totals it, organizes it in the end user's desired way.
- FTP flat file to a Windows directory, a batch job runs a macro on those files that converts them to XLS.
- Utilize another language or library that has methods to parse the data into an Excel file (https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx).
A limitation we will have is that we have to use what we have . . . Cache. We don't have IRIS or Zen Reports or anything like that (I'm not even sure if that stuff would enable what I'm talking about).
Your thoughts are appreciated!
Mike
Depends. Are you creating the reports programmatically or through some generic query? Programmatically, you can parse the output into Spreadsheet XML (Excel 2003) which can be opened directly with Excel and can include column widths and cell formats.
https://docs.microsoft.com/en-us/previous-versions/office/developer/offi...(v=office.10)
Hi @Charles Cross!
I don't think your link pasted right.
To answer your question . . . I would lean twoards programmatically. It's a mumps routine that prompts the user for some inputs and then builds out a temporary database with the required data and then writes it to a file with tab deliminators.
You're not the first to mention the XML solution, so I will look into this.
Mike
While the XML solution will work, XML is heavy. You’ll need to have it compiled post-creation, which can be done programmatically on a Windows Server with PowerShell. Otherwise, I’d use a python script to do the compilation on a Unix server.
I think he was referring to the excel xml format: wiki link
I've used it previously:
Create an example file in excel and save it in the xml format.
View the source in a text editor to see what it looks like.
Write out similar code to a file via COS.
Save as .xls
Ugly example:
Yes, this would still need to be compiled. To give an example, an uncompiled AR inventory report will be around 500MB in XML format. When compiled (utilize PowerShell to open the XML with Excel, and SaveAs and XLSX formatted file), the resulting file will be around 25MB.
We use the csv to xlsx python option.
In the same Caché routine,
· we generate de tab file
· with $ZF(-1 launch the "pythonscript.pyw tabgenerated"
· send the email with $ZF(-2 "sendemail excelgenerate"
HI
I am using example 1
and getting this error
X.1^1e^^^0
0 J‰"Cannot contact server on port 0.
w ##class(Utils.XLSX).generateFileFromSQL("a.xlxs")
Can you please send it to me as well nv@nv-enterprises.biz
Thank you in advance
Good day Sander,
As there are many ways to do this, please also forward to awessels@asone.global as we only use LINUX
Much appreciated.
Sander!
Could you also share it on Github and submit to Open Exchange, please?
Even better with ZPM module?
It really depends on what the acceptance criteria is, in my opinion. You don't have to name it to an excel extension, a .csv will open just fine in excel although you do lose some formatting abilities.
One of the things we recently implemented is just having a simple node server that accepts a JSON call via REST using the exceljs node library. Granted this is outside of cache but it does the job.
Depending on your memory settings calling out your reporting result to another service can hit a max string issue unless it takes a pure stream.
There is another post out here that tried to achieve this problem by actually creating html tables in excel (This may be what Charles was talking about but his link breaks for me so I just get a 404), but once again if it's the same approach I am thinking of you hit formatting issues.
One approach I am currently investigating is using an ADO .net connection to pull the data from the cache database and presenting it to the front end using an excel nugget package.
Hope this information helps and good luck, please let us know what you ended up with!
- Chris
@Chris Thompson
Thanks!
Re the JSON and REST call, are you basically saying create a web service that serves the report in JSON format, and then use https://github.com/exceljs/exceljs to write the XLS file?
Do you have any links or discussion on the ADO .net package? I think the problem here is that if it's not Cache no one want to touch it. If someone has to use .net that idea would get squashed right away :(
Mike
Hi Mike,
I have used your first suggested solution for numerous projects.
The benefits are that you can create graph and charts and format the Excel workbook as you wish with conditional formatting.
I run a import macro that grabs the file from the FTP, opens up the .csv file locally in the background and copies the data to the Excel cells before closing the file.
The CSV file is always provided in the same format so my import macro knows exactly where to look for the data.
I will have to keep playing with this.
I created the class and tried to run it (in SAMPLES) and when I pressed run it launched the management portal login and made me log in (it appeared it was trying to log into another namespace which might be significant).
An Excel file started to open but I got the 'wrong format/corrupt data' message. I thought maybe there was an issue since I'm running from samples, but it's trying to do something in the other namespace (we call it DVL). So I tried logging into the management portal first and switched the namespace to SAMPLE and then ran the report. Same thing happened as above: had me try to log into DVL portal.
I'll keep playing as it seems hopeful, but it may be a limitation on how our system was configured (which I'm too new to have much influence to change).
Mike
I suggested to load in "Samples" namespace because the class ZENDemo.Data.Employee is there ready,
and you can see the result immediately.
If you change the sql query and the fields with yours , you can start it in any namespace.
BTW I use CACHE 2017.2 on Windows 7 system and MS Office 2007.
If you change "xlsx" with "excel" (which means file extention ".xls")
Parameter DEFAULTMODE As STRING = "excel";
it should work wit MS Office 2003(Excel 1997-2003)
Some options:
> - Utilize another language or library that has methods to parse the data into an Excel file (https://stackoverflow.com/questions/17684610/python-convert-csv-to-xlsx).
If you want to use Python from Cache, check PythonGateway.
I didn't see the HTML option in the previous comments. Most of the Microsoft and other Windows type programs can now open a web page directly within the application. If you point Excel to a http://server/something.cls Cache Server Page class, it will open directly in Excel. If you create your web page using html tables, then they will format nicely when opened with Excel. Again many of the applications will even allow a "GET" style request like http://server/something.cls?date=20190501. This allows for individualized web reports.
looks like a Java-based server did not start.
How to start that?
Just installing java not enough?
It should be started automatically, but you can configure one explicitly at: SMP - System Administration - Configuration - Zen Reports - Excel Servers.
Set log file and try to start it from the same page. I think it would show the root problem.
JAVA_HOME was not set. It was directly set into PATH
Now it's running.
However the command still shows same error. How to route it to use the above tcp ports?
J‰"Cannot contact server on port 0.
Set ^%SYS("zenreport","excelserverport")=5005
Here is an example with ZenReports. The file opens with MS Excel 2007 directly without warning.
=====================================================================================
Class ZENApp.MyReportExcel1 Extends %ZEN.Report.reportPage
{
Parameter DEFAULTMODE As STRING = "xlsx";
Parameter EXCELMODE = "element";
Parameter XSLTMODE = "server";
/// XML that defines the contents of this report.
XData ReportDefinition [ XMLNamespace = "http://www.intersystems.com/zen/report/definition" ]
{
<report xmlns="http://www.intersystems.com/zen/report/definition" name='myReport'
sql="SELECT TOP 100 ID,Name,Title,Home_City,HireDate,Salary FROM ZENDemo_Data.Employee">
<group name='Name' breakOnField='Name'>
<element name='id' field='ID' excelName="id"/>
<element name='name' field='Name' excelName="Name"/>
<element name='title' field='Title' excelName="Title"/>
<element name='home_City' field='Home_City' excelName="Home_City"/>
<element name='hireDate' field='HireDate' excelName="HireDate"/>
<element name='salary' field='Salary' excelName="Salary"/>
</group>
</report>
}
XData ReportDisplay [ XMLNamespace = "http://www.intersystems.com/zen/report/display" ]
{
<report xmlns="http://www.intersystems.com/zen/report/display"
name='myReport' title='HelpDesk Sales Report' style='standard'>
</report>
}
}
======================================================================================
It should be loaded in "Samples" namespace. Don't remove the ReportDisplay part.
For those following along, the direction we've decided is to output the files as XML data. So I am trying to use the %XML.Writer and %XML.Adapter classes to do this, but the documentation is thick and difficult to understand. For example, I'm trying write to write the following tag and attributes:
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
</Workbook>
I can't seem to figure out how to write the tag with more than one attribute. The documentation says I can add an additional property and note "XMLPROJECTION="ATTRIBUTE", but that then required the initialization of these attributes every time we create the object. I would rather it be a parameter so it's remains static. Also you can define a property or parameter with a ":" which makes it difficult.
Does InterSystems have a tutorial on this somewhere? A video? I would love some help.
For the record, I'm a very new programmer if you do offer thoughts, context helps.
Mike
A very powerful (commercial) module for XLSX generation/manipulation is at https://www.npmjs.com/package/xlsx
Advantage: you could use REST calls to generate and fill your spreadsheet.
HTH,
Ward
The following link can be used to see an example of opening a web page in Excel.
https://www.bmirwin.com/vnode/dg2/index.php?ns=PSOE
Using a CSP Page or Class to create a web page with a table in Cache, then open it in Excel with the link will directly open the table in Excel.
Hi Mike,
A couple of years I have build a .xml generator in Caché on a Linux platform. With .xls as file format, so it opens in Excel.
It’s a kind of a template you can use to fill in the variables/parameters and it will send the file by mail.
If this is what you are searching for, let me know and I will share it with you.
Cheers,
Sander Cornet
Hi Mike,
sorry, I recently joined Community.
I think excel inside the database will solve any problems with export in .xls or .xlsx format.
Virtual-excel-inside-M on Linux or Windows, clients on MS.Excel on Windows
https://community.intersystems.com/post/m-cell
email sia.enters@inbox.lv
Alex