Adding a pivot table is corrupting excel files

Aspose.Cells Product Family
1

Hello!

Our application dynamically generates Excel files, and can include pivot tables. While in some cases it works, in most cases the Excel file generated is corrupted and at least one Pivot table is removed. I am using the latest version of Aspose cells, 18.5.

Below is the method that the console application references:

    protected void AddPivotTables(Worksheet dataSheet)
    {
        GeneratedComponent.CalculateFormula();

        Worksheet pivotSheet = GeneratedComponent.Worksheets[GeneratedComponent.Worksheets.AddCopy(dataSheet.Name)];
        int lastDataRow = pivotSheet.Cells.GetLastDataRow(tableStart.Column);
        pivotSheet.Cells.DeleteRows(tableStart.Row, (lastDataRow - tableStart.Row) + 1);

        string pivotSheetName = dataSheet.Name + " - Pivot";
        pivotSheet.Name = pivotSheetName.Length > 31 ? pivotSheetName.Substring(0, 31) : pivotSheetName;

        //since we may have had a RowFormatting Column, or other tables may have been wider than the current table, get the last row's last data cell
        Cell lastRowLastDataCell = dataSheet.Cells.Rows[dataSheet.Cells.LastCell.Row].LastDataCell ?? dataSheet.Cells.LastCell;

        PivotTable pivotTable = pivotSheet.PivotTables[pivotSheet.PivotTables.Add(string.Format("={0}!{1}:{2}", dataSheet.Name, tableImportStart.Name, lastRowLastDataCell.Name), tableStart.Name, dataSheet.Name.Replace(" ", "") + "PivotTable")];
        
        pivotTable.RowGrand = true;
        pivotTable.IsAutoFormat = true;
        pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight6;
        pivotTable.ShowPivotStyleColumnHeader = true;
        pivotTable.ShowPivotStyleRowStripes = true;
        pivotTable.ShowPivotStyleColumnStripes = true;

        for (int i = 0; i < pivotTable.BaseFields.Count; i++)
            pivotTable.AddFieldToArea(PivotFieldType.Row, i);
            
        pivotSheet.MoveTo(GeneratedComponent.Worksheets.IndexOf(dataSheet));
    }

And here is the code I ran through the VS console:

GeneratedComponent =  new Workbook("C:\\temp\\InputFile.xlsx");
tableImportStart = GeneratedComponent.Worksheets[0].Cells["A15"];
tableStart = tableImportStart;
AddPivotTables(GeneratedComponent.Worksheets[0]);
GeneratedComponent.Save("C:\\temp\\OutputFile.xlsx");

Attached are three files:
OriginalSave.xlsx - The files that our application Generates
InputFile.xlsx - The test file for the input
OutputFile.xlsx - The outputted file from the code run here.

AsposeFiles.zip (81.4 KB)
I can also provide more test files upon request.

2

@bzora

Thanks for using Aspose APIs.

We have looked into your issue and found your data sheet name has a Space inside it. i.e.

"Financial Summary"

So, your source data range should be like this

"='Financial Summary'!A15:Q90"

It should have apostrophe which you are missing i.e.

'

We tested this issue with this code and pivot table was added successfully and output file was not corrupt.

Download Link:
Good Output Excel File.zip (31.4 KB)

C#

    Workbook wb = new Workbook("InputFile.xlsx");
        
    Worksheet dataSheet = wb.Worksheets[0];

    Worksheet pivotSheet = wb.Worksheets.Add("Pivot");

    pivotSheet.PivotTables.Add("='Financial Summary'!A15:Q90", "B4", "MyPivot");

    //This line works fine
    pivotSheet.PivotTables[0].RefreshData();
        
    //Saves good file
    wb.Save("Output.xlsx");
3

Thank you! That fixes things! Your help is very much appreciated!

4

@bzora,

Good to know that your issue is sorted out by the suggested line(s) of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.