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!