hi,
I have attached excel pivot sheet and Aspose code to create Pivot .everything working fine but
there are 2 columns highlighted in yellow in Pivot table sheet
how do i create that columns based on rules Using Aspose Code.. i am able to create pivot till Grand Total columns but i am stuck at this 2 yellow columns this columns need to be create based on rules i mentioned in excel sheet.
This is bread and Butter for me as busness is define this as MOST critical they can save 3 days of work if we can give using Aspose to create this pivot excel.
thanks
Hi,
Well, those columns (in yellow i.e. H and I) are not the part of the Pivot Table. How could you set formulas in MS Excel based on your rules which should follow them exactly or strictly? Please write/ set formulas manually in MS Excel in your file which should provide the outcome values as pasted in the columns and save the Excel file to provide it here, we will check and help you through. If you are able to write the formulas for your defined rules, you may simply use Aspose.Cells APIs to do the same.
Here, I would write a sample code that would demonstrate on how to write formulas that follow certain part of your defined rules. The main thing is you got to write formulas after refreshing the pivot table and calculating its data. I have used your template file here and write shared formulas for a range of cells. I add the shared formulas (using Aspose.Cells Shared formulas feature) to the K column’s range of cells to follow the certain part of a rule for “11488-Posti Netting” field. You got to do or try to find similar way to accomplish the task but again if you could write the formulas following your rules precisely in MS Excel manually, you may do it via Aspose.Cells too.
e.g
Sample code:
var workbook = new Workbook(“e:\test2\ARAPPivot+(5).xlsx”);
//Get the pivot table
var pivot = workbook.Worksheets[1].PivotTables[0];
//Refresh the pivot table and calculate its data
pivot.RefreshData();
pivot.CalculateData();
pivot.CalculateRange();
//I add a shared formulas to the K column’s range of cells to follow the certain part of a rule for “11488-Posti Netting”
//G column is a Grand Total column
workbook.Worksheets[1].Cells[“K9”].SetSharedFormula("=IF(G9>0, G9, 0)", 40, 1);
workbook.Save(“e:\test2\out1.xlsx”);
Hope, this helps a bit.
Thank you.
hi,
I will use this sharedformula but this formula depends on netting ID column which is Pivot Row.
this Netting Id has only 3 value blnak,1 and 0. i already attached c# file and excel in previous post.
How do i get value of Netting ID in below formula from Pivot table as only first cell in that column have value for netting id when i loop through.
my formula would be some thing like this for 11488-Post Netting column
if(nettingID==1,(if(G>0,G,0),D)
for column 216900-Post Netting formula will be if(NettingID = 1,(if(G<0,G,0),E)
Thank,
Piyush
Hi,
Well, we requested you to provide a sample file (you may create manually in MS Excel) that should contain the formulas involving Pivot table fields based on your rules. So, please write/ set formulas manually in MS Excel in the file which should provide the outcome values as pasted in the columns and save the Excel file to provide it here, we will check and help you through. If you are able to write the formulas in the worksheet cells for your defined rules/ logic, then we can check on how to use Aspose.Cells APIs to do the same.
By the way, I am not sure how could you write the real time Excel formula in a cell based on your rule/ logic:
" if(nettingID==1,(if(G>0,G,0),D)".
Thank you.
hi,
This is extreamely important for us to resolve issue and give Business this 2 column based on PIVOT table.
please if you right click on ARAP Pivot Table TAB on excel file you can see Netting ID is Row Label. data source for this Pivot is ARAP PIVOT DATA sheet (very first sheet)
This Netting ID column is PIVOT row where G in formula is grand total of data (Sum of USD_Equiv)
so i want to know how do i access this Netting ID column loop through Pivot Table in Excel .
Rules mentioned in excel sheet is requirement and formula that i wrote that's gives this extra columns. challange is how do i loop and get value of Netting id for each Row.
CellArea area1 = pivotTable.RowRange;
int start1 = area1.StartRow;
for (int i = pivotTable.RowRange.StartRow; i <= pivotTable.RowRange.EndRow; i++)
{
??????? Netting id vlaue for each row ??
// pivotTable.DataField.Items.GetValue(i).ToString();
}
hi,
I have attached excel file with L and M columns having formula that needs to be doen via Aspose.
file name ARAPPivot.xls
Hi,
Thanks for the template file.
Please see the sample code for your reference. I used your template file and specified the SharedFormula feature to input a formula on K9 cell and then copy this shared formula to another 136 cells in that column (K).
e.g
Sample code:
var workbook = new Workbook(“e:\test2\ARAPPivot.xlsx”);
//Get the pivot table
var pivot = workbook.Worksheets[1].PivotTables[0];
//Refresh the pivot table and calculate its data
// pivot.RefreshData();
pivot.CalculateData();
// pivot.CalculateRange();
//I add a shared formulas to the K column’s range of cells to follow the rule/ your logic for “11488-Posti Netting”
//G column is a Grand Total column
workbook.Worksheets[1].Cells[“K9”].SetSharedFormula("=IF(B9=1,IF(G9>0,G9,0),D9)",135, 1);
// Set the width of the column a bit.
workbook.Worksheets[1].Cells.SetColumnWidth(10, 13);
workbook.Save(“e:\test2\out1.xlsx”);
Thank you.
thank you
this will help but issue is with column B when it's value is 1 all row that belogns to that Neeting Id=1 should have same formula but what happen for cell B91 to B129 it has blank cell value so it consider that as blank instead of 1. and gives wrong results.
for example : B91,B92,B93 are all 1 actually but cell deos not have value 1 so it takes false part of formula.
how to tackle this ..B91 to B129 where actual value(NEtting Id =1) but cell has nothing ..
Hi,
Well, you may try to set the repeated Item labels for “Netting ID” field to false for the time being and then apply the ShareFormula accordingly. Also, you may set the calculation mode for formulas manual and set refreshing pivot table on opening the file into MS Excel for your complex scenario.
Please use the sample codes as follows:
e.g.
Sample code:
[C#]
Workbook workbook = new Workbook(@"D:\ARAPPivot.xlsx");
//Get the pivot table
PivotTable pivot = workbook.Worksheets[1].PivotTables[0];
//get row PivotField collection
PivotFieldCollection rows = pivot.RowFields;
//get PivotField named "Netting ID"
PivotField field = rows["Netting ID"];
//set IsRepeatItemLabels is true
field.IsRepeatItemLabels = true;
//I add a shared formulas to the K column's range of cells to follow the rule/ your logic for "11488-Posti Netting"
//G column is a Grand Total column
workbook.Worksheets[1].Cells["K9"].SetSharedFormula("=IF(B9=1,IF(G9>0,G9,0),D9)", 135, 1);
// Set the width of the column a bit.
workbook.Worksheets[1].Cells.SetColumnWidth(10, 13);
workbook.Settings.CalcMode = CalcModeType.Manual;
pivot.CalculateData();
workbook.CalculateFormula();
//set IsRepeatItemLabels is true
field.IsRepeatItemLabels = false;
pivot.RefreshDataOnOpeningFile = true;
workbook.Save(@"D:\ ARAPPivot_out1.xlsx");
Hope, this helps a bit to figure it out now.
Thank you.
Excellent it works thank you
but how do i get Grand total and Subtotal for this column when Netting ID =1 because right now it gives total based on rule but actual column total is different.
i mean total at each level..
Hi,
Well, I am not sure how could you do this in MS Excel, you are mixing the PivotTable report with normal cells data in your own custom oriented formulas and logic (which uses PivotTable report fields for the scenario). How could you write and set formulas in MS Excel dynamically which should be based on your rules and at the same time also should follow your custom additions for getting subtotals and grand totals sections for certain rows for your custom columns outside of PivotTable report area.
If you could do this in MS Excel (other than manually re-insert new formulas in certain cells in the columns for Subtotals and Grand Totals for certain sections/areas), let us know, we will check it soon.
Thank you.
hi,
i have 2 more questions. i have attached c# code for your reference and excel file.
1.ARAPPivot.Xlsx file has 2 tabs Pivotdata and Pivot table is there way that when it show up on WebGrid PivotTable sheet will be display instead of PivotData. i want to show Pivot table sheet on web page as soon as webgrid load ..
what is a code for that.
2.currently Pivotdata tab is first tab on Webgrid and when it display on screen the Webgrid does not show the style below code when i export to excel than it does show up style in excel but not on web page. ?? any fix ??
issue no 2 is happening to all my reports. webgrid without style on asp.net page screen.
listObjects[0].TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium23;
is shows perfect style /color for Pivot table sheet which is sheet 2.
Aspose.Cells.Tables.ListObjectCollection listObjects = wb.Worksheets[0].ListObjects;
////Add the list object/ table.
listObjects.Add(4, 0, wb.Worksheets[0].Cells.MaxDataRow, wb.Worksheets[0].Cells.MaxDataColumn, true);
listObjects[0].TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleMedium23;
Hi,
1) Well, you may use ActiveSheetIndex property to display your desired sheet in the GridWeb matrix by default when the GriWeb loads an Excel file, see the sample line of code below:
e.g
Sample code:
Set the second sheet as active sheet.
GridWeb1.WebWorksheets.ActiveSheetIndex = 1;
2) I observed the issue regarding List objects/tables formatting/ style rendering when importing Excel spreadsheet (that has List objects/ tables and Pivot table etc.) to GridWeb. I simply tested with your attached template file and imported it to GridWeb, the style/ formatting of Pivot table is fine but the style/ formattings of List object/ table is removed. Do you confirm the issue? After your confirmation, we will log a ticket into our database so your issue could be figured out soon.
e.g
Sample code:
GridWeb1.WebWorksheets.ImportExcelFile(@"e:\test2\ARAPPivot_31-mar-2014+(4).xlsx");
Thank you.
hi,
I confirm PIVOT table Style is fine, only problem with PIVOT table DATA sheet.
please let meknow when you fix.
Hi,
Thanks for the confirmation.
I have now logged a ticket with an id “CELLSNET-42553” for your issue i.e. “List objects/tables formatting/ style lost when importing Excel file to GridWeb”. We will look into your issue soon.
Once we have any update on it, we will let you know here.
Thank you.
Hi,
Please try our latest version/fix: Aspose.Cells.GridWeb v8.0.0.2001 (attached).
We have fixed the issue "CELLSNET-42553 " now.
Let us know your feedback.
Thank you.
Hi,
We noticed the same issue in few of the users' machine. The pivoted group cell has no repeated value in excel, and hence the formula is not getting applied properly. However the same works on developer machine.
Could be one of the below reason:
1. We checked on the user's machine and they have Aspose.Cells.dll version 7.2 on their machine installed under a different application (Quality Centre), and we developers are having version 8.0.
How can we make sure that the user machine makes use of the latest dll (8.0) which is part of the bin folder in our application ?
Will that fix the issue??
2.To make the NettingID repeat on each cell in excel we have the same code as you provide here in your sample. But still the Netting ID is not repeating (neither in dev machine nor in users machine)
We tried field.IsRepeatItemLabels = true; and field.IsRepeatItemLabels = false; but nothing works
It works in webpage though, but not when exported to Excel. Can you please provide a solution so that this can work in excel.
Thanks in advance.
Hi,
1) Please use the System.Diagnostics APIs to get the version number for the Aspose.Cells.GridWeb assembly in your project:
e.g
Sample code:
string var = FileVersionInfo.GetVersionInfo(@“E:\test2\TestWebGrid1\TestWebGrid1\bin\Aspose.Cells.GridWeb.dll”).FileVersion;
2) Please create a sample project with v8.0.0.2001, zip it and post it here to show the issue, we will check it soon.
Thank you.
The issues you have found earlier (filed as CELLSNET-42553) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.