[ic] query2xls UserTag

[Carl Bailey]

On Oct 17, 2009, at 3:33 PM, Mike Heins wrote:

> Quoting Carl Bailey (carl at endpoint.com):
>>
>> On Oct 17, 2009, at 2:28 PM, Mike Heins wrote:
>>
>>> Haven't sent a new usertag skyward in a while....will work both
>>> with nvend and Interchange. Attached as well as inline.
>>>
>>> # Copyright 2009 Perusion <mikeh at perusion.com>
>>> #
>>> # This program is free software; you can redistribute it and/or  
>>> modify
>>> # it under the terms of the GNU General Public License as  
>>> published by
>>> # the Free Software Foundation; either version 2 of the License, or
>>> # (at your option) any later version.  See the LICENSE file for
>>> details.
>>> #
>>> # $Id: query2xls.tag,v 1.0 2009-10-12 22:02:57 mheins Exp $
>>>
>>> UserTag query2xls AddAttr
>>> UserTag query2xls Version  $Revision: 1.00 $
>>> UserTag query2xls Documentation <<EOD
>>> =head1 NAME
>>>
>>> query2xls -- Create XLS spreadsheet files from a SQL query
>>>
>>> =head1 SYNOPSIS
>>>
>>>  [query2xls
>>>  		query="select field1,field2,field3 from table1"
>>> 		sheet-name="Sheet 1 of 1"
>>> 		file-name="file-to-create.xls"
>>> 		base="tablename"
>>> 		deliver=1
>>> 		width=NN
>>> 		max-width=NNN
>>> 	]
>>>
> [snip]
>> May I suggest using Spreadsheet::WriteExcel::Big if available. (See
>> usage in backup_database.coretag.)
>> This will support more rows per sheet for larger tables, bigger
>> queries and newer versions of Excel.
>
> Deprecated in the latest version of Spreadsheet::WriteExcel:
>
>    NAME
> 	   Big - A class for creating Excel files > 7MB.
>
>    SYNOPSIS
> 	   The direct use of this module is deprecated. See below.
>
>    DESCRIPTION
> 	   The module is a sub-class of Spreadsheet::WriteExcel used for  
> creating Excel files greater than
> 	   7MB.
>
> 	   Direct use of this module is deprecated. As of version 2.17  
> Spreadsheet::WriteExcel can create
> 	   files larger than 7MB if OLE::Storage_Lite is installed.
>
> 	   This module only exists for backwards compatibility.
>

News to me.  In light of this, the I guess it would be smart to remove  
the reference to the "Big" version of the module from  
backup_database.coretag


>
>> Also consider supporting a maxrows setting in $opt.
>
> Don't think it's needed -- can you explain why it would be?


Just that Spreadsheet::WriteExcel has hard coded limits of 65536 rows  
and 256 columns and it's easy to find tables or queries that exceed  
those constraints.  The Spreadsheet module essentially ignores data  
outside those hard-coded bounds (or you can check the return codes on  
each write_string() call in the tag)  It seems like it would be  
possible to *optionally* specify constraints to the tag and let it  
more efficiently detect and handle the situation.  Why loop through  
and write out 65000+ rows to a worksheet when it will not contain the  
whole result set?

Excel 2007 allows more rows and columns, but the CPAN module has not  
caught up yet.

Finally, I think it might be a good idea to set compatability_mode()  
on the workbook object to make the XLS file generated more generically  
usable by apps other than Excel itself.

Carl
. . . . . . . . . . . . . . . . . .
Carl Bailey
End Point Corp.
t: 919-323-8025
. . . . . . . . . . . . . . . . . .