We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Issues with handling Scatter Chart, Trend Eq Label and Calculate()

Aspose.Cells Product Family
#1

Please see few issues in handling the attached file

trend_eq_issue.zip (12.8 KB)

by code:

var wb = new Workbook(GetFullPath("trend_eq_issue.xlsx"));
var sheet = wb.Worksheets[0];
sheet.Cells.ImportArray(new[] {1, 1, 1}, 1, 1, true);

foreach (var pt in sheet.PivotTables)
{
    pt.RefreshData();
    pt.CalculateData();
}

foreach (var chart in sheet.Charts)
{
    if (chart.PivotSource == null)
    {
        chart.Calculate();
    }
    else
    {
        chart.RefreshPivotData();
        chart.Calculate();
    }
}
wb.Save(GetFullPath("result.xlsx"), SaveFormat.Xlsx);
wb.Save(GetFullPath("result.pdf"), SaveFormat.Pdf);
  1. The produced PDF has the trend equation as y=E+00x + 1 while in Excel it would be y=1, R is incorrect as well.
  2. chart.Calculate() breaks the Scatter Chart (see the screenshot): incorrect legend, the trend equation label is incorrect and does not reflect changes in data and trend (both charts are affected).

image.png (3.1 KB)

The issue is observed with Aspose.Cells 17.8.0.

#2

@dzmitry.martynau,

Thanks for the template file, sample code and screenshot.

I have evaluated your issue using your template file and sample code and found the following issues:

  1. the equation is rendered as “y = E+00x + 1” while it should be: “y = 1”. Also regarding R, it should be:“R² = #N/A”
  2. Regarding

I could not see incorrect legend items.

Could you confirm the issue 1). Also, it would be better if you could provide us your expected Excel file (containing your desired charts and pivot table) and a screenshot to highlight the problematic areas (by encircling in red color), so we should not miss anything. We will log appropriate tickets to figure out your issues upon your confirmations and given details.

Thank you.

#3

Attaching the produced file: result.zip (14.3 KB)

There are two issues after chart.Calculate():

  1. Trend label is incorrect (as you confirmed above), but another issue it that the label is fixed, i.e. the file opened in Excel, data are changed, the plot redraws the trend line, but the label is displayed unchanged (. See the screenshots)
  2. Observer the marked legend area:
    scatter_issue1.PNG (13.7 KB)
    scatter_issue2.PNG (18.4 KB)

Btw the PDF file produced by Aspose looks ok, so it seems for like a translation issue during save to XLSX: result.pdf (44.0 KB)

#5

@dzmitry.martynau,

Thanks for providing us further details and sample files.

I have logged the following two issues:

  • CELLSNET-45602 - Issue with trend equations in the chart
  • CELLSNET-45603 - Issue with Chart.Calculate() method

Our product team will evaluate your issues in details and figure these out soon.

Once we have an update on any of the issues,we will let you know here.

Thank you.

#6

@dzmitry.martynau

Please download and try the following fix and let us know your feedback.

#7

Unfortunately, my 1. is not fixed. But 2. looks ok.

The trend label with equations is not updated when data are changed, for the file saved via Aspose.Cells API if Calculate is called on the chart object. It look like the trend label behaves as a static label with the text fixed on a value generated by Aspose.Cells, changes in data move the trend line and the label along with it. But the contents of the label does not change as expected.

#9

@dzmitry.martynau

We have checked your first issue relating to trend label with equations with the 17.8.6 and found, everything is working good.

Please check the output Excel and Pdf files for your reference.

Download Links:
result Excel file.zip (14.3 KB)
result.pdf (46.9 KB)

#10

The issues you have found earlier (filed as CELLSNET-45602;CELLSNET-45603) have been fixed in Aspose.Cells for .NET 17.9.

#11

Unfortunately, I didn’t respond earlier. The issue is observed even in the output file Shakeel attached in his response. If I open “result Excel file”.zip in Excel 2013, and change the value in B4 from 1 to 10, the line is updated reflecting the change in the trend, but the label is not updated – and this is the issue I reported under my p.1.

