#DIV/0! Error

Hi Aspose Support team,

I am facing the issue while i used the version 7.0.2.7. The issue is In the version 7.0.2.7, the pivot table Data field is showing as #DIV/0!. But earlier i have used the version 5.0.2.5, in this version the pivot table data field is showing as blank if not having value. I didnot change anything in the code, i just changed the Aspose DLL only. For your kind reference i have attached images(5.0.2.5Version.jpeg and 7.0.2.7Version.jpeg)

Could you please advice how to avoid this issue with the version 7.0.2.7? if you have any sample piece of code, please provide me.

Thanks
Saravanan

Hi,

Thanks for your posting and using Aspose.Cells.

Please also share your source xls/xlsx file and the sample code replicating this issue. We will look into it and provide you a fix if any asap.

Hi Aspose Support team,

Please find the attached sample xlsx file and the below sample code for your kind reference. Please help me ASAP. This is an very urgent for me.

Sample code:

-------------

PivotTableCollection pivotTables = obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables;
string pivotDataSourceArea = "DATA!A8:" + obj.ExcelWorkBook.Worksheets["DATA"].Cells[dtDetails.Rows.Count + 7, dtDetails.Columns.Count - 1].Name;
string startingCell = "A36";
string pivotTableName = "PivotTable";
int index = pivotTables.Add(pivotDataSourceArea, startingCell, pivotTableName);
PivotTable pivotTable = pivotTables[index];

pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["Terminal/Berth - First line ashore"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Terminal/Berth/ETA"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["Cargo Activity"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Cargo No"].Ordinal);
pivotTable.RowFields.AddByBaseIndex(dtDetails.Columns["PortLog - Trade Name"].Ordinal);

pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["All Fast"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["All Fast to hose connected"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Hose connected to cargo ops commence"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Cargo Ops"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Cargo Ops to hose disconnect"].Ordinal);
pivotTable.DataFields.AddByBaseIndex(dtDetails.Columns["Hose disconnected to all lines on deck"].Ordinal);


PivotField pivotDataField1 = pivotTable.DataFields[0];
pivotDataField1.Function = ConsolidationFunction.Min;
pivotDataField1.NumberFormat = "dd-mmm-yy hh:mm";

PivotField pivotDataField2 = pivotTable.DataFields[1];
pivotDataField2.Function = ConsolidationFunction.Average;
pivotDataField2.NumberFormat = "[hh]:mm";

PivotField pivotDataField3 = pivotTable.DataFields[2];
pivotDataField3.Function = ConsolidationFunction.Average;
pivotDataField3.NumberFormat = "[hh]:mm";

PivotField pivotDataField4 = pivotTable.DataFields[3];
pivotDataField4.Function = ConsolidationFunction.Average;
pivotDataField4.NumberFormat = "[hh]:mm";

PivotField pivotDataField5 = pivotTable.DataFields[4];
pivotDataField5.Function = ConsolidationFunction.Average;
pivotDataField5.NumberFormat = "[hh]:mm";

PivotField pivotDataField6 = pivotTable.DataFields[5];
pivotDataField6.Function = ConsolidationFunction.Average;
pivotDataField6.NumberFormat = "[hh]:mm";

pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);
pivotTable.ColumnGrand = false;


Style DataStyle = obj.ExcelWorkBook.CreateStyle();

DataStyle.Font.Size = 8;
DataStyle.Font.Name = "Arial";
DataStyle.Font.Color = Color.Black;
DataStyle.RotationAngle = 90;

pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;

pivotTable.DataFields[0].DisplayName = "All Fast ";
pivotTable.DataFields[1].DisplayName = "All fast to hose connected";
pivotTable.DataFields[2].DisplayName = "Hose conn - cargo ops commence";
pivotTable.DataFields[3].DisplayName = "Cargo Ops";
pivotTable.DataFields[4].DisplayName = "Cargo Ops complete - hose disconnect";
pivotTable.DataFields[5].DisplayName = "Hose disconnected to all lines on deck";

pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Vessel Name"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Cargo details-Voyage No"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Port Name"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Terminal"].Ordinal);
pivotTable.PageFields.AddByBaseIndex(dtDetails.Columns["Berth"].Ordinal);


