topic excel formatting in Operating System - OpenVMS

excel formatting

Jerry Rieman — Sat, 16 Dec 2006 13:03:27 GMT

Does anyone have any suggestions on methods or software for creating true excel format files on VMS. We create many csv format files and e-mail them directly to users from VMS. Unfortunately, many of these users are very limited in their computer skills and are unable (or unwilling!) to go through the text to column formatting that is available in excel. I would like to deliver the e-mail attachment to them in true excel format which they can simply click to open and have a nicely formatted spreadsheet.

Thanks in advance for any assistance.

Re: excel formatting

Hein van den Heuvel — Sat, 16 Dec 2006 15:26:04 GMT

Hmm,

If csv files are named .csv then the default open under Windoze activates excel without needing to do the text-to-column.

I don't think there is a Perl OLE.pm module available for OpenVMS, that woudl be too easy :-)

XL97 and up can read a HTML table saved with an .xls extension.
This allows you to add some basic formatting which will be preserved
when the file is opened in XL. Just write out a text file with the table
from to

and give it an .xls extension (if you want to
link to it)

I just tried it, going into excel first, and saving a trivial worksheet as html. Then rename to xls, Use notepad to change some entries to prove you can play with with, re-display with excel.

Use the excel saved table text as template for an alternative to .csv.
An interesting benefit could be that you can use a browser on OpenVMS to pre-view the information.

hth,
Hein.

Re: excel formatting

Jean-François Piéronne — Sun, 17 Dec 2006 12:36:34 GMT

Python for OpenVMS include 2 modules doing this: pyXLWriter and pyXLWriter

The first one is obsolete and replace by the second one.

From http://sourceforge.net/projects/pyexcelerator
"""
Generating Excel 97+ files with Python 2.4+ (need decorators), importing Excel 95+ files, support for UNICODE in Excel files, using variety of formatting features and printing options, Excel files and OLE2 compound files dumper. No need in Windows/COM
"""

JF

Re: excel formatting

Phil.Howell — Mon, 18 Dec 2006 01:42:07 GMT

other options ...

Leave the data as csv, but provide a macro to do the formatting.
see http://support.microsoft.com/kb/272729

You could construct an XML file instead of csv and then use excel to open as a read-only workbook.
see http://office.microsoft.com/en-us/excel/HA011019641033.aspx

Phil

Re: excel formatting

Robert Atkinson — Mon, 18 Dec 2006 03:57:24 GMT

Jerry,
PHP is capable of creating Excel, Word, PDF, etc files :-

http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.intro.php

PHP comes free with VMS, and the advantage is it's very easy to learn and create new scripts.

Rob.

Re: excel formatting

EdgarZamora_1 — Mon, 18 Dec 2006 12:26:16 GMT

You can simply just use MIME on the VMS side and then email the file to the users just as you're doing now. Is that not good enough?

$ mime :== $SYS$SYSTEM:MIME.EXE
$ mime
MIME> new filetosend.csv
*exit
MIME> add yourcsv.csv
MIME> save
MIME> exit
$ mail/subj="blah" filetosend.csv "me@me.com"

The Outlook recipient should be able to double-click on the attachment and get a nicely formatted CSV file in EXCEL.

Hope that helps.

Re: excel formatting

Craig A Berry — Mon, 18 Dec 2006 12:42:28 GMT

Hein, there is a Perl module called Spreadshet::WriteExcel:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.17/

I can't remember if I've ever used it on OpenVMS, but it's pure Perl (no C compiler necessary) so it should be easy enough to install if you have Perl.

I have used the Perl module RTF::Writer on VMS to create tabular reports in rich text format, and as others have indicated, there are a number of ways to create and send files in PC-friendly formats.

What the OP chooses to use as a solution depends in part on where the data are coming from and what tools he is comfortable using. I.e., do you need to pull data out of a database or RMS indexed file? What methods do you currently use to do that? Which of the many methods for sending e-mail attachments are you using or comfortable with?

As an example, if I had data in an RMS indexed file that needed to be sent as an Excel attachment, I would use Perl and the modules VMS::IndexedFile to retrieve the data, Spreadsheet::WriteExcel to generate the reports, and MIME::Lite to send the files as e-mail attachments.

Re: excel formatting

Andy Bustamante — Mon, 18 Dec 2006 14:09:09 GMT

We generate tab delimited files from a browser based database. The user downloads these and Excel seems to handle the format with less user prompting.

Andy