We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

When calculating pivot table data then exporting as image, some rows or columns are hidden

I’m creating a workbook from an existing excel spreadsheet, but when I run CalculateData against the pivot tables obtained from the sheet then export that spreadsheet to an image, the image produced is in some cases missing rows or columns.

Here is an example of a pivot table that is missing rows when exported:
image.png (20.4 KB)
image.png (35.1 KB)

Here is one that is missing columns
image.png (44.9 KB)
image.png (40.6 KB)

To me it seems the obvious answer here is that somehow the filters are getting out of synce between the imported file and Aspose.Cells’ representation of it, but I’m at a loss as to how to remedy the situation. Any help would be greatly appreciated and more context can be given if needed, so don’t hesitate to ask. Thank you in advance for any help.

@IsaacCWelch,

Thanks for the screenshots and details.

Please also make sure you call PivotTable.RefreshData before calling pivotTable.CalculateData if it makes any difference. Also, try using our latest version/fix: Aspose.Cells for .NET v20.3.x (if you are not already using it). If you still find the issue, kindly attach your template Excel file and sample code (runnable) to reproduce the issue, we will check it soon.

PS. please zip the files prior attaching.

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/discourse_instance3/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;
	}
}

@IsaacCWelch,
It seems that you have missed attaching the template Excel file here. Please share the template file having issues along with one sample template file which works fine in your environment. Also, this code contains lot of missing references therefore it cannot be compiled. Please share a compilable complete console application solution (with all references resolved) for our testing.

		// Opening through Path
	// Creating a Workbook using the provided excel file
	Workbook workbook1 = new Workbook(dataDir + "Book1.xlsx");
    // Set get byte array for pivot tables
	var byteArraysForPivotTables = _CreateTabContentsFromWorkbook(workbook);

	//Convert Base64 Encoded string to Byte Array.
	byte[] imageBytes = Convert.FromBase64String(byteArraysForPivotTables[0]);

	//Save the Byte Array as Image File.
    //This image seems as if the filters are out of sync

	string filePath = "/output.svg";
	File.WriteAllBytes(filePath, imageBytes);

private static ImmutableList<string> _CreateTabContentsFromWorkbook(Workbook workbook)
{
	return _CreateImageContentsFromPivotTables(w);
}

private static ImmutableList<byte[]> _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<byte[]>.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 image;
			})
		.ToImmutableList();
	// Return image contents
	return imageContents;
}

private static byte[] _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();
	return bytes;
}
}

public struct SCellAddress : IEquatable<SCellAddress>
	{
		public static bool operator ==(SCellAddress left, SCellAddress right) => left.Equals(right);
		public static bool operator !=(SCellAddress left, SCellAddress right) => !(left == right);

		public string Name => CellsHelper.CellIndexToName(Row, Column);

		public int Row { get; }
		public int Column { get; }

		public SCellAddress(int row, int column)
		{
			// Set row
			Row = row;
			// Set column
			Column = column;
		}
		public SCellAddress(string name)
		{
			// Get row and column
			CellsHelper.CellNameToIndex(name, out var row, out var column);
			// Set row
			Row = row;
			// Set column
			Column = column;
		}

		public override bool Equals(object obj) => obj is SCellAddress cellAddress && Equals(cellAddress);
		public bool Equals(SCellAddress other) => Row == other.Row && Column == other.Column;

		public override int GetHashCode() => HashCode.Combine(Row, Column);
	}

Gross Profit.zip (102.1 KB)
This should be the proper attachment. All the dependencies have either been included or removed.

@IsaacCWelch,

Still this code is not compiled. I tried to provide the argument as worksheet from the argument workbook but it is not working. It is requested that please send us a working console application which can be compiled and executed here for testing.

public static void LinkWorkbooks
	(
	)
	{
		// Opening through Path
		// Creating a Workbook using the provided excel file
		Workbook workbook = new Workbook("/ExcelTest.xlsx");
		// Set get byte array for pivot tables
		var byteArraysForPivotTables = _CreateTabContentsFromWorkbook(workbook);

		//Save the Byte Array as Image File.
		//This image seems as if the filters are out of sync

		string filePath = "/output.svg";
		System.IO.File.WriteAllBytes(filePath, byteArraysForPivotTables[0]);
	}

	private static List<byte[]> _CreateTabContentsFromWorkbook(Workbook workbook)
	{
		var byteArrays = new List<byte[]>();
		foreach (var ws in workbook.Worksheets)
		{
			var byteArraysFromWorksheet = _CreateImageContentsFromPivotTables(ws);
			byteArrays.Concat(byteArraysFromWorksheet);
		}
		return byteArrays;
	}

	private static ImmutableList<byte[]> _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<byte[]>.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 image;
				})
			.ToImmutableList();
		// Return image contents
		return imageContents;
	}

	private static byte[] _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();
		return bytes;
	}
}

public struct SCellAddress : IEquatable<SCellAddress>
{
	public static bool operator ==(SCellAddress left, SCellAddress right) => left.Equals(right);
	public static bool operator !=(SCellAddress left, SCellAddress right) => !(left == right);

	public string Name => CellsHelper.CellIndexToName(Row, Column);

	public int Row { get; }
	public int Column { get; }

