Charting via Excel hits a limit

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

Replies are listed 'Best First'.
Re: Charting via Excel hits a limit by Limbic~Region (Chancellor) on Jun 26, 2005 at 16:36 UTC
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. Cheers - L~R	[reply]
Re^2: Charting via Excel hits a limit by spurperl (Priest) on Jun 26, 2005 at 16:53 UTC
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.	[reply]
Re: Charting via Excel hits a limit by neniro (Priest) on Jun 26, 2005 at 15:36 UTC
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 .	[reply]
Re: Charting via Excel hits a limit by davidrw (Prior) on Jun 26, 2005 at 19:31 UTC
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.	[reply]
Re: Charting via Excel hits a limit by jmcnamara (Monsignor) on Jun 26, 2005 at 23:34 UTC
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.	[reply]
Re: Charting via Excel hits a limit by sk (Curate) on Jun 26, 2005 at 20:54 UTC
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	[reply]
Re: Charting via Excel hits a limit by PerlingTheUK (Hermit) on Jun 26, 2005 at 21:10 UTC
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 Cheers, PerlingTheUK	[reply]
Re: Charting via Excel hits a limit by trammell (Priest) on Jun 26, 2005 at 19:59 UTC
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.	[reply]