Question regarding error when trying to generate spreadsheet with Excel::Writer::XLSX

atcroft has asked for the wisdom of the Perl Monks concerning the following question:

(Apologies, but since I am not on my work system at the moment, the following is based on memory.)

Last week I was working on several scripts for $job[0] that generate and email data in spreadsheets. Previously I have used (very successfully) Spreadsheet::WriteExcel to generate XLS documents, but for various reasons was asked to change to generating XLSX documents. Following the suggestion of the documentation, I modified the script on Thursday to instead use the Excel::Writer::XLSX module. (The extent of the changes were to change the use statement, change the extension on the generated filename, and the new() call.) Friday morning, two of the scripts failed to generate their expected output, instead complaining about "Couldn't create subdirectory xl/worksheets".

I spent the better part of Friday trying such things as:

changing croak() to confess() to try to get more information,
attempting to set permissions (via chmod) on the directory (and/or its parents) in the Excel::Writer::XLSX::Package::Packager::_mkdir routine,
specifying a temp directory using the set_tempdir() method (specified directory was set as 01777 permissions),
adding in File::Path and using the mkpath function to build the required directories,
installing a newer version of Excel::Writer::XLSX (System is running on a RH6.x-based system, meaning perl 5.10.x, and Excel::Writer::XLSX v. 0.84.)

All to this point has been to no avail. (I cannot recall if there was anything else I tried. In my attempts at trying to solve the problem myself, I did notice that File::Temp is used to create the temporary directory, and in trying to track if that might be the cause saw a number of comments regarding permissions on temporary files and directories. I am not sure at the moment if that bit of information adds to or detracts from the question, however.)

Anyone run into this before, and more importantly, can offer a possible way out?

Thank you in advance for your time, attention, and any assistance you may be able to provide.

Comment on Question regarding error when trying to generate spreadsheet with Excel::Writer::XLSX

Replies are listed 'Best First'.

Re: Question regarding error when trying to generate spreadsheet with Excel::Writer::XLSX
by bliako (Monsignor) on Jul 01, 2018 at 23:39 UTC

In Excel::Writer::XLSX::Package::Packager there is:

sub _create_package {
    ...
    $self->_write_worksheet_files();
}   
sub _write_worksheet_files {
    my $self = shift;
    my $dir  = $self->{_package_dir};

    _mkdir( $dir . '/xl' );
    _mkdir( $dir . '/xl/worksheets' );
    ...
}
sub _mkdir {
    my $dir = shift;

    return if -e $dir;

    my $ret = mkdir( $dir );

    if ( !$ret ) {
        croak "Couldn't create sub directory $dir: $!";
    }
}
[download]

In Excel::Writer::XLSX::Workbook there is:

sub new {
    my $class = shift;
    my $self  = Excel::Writer::XLSX::Package::XMLwriter->new();

    ...
    $self->{_tempdir}            = undef;
    ...
}
sub _store_workbook {
    ...
    my $tempdir  = File::Temp->newdir( DIR => $self->{_tempdir} );
    ...
    $packager->_set_package_dir( $tempdir );
    $packager->_create_package();
    ...
}
sub set_tempdir {
    my $self = shift;
    my $dir  = shift;

    croak "$dir is not a valid directory" if defined $dir and not -d $
+dir;

    $self->{_tempdir} = $dir;
}
[download]

The Workbook most likely is created with an undef _tempdir (the default). It passes a $tempdir to Packager via a call to File::Temp->newdir( DIR => $self->{_tempdir} ). E.g. for me, it created drwx------. /tmp/4zsSPLpSBSM when given DIR=>undef. The tempdir created in this way is supposedly automatically deleted.

Then Workbook calls the packager with previous tempdir value as its _package_dir

Then Packager creates subdir 'xl', and then 'xl/worksheets'. _mkdir() will not complain if asked to re-create already existing dir (or file!, it checks -e)

I would set some debug messages as follows (in ..../perl5/Excel/Writer/XLSX/Package/Packager.pm):

sub _store_workbook {
    ...
    my $tempdir  = File::Temp->newdir( DIR => $self->{_tempdir} );
    print "tempdir='$tempdir'\n"; # debug
    if( ! -d $tempdir ){ croak "$tempdir was not created" } # debug
        my $mode = (stat($tempdir))[2];
        printf "Permissions are %04o\n", $mode & 07777;
    
    ...
    $packager->_set_package_dir( $tempdir );
    $packager->_create_package();
    ...
}
[download]

[reply]
[d/l]
[select]


Come for the quick hacks, stay for the epiphanies.
	PerlMonks