Issue with copying piovt table (Formatting)

Aspose.Cells Product Family
1

I have created Pivot table using aspose cell and used the following code for formatting.

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Table6;

after that i am copying the that pivot table to to another workbook as follows

string mytabname = workbookToCombine.Worksheets[k].Name;

CopyOptions _copypt = new CopyOptions();

_copypt.CopyInvalidFormulasAsValues = true;

objWorkBook.Worksheets[mytabname].Copy(workbookToCombine.Worksheets[mytabname], _copypt);

it is not preserving the formatting in the copied workbook.

is there any way to fix it?

2

Hi,

Thanks for your question.

Are you saving your workbook into xls or into xlsx format?

If you are saving your workbook into xlsx format, then you should use pivotTable.PivotTableStyleType property and not the pivotTable.AutoFormatType

Please provide us your full test code that we could run at our end and look into your issue. If you are using any source xls/xlsx file, please also provide them.

Please also download and try the latest version:
Aspose.Cells
for .NET v7.2.1.6

Screenshot:

3

I tried the option which you sugested , still have the issue, one more issue i want to bring to your attention, when i am using .xlsx format the rowheder filter is displaying as "Row Lables".

for example in ur Screen shot the you have selected column "Sport" as the Row fileld, and we are supposed to Showthe row filter as "Sport" but when we use .xlsx it says "Row Label".

is there any way to get rid off it?

4

Hi,

Thanks for your feedback.

Could you please download output.xlsx file and open it in Ms-Excel 2010 and manually make a change inside as you want to have it and attach it again?

We will look into your changed xlsx file and then provide you a sample code.

5

I have attached the edited output.xlsx

also i have attached a screenshot of the issue.

Please send us the sample code.

Thank you in advance

Thomas Joseph

6

Hi,

Thanks for your output file and the screenshot for explanation.

Please see the following code, it achieves your desired results. You need to set the PivotTable.RowHeaderCaption and PivotTable.ColumnHeaderCaption properties.

I have attached the source and output xlsx file for your reference.

C#


string filePath = @“F:\Downloads\source.xlsx”;


//Create a workbook object

Workbook workbook = new Workbook(filePath);


//Access its first worksheet

Worksheet worksheet = workbook.Worksheets[0];


PivotTable pivotTable = worksheet.PivotTables[0];


//Change row and column header captions

pivotTable.RowHeaderCaption = “Sport”;

pivotTable.ColumnHeaderCaption = “Quarter”;


workbook.Save(filePath + “.out.xlsx”);

Screenshot:

7

Hi,

Your suggestion worked well,

But when I am copying that worksheet to another workbook the RowHeaderCaption and ColumnHeaderCaption went back to the default.

Here is the test code.

I am also attaching the two work book which is the out put of the code.

Please let me know what i am missing.

Thanks and Regards,

Thomas Joseph


Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Name = "myDemo";

Cells cells = sheet.Cells;

//Setting the value to the cells

Aspose.Cells.Cell cell = cells["A1"];

cell.PutValue("Sport");

cell = cells["B1"];

cell.PutValue("Quarter");

cell = cells["C1"];

cell.PutValue("Sales");

cell = cells["A2"];

cell.PutValue("Golf");

cell = cells["A3"];

cell.PutValue("Golf");

cell = cells["A4"];

cell.PutValue("Tennis");

cell = cells["A5"];

cell.PutValue("Tennis");

cell = cells["A6"];

cell.PutValue("Tennis");

cell = cells["A7"];

cell.PutValue("Tennis");

cell = cells["A8"];

cell.PutValue("Golf");

cell = cells["B2"];

cell.PutValue("Qtr3");

cell = cells["B3"];

cell.PutValue("Qtr4");

cell = cells["B4"];

cell.PutValue("Qtr3");

cell = cells["B5"];

cell.PutValue("Qtr4");

cell = cells["B6"];

cell.PutValue("Qtr3");

cell = cells["B7"];

cell.PutValue("Qtr4");

cell = cells["B8"];

cell.PutValue("Qtr3");

cell = cells["C2"];

cell.PutValue(1500);

cell = cells["C3"];

cell.PutValue(2000);

cell = cells["C4"];

cell.PutValue(600);

cell = cells["C5"];

cell.PutValue(1500);

cell = cells["C6"];

cell.PutValue(4070);

cell = cells["C7"];

cell.PutValue(5000);

cell = cells["C8"];

cell.PutValue(6430);

PivotTableCollection pivotTables = sheet.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add("=A1:C8", "E3", "PivotTable2");

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

//Draging the first field to the row area.

pivotTable.AddFieldToArea(PivotFieldType.Row, 0);

//Draging the second field to the column area.

pivotTable.AddFieldToArea(PivotFieldType.Column, 1);

//Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Data, 2);

pivotTable.RowHeaderCaption = "Sports";

pivotTable.ColumnHeaderCaption = "Quarter";

pivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark20;

Random random = new Random(DateTime.Now.Millisecond);

string sRandomExt = "_" + random.Next().ToString();

// saving the first work book to see how it is looking here RowHeaderCaption and ColumnHeaderCaption are good

workbook.Save("C:\\CFOReporting_25may\\MyTestPivot_" + sRandomExt + ".xlsx", FileFormatType.Xlsx);

//copy the workseets of the First work book to another workbook

Workbook Masterbook = new Workbook();

WorksheetCollection wrksheets = workbook.Worksheets;

foreach (Worksheet ws in wrksheets)

{

Masterbook.Worksheets.Add(ws.Name);

Masterbook.Worksheets[ws.Name].Copy(ws);

}

// saving the second work book to see how it is looking here RowHeaderCaption and ColumnHeaderCaption went back to the default.

Masterbook.Save("C:\\CFOReporting_25may\\MyMasterPivot_" + sRandomExt + ".xlsx", FileFormatType.Xlsx);
8

Hi,

Thanks for your feedback.

I was able to notice with your code that when the worksheet is copied to another workbook, the pivottable RowHeaderCaption and ColumnHeaderCaption went back to the default.

I have tested it with the latest version:
Aspose.Cells
for .NET v7.2.1.6


We have logged this issue in our database. We will look into your issue. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40728.

C#



string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


WorksheetCollection wrksheets = workbook.Worksheets;


//copy the workseets of the First work book to another workbook

Workbook Masterbook = new Workbook();


foreach (Worksheet ws in wrksheets)

{


Masterbook.Worksheets.Add(ws.Name);


Masterbook.Worksheets[ws.Name].Copy(ws);


}


// saving the second work book to see how it is looking here RowHeaderCaption and ColumnHeaderCaption went back to the default.

Masterbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);



Screenshot:
9

Thank you for the update, I will wait for the fix. Mean while i want tp programtically change one of the Pivot tabel option.

I want to change PivotTable Option-->Display-->"Clasic PivotTable layout to true"

attaching the screenshot what i want to achive.

using aspose.cell api how i will be able to do that.

Thanks and Regards

Thomas Joseph

10

Hi,

Thanks for your question.

I have looked into your issue. Please set the following property to get your desired settings.

PivotTable.IsGridDropZones = true;

I have attached the screenshot for your reference.

Screenshot:

11

Thank you it worked.

So now i have two open tickets with you.

waiting for the updates.

12

Hi,

We have fixed this issue.



Please download and try the latest fix: Aspose.Cells for .NET v7.2.1.7

13

The issues you have found earlier (filed as CELLSNET-40728) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.