Incomplete file write under Dancer2

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

I want to generate a report (which must be in XLSX format). I have run the code in the SYNOPSIS of Excel::Writer::XLSX successfully. If I run the exact same code in the context of a Dancer2 route, the resulting file is incomplete. The file is created, the file is a valid zip file and can be opened in a zip program, but it only contains one of the several files and folders that make up this file type. There are no errors returned from any of the methods.

I have found the file handle property of the workbook object and called flush() on it, I have tried waiting a few seconds after workbook close. No joy.

This is the code that works:

use Excel::Writer::XLSX;
 
# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
 
# Add a worksheet
$worksheet = $workbook->add_worksheet();
 
#  Add and define a format
$format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );
 
# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write( $row, $col, 'Hi Excel!', $format );
$worksheet->write( 1, $col, 'Hi Excel!' );
 
# Write a number and a formula using A1 notation
$worksheet->write( 'A3', 1.2345 );
$worksheet->write( 'A4', '=SIN(PI()/4)' );
 
$workbook->close();
[download]

This is the code that doesn't (other methods and uses hidden):

use Dancer2;

post '/report' => sub {

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
 
# Add a worksheet
my $worksheet = $workbook->add_worksheet();
 
#  Add and define a format
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );
 
# Write a formatted and unformatted string, row and column notation.
my $col = my $row = 0;
$worksheet->write( $row, $col, 'Hi Excel!', $format );
$worksheet->write( 1, $col, 'Hi Excel!' );
 
# Write a number and a formula using A1 notation
$worksheet->write( 'A3', 1.2345 );
$worksheet->write( 'A4', '=SIN(PI()/4)' );
 
$workbook->close();

    template 'ok';
};
[download]

I'm aware I could generate an ODS instead and give that to the Excel-wielding masses, but I'd like to try and fix this first. Thanks!

Comment on Incomplete file write under Dancer2 Select or Download Code

Replies are listed 'Best First'.
Re: Incomplete file write under Dancer2 by bliako (Abbot) on Jul 24, 2024 at 08:46 UTC
Excel::Writer::XLSX uses Archive::Zip internally. So you can test that this does the job right inside your provided Dancer2 handler. But it is likely that the culprit is here: https://metacpan.org/module/Excel::Writer::XLSX::Workbook/source#L1195 where File::Find finds files and also in the previous line: `my $wanted = sub { push @xlsx_files, $File::Find::name if -f };` [download] If for some reason the file paths are ~~different~~ not visible or not accessible inside Dancer then the above silently ignores them. One simple way to debug this is to edit https://metacpan.org/module/Excel::Writer::XLSX::Workbook/source#L1195 and print messages etc. as to which files are found and added (`whichpm Excel::Writer::XLSX::Workbook` or `perldoc -D Excel::Writer::XLSX::Workbook` will tell you where said module is located)	[reply] [d/l] [select]
Re^2: Incomplete file write under Dancer2 by realflash (Acolyte) on Jul 25, 2024 at 18:02 UTC
Getting closer... After sprinkling File::Find with print statements I find that in the latest version of File::Find does this at line 444: `if (-d _) {` This is the code that decides whether or not the thing it has found is a directory, and whether to recurse into it. Under Dancer this test fails for the subdirectories and it never recurses into them. But what is _ meant to be? Is that a thing I don't know about? If I change it to $_ it does recurse into the directory, and I end up with a valid XLSX file. There are lots of uses of an underscore on its own across this module.	[reply] [d/l]
Re^3: Incomplete file write under Dancer2 by swl (Prior) on Jul 25, 2024 at 23:04 UTC
Possibly it is related to the lstat call on line 442. What happens if you add a stat call with the same arguments immediately after it? This will reset the value in _ `... $sub_nlink = (lstat ($no_chdir ? $dir_pref . $FN : $FN))[3]; my $debug = stat ($no_chdir ? $dir_pref . $FN : $FN))[3]; if (-d _) { ...` [download]	[reply] [d/l]
Re^3: Incomplete file write under Dancer2 by NERDVANA (Priest) on Jul 25, 2024 at 19:14 UTC
I forget where this is described in the docs, but it refers to the most recently called 'stat' results. It's handy, but a bit arcane.	[reply]
Re^4: Incomplete file write under Dancer2 by pryrt (Abbot) on Jul 25, 2024 at 19:25 UTC
Re^2: Incomplete file write under Dancer2 by realflash (Acolyte) on Jul 25, 2024 at 16:27 UTC
Thank you, you were right that the problem is here; interestingly it seems to be that File::Find enumerates the directories but then doesn't recurse into them. Whereas on the command line it does. Thus the files at the top level end up in the zip file, but anything below it doesn't. This is all happening in a temp dir, so it can't be a permissions things as the files in the temp directory are being created (successfully) by the same process as creates the temp directory; indeed I see the user running Dancer (me) has full control over the temp dir, the top level files and the subdirs and the files in them as I would expect; identical permissions throughout. This is all on Windows. So why is File::Find not recursing in?	[reply]
Re^2: Incomplete file write under Dancer2 by realflash (Acolyte) on Jul 25, 2024 at 18:54 UTC
The problem is actually the line before - the stat of the sub-directories is failing so it can't tell if the file is a directory, so it is not recursing into it. Getting closer, and further out of my depth.	[reply]