Charting via Excel hits a limit

[spurperl]

Dear monks,

In one of my projects I'm using Excel to generate charts from data (via Win32::OLE). I ran into a most annoying problem - Excel has a limit on the number of rows - 2^16.

Now, for ordinary users it's not a real problem, but when you generate an Excel spreadsheet programatically from Perl and expect to shove in lots of data !

Has anyone seen any workaround for this ?

I might have to start looking for other charting options, it seems.

[Limbic~Region]

spurperl,
The limit is per sheet is it not, with the number of sheets limited only by available memory? I assume you can create a graph that uses data points from multiple sheets. I have only used the Spreadsheet:: modules when playing with Excel otherwise I would try to put something together for you.

[spurperl]

The limit is indeed per sheet... but you're right, it might be possible to combine from multiple sheets, though I fear I may run into the same limitation with charts. Anyway I'll give it a try, thanks.

[neniro]

Ploticus http://ploticus.sourceforge.net/doc/welcome.html is fine for creating charts. You can find infos about ploticus limitations here: http://ploticus.sourceforge.net/doc/limits.html. It has no perl-api but it shouldn't be to hard to write a simple wrapper .

[davidrw]

Yeah, that's an extremely irritating limitation of excel. Hopefully the multi-sheet suggestion will work for you. My recommendation is gnuplot -- i've used it in the past for large datasets. Simple stuff is easy (and some of the more advanced stuff), and lots of output format options.

[jmcnamara]

It is actually a little worse than you think. Although, Excel supports 65,536 rows it only allows you to plot 32,000 points in a 2D data series, 4,000 in a 3D data series and 256,000 points overall.

See Excel specifications and limits for more details.

The usual workaround is to add more than one data series to a chart, each with 32,000 points, up to the limit of 256,000.

--
John.

[sk]

One of the most annoying parts of Excel!

However I think if MS is going to handle more than 2^16 the app is going to crawl anyways and I am better off using other utilities mentioned above :)

If you are really looking for a quick solution. I would suggest that you look into Pivot charts! (I am assuming you are creating the charts in Excel and not in Perl). Pivot charts can handle "external data" and it does not have a row limitation as long you do NOT display more than 2^16 rows in the pivot chart filter

cheers

SK

[PerlingTheUK]

Spurperl,

As others stated this seems to be a fix limit. However that buggered my programs up only when some sheet was spread onto two and the normally fixed count of worksheet contents was messed up. This is a pain but it may help that you can use as many spreadsheets as you like (well at least I am sure more than 106) but programming the graphics will be a real pain then if possible at all.

As for practical reasons if you do not need to apply any formatting in Excel later this Solution might work:
I only move data out of Excel. The Spreadsheet::ParseExcel and for file sizes that do not expand beyond sensible memory limits (20MB Excel file easily expand to 1GB of RAM) and have useable formatting. Otherwise use the OLE to get data out of Excel, which proves dead slow. Data is stored to XML, csv or sqlite. Graphics are done preferably with the Postscript::Simple module

[trammell]

Excel has a limit on the number of rows - 2^16.

Excel also has a 256-column limit. Or at least the versions I'm familiar with do.