Aspose Team,
We’ve come across an odd issue using Spill Formulas with charts. The attached workbook creates a chart over a range of spilled data. Named ranges need to be used to chart the spill data since Microsoft doesn’t fully support the Spill syntax (#) in the Select Data screens. Charting spill data is supported though. The problem we’re seeing in Aspose is that we can not take a screenshot of a range that includes this chart. The screenshot comes back as blank.
We have isolated the problem to the CHOOSECOLS formula in the Data named range with formula =CHOOSECOLS(‘Sheet 1’!$G$4#,2). If you change this formula to point directly to the spill range =E4#, then the screenshot will appear as expected.
Here’s the code for the screenshot.
var filename = @"C:\Data\Systems\Demo\Reports\Support\SpillFormulaInChart.xlsx";
var workbook = new Workbook(filename);
var worksheet = workbook.Worksheets["Sheet 1"];
MemoryStream stream = new MemoryStream();
worksheet.PageSetup.PrintArea = "K2:Q15";
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
SheetRender sr = new SheetRender(worksheet, new ImageOrPrintOptions
{
OnePagePerSheet = true,
ImageType = Aspose.Cells.Drawing.ImageType.Png,
HorizontalResolution = 200,
VerticalResolution = 200,
Transparent = true,
OnlyArea = true,
});
sr.ToImage(0, stream);
stream.Seek(0, SeekOrigin.Begin);
stream.Rewind();
using (FileStream fs = File.Create("ChartScreenshot.png"))
{
stream.CopyTo(fs);
fs.Close();
}
SpillFormulaInChart.zip (14.6 KB)