New post
Question
· Apr 1, 2019

Use Cache to Write Excel Files

#Beginner #Caché

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

Discussion (28)4
Log in or sign up to continue
Michael Davidovich · Apr 2, 2019

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

1 0
Nicki Vallentgoed · May 2, 2019

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:

s xls=##class(%Stream.FileCharacter).%New()
d xls.LinkToFile("/ensemble/nicki/a.xls")
d xls.WriteLine("<?xml version=""1.0""?><?mso-application progid=""Excel.Sheet""?><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""> <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""> <Author>me</Author> <LastAuthor>me</LastAuthor> <Created>"_$ZDT($H,3,8)_"</Created> <Company>AMPATH</Company> <Version>1.0</Version> </DocumentProperties>")
d xls.WriteLine("<Styles><Style ss:ID=""header""><Font ss:Bold=""1"" ss:Size=""12""/></Style><Style ss:ID=""a1""><Font ss:Color=""#ff3333""/></Style><Style ss:ID=""a2""><Font ss:Color=""#FFA500""/></Style></Styles>")
d xls.WriteLine("<Worksheet ss:Name=""Sheet1""><Table>"
_"<Column ss:Width=""120""/>"
_"<Column ss:Width=""80""/>"
_"<Column ss:Width=""100""/>"
_"<Column ss:Width=""100""/>")
d xls.WriteLine("<Row ss:Height=""20"">"
    _"<Cell ss:StyleID=""header""><Data ss:Type=""String"">Col 1</Data></Cell>"
    _"<Cell ss:StyleID=""header""><Data ss:Type=""Integer"">2</Data></Cell>"
    _"<Cell ss:StyleID=""header""><Data ss:Type=""String"">Col 3</Data></Cell>"
    _"<Cell ss:StyleID=""header""><Data ss:Type=""String"">Col 4</Data></Cell></Row>")
    
    d xls.WriteLine("<Row ss:StyleID=""a1"">"
    _"<Cell><Data ss:Type=""String"">Col 1</Data></Cell>"
    _"<Cell><Data ss:Type=""Integer"">2</Data></Cell>"
    _"<Cell><Data ss:Type=""String"">Col 3</Data></Cell>"
    _"<Cell ss:StyleID=""a2""><Data ss:Type=""String"">Col 4</Data></Cell></Row>")
    
d xls.WriteLine("</Table></Worksheet></Workbook>")
d xls.%Save()

1 0
Chris Thompson · Apr 1, 2019

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

1 0
Michael Davidovich · Apr 2, 2019

@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

1 0
Alex Warrington · Apr 2, 2019

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.

1 0
Michael Davidovich · Apr 2, 2019

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

1 0
Vladimir Iliychev · Apr 2, 2019

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)

0 0
Eduard Lebedyuk · Apr 2, 2019

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.

3 0
Benjamin Irwin · May 1, 2019

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.

1 0
Neerav Verma · Jun 4, 2019

JAVA_HOME was not set. It was directly set into PATH

Now it's running. 

Starting ZRExcelServer...

Executing: C:\InterSystems\Ensemble2018\lib\ExcelExporter\runserver.bat -port 5005 -numthreads 5 -loglevel 3 -logfile C:\InterSystems\Ensemble2018\mgr\Temp\ -maxlogfilesize 32000 -logrotationcount 100 -numpingthreads 5 -pingport 5006

Returned from execution:
CACHEPATH=C:\InterSystems\Ensemble2018\lib\ExcelExporter\..\..
LOCALCLASSPATH=;C:\InterSystems\Ensemble2018\lib\ExcelExporter\..\..\lib\ExcelExporter\excelexporter.jar
jun. 04, 2019 8:30:14 A. M. com.intersys.excel.ExcelServer go
INFO: Listening on port 5005
jun. 04, 2019 8:30:14 A. M. com.intersys.excel.PingServer init
INFO: Ping Server Listening on port 5006

The Excel Server has been successfully started

However the command still shows same error. How to route it to use the above tcp ports?

J‰"Cannot contact server on port 0.  

1 0
Vladimir Iliychev · Apr 2, 2019

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

1 0
Michael Davidovich · Apr 29, 2019

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

1 0