biswanath_c has asked for the wisdom of the Perl Monks concerning the following question:


Hi,


I am currently using the Spreadsheet::WriteExcel API to write data into spreadsheets in one of the tools that I developed. But looks like there is some kind of size constraint in the no. of characters that could be written to one cell. But I would like to write more data into the spreadsheet. Could you please suggest a better way that would allow me to write more data into one cell in a spreadsheet?


Thanks

Biswanath

Replies are listed 'Best First'.
Re: Writing to Spreadsheets in PERL...
by JavaFan (Canon) on May 10, 2010 at 14:17 UTC
    From the Spreadsheet::WriteExcel manual page: 
           The following limits are imposed by Excel:

           Description                          Limit
           -----------------------------------  ------
           Maximum number of chars in a string  32767
           Maximum number of columns            256
           Maximum number of rows               65536
           Maximum chars in a sheet name        31
           Maximum chars in a header/footer     254
    So, if you want more than 32767 characters in a cell, Excel isn't the appropriate format.
[reply]

      Thanks for the immediate reply... One of my friends was suggesting that i should write into XLSX files instead of XLS files to overcome this issue. Would you know anything about this? Would XLSX help me resolve this issue? How do I write into XLSX files from PERL?
[reply]

        Have you tried searching CPAN for XLSX? Still, (Excel) Spreadsheets are for presenting tabular information. It makes no sense to put so much information into one cell.

[reply]
[reply]

        biswanath_c:

        If it's an Excel limitation, then you may be able to put the data into an XLSX file, but then Excel will probably complain when you try to load the spreadsheet.

        ...roboticus

[reply]
        No idea. All I know is that Spreadsheet::{Read|Write}Excel doesn't do XLSX files. I always send them back if $WORK gives me one.
[reply]
        You might try Spreadsheet::WriteExcelXML which has the same API but fewer features than Spreadsheet::WriteExcel. The author recommends this module for exceeding the row limits imposed by .xls, perhaps you can exceed the character-cell limit also.

        Alternatively, if you have Excel 2007 you can use Win32::OLE and invoke Microsoft's API to make xlsx files.

[reply]
Re: Writing to Spreadsheets in PERL...
by Corion (Patriarch) on May 10, 2010 at 14:21 UTC

    If you're reaching the limits of Excel, maybe what you want to use instead is a database? are you sure that storing that much data per cell makes sense? Nobody will ever look at a cell that has more than 32000 characters in it.

[reply]
Re: Writing to Spreadsheets in PERL...
by CountZero (Bishop) on May 10, 2010 at 16:05 UTC
    Directly from Microsoft on Excel 2007:
    Characters in a cell that can be displayed and printed: Increased to 32,000.

    There is still a hard limit of 32,767 characters per cell of which you can see only 32,000 characters!

    So even using the new file format will not help you.

    And it does not improve in Excel 2010: The official blog of the Microsoft Excel product team: Some other numbers ....

    Fortunately some other limits were increased: rejoice all because your Excel 2010 workbook can now contain 4.3 billion unique colors!

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

[reply]
Re: Writing to Spreadsheets in PERL...
by sierpinski (Chaplain) on May 10, 2010 at 14:24 UTC
    I'm surprised nobody yet has mentioned the OP's reference to PERL.

    On a lighter note, I agree with Corion. If you need to store a lot of data, a database is what you should be using. Having a cell with more than 30,000 characters is just screaming for problems.
[reply]

Back to Seekers of Perl Wisdom