pivotTable.RowFields[0].IsAutoSort = true;
pivotTable.RowFields[1].IsAutoSort = true;
pivotTable.RowFields[2].IsAutoSort = true;

pivotTable.PageFields[0].IsAutoSort = true;
pivotTable.PageFields[1].IsAutoSort = true;
pivotTable.PageFields[2].IsAutoSort = true;
pivotTable.PageFields[3].IsAutoSort = true;
pivotTable.PageFields[4].IsAutoSort = true;

obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[1].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[2].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[3].IsAutoSubtotals = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].ShowInOutlineForm = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].ShowRowHeaderCaption = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].NumberFormat = "m/d/yyyy h:mm";

obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].RowFields[0].ShowInOutlineForm = false;
obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].ShowRowHeaderCaption = false;

obj.ExcelWorkBook.Worksheets["Pivot"].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].PivotSource = "Pivot!PivotTable";
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ChartArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].PlotArea.Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].Title.Text = "";
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ShowLegend = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].Legend.Position = LegendPositionType.Top;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabels.Rotation = 45;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabels.Font.IsBold = false;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.MajorTickMark = TickMarkType.Outside;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].ValueAxis.MinorTickMark = TickMarkType.None;

obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.IsPlotOrderReversed = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.HasMultiLevelLabels = true;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.MajorTickMark = TickMarkType.Inside;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.MinorTickMark = TickMarkType.None;
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].CategoryAxis.TickLabelPosition = TickLabelPositionType.NextToAxis;

Hi,

Thanks for your sample code and the file.

Please let us know if Sample_Report.xlsx is an input file (which you load) or output file (generated by Aspose code).

Please also provide me the file with your desired (expected) output.

Hi,

If the data field function is “Average”, the user’s sample code is pivotDataField6.Function = ConsolidationFunction.Average

Then when we create the pivot table using MS Excel manually and we found that the value is also showing as “#DIV/0!”.

Hi Support team,

Sample_Report.xlsx is generated by the Aspose code with Version 7.0.2.7. For that pivot table creation, input data from the Data sheet of the same excel. The desired output is attached herewith. if data is availabale means, it will display the data with hh:mm format(refer ExpectedData.jpeg) otherwise it will display like blank(refer the ExpectedOutput.xlsx). It will not show like #DIV/0!.

In the earlier version(5.0.2.5.) it show like blank. The ExpectedOutput.xlsx is generated by the Aspose version 5.0.2.5. Now i am expecting the compatipility because the report is working fine in the live for past one year. For few feature i have changed the Latest version 7.0.2.7. but if i want to use this DLL means it must be showing like blank.

In addition, i need to display the data header with rotate 90 degree angle(For this please refer the ExpectedData.jpeg). i have used the following code. but i cannot able to achive this feature.

Style DataStyle = obj.ExcelWorkBook.CreateStyle();

DataStyle.Font.Size = 8;
DataStyle.Font.Name = "Arial";
DataStyle.Font.Color = Color.Black;
DataStyle.RotationAngle = 90;

pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;

pivotTable.DataFields[0].DisplayName = "All Fast ";

obj.ExcelWorkBook.Worksheets["Pivot"].PivotTables[0].Format(35, 5, DataStyle);

Please help me to achieve this feature/issue.

Thanks
Saravanan

Hi,

Please set DisplayErrorString flag of pivot table to true,
Sample code:
wb.Worksheets[1].PivotTables[0].DisplayErrorString = true;
also we continue to look into the format style issue. Once we have any update, we will let you know here.

Thank you.

Hi,

We create the pivot manually using the same source and set the cell's ["I8"] style manually in Ms Excel and we found that the style also could not be setting in this cell.
Please find attached the file with the pivot created manually.

Thank you.