I am
getting error “The PivotTable field name is invalid.” I have attached the test
application for you to debug at your end. Please suggest a fix of this error.
Also I have
attached a screenshot where you will see the column with text “Values”. This
used to show with previous dll but not with the new version of Aspose.Cells.
Please suggest what change should I do in code so that it shows “Values” text
for columns.
Please
let me know if you require any further input from me.
Thanks.
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
<![endif]–>
Hi Radha,
Thank you for providing the sample application for our review.
I have worked with your scenario a bit. It seems that the problem is with your data import routines. As a result of which the column headers are not imported correctly, therefore while creating a PivotTable the program throws exception for missing PivotTableField. Please check the attached snapshot for your reference. The spreadsheet shown in the image was generated by commenting the code segments related to the PivotTable.
Please also check the attached modified sample application. Instead of manually setting the data to tempMexicoSheet cells (calling CreateTempSheet method), I have used the Cells.ImportData method to import the filtered data from DataView. This way I was able to generate a correct PivotTable but the source sheet (tempMexicoSheet) was left un-formatted. You may surely amend the code as per your original requirement.
Attached to this post is the output of your modified application. Please check that your other mentioned issue related to the text “Value” in the column was not produced with latest Aspose.Cells for .NET assembly v7.5.3.4 (included in project archive).
Hello again,
I was running this application and it still does not show the “Values” text in the pivot. I have reattached the sample here and would appreciate if you could please run it at your end and suggest what should I do to show it in the pivot sheet.
Thanks.
Hi Radha,
Thank you for writing back.
We have executed your provided sample application while using the latest version of
Aspose.Cells for .NET 8.1.0. The PivotTable in the resultant spreadsheet contains the text “Values” as you may observe in the attached file. Please note, we didn’t make any changes to the source code.
In case our understanding is not correct, please provide the desired results that you may create using MS Excel application and provide it here for better elaboration.
Thanks Babar for the reply. I would appreciate if you could please run it with 7.5.3 version dll as that is what being used in my application. As suggested in my first post (the image attached) it shows “Values” text. Similiarly I want to show the “Values” text in the pivot table of the sheet. Now when you run the sample (with dll 7.5.3) and check the sheet designed with the code you will notice that the line number “112” is there but it does not reflect the changes in the sheet.
Please suggest a workaround as how can I show this text.
ps. This may be helpful in suggesting a solution:
I have noticed that the sheet shows the “Values” text when the temporary sheet is not deleted but once I delete the temporary sheet it does not show the “Values” text.
Looking forward to some solution.
Hi Radha,
Thank you for writing back.
I have executed the same sample project with Aspose.Cells for .NET 7.5.3, and have observed the same behavior as discussed earlier. The “sum of Values” field (value) appears in the column area field but not on the Pivot Table. The aforesaid behavior is clearly a bug in v7.5.3 that was fixed in later versions of the API. This is the same reason that we were unable to replicate the problem with recent releases of Aspose.Cells for .NET API.
It is strongly recommended that you should upgrade the API to latest available version, that is v8.1.0. If you are reluctant to upgrade the API due to any reason then you may workaround the situation by hiding the “tempMexico” worksheet instead of removing it from the workbook. This is because we confirmed that while using v7.5.3, if worksheet “tempMexico” isn’t removed, the “sum of Values” field (value) appears perfectly on the Pivot Table.
Please replace the following statement
wb.Worksheets.RemoveAt(“tempMexico”);
with
wb.Worksheets["tempMexico"].IsVisible = false;
Hope this helps a bit.
I am afraid but this does not work for me. I have to send these excel files to clients and I can not hide them and dispatch the files. Also this version is being used for many other projects so it won’t be easy/quick to integrate the newer version in all the projects. I would appreciate if someone more experienced person can look into this matter and suggest a workaround. I would prefer if “Amjad Sahi” (Free Support Forum - aspose.com) can reply on this.
Thank you for your help.
Hi Radha,
I have requested Amjad, as well as core development team to analyze the presented scenario to suggest another, more appropriate workaround for the situation. We will shortly respond back with more updates in this regard.
Hi,
We have analyzed your issue a bit. I have also discussed with the relevant developer. Well, it looks some sort of limitation in the older version that you are using (i.e., removing the data sheet of the PivotTable may cause some data values lost in the Pivot table report). However, the Pivot table manipulation feature is enhanced in the newer versions though. We are afraid it is our policy not to provide a fix or enhanced version based on older version. So, we cannot help you much as we cannot go against our policy. We can only recommend you to kindly try the workaround as Babar suggested. Alternatively, kindly upgrade to newer version of the product which works fine. Upgrading to latest version of the product has some other benefits here, e.g if you got any issue with the latest version, we are bound to fix it where as if you found any issue in your older versions, we cannot fix in them at all.
Sorry for any inconvenience caused!