Axis scale change for line chart

Aspose.Cells Product Family
1

Hi,


I am trying to convert a line chart shape named “Chart 2” in the attached excel file to byte array and saving that byte array as png image.

However, when I save the image bytes, I see that the scale of y axis of the line chart has changed; instead of an increase of 50k, the step increase is now of a 100k. so it goes from 0, to $100,000, when in the excel file, the scale goes from 0 to $50,000.

I used two methods to generate the image:

1. get the shape form the excel sheet, and convert shape to byte array using the following code:
Workbook wb = new Workbook(“Various_shapes.xlsx”);
Worksheet sheet= wb.getWorksheets().get(“Charts”);
Shape shape = sheet.getShapes().get(“Chart 2”);
ByteArrayOutputStream opStream = new ByteArrayOutputStream();
shape.toImage(opStream, ImageFormat.getPng());
byte[] imageBytes = opStream.toByteArray();
// save this byte array using FileWriter

2. use SheetRender to print the area occupied by the chart using the code below:
Workbook wb = new Workbook(“Various_shapes.xlsx”);
Worksheet sheet= wb.getWorksheets().get(“Charts”);
PageSetup setUp = sheet.getPageSetup();
// the purpose of the test is to demonstrate that the y-axis’ scale has changed,
// and hence it is not required to set other setup options like header/footer or margins.
// we can only set the print area as the range of cells covered by the chart.
setUp.setPrintArea(“F22:N36”);
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.setImageFormat(ImageFormat.getPng());
ByteArrayOutputStream opStream2 = new ByteArrayOutputStream();
new SheetRender(sheet, opts).toImage(0, opStream2);
byte[] imageBytes = opStream2.toByteArray();
// save this byte array using FileWriter

I can see this behavior in both of the approaches that I mentioned above.

Attached are the following files:
1. Various_Shapes.xlsx – the source excel file
2. lineChart_true.PNG – the expected png image
3. lineChart_fromShape.png – the png image using approach 1.
4. lineChart_toImage_sheetRender.png – teh ong image using approach 2.

I can see this issue in both 16.02.0 and 16.3.0 of aspose cells for java.

My environment details: Oracle Linux 5 (Intel 64-bit)
Java version: 1.7.0_85(default)
Java SE version: 1.7.0_85 (default)

Thanks.

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells for Java v17.4. It seems to be working fine.

We have tested this issue with the following code using the latest version and the output chart image inside the output pdf looks good. Let us know your feedback.

Java

Workbook wb = new Workbook(dirPath + “Various_shapes.xlsx”);
Worksheet ws = wb.getWorksheets().get(“Charts”);
Chart ch = ws.getCharts().get(“Chart 2”);
//ch.calculate();
ch.toPdf(dirPath + “out.pdf”);
3

Thanks for your response Shakeel.

We need to revisit this issue at this time because the issue is still occurring under the latest Aspose Cells version 17.10. Although we did observe that the Chart’s scale is correct for PDF output (as you stated above), our requirement is to generate a PNG image from an Excel Chart.

We can reproduce this issue using Aspose Cells 17.10, the attached Workbook (WorkbookToPdf.zip 11.7 KB) and the following code:

  try {
      String fileName = "C:\\WorkbookToPdf.xlsx";

      Workbook wb = new Workbook(fileName);
      Worksheet sheet = wb.getWorksheets().get("Charts");
      Shape shape = sheet.getShapes().get("Chart 1");

      fileName = fileName.replace(".xlsx", "_Chart1.png");
      try (ByteArrayOutputStream inStream = new ByteArrayOutputStream();
           FileOutputStream outStream = new FileOutputStream(fileName)) {

          // convert the chart to a PNG image
          shape.toImage(inStream, ImageFormat.getPng());

          // save the PNG bytes as a file
          outStream.write(inStream.toByteArray());
      }

  } catch (Exception ex) {
      System.out.println("Unexpected EXCEPTION: \n\t" + ex.getMessage());
      ex.printStackTrace();
  }

The most noticeable deltas between the PNG file produced and the original Excel Chart are:

  1. Scale on Y-axis is completely different
  2. The textual characters seem more condensed in the PNG
  3. The chart legend is in a different vertical position.

Note that different Excel charts produce other deltas, but if there’s a way to fix the issues described above, it will be very helpful.

