Using getpivot with smart markers

Aspose.Cells Product Family
1

Hi,


I have a DataTable in my .net code,
A column in DataTable [Raw_Data]- NetSalesCategory has the following getpivot formula:

&=&=GETPIVOTDATA(“NetSales ($) Amount”,Pivot_Tables!$B$2,“Country”,$C{r},“Year”,$A{r},“Month”,$B{r},“CategoryId”,$D{r},“SubCategoryId”,$F{r})

The smart marker used in the excel is - &=[Raw_Data].NetSalesCategory.

The getpivot formula is appearing n the excel instead of the data from the pivot.

Please let me know how to use getpivot using smart markers.

Attached Excel template(Global_CR_Analysis_Report_Template_V1.0.xlsx) and report(Global_CR_Analysis_Report.xlsx)
Please refer column X in the attached report and report template.

Thanks
Shobha
2
Hi,

Please refer 'Consolidated raw data' tab in both the excel.Please let me know how to resolve this issue.


Thanks
Shobha

3
Hi,

shobha.vinay:

&=&=GETPIVOTDATA("NetSales ($) Amount",Pivot_Tables!$B$2,"Country",$C{r},"Year",$A{r},"Month",$B{r},"CategoryId",$D{r},"SubCategoryId",$F{r})

The smart marker used in the excel is - &=[Raw_Data].NetSalesCategory.

The getpivot formula is appearing n the excel instead of the data from the pivot.

Please let me know how to use getpivot using smart markers.

We cannot evaluate your issue properly, also, it looks like your input (template) file and output file do not match completely. Please create a sample (runnable) console application, zip it and post it here to reproduce the issue on our end with all the files, you may use dynamic DataTables with hard coded values for the data sources. Also, you may simplify your template Excel file to highlight your problem only, it will be easier to trace down the issue and to rectify it soon.

We also recommend you to kindly download and use this fix: Aspose.Cells for .NET v7.3.4.5

Thank you.
4

Hi,


Thanks for your reply.
PFA the sample application.
Place the excel - Report_Template.xlsx in C:\Apps folder.

I want to use the data from the pivot in ‘Pivot_Table’ tab in the ‘Consolidated_Data’ tab.
For my requirement i need add the getpivot formula, to get the data from pivot through the .net code.
[ToTalSales Column in ‘Consolidated_Data’ tab]

If i try to use getpivot formula in the .net code, the formula itself appears in the Final_Report.xlsx file instead of data from the pivot.
Please let me know if you need any further details.


Thanks
Shobha
5

Hi,


Thanks for the project.

Well, In MS Excel regarding data, you may either specify/ input data as value or as formula. I have checked your project a bit. In the D column (in the “Consolidated_Data” worksheet of your template file) your marker is: “&=[Raw_Data].TotalSales” where as its corresponding field is:
dt.Columns.Add(“TotalSales”, typeof(string));
Actually you are saving formula string to TotalSales column in the table, so when the marker is processed data (which contains formula string) is filled into the cells of the D column as values and not as formulas. So, consequently the formula strings in the D column are shown as they are and not processed (calculated) because the data is stored as values and not as formulas. For your needs, you may try to loop through the D column and update each cell’s value to place them as formula using the line of code e.g
cell.Formula = cell.StringValue;

Hope, you understand now.
6

Hi,


Thanks for your reply.
I tried for one cell and it seems to work fine.
Can you show me some pointers/links regarding how to loop through all the cells in the worksheet.

Thanks for your help.
Shobha
7

Hi,


See the following sample code segment for your requirements:

Sample code:
e.g
//…
Cells cells = worksheet.Cells;

//Browse all the cells in the sheet.
foreach (Aspose.Cells.Cell cell in cells)
{
if (cell.Value != null)
{
//Browse D column
if (cell.Row == 3)
{
cell.Formula = cell.StringValue;
}
}
}

Thank you.