Creating Pivot table in excel issue

I am creating Pivot table in excel using aspose.cells

while open the pivot sheet

it will throw the below error

Cannot edit a PivotTable in group edit mode


This message was posted using Email2Forum by Tahir Manzoor.

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.2.1.x (if you are not already using it).

Please provide us a sample console application, zip it and post us here to reproduce the issue on our end. Also, kindly attach your template files (if any), we will check it soon.

Thank you.

Hi Amjad Sahi,


Herewith I have attached the sample console application and error screen shot. I am not able to attach the license file. So Please add it and test it. Please provide the solution as soon as possible.

Thanks,
Balasubramanian N.

Hi,


Thanks for the sample project.

I have tested your issue with our latest version/fix (please try our latest version/fix: Aspose.Cells for .NET 8.2.1.3) and it works fine. I have attached the output file for your reference here.

Thank you.

Hi Amjad Sahi,


I have used the Same(Aspose.Cells for .Net 8.2.1.3) Version. If i am going to evaluation version it works fine. Once I put the license file, it throws the error . Attached screen shot for your reference. Kindly check setting the license file also.

Thanks,
Balasubramanian N.

Hi,


Thanks for providing further details and screen shot.

After further evaluation and using a valid license to run your project, I observed the issue as you mentioned. The error “Cannot edit a PivotTable in group edit mode” is prompted by MS Excel in the output Excel file generated by Aspose.Cells APIs in the License mode, it works fine in evaluation mode (without setting the valid license) though.
e.g
Sample code:

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(“E:\Licenses\Aspose.Cells.lic”);

// licenseCellHW.SetLicense(path);

var workbook = new Workbook();
//Obtaining the reference of the first worksheet
var sheet = workbook.Worksheets[0];
//Name the sheet
sheet.Name = “data”;
var cells = sheet.Cells;
int urange = 1;

cells[“A” + urange].PutValue(“State”);
cells[“B” + urange].PutValue(“City”);
cells[“C” + urange].PutValue(“Population”);

urange++;
cells[“A” + urange].PutValue(“TN”);
cells[“B” + urange].PutValue(“Chennai”);
cells[“C” + urange].PutValue(“50”);

urange++;
cells[“A” + urange].PutValue(“KL”);
cells[“B” + urange].PutValue(“Cochin”);
cells[“C” + urange].PutValue(“70”);

urange++;
cells[“A” + urange].PutValue(“KA”);
cells[“B” + urange].PutValue(“Bangalore”);
cells[“C” + urange].PutValue(“80”);

urange++;
cells[“A” + urange].PutValue(“AP”);
cells[“B” + urange].PutValue(“Hydrapad”);
cells[“C” + urange].PutValue(“63”);

var listObject = sheet.ListObjects[sheet.ListObjects.Add(“A1”, “C5”, true)];

//To fit the column as per test size.
sheet.AutoFitColumns(0, 26);

//Adding Default Style to the table

listObject.TableStyleType = ExcelTableStyle;

var sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];

//Naming the sheet
sheet2.Name = “PivotSheet”;

//Getting the pivottables collection in the sheet
var pivotTables = sheet2.PivotTables;
//Adding a PivotTable to the worksheet
var index = pivotTables.Add("=" + sheet.Name + “!A1:C5”, “A1”, sheet2.Name);

//To fit the column as per test size.
sheet2.AutoFitColumns(0, 24);

//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];

//Showing the grand totals
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.IsAutoFormat = true;

pivotTable.RowHeaderCaption = “State”;
pivotTable.AutoFormatType = PivotTableAutoFormatType.None;
pivotTable.PageFieldOrder = PrintOrderType.DownThenOver;
pivotTable.PivotTableStyleType = ExcelPivotTableStyle;

pivotTable.AddFieldToArea(PivotFieldType.Row, “State”);
pivotTable.AddFieldToArea(PivotFieldType.Row, “City”);
pivotTable.AddFieldToArea(PivotFieldType.Data, “Population”);

var pivotFields = pivotTable.RowFields;

//Hide the subtotal.
for (var i = 0; i < pivotFields.Count; i++)
{
pivotTable.RowFields[i].SetSubtotals(PivotFieldSubtotalType.None, true);
}
sheet2.MoveTo(0);//Move PivotTable Sheets to 0th Position in the workbook
workbook.Save(“e:\test2\pivottest1.xlsx”, SaveFormat.Xlsx);

I have logged a ticket with an id “CELLSNET-43070” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

Hi,


What is the update on this?. I need to fix the problem soon. kindly help me asap.

Thanks,
Balasubramanian N.

Hi Balasubramanian,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comment in our database and requested the development team to provide some ETA or fix for this issue. Once, there is some update for you, we will let you know asap.

Hi Balasubramanian,

Thanks for using Aspose.Cells.

This issue should be fixed in about October 17, 2014.

Hi Balasubramanian,

Thanks for your using Aspose.Cells.

We have fixed this issue.

Please download and try the latest fix: Aspose.Cells for .NET v8.2.1.5 and let us know your feedback.

Much Thanks.


Let me Check and Let you know.

Hi,


I am ok this fixes. I will check with my client and come back to you.

Shall we show or highlight the Pivot sheet at load time instead of showing Data Sheet?


Thanks,
Balasubramanian N.

Hi Balasubramanian,


Thank you for the confirmation on the provided fix.

Regarding your recent inquiry, it is your own choice which worksheet you wish to display when the resultant spreadsheet is loaded with MS Excel application. Aspose.Cells for .NET API provides the means to set any worksheet as active by using the WorksheetCollection.ActiveSheetIndex property. Please check the following piece of code to set second worksheet as active.

C#

workbook.Worksheets.ActiveSheetIndex = 1;

If you meant something else, please elaborate your question further.

Hi,


I tested my end. It is working fine. Thank you Very Much. :slight_smile:


Thanks,
Balasubramanian N.

Hi Balasubramanian,


It is good to know that you are up & running again. Please feel free to contact us in case you face any difficulty.

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


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.