Thanks.

4

@oraspose

We were able to observe the issue and found chart image is not alike Microsoft Excel. We have logged this issue in our database for product team investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42453 - Chart Image is not alike Microsoft Excel

Download Link:
Image of Chart.zip (5.3 KB)

Java

Workbook wb = new Workbook(dirPath + "WorkbookToPdf.xlsx");

wb.calculateFormula();

String chartName = "Chart 1";

Worksheet sheet = wb.getWorksheets().get("Charts");
Shape shape = sheet.getShapes().get(chartName);
  
try 
{
	ByteArrayOutputStream inStream = new ByteArrayOutputStream();
	FileOutputStream outStream = new FileOutputStream(dirPath + chartName + ".png");

	// convert the chart to a PNG image
	shape.toImage(inStream, ImageFormat.getPng());

	// save the PNG bytes as a file
	outStream.write(inStream.toByteArray());

}
catch (Exception ex) 
{
    System.out.println("Unexpected EXCEPTION: \n\t" + ex.getMessage());
    ex.printStackTrace();
}
5

@oraspose

1. Scale on Y-axis is completely different

As the Major Unit of Y-axis is Automatic (you can see the setting by Right-Click Y-axis–>Format Axis–>Axis Option–>Major), now we cannot do exactly same as MS Excel. It is too complex. It relates to lots of factors e.g. height of axis, font of axis, numbers of axis labels and so on. If the labels of axis is not overlapping, we think it is okay. If you drag the corner of chart and enlarge/reduce the size of chart, you will find the change.

2. The textual characters seem more condensed in the PNG

It relates to the Graphics API of Java. We provide the setting.

Java

ImageOrPrintOptions option = new ImageOrPrintOptions(); 
option.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);

3.The chart legend is in a different vertical position.

We will continue look into this issue.
6

@oraspose

We have fixed this issue. Please download and try the latest fix and let us know your feedback.

7

Shakeel,

Thanks again for your prompt responses and fixes. I tried using version 17.11.3 that you provided and I see improvements in the position of the chart’s legend.

Can we revisit the Axis Scale issue? While I understand the complexities in converting the chart to an image, our users expect the image to appear as close as possible to the original chart.

Given the information you provided, I found a code workaround that I hope may shed some light on the underlying issue. If we reduce the height of the image by 2 pixels, the scale on the axis appears as it is shown in Excel. For example, we can tweak the code as such:

Worksheet sheet = wb.getWorksheets().get("Charts");
Shape shape = sheet.getShapes().get(chartName);

if (shape.getMsoDrawingType() == MsoDrawingType.CHART) {

  // reducing the height by 2 pixels circumvents the axis scale bug
  shape.setHeight(shape.getHeight() - 2);
 }
 . . .

Please note that we would prefer not to use this workaround because we don’t know what side-effects it may have. However, I wanted to share this with you in case it sheds light on the complexities.

On a related note, I’m finding that the image generated from the chart is consistently wider than the chart’s original width. For example, Excel indicates that the original dimensions of the chart are 3 inches tall x 4.035 inches wide.
If we add the following code:

  System.out.format("Original Dimensions in inches: %f tall x %f wide %n",
                     shape.getHeightInch(), shape.getWidthInch());

The output is:
Original Dimensions in inches: 3.000000 tall x 4.312500 wide

So while the 3 inch height matches what Excel reports, the width does not. Once again, I wonder if this discrepancy is affecting the chart’s scale on the axis and maybe even contributing towards the other two issues (chart title and legend)?

Thanks

8

@oraspose

We have logged your feedback and concerns for evaluation in our database as CELLSJAVA-42472. We will look into them and update you. Once, there is some news for you, we will let you know asap.

9

@oraspose

Please check the attached Test.xlsx.

Test Excel File.zip (12.6 KB)

From First Chart to Second Chart, the Scales of Y-axis change. We can see the rule.

  • Step 1: Figure out the base scales (0, 10000, 2000,…,9000,100000) from the source data.
  • Step 2: Reduce the scales accordingly by the height of Y-axis.

Now the Step 1 is right but Step 2 is not right. Please check Test.png shown below.

Test.png

For getting the height of Y-axis, we need to know Blank1, Title height, Blank2, X-axis height and Blank3. The values are not provided from the Excel source file and they are dynamic for different Charts. Sometimes measured height of text by Graphics 2D of JDK is not same as Excel.

