[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