Please test the following code with the provided excel document, I’ve also tried this same code, inserting wb.RecalculateFormula and pt.RefreshData, and I did not get any noticeable change in behavior. Its worth noting that this doesn’t happen for all pivot tables and files, just a select few. The one I linked being among them.
// Opening through Path
// Creating a Workbook using the provided excel file
Workbook workbook1 = new Workbook(dataDir + "Book1.xlsx");
// Set tab contents
TabContents = _CreateTabContentsFromWorkbook(workbook);
//This should get the pivot table image that is break, if it is not selecting that image properly, you can just run this test over all of the pivot table images and it should work then
var pivotTable = TabContents.FirstOrDefault().PivotTables.FirstOrDefault().Image.Base64;
//Convert Base64 Encoded string to Byte Array.
byte[] imageBytes = Convert.FromBase64String(pivotTable);
//Save the Byte Array as Image File.
//This image seems as if the filters are out of sync<a class="attachment" href="/uploads/default/36899">Gross Profit.zip</a> (102.1 KB)
string filePath = "/output.svg";
File.WriteAllBytes(filePath, imageBytes);
private static ImmutableList<ExcelTabContent> _CreateTabContentsFromWorkbook(Workbook workbook)
{
// Get named ranges
var namedRanges = workbook.Worksheets.GetNamedRanges() ?? Array.Empty<Range>();
// Create worksheet index to image contents for named ranges mappings
var worksheetIndexToNamedRangeImageContentsMappings = namedRanges
.Where(nr => !nr.Name.StartsWith("_xl"))
.GroupBy(nr => nr.Worksheet.Index)
.ToDictionary
(
g => g.Key,
g => g
.Select
(nr =>
{
// Get name
var name = nr.Name;
// Get image
var image = _CreateImageFromRange(nr.Worksheet, nr.GetUpperLeftCellAddress(), nr.GetLowerRightCellAddress());
// Create image content
var imageContent = new ExcelImageContent(ExcelContentTypes.NAMED_RANGE, name, image);
// Return image content
return imageContent;
})
.ToImmutableList()
);
// Create tab contents
var tabContents = workbook.Worksheets
.Select
(w =>
{
// Get name
var name = w.Name;
// Create charts
var charts = _CreateImageContentsFromCharts(w);
// Create list objects
var listObjects = _CreateImageContentsFromListObjects(w);
// Create pivot tables
var pivotTables = _CreateImageContentsFromPivotTables(w);
// Create named ranges
var namedRanges = worksheetIndexToNamedRangeImageContentsMappings.ContainsKey(w.Index)
? worksheetIndexToNamedRangeImageContentsMappings[w.Index]
: ImmutableList<ExcelImageContent>.Empty;
// Return tab content
return new ExcelTabContent(name, charts, listObjects, namedRanges, pivotTables);
})
.ToImmutableList();
// Return tab contents
return tabContents;
}
private static ImmutableList<ExcelImageContent> _CreateImageContentsFromListObjects(Worksheet worksheet)
{
// Get list objects
var listObjects = worksheet.ListObjects;
// Check if list objects do not exist
if (listObjects == null)
{
// Return no image contents
return ImmutableList<ExcelImageContent>.Empty;
}
// Create image contents
var imageContents = listObjects
.Select
(lo =>
{
// Get name
var name = lo.DisplayName;
// Get image
int startRow = lo.StartRow + 1;
int endRow = lo.EndRow + 1;
int startColumn = lo.StartColumn + 1;
int endColumn = lo.EndColumn + 1;
var image = _CreateImageFromRange(worksheet, new SCellAddress(startRow, startColumn), new SCellAddress(endRow, endColumn));
// Return image content
return new ExcelImageContent(ExcelContentTypes.LIST_OBJECT, name, image);
})
.ToImmutableList();
// Return image contents
return imageContents;
}
private static ImmutableList<ExcelImageContent> _CreateImageContentsFromCharts(Worksheet worksheet)
{
// Get charts
var charts = worksheet.Charts;
// Check if charts do not exist
if (charts == null)
{
// Return no image contents
return ImmutableList<ExcelImageContent>.Empty;
}
// Create image contents
var imageContents = charts
.Select
(c =>
{
// Get name
var name = c.Name;
// Get image
using var chartStream = new MemoryStream();
var options = new ImageOrPrintOptions { SaveFormat = SaveFormat.SVG };
c.ToImage(chartStream, options);
var image = new ExcelImage
(
width: c.ActualChartSize.Width,
height: c.ActualChartSize.Height,
bytes: chartStream.ToArray().ToImmutableArray()
);
// Return image content
return new ExcelImageContent(ExcelContentTypes.CHART, name, image);
})
.ToImmutableList();
// Return image contents
return imageContents;
}
private static ImmutableList<ExcelImageContent> _CreateImageContentsFromPivotTables(Worksheet worksheet)
{
// Get pivot tables
var pivotTables = worksheet.PivotTables;
// Check if pivot tables do not exist
if (pivotTables == null)
{
// Return no image contents
return ImmutableList<ExcelImageContent>.Empty;
}
// Create image contents
var imageContents = pivotTables
.Select
(pt =>
{
pt.CalculateData();
// Get name
var name = pt.Name;
// Get image
int startRow = pt.TableRange2.StartRow + 1;
int endRow = pt.TableRange2.EndRow + 1;
int startColumn = pt.TableRange2.StartColumn + 1;
int endColumn = pt.TableRange2.EndColumn + 1;
var image = _CreateImageFromRange(worksheet, new SCellAddress(startRow, startColumn), new SCellAddress(endRow, endColumn));
// Return image content
return new ExcelImageContent(ExcelContentTypes.PIVOT_TABLE, name, image);
})
.ToImmutableList();
// Return image contents
return imageContents;
}
private static ExcelImage _CreateImageFromRange(Worksheet worksheet, SCellAddress cellAddressUpperLeft, SCellAddress cellAddressLowerRight)
{
// Set the print area range
worksheet.PageSetup.PrintArea = cellAddressUpperLeft.Name + ":" + cellAddressLowerRight.Name;
// Set all margins to zero
worksheet.PageSetup.LeftMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.BottomMargin = 0;
// Create image options
var options = new ImageOrPrintOptions
{
OnePagePerSheet = true,
SaveFormat = SaveFormat.SVG,
HorizontalResolution = 200,
VerticalResolution = 200
};
// Create image of the worksheet
var sheetRender = new SheetRender(worksheet, options);
using MemoryStream rangeStream = new MemoryStream();
sheetRender.ToImage(0, rangeStream);
var size = sheetRender.GetPageSize(0);
var bytes = rangeStream.ToArray();
// Create image
var image = new ExcelImage
(
width: size.Width * 0.5,
height: size.Height * 0.5,
bytes: bytes.ToImmutableArray()
);
// Return image
return image;
}
}