	public SCellAddress(int row, int column)
	{
		// Set row
		Row = row;
		// Set column
		Column = column;
	}
	public SCellAddress(string name)
	{
		// Get row and column
		CellsHelper.CellNameToIndex(name, out var row, out var column);
		// Set row
		Row = row;
		// Set column
		Column = column;
	}

	public override bool Equals(object obj) => obj is SCellAddress cellAddress && Equals(cellAddress);
	public bool Equals(SCellAddress other) => Row == other.Row && Column == other.Column;

	public override int GetHashCode() => HashCode.Combine(Row, Column);
}

This should run properly I removed or included all dependencies except the excel and system dependencies, as well as making sure this code compiles. They are just a set of static functions though so you’ll need to put them in a class and call the entry function LinkWorkbooks

@IsaacCWelch,
Thank you for providing the compilable code. It executes fine but the output file is not generated as the following line does not perform the required task.

byteArrays.Concat(byteArraysFromWorksheet);

Could you please download the complete solution from the following link and check if above line works fine at your end. It always results into 0 bytes and ultimately 0 bytes are returned from this function. Please make this project functional so that the issue can be reproduced and upload it to some public file sharing server and share the download link here for our reference.

We are sorry for the inconvenience caused to you in this regard.

PivotTableTest.zip (8.7 MB)

public class ExcelWorkbookContent
{

	public ExcelWorkbookContent()
	{
		LinkWorkbooks();
	}

	public static void LinkWorkbooks
(
)
	{
		// Opening through Path
		// Creating a Workbook using the provided excel file
		Workbook workbook = new Workbook("/ExcelTest.xlsx");
		// Set get byte array for pivot tables
		var byteArraysForPivotTables = _CreateTabContentsFromWorkbook(workbook);

		//Save the Byte Array as Image File.
		//This image seems as if the filters are out of sync

		string filePath = "\\output.svg";
		System.IO.File.WriteAllBytes(filePath, byteArraysForPivotTables[0]);
	}

	private static List<byte[]> _CreateTabContentsFromWorkbook(Workbook workbook)
	{
		var byteArrays = new List<byte[]>();
		foreach (var ws in workbook.Worksheets)
		{
			var byteArraysFromWorksheet = _CreateImageContentsFromPivotTables(ws);
			foreach (var a in byteArraysFromWorksheet)
			{
				byteArrays.Add(a);
			}
			byteArrays.Concat(byteArraysFromWorksheet);
		}
		return byteArrays;
	}

	private static ImmutableList<byte[]> _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<byte[]>.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 image;
				})
			.ToImmutableList();
		// Return image contents
		return imageContents;
	}

	private static byte[] _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();
		return bytes;
	}
}

public struct SCellAddress : IEquatable<SCellAddress>
{
	public static bool operator ==(SCellAddress left, SCellAddress right) => left.Equals(right);
	public static bool operator !=(SCellAddress left, SCellAddress right) => !(left == right);

	public string Name => CellsHelper.CellIndexToName(Row, Column);

	public int Row { get; }
	public int Column { get; }

	public SCellAddress(int row, int column)
	{
		// Set row
		Row = row;
		// Set column
		Column = column;
	}
	public SCellAddress(string name)
	{
		// Get row and column
		CellsHelper.CellNameToIndex(name, out var row, out var column);
		// Set row
		Row = row;
		// Set column
		Column = column;
	}

	public override bool Equals(object obj) => obj is SCellAddress cellAddress && Equals(cellAddress);
	public bool Equals(SCellAddress other) => Row == other.Row && Column == other.Column;

	public override int GetHashCode() => HashCode.Combine(Row, Column);
}

I’ve fixed the issue and put this inside of a class. If you instantiate a new instance of this class the code should run without problem. Just make sure to set the input and output paths correctly. I’ve tested this and I see the error that I’ve communicated previously.

@IsaacCWelch,
Thank you for the sample code and it worked fine. The output SVG files are attached here but these are different than the images you shared in the first post. I used your sample file Gross Profit.xlsx shared here.

Could you please explain the issue in the generated images and expected output? Please share the steps to create expected output using any other third-party tool like MS Excel etc. for our reference. We will reproduce the scenario here by comparing the program output with the reference images and share our feedback.
output.zip (69.5 KB)

If you go through the code, the output given my aspoce cells looks like this: image.png (11.2 KB)

What I expect is that when you run this code the output looks like this: image.png (31.7 KB)

I generated the second image by opening the file in excel and just taking a screenshot. Both of these tables (should) be showing the sum of revenue pivot table on worksheet sales register, but obviously there is something going wrong to make these look different. I think it may have something to do with the filters on the pivot table, but I’m not certain.

@IsaacCWelch,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47301 – Rows/Columns are hidden by exporting pivot table to image after calculation

@IsaacCWelch,
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-47301”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@IsaacCWelch,

Please try our latest version/fix: Aspose.Cells for .NET v20.4.4 (attached)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells20.4.4 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.4.4 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.4.4 For .NetStandard20.Zip (5.3 MB)

The issues you have found earlier (filed as CELLSNET-47301) have been fixed in Aspose.Cells for .NET v20.5. This message was posted using Bugs notification tool by Amjad_Sahi