DataBordyRange is not returning correct area

Aspose.Cells Product Family
1

I want to find the area of the pivot after the data is loaded. but the DataBordyRange property is not returning correct area.

I am using Aspose.Cell 5.1.2

the code is copy from the sample

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet

Worksheet sheet = workbook.Worksheets[0];

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);

Aspose.Cells.Pivot.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

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Unshowing grand totals for rows.

pivotTable.RowGrand = false;

//Draging the first field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);

//Draging the second field to the column area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 1);

//Draging the third field to the data area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);

int lastColumn = pivotTable.DataBordyRange.EndColumn; // 5

int lastRow = pivotTable.DataBordyRange.EndRow; // 5

int firstRow = pivotTable.DataBordyRange.StartRow; // 3

int firstColumn = pivotTable.DataBordyRange.StartColumn; // 5

//Saving the Excel file

workbook.Save("C:\\book1.xls");

This message was posted using Page2Forum from DataBordyRange Property - Aspose.Cells for .NET
2

Hi,

Well, I think you may use PivotTable.TableRange1 and PivotTable.TableRange2 attributes to get the entire area of the pivot table report. The DataBordyRange is used to get the Data area only.

Thank you.


3

if I change it to use pivotTable.TableRange1, I got firstRow = 2, lastRow = 15, firstColumn = 4, lastColumn = 10. but the actual saved excelsheet show the pivot table data in area E3:G7 which means the firstRow = 2, lastRow = 6, firstColumn = 4, lastColumn = 6. they don't match. what did i do wrong? thanks.

4

Hi,

I am using v5.1.2.2 and here are my results:
firstRow = 2, lastRow = 5, firstColumn = 4, lastColumn = 5

Anyways, I have logged your issue into our issue tracking system with an id: CELLSNET-19542. We will look into it and get back to you soon.

Thank you.

5

Hi,

Please use the updated version Aspose.Cells.dll v5.1.2.4 attached. The issue has been fixed.

Thanks,

6

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

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