For the Chart you provided, we can adjust our calculating rules to fit the case. But in our testing system, lots of Charts will be not right. We need to take care of changing strategy of calculating the scales. If the scales are not overlapping, we think it is not a serious issue.

Also, the Legend issue is now fixed. In old version, we did not consider the Title and the Legend is placed at the center of Chart. But actually, the Legend is at the center of Plot Area.

10

Thanks for the update and details Shakeel.
I’m still concerned about the discrepancy with the chart’s width. The width reported by Cells is nearly 1/3 of an inch wider than what Excel reports. Surely the Excel source file provides the chart object’s overall width. More importantly, can this width discrepancy be contributing towards the Y-Axis scaling issue?

Thanks again.

11

@oraspose

We have logged your comment in our database for investigation.

Ticket ID: CELLSJAVA-42480

Once, there is some update for you, we will let you know asap.

12

@oraspose

Please show us DPI of your machine.

The returned width of Aspose.Cells is same as MS Excel in our machine of which DPI is 96.

13

Hi Shakeel,

OK that makes sense. The DPI on my monitor is 120 DPI. In order to reconcile the DPI setting, I tried to add the following code:

 ImageOrPrintOptions option = new ImageOrPrintOptions();
 option.setImageFormat(ImageFormat.getPng());
 // **set H-V resolution to 96 DPI**
 option.setHorizontalResolution(96);
 option.setVerticalResolution(96);
  . . .

However, this change didn’t seem to have any impact and produced the same output. The resulting image is still wider than the original chart.

More importantly, how do we ensure that all of our user’s image output is the same and independent of the underlying DPI settings on their system?

14

@oraspose,

I think you should try to set the DPI at the start as following:
e.g
Sample code:

..........
//Set dpi to 96
CellsHelper.setDPI(96);
15

Hi Amjad,

Thanks for your prompt response. I tried using the solution you provided, but it didn’t seem to have any effect. The resulting image’s width is still nearly 1/3 of an inch wider than the original chart.

In fact, even without invoking setDPI - if I add:
System.out.println("Cells global DPI setting: " + CellsHelper.getDPI());
The output is always: 96.0

Is there anything else you can think of that may help?
Thanks again.

16

@oraspose

Thanks for using Aspose APIs.

Please provide us full runnable sample code (preferably sample project) and screenshots showing the differences between Excel and Aspose.Cells. We will look into this issue and update you asap.

17

Hi Shakeel,

After some investigation, I now understand the discrepancy :grinning:

It turns out that Excel (the application) is affected by the so-called “high DPI scaling” display setting in Windows. As noted above, my display setting is set at 120 DPI.

Under 120 DPI, Excel reports the chart (i.e. “Chart 1” in WokrbookToPdf.xlsx) to be 4.04 inches wide. However, if I change my Windows display settings to 96 DPI, Excel will now report the same chart to be 4.31 inches wide (the same width reported by Aspose Cells). I see the same results when I disable the “DPI aware” setting in the “excel.exe.manifest” file and then launch Excel.

The following screen shot shows what Excel reports under both 96 DPI and 120 DPI displays:

ExcelChart1_Dimensions.png (8.7 KB)

Given the above, I think we’re good with the chart width issue.

The only outstanding issue is the original axis scale issue in the chart (which does not seem to be affected by the “high DPI scaling”). As I previously mentioned, we found a workaround in reducing the chart height by 2 pixels. However, we’re hesitant to use this workaround since we don’t know what side-effects it can have. We certainly understand that this may be a complex issue, but do you think you may be able to resolve it in a future release?

Thanks again for all your help.

18

@oraspose

Thanks for your feedback and considering Aspose APIs.

We have logged your comments in our database with screenshot for further investigation and evaluation. Once, we will have some update for you, we will let you know asap.

19

The issues you have found earlier (filed as CELLSJAVA-42453) have been fixed in newer version of Aspose.Cells for Java (Download | Maven).

Please also check the following document/blog for your reference:

20

@oraspose

Thanks for considering Aspose APIs.

We are afraid, we cannot fix the Axis Scales Issue in near future, so there is no ETA for this issue. You may check back with us in Year 2019 (after one full year). If we could fix this earlier than this we will let you know by posting in this topic.