Excel::Writer on OpenVMS

[Brian Schenkenberger, VAXman-]

Hi,

I need to get Excel::Writer functioning on VMS. If you download the first
of the example scripts for this package, you'll see that it complains when
trying to create temporary directories into which worksheets and workbooks
are stored.

I've played about with defining various DECC$features logicals and results
change but the errors all seem to focus about the creation of the temporary
directories and files.

Has anyone got this package working on VMS?

Thanks in advance.

FWIW, I did have Spreadsheet::WriteExcel working to write .XLS files but I
now need to write .XLSX files and Excel::Writer::XLSX looks to be the only
package available.

--
VAXman- A Bored Certified VMS Kernel Mode Hacker VAXman(at)TMESIS(dot)ORG

I speak to machines with the voice of humanity.

[Craig A. Berry]

> On Oct 29, 2015, at 2:40 PM, Craig A. Berry <craig...@me.com> wrote:
>
>
> $ perl -"I[.blib.lib]" examples/array_formula.pl
> (in cleanup) Couldn't create sub directory mda0:[craig.scratch.QHki3CLXLC]/xl: no such file or directory at blib/lib/Excel/Writer/XLSX/Workboo
> k.pm line 957.
>

The fix for that is simple:

$ gdiff -pu lib/Excel/Writer/XLSX/Workbook.pm;-0 lib/Excel/Writer/XLSX/Workbook.pm
--- lib/Excel/Writer/XLSX/Workbook.pm;-0 2015-10-29 14:09:16 -0500
+++ lib/Excel/Writer/XLSX/Workbook.pm 2015-10-29 14:47:56 -0500
@@ -918,6 +918,8 @@ sub _store_workbook {
my $packager = Excel::Writer::XLSX::Package::Packager->new();
my $zip = Archive::Zip->new();

+ # We'll be appending Unix-style paths onto this.
+ $tempdir = VMS::Filespec::unixpath($tempdir) if $^O eq 'VMS';

# Add a default worksheet if non have been added.
$self->add_worksheet() if not @{ $self->{_worksheets} };
[end]

Now it runs but creates empty files. I’ll have to look into that sometime but have to get back to real work at the moment.

________________________________________
Craig A. Berry
mailto:craig...@mac.com

"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser

[Craig A. Berry]

> On Oct 29, 2015, at 1:53 PM, Brian Schenkenberger, VAXman- <sys...@TMESIS.COM> wrote:
>
> Hi,
>
> I need to get Excel::Writer functioning on VMS. If you download the first
> of the example scripts for this package, you'll see that it complains when
> trying to create temporary directories into which worksheets and workbooks
> are stored.

What do those complaints look like? This, perhaps?

$ perl -"I[.blib.lib]" examples/array_formula.pl
(in cleanup) Couldn't create sub directory mda0:[craig.scratch.QHki3CLXLC]/xl: no such file or directory at blib/lib/Excel/Writer/XLSX/Workboo
k.pm line 957.

Looks like mongrel syntax to me. It’s getting a temp file in native syntax, then pasting “/xl” onto it. I’ll see if I can spot where it’s doing that and what to do about it as this would be a very nice module to have available.

> I've played about with defining various DECC$features logicals and results
> change but the errors all seem to focus about the creation of the temporary
> directories and files.
>
> Has anyone got this package working on VMS?

I think I might’ve built a previous version once but don’t recall actually using it.

>
> Thanks in advance.
>
> FWIW, I did have Spreadsheet::WriteExcel working to write .XLS files but I
> now need to write .XLSX files and Excel::Writer::XLSX looks to be the only
> package available.
>
> --
> VAXman- A Bored Certified VMS Kernel Mode Hacker VAXman(at)TMESIS(dot)ORG
>
> I speak to machines with the voice of humanity.

[Craig A. Berry]

> On Oct 29, 2015, at 2:58 PM, Craig A. Berry <craig...@me.com> wrote:

> Now it runs but creates empty files. I’ll have to look into that sometime but have to get back to real work at the moment.

Oh, for goodness sake, when are these messages ever going to make it through moderation and show up on list?

I got somewhat farther by setting this:

$ DEFINE DECC$FILENAME_UNIX_REPORT 1

and testing with [.examples]demo.pl, but the resulting file is corrupt. Since an xlsx file is just a zip file containing a bunch of different Excel-specific files, you can examine its contents with unzip. Here’s what’s in it when created on VMS:

$ unzip -l demo.xlsx
Archive: demo.xlsx
Length Date Time Name
-------- ---- ---- ----
848 10-29-15 17:49 docProps/app.xml
592 10-29-15 17:49 docProps/core.xml
606 10-29-15 17:49 xl/sharedStrings.xml
1992 10-29-15 17:49 xl/styles.xml
652 10-29-15 17:49 xl/workbook.xml
1018 10-29-15 17:49 xl/drawings/drawing1.xml
291 10-29-15 17:49 xl/drawings/_rels/drawing1.xml.rels
6263 10-29-15 17:49 xl/media/image1.png
6995 10-29-15 17:49 xl/theme/theme1.xml
1802 10-29-15 17:49 xl/worksheets/sheet1.xml
438 10-29-15 17:49 xl/worksheets/sheet2.xml
438 10-29-15 17:49 xl/worksheets/sheet3.xml
460 10-29-15 17:49 xl/worksheets/_rels/sheet1.xml.rels
979 10-29-15 17:49 xl/_rels/workbook.xml.rels
587 10-29-15 17:49 _rels/.rels
-------- -------
23961 15 files

Here’s what’s in it when created on OS X:

$ unzip -l demo.xlsx
Archive: demo.xlsx
Length Date Time Name
-------- ---- ---- ----
1609 10-29-15 18:02 [Content_Types].xml
587 10-29-15 18:02 _rels/.rels
848 10-29-15 18:02 docProps/app.xml
592 10-29-15 18:02 docProps/core.xml
606 10-29-15 18:02 xl/sharedStrings.xml
1992 10-29-15 18:02 xl/styles.xml
652 10-29-15 18:02 xl/workbook.xml
979 10-29-15 18:02 xl/_rels/workbook.xml.rels
1018 10-29-15 18:02 xl/drawings/drawing1.xml
291 10-29-15 18:02 xl/drawings/_rels/drawing1.xml.rels
6263 10-29-15 18:02 xl/media/image1.png
6995 10-29-15 18:02 xl/theme/theme1.xml
1802 10-29-15 18:02 xl/worksheets/sheet1.xml
438 10-29-15 18:02 xl/worksheets/sheet2.xml
438 10-29-15 18:02 xl/worksheets/sheet3.xml
460 10-29-15 18:02 xl/worksheets/_rels/sheet1.xml.rels
-------- -------
25570 16 files

Nothing looks greatly amiss in the VMS version except that the oddly named file [Content_Types].xml is missing. The file does exist in the temp directory on VMS:

$ dir/size [...]^[Content_Types^].xml;1

Directory MDA0:[CRAIG.SCRATCH.bGENbxoyB2]

^[Content_Types^].xml;1
3

Total of 1 file, 3 blocks.

so something is preventing that file from getting added to the zip/xlsx archive. That’s got to be happening somewhere around here:

https://github.com/jmcnamara/excel-writer-xlsx/blob/master/lib/Excel/Writer/XLSX/Workbook.pm#L962

and needs further investigation.

[Craig A. Berry]

> On Oct 29, 2015, at 6:44 PM, Craig A. Berry <craig...@me.com> wrote:
>
>
> I got somewhat farther by setting this:
>
> $ DEFINE DECC$FILENAME_UNIX_REPORT 1
>
> and testing with [.examples]demo.pl, but the resulting file is corrupt.

> Nothing looks greatly amiss in the VMS version except that the oddly named file [Content_Types].xml is missing. The file does exist in the temp directory on VMS:
>
> $ dir/size [...]^[Content_Types^].xml;1
>
> Directory MDA0:[CRAIG.SCRATCH.bGENbxoyB2]
>
> ^[Content_Types^].xml;1
> 3
>
> Total of 1 file, 3 blocks.
>
> so something is preventing that file from getting added to the zip/xlsx archive.

And that something is the following line of code:

my $wanted = sub { push @xlsx_files, $File::Find::name if -f };

The -f file test operator is a pretty thin wrapper around the CRTL stat function, which fails with [Content_Types].xml, presumably because, while it’s a valid Unix-format specification, it’s also a valid VMS-format directory and file specification. There is no choice about what to name this file — it’s a standard part of an XLSX archive.

If you give it a hint by prepending a little Unix syntax (“./“) or escape the brackets to make it unambiguously a VMS-syntax file, it’s ok, but on its own it is not recognized as a file.

$ perl -e "print -f './[Content_Types].xml' ? 'Y' : 'N';"
Y
$ perl -e "print -f '^[Content_Types^].xml' ? 'Y' : 'N';"
Y
$ perl -e "print -f '[Content_Types].xml' ? 'Y' : 'N’”
N

The solution is simple because what the code in question is doing is trying to exclude directories from its list of files to include in the archive and only include files. So instead of saying “is it a file?” we can say “is it not a directory?”, like so:

$ gdiff -pu lib/Excel/Writer/XLSX/Workbook.pm;-0 lib/Excel/Writer/XLSX/Workbook.pm
--- lib/Excel/Writer/XLSX/Workbook.pm;-0 2015-10-29 14:09:16 -0500

+++ lib/Excel/Writer/XLSX/Workbook.pm 2015-10-30 09:14:16 -0500
@@ -964,7 +964,7 @@ sub _store_workbook {
# with File::Find and pass each one to addFile().
my @xlsx_files;

- my $wanted = sub { push @xlsx_files, $File::Find::name if -f };
+ my $wanted = sub { push @xlsx_files, $File::Find::name unless -d };

File::Find::find(
{
[end]

That in conjunction with DECC$FILENAME_UNIX_REPORT is all you need to get a valid XLSX file. You can ignore my previous suggestion to convert the temp directory to Unix format as the DECC$ setting already does the equivalent. In case it’s not obvious you also need an ODS-5 disk and a recentish version of Perl (I tested with 5.20.1).

It still fails to clean up the temp directory and I think it’s that same [Content_Types].xml file that is causing the problem. Don’t have a solution for that yet.

[Carl Friedberg]

Craig,

Wow, thanks for your meticulous research. Great job!

Carl

Carl Friedberg
frie...@esb.com (212) 798-0718
www.esb.com
The Elias Book of Baseball Records
2015 Edition


-----Original Message-----
From: Craig A. Berry [mailto:craig...@mac.com]
Sent: Friday, October 30, 2015 4:59 PM
To: vmsperl (vms...@perl.org)
Subject: Re: Excel::Writer on OpenVMS

> On Oct 30, 2015, at 9:43 AM, Craig A. Berry <craig...@mac.com> wrote:
>
> It still fails to clean up the temp directory and I think it’s that same [Content_Types].xml file that is causing the problem. Don’t have a solution for that yet.

Now I do. When the File::Temp destructor tries to clean up the temp directory, it calls File::Path::rmtree, which navigates to each directory and deletes each file in it before deleting the directory. That fails because [Content_Types].xml as a relative specification is ambiguous, so the delete of that file fails and then the attempt to delete the directory containing it fails.

Deleting [Content_Types].xml as part of an absolute path is fine, though, as it’s not ambiguous when there are directory components present. We can help things out by deleting the files we’ve created before the File::Temp destructor ever attempts to go after them.

So along with DECC$FILENAME_UNIX_REPORT, the following gets us a working Excel::Writer::XLSX on VMS:

$ gdiff -pu lib/Excel/Writer/XLSX/Workbook.pm;-0 lib/Excel/Writer/XLSX/Workbook.pm
--- lib/Excel/Writer/XLSX/Workbook.pm;-0 2015-10-29 14:09:16 -0500

+++ lib/Excel/Writer/XLSX/Workbook.pm 2015-10-30 13:07:34 -0500

@@ -964,7 +964,7 @@ sub _store_workbook {
# with File::Find and pass each one to addFile().
my @xlsx_files;

- my $wanted = sub { push @xlsx_files, $File::Find::name if -f };
+ my $wanted = sub { push @xlsx_files, $File::Find::name unless -d };

File::Find::find(
{

@@ -1010,6 +1010,7 @@ sub _store_workbook {
print { $self->{_filehandle} } $buffer;
}
}
+ for my $f ( @xlsx_files ) { 1 while unlink $f; }
}


[end]

[Craig A. Berry]

> On Oct 30, 2015, at 9:43 AM, Craig A. Berry <craig...@mac.com> wrote:
>

> It still fails to clean up the temp directory and I think it’s that same [Content_Types].xml file that is causing the problem. Don’t have a solution for that yet.

Now I do. When the File::Temp destructor tries to clean up the temp directory, it calls File::Path::rmtree, which navigates to each directory and deletes each file in it before deleting the directory. That fails because [Content_Types].xml as a relative specification is ambiguous, so the delete of that file fails and then the attempt to delete the directory containing it fails.

Deleting [Content_Types].xml as part of an absolute path is fine, though, as it’s not ambiguous when there are directory components present. We can help things out by deleting the files we’ve created before the File::Temp destructor ever attempts to go after them.

So along with DECC$FILENAME_UNIX_REPORT, the following gets us a working Excel::Writer::XLSX on VMS:

$ gdiff -pu lib/Excel/Writer/XLSX/Workbook.pm;-0 lib/Excel/Writer/XLSX/Workbook.pm
--- lib/Excel/Writer/XLSX/Workbook.pm;-0 2015-10-29 14:09:16 -0500

+++ lib/Excel/Writer/XLSX/Workbook.pm 2015-10-30 13:07:34 -0500

@@ -964,7 +964,7 @@ sub _store_workbook {
# with File::Find and pass each one to addFile().
my @xlsx_files;

- my $wanted = sub { push @xlsx_files, $File::Find::name if -f };
+ my $wanted = sub { push @xlsx_files, $File::Find::name unless -d };

File::Find::find(
{

@@ -1010,6 +1010,7 @@ sub _store_workbook {
print { $self->{_filehandle} } $buffer;
}
}
+ for my $f ( @xlsx_files ) { 1 while unlink $f; }
}


[end]

[Craig A. Berry]

On Nov 02, 2015, at 11:09 AM, Brian Schenkenberger <VAX...@tmesis.com> wrote:

-----Original Message-----

From: Craig A. Berry [mailto:craig...@mac.com]

 

$ gdiff -pu lib/Excel/Writer/XLSX/Workbook.pm;-0 lib/Excel/Writer/XLSX/Workbook.pm

--- lib/Excel/Writer/XLSX/Workbook.pm;-0        2015-10-29 14:09:16 -0500

+++ lib/Excel/Writer/XLSX/Workbook.pm    2015-10-30 13:07:34 -0500

@@ -964,7 +964,7 @@ sub _store_workbook {

# with File::Find and pass each one to addFile().

Whatever version of the package was current on the CPAN the day you originally posted.  Apparently that's been 0.86 since 18 October.

Line 964 in mine is not:

 

sub _store_workbook {

No, of course not, but it is *within* the _store_workbook function.  The part after the second "@@" in a GNU unified diff section header is just a hint to the patch program and/or the human reader.

 

[Craig A. Berry]

On Nov 02, 2015, at 12:46 PM, Brian Schenkenberger <VAX...@tmesis.com> wrote:

I didn't realize that those line numbers were only hints.

The line numbers are exact for the context lines they refer to.  The hint is the function name in which those lines occur.  I would have sent a DIFFERENCES/SLP if I'd known the GNU diff would confuse you :-).

I was able to make the edits from what you'd posted and it works.
However, the one CSV file has over 35K lines giving an out of memory
error.

Have you tried the set_optimization() method as recommended here:

<http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.86/lib/Excel/Writer/XLSX.pm#SPEED_AND_MEMORY_USAGE>

?

[Brian Schenkenberger]

I added that. THe conversion completed without an out of memory error
but the resultant file is corrupt or, at least, LibreOffice seems to
think so. No M$ here with which to try.

[Brian Schenkenberger]

On Mon, 2015-11-02 at 18:39 +0000, Craig A. Berry wrote:
>
>
>

I didn't realize that those line numbers were only hints.

I was able to make the edits from what you'd posted and it works.
However, the one CSV file has over 35K lines giving an out of memory
error.

I do wish I could get the Spreadsheet::WriteExcel::Big to work. I'm at
a loss there. Any data that takes the .XLS file over 7M fails.

>
>

[Brian Schenkenberger]

What version of Workbook.pm do you have? Line 964 in mine is not:

sub _store_workbook {

[Craig A. Berry]

On Nov 02, 2015, at 01:35 PM, Brian Schenkenberger <VAX...@tmesis.com> wrote:

On Mon, 2015-11-02 at 18:54 +0000, Craig A. Berry wrote:

Have you tried the set_optimization()

I added that. THe conversion completed without an out of memory error
but the resultant file is corrupt or, at least, LibreOffice seems to
think so. No M$ here with which to try.

Have you messed about with page file quota and /or working set?    If the working data is over 2GB, the limitations imposed by 32-bit pointers could be kicking in, but if not, then giving it as much as it wants might do the trick.

[Brian Schenkenberger]

Actually, I sftped the .XLSX file to my Linux laptop. For some reason,
the first sftp quit before the entire file copied over. You known how
much UNZIP loves an incomplete archive. ;) A subsequent sftp pulled
the .XLSX file over and it's intact and looks beautiful!!! Even parts
of my code that embed logo graphics and setup cell formats (foreground
and background colors, fonts, etc.) appear to be fine!

Thanks for your help. That set_optimization() did the trick. It's not
fact on my DS10L test box but the customer has a cluster of much faster
boxes. I might see how quickly all this converts on my ES45 or ES47,
but I'm not overly concerned.