Hello,
We at theTradeDesk are using heavily pivot tables. So far we were using Aspose.Cell (for C#) version 16.10.2, after upgrading to the newest version we noticed our pipeline started producing corrupted xlsx files. After investigating the issue we figured out that:
- The latest version that produces valid xlsx files is: 20.04.0
- With version 20.05.0 our builds fail
- Starting with version 20.06.0 the produced xlsx files are corrupted
The corruption happens if the file contains a pivot table with cells of PivotFieldType.Row type. If one sets the style of such a cell using Style object created with default costructor (without setting any properties on the object) the resulting output xlsx file is corrupted.
To illustrate this issue I created following reproducer:
using System.Collections.Generic;
using Aspose.Cells;
using Aspose.Cells.Pivot;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace TTD.Domain.Shared.Excel.UT
{
[TestClass]
public class AsposeCellRegressionTests
{
[TestMethod]
public void Test3()
{
License AsposeLicenseFile = new License();
AsposeLicenseFile.SetLicense( "Aspose.Cells.lic" );
using ( var workbook = new Workbook() )
{
var columns = new List<string>() { "Column name 1", "Column name 2" };
CreateDataSheet( workbook, columns );
CreatePivotSheet( workbook, columns );
workbook.Save( "/tmp/output.xlsx", SaveFormat.Xlsx );
}
}
private void CreateDataSheet( Workbook workbook, List<string> columns )
{
var dataSheet = workbook.Worksheets.GetSheetByCodeName( "Sheet1" );
for( int i = 0; i < columns.Count; ++ i )
{
dataSheet.Cells[ 0, i ].Value = columns[i];
var sampleDataCell = dataSheet.Cells[ 1, i ];
sampleDataCell.Value = "Sample value";
}
var dataSheetDataRange = dataSheet.Cells.CreateRange( dataSheet.Cells.FirstCell.Name, dataSheet.Cells.LastCell.Name );
dataSheetDataRange.Name = "Data0";
}
private void CreatePivotSheet( Workbook workbook, List<string> columns )
{
var pivotSheet = workbook.Worksheets.Add( "Pivot1" );
var pivotTableIndex = pivotSheet.PivotTables.Add( string.Format( "'{0}'!{1}", "Pivot1", "Data0" ), "A5", "Pivot" );
var pivotTable = pivotSheet.PivotTables[ pivotTableIndex ];
foreach( var column in columns )
{
pivotTable.AddFieldToArea( PivotFieldType.Row, column );
}
pivotTable.CalculateData();
foreach ( var column in columns )
{
var cell = pivotTable.GetCellByDisplayName( column );
if( cell == null )
continue;
var style = new Style(); // this used to work in 20.04 but causes corruption starting with 20.06
pivotTable.Format( cell.Row, cell.Column, style );
}
}
}
}
I would appreciate if you could investigate this regression!
Thank you in advance for your help.
Kindest,
Michal