Hi,
I have created a pivot table and I would like to change the background color
of the top two lines and the grand total line. (Please see the attached picture.
Also uploaded the excel file itself.)
How can I do this using Aspose Cells dotnet?
I tried to use pivottable.Format(row, column, style) to do it cell by cell but it does not really work.
(Only worked on some of the cells or not at all) When I used pivottable.FormatAll() it worked as it
was supposed to. (For all cells)
I even tried to define a range and do range.applystyle, but this did work either.
I would appreciate any help with this.
Thank you,
Tamas
Hi Tamas,
Thanks for your posting and using Aspose.Cells.
Which version of Aspose.Cells you are using? Please let us know. We tested pivottable.FormatAll() in the latest version: Aspose.Cells for .NET (Latest Version) and it did not work well.
In the similar way, pivottable.Format(row, column, style) does not work well either.
Once, you tell us your version, we will test it against it and log this issue in our database for investigation and fix.
Hi Shakeel,
Thanks for your reply.
download after registration. Hope it could be fixed soon.
Also, may I suggest that a method or methods that would apply style for a line or a column
would be very handy for achieveing a simple design for it fast.
(In lots of cases it is enough just the modify “header” and “footer” of the table.)
A question: is the version 8.0.0 stable? I had another problem with pivot table filtering.
(Maybe I did something wrong, or it is just another bug, I am not sure.)
Do you think it would be better for me to test an earlier version?
Thank you,
Tamas
Hi Tamas,
Thanks for your posting and using Aspose.Cells.
It seems, there is some issue with the PivotTable.Format and FormatAll methods and I have logged my findings in our database so that this issue could be investigated and fixed accordingly.
When, some style is applied via PivotTable.Format or FormatAll method and output file is opened in MS-Excel 2010, it appears fine for a blink of eye and then area is whitened again.
So, this issue should be fixed. We will let you know once, we sort out this issue. This issue has been logged as CELLSNET-42532.
Hi,
Thank you for analyzing the issue I have found.
As I mentioned earlier, we are currently evaluate Aspose Cells to find out
if it is eligible for us to use it in our products.
Unfortunately the the evaluation period is going to end soon so a decision needs to be made.
Because I would like to request an estimation regarding this issue as soon as possible.
Thank you for your understanding.
(I was informed about your Chinese developers, their “Qingming Festival” and their expected
arrival tomorrow so we can wait a little bit more.
)
Finally a question:
How can we add a report filter for an existing pivot table using .net, so the filter would appear
as a dropdown before the pivot table itself?
I attached a zip containing a screenshot and an example excel file to demonstrate what
I would like to achieve.
Thanks a lot!
Hi Tamás,
Thank you for your understanding & cooperation.
You may use the following line of code to drag any field to the Report Filter area. Please note, the second parameter is the field name as a string, whereas you may also refer it using the base field index.
C#
//Dragging Fields to Report Filter Area
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, “Type”);
Please feel free to write back in case you need our further assistance.
Hi,
Yes, your solution works! Now I am able to put fields in the Report Filter area.
Thank you.
Please let me know if you have any kind of estimation regarding my original formatting issue!
I need to report to my superiors.
Kind regards,
Tamas
Hi Tamas,
Good to know that you have moved forward with your requirement of Report Filter. Regarding the ticket CELLSNET-42532, it seems that the ticket has already been scheduled for analysis, and now a note has been recorded for the concerned development team member to provide the insight of the this issue. As soon as we receive some news in this regard, we will post here for your kind reference.
Hi Tamas,
We have thoroughly analyzed your requirements and found an other way to achieve it while using the latest build of Aspose.Cells for .NET API. Please check the below provided code snippet and the attached resultant spreadsheet for your kind reference.
C#
string filePath = myDir + “pastry2.xlsx”;
//Load workbook
Workbook workbook = new Workbook(filePath);
//Get worksheet containing the PivotTable using worksheet name
Worksheet worksheet = workbook.Worksheets[“PivotTable”];
//Get PivotTable instance
PivotTable table = worksheet.PivotTables[0];
//Create an instance of Style and add it to workbook style collection
Style style = workbook.CreateStyle();
//Set the style background color to yellow
style.BackgroundColor = Color.Yellow;
//Set the style pattern
style.Pattern = BackgroundType.Solid;
//Get the PivotTable range containing all cells of PivotTable
CellArea area = table.TableRange1;
//Iterate over the rows in the PivotTable range
for (int row = area.StartRow; row <= area.EndRow;row++ )
{
//Check which rows to paint
//In this case, 1st, 2nd and last
if (row == area.StartRow || row == area.StartRow + 1 || row == area.EndRow)
{
//Initialize an int variable to keep track of columns
int col = 0;
//Iterate till last column
while (col <= area.EndColumn)
{
//Set the format of each cell while accessing it through row and col indexes
//and apply the previously configured Style object
table.Format(row, col, style);
col++;
}
}
}
workbook.Save(myDir + “output.xlsx”);
Please feel free to write back in case you need our further assistance.
Hi,
Thanks a lot for providing me this example code.
I executed it but it seems that it still not producing the desired effect.
I attached a zip file to this message that contains the complete solution
that I executed the source xlsx (pastry2.xlsx) and also the resulting xlsx files.
(They can be found in ~\Aspose05CS\Aspose05CS\bin\Debug)
Also I am going to attach a screenshot about the resulting pivot table.
This includes your example code only.
I made one modification only: the code now saves three versions of the excel file:
the first after the first row is done, the second after the second row is done and
finally when the whole table is done. So it is possible to see what the code did
after each relevant row (first, second and the last)
What is odd is that when the table range of the pivot table is requested,
the result is: A1:G14 which is significantly bigger than the A1:E8 which is the real area
of the pivot.
I hope this info helps you.
I find it strange that using the same version you were able to produce the proper xlsx
file. I was using Aspose Cells version 8.0.0.0.
Hi Tamas,
Thank you for providing the sample project. The problem as mentioned in your previous post is happening due to the incorrect PivotTable range calculated by the Aspose.Cells for .NET 8.0.0. In past week we have published 8.0.0.1 of Aspose.Cells for .NET with some bug fixes reported in previous releases. The spreadsheet shared in my previous response was generated with v8.0.0.1, and I have again verified the results using your provided code snippet as well. We would request you to please download and use
Aspose.Cells for .NET (Latest Version) for your testing.
We are sorry for the inconvenience caused.
Hi,
This is now working perfectly!
Thanks a lot for all your help!
Kind regards,
Tamas
Hi Tamas,
Good to know that you are up and running again. Please feel free to write back in case you need our further assistance.
Hi Babar,
Thank you!
Actually I have one more question.
Is it possible in Aspose Cells to add a macro using a dotnet code to a spreadsheet?
(e.g. by putting the macro code in a string variable and then adding that macro to
a spreadsheet?)
I have found a “removemacro” method that deletes a macro from a spreadsheet.
and a “has macro” method in the documentation but I have not found an "AddMacro"
method anywhere.
Could you please confirm that such functionality exist in Aspose Cells?
Thanks a lot,
Tamas
Hi Tamas,
I am afraid, Aspose.Cells APIs can only remove or preserve the macros in a given spreadsheet. Addition and execution of the macros isn’t supported, nor we are considering to provide such support in near future.