According to Microsoft Excel, the number of worksheets in a workbook is "limited only by available memory." Current versions of Excel cannot use more than 1 GB of memory even if more memory is available on the system. (Excel 2007 will use as much memory as the system will give it.)
In my experience, even if you could generate a well-formed workbook with 650 sheets, it is unlikely that any Excel user would be able to open it without running into Excel's memory limit. I'm impressed that you could even get to 200 sheets without a problem, honestly. :)
--
Jason
Some background: Spreadsheet::WriteExcel creates a temporary file for each worksheet and workbook. This helps to greatly reduce the amount of memory required by S::WE and increases the speed substantially.
Question: Is there a limitation in the number of sheet in a single workbook?
Not from Spreadsheet::WriteExcel. However, there is generally a system limit on the maximum number of open files and this will in turn impose a limit of the number of temporary files that the module can use. If a temporary file cannot be created then the module falls back to storing the data in memory. If you have warnings turned on in your program then you should see a warning message about this. (Note: there is also a memory limit in Excel as pointed out by Jason above but that is a separate issue).
Question: Does Spreadsheet-WriteExcel keeps those temporary files opened 'til the end.
Yes. S::WE needs to know the final size of each Worksheet before it can merge them into a workbook.
Question: What may explain that the final workbook be empty when *no* error is detected by the both Spreadsheet-WriteExcel package and my Perl program?
I'd suspect that you've exceeded S::WE's 7MB limit. Again "-w" or "use warnings" should tell you if this is happening. Try using Spreadsheet::WriteExcel::Big instead (just change the use and new() statements in your program).
John.Can you run the following program with some upper value that will exceed the maximum number of open files that your system allows:
Then let me know what the output is. Also, let me know your OS and perl version.
John.I need a little more information. Can you let me know the maximum open file value on your system returned by 'ulimit -n'. Also can you run the following program and post the output. Replace 2000 with something that is larger than the value of 'ulimit -n':
Also to ensure that we are comparing like with like can you post the output from the following program.
Failed to created file number 254.
This is the same limit that S::WE is encountering. I don't know why it is appoximately 256 and not 1024 as indicated by ulimit. Perhaps there is a smaller limit per process.
Anyway, the 253 file limit indicated by the above program is also the limit to the number of worksheets that you can add. If you can increase this limit then you should be able to add more worksheets.
John.> Just one last question, why then S::WE silently accept requests to add sheets above the system limit w/o issuing any warning messages?
I don't know why that is. When I ran the test program on a system similar to yours I got loud warnings from Perl about not being able to load Carp::Heavy (caused by a lack of available file handles).
John.