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

  • 0
  • 2
  • 134
  • 8
  • 2

Answers

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.

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

Comments

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

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

Here is an example with ZenReports. The file opens with MS Excel 2007 directly without warning.

=====================================================================================

/// Test case for EXCEL Zen Report.
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/definitionname='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.

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)