Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Need to generate excel having more than 70,000 records

by gube (Parson)
on Apr 02, 2009 at 02:08 UTC ( #754831=perlquestion: print w/ replies, xml ) Need Help??
gube has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I am generating an excel file based on the data from my table. The record exceeded more than 70,000. But, when i create excel using Spreadsheet::WriteExcel i cannot able to create records more than 65,536. Since my client using latest windows excel he is expecting all the records having more than 70,000. Can any one please throw me some modules or ideas to proceed ?

Please see below sample code:

use Spreadsheet::WriteExcel; # Create a new Excel workbook my ($col, $row); $col = $row = 0; my $workbook = Spreadsheet::WriteExcel->new('perl1.xls'); # Add a worksheet my $worksheet = $workbook->add_worksheet("test"); for (0..70000) { $worksheet->write($_, $col, "Testing"); } $workbook->close();

Comment on Need to generate excel having more than 70,000 records
Download Code
Re: Need to generate excel having more than 70,000 records
by Ish (Acolyte) on Apr 02, 2009 at 02:15 UTC
    Can you not just produce a CSV file for them to import? Just a thought if the current module does not support > 64k rows.
Re: Need to generate excel having more than 70,000 records
by gube (Parson) on Apr 02, 2009 at 02:19 UTC

    Thanks Ish. But, they need excel since for some formatting purpose.

    Gubs
      Hi, I'm sure they will use Excel, but there is any reason why you couldn't produce a CSV file and let them import it into Excel? CSV files are the most frequently used 'glue' between things like databases and spreadsheets (and ordinary files of course). A sort of common denominator as it were.
        There is absolutely a good reason and you were just told it. CSV is the wrong answer if you want any control of formatting. And there are a lot of valid reasons to care about formatting, from keeping Excel from breaking things by trying to convert things to dates to producing something that looks nice because that is what the client was promised.

      Ish is correct. If the report is just plain tabular data then just create a CSV file and slap a '.xls' extension on the end of it. When you go to open it with Excel, Excel will do the right thing.

      -derby
Re: Need to generate excel having more than 70,000 records
by Gangabass (Priest) on Apr 02, 2009 at 02:23 UTC

    The only way i can think is to use Win32::OLE.

Re: Need to generate excel having more than 70,000 records
by whakka (Hermit) on Apr 02, 2009 at 02:56 UTC
    Excel is not the way to go for lots of data - no module will give you what you want since the limiting factor is built into Excel itself. I would follow the advice of Ish and output .csv which can be read in many types of software and inform your client what he/she wants isn't possible. Either that or split the data into multiple worksheets.

    Here's Microsoft's outline of Excel's specification and limits.

    Update: It appears I should have read the fine print, sorry for the dated information.

      Excel 2007 no longer has those limits. (Well it has limits, but they are much more generous ones.)

      The OP didn't specify which version of Excel would need to parse the file so I think that you have provided a perfectly acceptable answer.

        OP: "Since my client using latest windows excel he is expecting all the records having more than 70,000.".


        True laziness is hard work
Re: Need to generate excel having more than 70,000 records
by tilly (Archbishop) on Apr 02, 2009 at 04:48 UTC
    The format produced by Spreadsheet::WriteExcel is the one used by older versions of Excel. As you've found, it has the limitations of the older version of Excel. Therefore you need to use a newer format.

    The simplest option is csv. If this will not work (formatting is the usual reason why it won't) then you can step things up by creating an HTML file with a single table. Excel will load this without difficulty and you can do some formatting, but you will not have access to more advanced features of Excel. If you want that, Excel now saves files internally as XML in a zipped file. Nothing in principle stops you from creating the same exact XML.

    If you wish to go this direction I would advise creating an Excel spreadsheet that looks like what you want it to look like and has all of the features you want. Unzip it. Then open it up and see if you can find where the stuff that is important for the data is. Then dynamically write that part, leave alone anything you can, and zip it up again. Then pray and try opening your file in Excel.

    If you're really brave you could go to http://technet.microsoft.com/en-us/library/cc179190.aspx and try to figure out the format from the documentation that Microsoft provides.

Re: Need to generate excel having more than 70,000 records
by imrags (Monk) on Apr 02, 2009 at 09:30 UTC
    Use Win32::OLE
    a good tutorial can be found at Tutorial
    Raghu
Re: Need to generate excel having more than 70,000 records
by holli (Monsignor) on Apr 02, 2009 at 09:40 UTC
    Try generating the file in the Excel XML-Format.


    holli

    When you're up to your ass in alligators, it's difficult to remember that your original purpose was to drain the swamp.
Re: Need to generate excel having more than 70,000 records
by jurple (Initiate) on Apr 02, 2009 at 10:46 UTC
    Could you convince them to accept the data spread over multiple worksheets in the same workbook? That would be a very quick fix...
Re: Need to generate excel having more than 70,000 records
by dragonchild (Archbishop) on Apr 02, 2009 at 12:33 UTC
    use Excel::Template; my $template = Excel::Template->new( RENDERER => Excel::Template->RENDER_XML, );
    or Excel::Template->RENDER_BIG. Either way, E::T handles all that stuff for you.

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
      I had not known about this module. Now that I know about it, it is clearly the right way to go.
      I don't think that will work. Excel::Template says those options use Spreadsheet::WriteExcelXML or Spreadsheet::WriteExcel::Big as backends, and those modules still say they have a row limit of 65536. Spreadsheet::WriteExcel::Big lets you create files > 7MB, but still has the 65536 rows limit.

      Update: The XML "template" of Excel::Template is a mini-DSL that the author uses to produce different kinds of files using the same code, and has nothing to do with the XML in Excel.

Re: Need to generate excel having more than 70,000 records
by sundialsvc4 (Monsignor) on Apr 02, 2009 at 12:34 UTC

    A client who's got 70,000 rows in a spreadsheet needs to learn about Microsoft Excel lists. Especially when used in conjunction with Microsoft SharePoint (which is a thin wrapper over SQL databases and file-synchronization technology), this represents a much more intelligent way to manage this data.

    And if you don't want to go that way, well, the client undoubtedly has Microsoft Access, and it is easy to define a spreadsheet list which links directly and invisibly to that database. They won't have to change their data, formatting or basic work-flow.

    (Eeeee-yuck! I've just caught myself recommending Microsoft, and I didn't say one thing about Perl!)   ;-)

Re: Need to generate excel having more than 70,000 records
by roboticus (Canon) on Apr 02, 2009 at 16:22 UTC
    gube:

    If you need only limited control of formatting, you could emit the data as an HTML document and load it into Excel. I've had good success with that in the past, with the ability to control much (but not all) of the formatting.

    ...roboticus
      I like imrags' suggestion. Win32::OLE is cool. You can do pretty much anything you do with Visual Basic inside Excel.

      The drawback is that you need Excel installed and running (i.e., you have to open your document to start working on it). If you have the newer version that supports the number of records you want, you're good to go.

      VMat

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://754831]
Approved by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (10)
As of 2014-07-29 23:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (229 votes), past polls