So the issue is not fixed (I retested it using 17.9.0.0). Please, take a look.

#12

@dzmitry.martynau,

Please provide your expected Excel file in which if we change B4 value from 1 to 10 manually in MS Excel, the trendline label should be changed accordingly, you may create your expected file manually in MS Excel 2013 and share it here, we will check it soon.

#13

Attaching two files result Excel file.zip (33.4 KB):

  • result 1to10inB4only.xlsx: your output file saved after B4 updated to 10, with the issue that the trendline label is not updated (see the screenshot broken_trendline.PNG (2.1 KB))
  • result fixed.xlsx: the same file after I recreated the trendline, label shows expected equation (see fixed_trendline.PNG (2.4 KB))

The issues in the label for the trendline that displays the equation and R2 for the trendline.

#14

@dzmitry.martynau,

Thanks for the sample files, screenshots and details.

I am able to reproduce the issue as you mentioned by using your original code with your template file. I found that trendline labels are not updated in MS Excel accordingly when we manually change the source value in the cells. The issue can be confirmed by opening the output XLSX file into MS Excel and manually update the value of B4 from 1 to 10.

I have logged a separate ticket with an id “CELLSNET-45667” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

#15

@dzmitry.martynau

Please download and try the latest fix and let us know your feedback.

#16

Tested with 17.9.2, it still does not fix it.

Opened the result file look like this: trend_1.PNG (14.3 KB)
After I change B4 to 10 it look like: trend_2.PNG (15.1 KB) – The trend label fails to update.

#17

@dzmitry.martynau

Thanks for using Aspose APIs.

We have tested it with the most recent version i.e. Aspose.Cells for .NET v17.9.4 and it worked fine.

Download Links:
Input and Output Files.zip (54.1 KB)

C#

Workbook wb = new Workbook("trend_eq_issue.xlsx");

Worksheet ws = wb.Worksheets[0];

ws.Cells["B2"].PutValue(1);
ws.Cells["B3"].PutValue(1);
ws.Cells["B4"].PutValue(10);

wb.Save("output.pdf");

Screenshot

#18

The issue’s not in the produced PDF, but in the produced XLSX. I change B4 in Excel, not via Aspose API. And it’s in the Excel the change in data is not reflected properly in the trend legend (in my previous post the screenshots were made from Excel).

#19

@dzmitry.martynau,

Please make sure that you are using v17.9.4 (latest fix). i have tested using the following sample code with your original file, it works fine
e.g
Sample code:

        var wb = new Workbook("e:\\test2\\trend_eq_issue.xlsx");
        var sheet = wb.Worksheets[0];
        sheet.Cells.ImportArray(new[] {1, 1, 1}, 1, 1, true);
        sheet.ListObjects[0].AutoFilter.Refresh();
        foreach (PivotTable pt in sheet.PivotTables)
        {
            pt.RefreshData();
            pt.CalculateData();

        }

        foreach (Chart chart in sheet.Charts)
        {
            if (chart.PivotSource == null)
            {
                chart.Calculate();
            }
            else
            {
                chart.RefreshPivotData();
                chart.Calculate();
            }
        }
        
        wb.Save("e:\\test2\\out1.xlsx", SaveFormat.Xlsx);

Now I open the output file (attached) into MS Excel and insert 10 into B4 cell, the equation line and legend is changed accordingly. See the screenshots below for your reference.

  1. I open the file into MS Excel manually:
    https://i.imgur.com/ZNiFcbo.png

  2. Now I change the B4 value to 10. You will see that data is reflected in the trendline and legend:
    https://i.imgur.com/Ao2ZKup.png

Please open the output file into Ms Excel manually and change the value in B4 cell manually.
Let us know if you still find the issue.
file1.zip (14.8 KB)

#20

Yes, I confirm 17.9.4 fixes my issue. Thanks

#21

@dzmitry.martynau

Thanks for sharing good news with us. If you face any other issue, please feel free to let us know, we will be glad to look into your issue and help you further.

#22

The issues you have found earlier (filed as CELLSNET-45667) have been fixed in Aspose.Cells for .NET 17.10.