Hi all, I created a pivot table with rowFields, columnFields, and DataFields. I’d like to know what is the equivalent in Aspose for show items with no data property in Excel.
Right click on the columnField ==> Field Settings ==> tab Print & Layout Show items with no data.
Because
when I filter by any rowField to narrow the results, some rows has no
values, and the columnField columns are not displayed. I want them to be displayed
even when they have no value.
Aspose 7.5 .Net
I wil appreciate any help on this.
Regards!
Hi,
I think you may try to use PivotTable.ShowEmptyCol attribute for your requirements. It specifies a boolean value that indicates whether to include empty columns in the table or not.
Thank you.
Thank you Amjad for your response, I've already tried with that but it didn't work. Please check the code, I commented some other properties I used as well to achieve what I need.
private void test()
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet2 = workbook.Worksheets.Add("Data");
DataTable dt = GetPivotTimesData();
sheet.Name = "PivotTable";
sheet2.Cells.ImportDataTable(dt, true, "A3");
PivotTable pivotTable = CreateAllPivotTables(workbook, dt);
sheet2.IsVisible = false;
//pivotTable.ShowEmptyCol = true;
//pivotTable.ColumnFields[0].IsAutoShow = true;
//pivotTable.ShowEmptyCol = true;
//pivotTable.DisplayImmediateItems = true;
//pivotTable.ColumnFields[0].ShowAllItems = true;pivotTable.ShowEmptyCol = true;
pivotTable.PreserveFormatting = true; Also I tried to keep the format in order to display the columns with no value in the column field after trying with different properties as you can see commented.
//Saving the Excel file
workbook.Save("C:\\test\\Test.xlsx");
}
private PivotTable CreateAllPivotTables(Workbook workbook, DataTable dt)
{
Worksheet sheet = workbook.Worksheets["PivotTable"];
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A3:N" + (3 + dt.Rows.Count).ToString(), "A3", "PivotTable", true);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
unshowTotals(pivotTable);
//unshowTotals(pivotTable2);
////Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
////Draging the second field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
////Draging the third field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 2);
pivotTable.AddFieldToArea(PivotFieldType.Row, 3);
pivotTable.AddFieldToArea(PivotFieldType.Row, 4);
pivotTable.AddFieldToArea(PivotFieldType.Row, 5);
pivotTable.AddFieldToArea(PivotFieldType.Row, 6);
pivotTable.AddFieldToArea(PivotFieldType.Row, 7);
pivotTable.AddFieldToArea(PivotFieldType.Row, 8);
pivotTable.AddFieldToArea(PivotFieldType.Row, 9);
pivotTable.AddFieldToArea(PivotFieldType.Row, 10);
pivotTable.AddFieldToArea(PivotFieldType.Page, 11);
pivotTable.PageFields[0].InsertBlankRow = false;
// Stage
pivotTable.AddFieldToArea(PivotFieldType.Column, 12);
pivotTable.AddFieldToArea(PivotFieldType.Data, 13);pivotTable.DataFields[0].ShowAllItems = true;
pivotTable.DataFields[0].NumberFormat = "yyyy/mm/dd";
pivotTable.DataFields[0].DisplayName = "MIN of DATE";
SettingFilters(pivotTable, ConsolidationFunction.Max);
//Accessing the row fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;
Cells cells = sheet.Cells;
//Accessing the first row field in the row fields.
for (int i = 0; i < pivotFields.Count; i++)
{
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[i];
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);
pivotField.IsAutoShow = false;
pivotField.IsAutoSubtotals = false;
cells.SetColumnWidth(i, 20);
}
setPivotStyle(sheet, pivotTable, workbook);
pivotTable.ShowEmptyCol = true;
pivotTable.IsGridDropZones = true;
pivotTable.RefreshDataFlag = true;
//pivotTable.RefreshDataOnOpeningFile = true;
//pivotTable.ShowValuesRow = true;
//pivotTable.ShowEmptyRow = true;
//pivotTable.ShowEmptyCol = true;
//pivotTable.ShowValuesRow = true;
return pivotTable;
}
private void SettingFilters(PivotTable pivotTable, ConsolidationFunction function)
{
PivotFieldCollection col = pivotTable.ColumnFields;
if (col.Count > 0 )
{
pivotTable.ColumnFields[0].Function = function;
pivotTable.ColumnFields[0].SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);
pivotTable.ColumnFields[0].DisplayName = "Stage";
pivotTable.ColumnFields[0].IsAutoShow = false;
}
col = pivotTable.DataFields;
if (col.Count > 0)
// Setting the Value Type Setting
pivotTable.DataFields[0].Function = function;
}
private void unshowTotals(PivotTable pivotTable)
{
//UnShowing the grand totals
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.ShowPivotStyleRowHeader = true;
pivotTable.ShowValuesRow = true;
pivotTable.ShowPivotStyleLastColumn = true;
}
private void setPivotStyle(Worksheet sheet, Aspose.Cells.Pivot.PivotTable pivotTable, Workbook workbook)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
Aspose.Cells.Style DataStyle = workbook.CreateStyle();
DataStyle.Font.Size = 8;
DataStyle.Font.Name = "Arial";
DataStyle.Font.Color = Color.Black;
DataStyle.RotationAngle = 70;
CellArea area = pivotTable.ColumnRange;
int start = area.StartColumn;
Cells cells = sheet.Cells;
for (int i = start; i <= area.EndColumn; i++)
{
pivotTable.Format(3, i, DataStyle);
//bool test = cells.Columns[i].IsHidden;
//cells.UnhideColumn(i, cells.Columns[i].Width);
}
pivotTable.ShowDrill = false;
//also I tried doing this, unhide the columns, because I need them visible always with or without data.
cells.UnhideColumns(start, area.EndColumn, cells.Columns[start].Width);
}
private DataTable GetPivotTimesData()
{
//Create a datatable and add three columns to it
DataTable dataTable = new DataTable("PivotTimes");
dataTable.Columns.Add("PROJECT ID", typeof(Int32));
dataTable.Columns.Add("PROJECT CODE", typeof(string));
dataTable.Columns.Add("PROJECT TITLE", typeof(string));
dataTable.Columns.Add("PROJECT MODE", typeof(string));
dataTable.Columns.Add("PROJECT TYPE", typeof(string));
dataTable.Columns.Add("USER DIRECTOR ID", typeof(string));
dataTable.Columns.Add("MANAGMENT AREA", typeof(string));
dataTable.Columns.Add("AMOUNT TYPE", typeof(string));
dataTable.Columns.Add("RELEASED DATE", typeof(string));
dataTable.Columns.Add("RELEASED MONTH", typeof(string));
dataTable.Columns.Add("RELEASED YEAR", typeof(string));
dataTable.Columns.Add("AMBIT", typeof(string));
//Stage Columns
dataTable.Columns.Add("STAGE", typeof(string));
dataTable.Columns.Add("MAX DATE", typeof(DateTime));
dataTable.Columns.Add("MIN DATE", typeof(DateTime));
//Add the first record to it
DataRow dr = dataTable.NewRow();
dr[0] = 4212;
dr[1] = "CODE04212";
dr[2] = "Interfaces";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "User13909";
dr[6] = "IT Canada";
dr[7] = "10";
dr[8] = "3/7/2012 9:29:20 PM";
dr[9] = "April";
dr[10] = "2012";
dr[11] = "IT";
// Stage values
dr[12] = "START";
dr[13] = DateTime.Now.AddDays(2);
dr[14] = DateTime.Now.AddDays(1);
dataTable.Rows.Add(dr);
dr = dataTable.NewRow();
dr[0] = 4212;
dr[1] = "CODE04212";
dr[2] = "Interfaces";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "User13909";
dr[6] = "IT Canada";
dr[7] = "10";
dr[8] = "3/7/2012 9:29:20 PM";
dr[9] = "April";
dr[10] = "2012";
dr[11] = "IT";
// Stage values
dr[12] = "START";
dr[13] = DateTime.Now.AddDays(2);
dr[14] = DateTime.Now.AddDays(1);
dataTable.Rows.Add(dr);
dr = dataTable.NewRow();
dr[0] = 4212;
dr[1] = "CODE04212";
dr[2] = "Interfaces";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "User13909";
dr[6] = "IT Canada";
dr[7] = "10";
dr[8] = "3/7/2012 9:29:20 PM";
dr[9] = "April";
dr[10] = "2012";
dr[11] = "IT";
// Stage values
dr[12] = "FINAL";
dr[13] = DateTime.Now.AddDays(2);
dr[14] = DateTime.Now.AddDays(1);
dataTable.Rows.Add(dr);
//Add the second record to it
dr = dataTable.NewRow();
dr[0] = 4585;
dr[1] = "CODE04585";
dr[2] = "Test SA";
dr[3] = "Project";
dr[4] = "Investment";
dr[5] = "User123";
dr[6] = " Manufacturing System";
dr[7] = "20";
dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");
dr[9] = "April";
dr[10] = "2012";
dr[11] = "IT";
// Stage values
dr[12] = "Quality";
dr[13] = DateTime.Now.AddDays(2);
dr[14] = DateTime.Now.AddDays(1);
dataTable.Rows.Add(dr);
//Add the second record to it
dr = dataTable.NewRow();
dr[0] = 4821;
dr[1] = "CODE4135";
dr[2] = " e-commerce-";
dr[3] = "Extension";
dr[4] = "Investment";
dr[5] = "123";
dr[6] = "IT Argentina";
dr[7] = "15";
dr[8] = Convert.ToDateTime("3/7/2012 9:29:20 PM");
dr[9] = "April";
dr[10] = "2012";
dr[11] = "Other";
// Stage values
dr[12] = "START";
dr[13] = DateTime.Now.AddDays(2);
dr[14] = DateTime.Now.AddDays(1);
dataTable.Rows.Add(dr);
return dataTable;
}
Any help on this will be much appreciated, perhaps some functionality is overlapping the property value you suggested: pivotTable.ShowEmptyColumn = true;
For instance, if I select the project code = CODE04585 the column field Stage hide the columns, in excel I could display them checking the Show items with no data option in the print & layout, but with Aspose I couldn't. Other thing I notice is that Excel does it by pivot field, and not for pivot table label. In this case I want to apply that property to Column field, and not to the the RowFields or the rest of the Pivot Table. Is this possible?
Help me please!
Regards!
Hi,
I have evaluated your issue a bit by using your sample code. I observed the issue after an initial test. Could you also provide us your template file (that you may manually create in MS Excel) with your desired pivot table report. We will check it and log a ticket into our database, so it could be figured out soon.
Thank you.
Hi Amjad, attached you'll find the requested template data and also the desired pivot table displaying all the items with no data when filtering by any other column for instance project_code.
Thanks in Advance,
Regards!
Hi,
Thanks for providing us the sample file with your desired pivot table report.
We will look into your sample code further if you could guide you through on how to achieve your requirements as per your file “TestAposeShowColumnWithNoDataIssue.xlsx” containing your desired pivot table report. I have logged a ticket with an id “CELLSNET-41875” for your issue. Our concerned developer will look into it soon.
Once we have any update on it, we will let you know here.
Thank you.
Hi,
Please use this sample code to show all items:
E.g.
pivotTable.DataFields[0].ShowAllItems = true;
Thank you.
Hi Amjad, thank you very much! It worked!!!
I had tried with ColumnField because in excel I changed the settings for that field, but in Aspose was different.
I really appreciate your time and effort to help people who work with Aspose!
Have a great day!
Regards :)
Hi,
Good to know it sorts out your issue.
Feel free to contact us any time if you need further help or have some other issue, we will be happy to assist you soon.
Thank you.
Hi Amjad! After doing some testing with more data, I noticed that it worked partially, I mean some of the data is displayed and some other is not.
For instance, my column field has 10 items and in case I filter by any other column in the pivot table, not all the items are displayed, just a few. When I check the list of items in the column field there are more which weren't being put in columns.
Is there a way to fix this?
Thanks in Advance!
Regards!
Hi,
If you still find the issue, kindly create a sample console application to reproduce the issue on our end. Also, provide provide us your template file (if you have any) and output file by Aspose.Cells. Moreover, please provide your expected file (that you may manually create in MS Excel) with your desired pivot table report. Also, attach some screen shots to highlight the problematic areas comparing the output with your desired result. We will check it soon.
Thank you.