[ic] backup_database coretag -- some suggested improvements

Mike Heins mike at perusion.com
Fri May 12 16:33:49 EDT 2006


Quoting Carl Bailey (carl at triangleresearch.com):
> We ran into a problem recently doing a multi-table export to an xls 
> file from the UI in IC 5.4.  Basically the export looked like it was 
> working, but the result was a zero-byte xls file.  Well, after much 
> toil and strife, we found out that there is a file-size limit of 
> 7087104 bytes imposed in the Workbook.pm (part of 
> Spreadsheet::WriteExcel).  It's not clear what's behind that 
> odd-sounding number, but right there in the module it says, if you need 
> bigger spreadsheets, use Spreadsheet::WriteExcel::Big.  That seems to 
> be a better default module for Interchange to use.
> 
> We made some changes to ~interch/code/UI_Tag/backup_database.coretag
> 
> -                require Spreadsheet::WriteExcel;
> -                import Spreadsheet::WriteExcel;
> +                require Spreadsheet::WriteExcel::Big;
> +                import  Spreadsheet::WriteExcel::Big;
> 
> -                $xls = 
> Spreadsheet::WriteExcel->new("$backup_dir/DBDOWNLOAD.xls");
> +                $xls = 
> Spreadsheet::WriteExcel::Big->new("$backup_dir/DBDOWNLOAD.xls");
> 
> ... and this worked very well indeed, since the "Big" module was 
> already installed on our server.  No more zero-byte results.

Thanks for this, Carl.

Do you know of any downside to Spreadsheet::WriteExcel::Big? If
there is none, we can do:

    my $xls;

    eval {
        require Spreadsheet::WriteExcel::Big;
        import Spreadsheet::WriteExcel::Big;
        $xls = Spreadsheet::WriteExcel::Big->new("$backup_dir/DBDOWNLOAD.xls");
    } if $opt ->{xls};

    eval {
        require Spreadsheet::WriteExcel;
        import Spreadsheet::WriteExcel;
        $xls = Spreadsheet::WriteExcel->new("$backup_dir/DBDOWNLOAD.xls");
    } if $opt ->{xls} and ! $xls;

    undef $opt->{xls} unless $xls;

> 
> However, we also noticed that the coretag creates XLS files that are 
> much larger than necessary for sparsely populated tables (where many of 
> the columns are blank, as was true in our case).  So the following 
> change (also to backup_database.coretag) made the resulting XLS file 
> substantially smaller:
> 
> -                $sheet->write_string($i, $j, $fields[$j]);
> +                $sheet->write_string($i, $j, $fields[$j]) if 
> length($fields[$j]);
> 
> This same patch should also work for IC 5.2.  We have not checked 
> earlier versions.

This makes sense, and can go in straightway.

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

Unix version of an Outlook-style virus:
It works on the honor system. Please forward this message to everyone
you know, and delete a bunch of your files at random.


More information about the interchange-users mailing list