1

I'm crawling "lots" (100K+) of excel files using spreadsheetgear, however i found out that when Spreadsheetgear hits a chart with lots of points, everything breaks loose: memory isn't released & it takes up a lot of memory as well as being very slow.

For example i have a 63MB excel file, containing 35 charts with 96K points each, getting the bitmap for each of those is taking up 100MB+ per and does not get released (at least not untill i close the worksheet). If i let my program run on all charts i end up with >9GB commited! And i definately can't ask my customers to upgrade to the 32GB+ required to support all files.

Are there any alternatives to doing this? I'd be fine with skipping files that would be slow & tried to do this (checking the count on Points in seriescollection) however it seems that Points access is the issue so this didn't help (just checking points.count seems to load all the points as well as their associated data!).

Any help is most appreciated, either to be able to grab the graph in an alternate way (with spreadsheetgear OR with a library that doesn't require anything installed AND supports all excel file formats) or a way to check for graphs that have too many points without using points.count.

It's a bit of a wild shot asking something so specific but i'm really lost there.

Ronan Thibaudau
  • 3,413
  • 3
  • 29
  • 78

1 Answers1

0

Not exactly the same problem but we had all sorts of trouble trying to keep Spreadsheet gear chart images in context in our MVC applications and decided to go with a method where the charts are saved onto the server as a png image and then recalled from the server to the view.

This is the code to save an Excel chart on the server and retrieve it in a razor view:

<!-- language: lang-cs -->
// Get the image from the spreadsheet
SpreadsheetGear.Shapes.IShape chart7 = worksheet.Shapes["Chart 7"];
SpreadsheetGear.Drawing.Image image7 = new SpreadsheetGear.Drawing.Image(chart7);
saveChartImage(image7, "Chart7.png");

//Save chart images to the server
private void saveChartImage(SpreadsheetGear.Drawing.Image image, string chartName)
{
    var imageFile = System.IO.Path.Combine(imagePath, chartName);
    System.Drawing.Image bitmap = image.GetBitmap();
    bitmap.Save(imageFile, System.Drawing.Imaging.ImageFormat.Png);
}

// Action result to get image    
public ActionResult getImage(string imageDir, string filename)
{
    var file = filename;
    var fullPath = Path.Combine(imageDir, file);
    return File(fullPath, "image/png", file);
}

Retrieve the image in the view:

<img src="@Url.Action("getImage", new { imageDir = "~/images/" "filename = "Chart7.png" })" />

We have used this method on some very large and complex spreadsheets (6 - 8MB). The code calls up images from 16 scatter charts with 400+ data points. These charts are about 16KB on the server and as far as I can tell the save / retrieve time is limited by the internet connection rather than the server.

The downside of this method is that it doesn't cater for a multi-user environment. To do that you need to create user sessions and give the charts names that are unique to the user.

Xcheque
  • 583
  • 1
  • 5
  • 14
  • I don't really see how this relates to my issue at all? The code you're showing is already the one i have, it "is" the issue, the problem isn't with the resulting image, it's with making it, spreadsheetgear allocates a lot of memory per point and fragments it a lot, and when you have charts which have 96 000 points each, and you have 100 charts per file like that, all hell breaks loose. And the issue is i can't even seem to work around it by checking how many points a chart has (points.count) because just that already allocates the points! – Ronan Thibaudau Jul 23 '13 at 01:54
  • Just to be clear, we're not talking about the same data size at all, my files are up to 300MB, i have 100 000 files containing up to 300 or so graphs each, with each chart having up to 96000 point "per chart" (vs 400 for you), so it's pretty normal that you're not hitting those limitations. – Ronan Thibaudau Jul 23 '13 at 02:01