Difference in unedited excel charts

Aspose.Cells Product Family
1

Hi,

I am using aspose cells 23.4 with Java and have an issue with unedited charts showing up different when parsed as image. I am attaching two files, that contain seemingly identical charts. The file original.xlsx has been opened in excel, not edited at all and saved as new.xlsx. The example code snippet shown below compares the first chart in the original file with the first chart in the new file. The areChartsEqual() method returns false, when expected is true. Similar problem with the rest of the charts. If I further edit the file new.xlsx, the problem does not persist.

original and new.zip (49.3 KB)

private boolean areChartsEqual() throws Exception {
    Workbook originalWorkbook = new Workbook(new FileInputStream("C:\\path\\to\\original.xlsx"), new LoadOptions());
    Workbook newWorkbook = new Workbook(new FileInputStream("C:\\path\\to\\new.xlsx"), new LoadOptions());

    Chart originalChart = originalWorkbook.getWorksheets().get(0).getCharts().get(0);
    Chart newChart = newWorkbook.getWorksheets().get(0).getCharts().get(0);

    ByteArrayOutputStream originalImage = chartToByteArrayStream(originalChart);
    ByteArrayOutputStream newImage = chartToByteArrayStream(newChart);

    return Arrays.equals(originalImage.toByteArray(), newImage.toByteArray());
}

  private static ByteArrayOutputStream chartToByteArrayStream(Chart chart) {
    try (ByteArrayOutputStream stream = new ByteArrayOutputStream()) {
      ImageOrPrintOptions imgOpts = new ImageOrPrintOptions();
      imgOpts.setVerticalResolution(300);
      imgOpts.setHorizontalResolution(300);
      imgOpts.setImageType(ImageType.PNG);
      chart.toImage(stream, imgOpts);
      return stream;
    } catch (Exception e) {
       throw new RuntimeException(e);
    }
  }
2

@Vaidas
When I unzip the zip file and open the “original.xlsx” file via MS-Excel, MS-Excel enters the protected view. I cannot directly save as a file. After I click on “Enable Editing” and save the file as “original_Bak. xlsx”, run the sample code, and I can obtain true value. Please refer to the attachment (108.5 KB).

3

@Vaidas
The default row height stored in the file is not correct and it should match the size of default font .
Please auto fit row height after loading file .

Workbook originalWorkbook = new Workbook(new FileInputStream(dir +"original.xlsx"), new LoadOptions());
originalWorkbook.getWorksheets().get(0).autoFitRows(true);

Automatic adaptation of line height is a time-consuming task, so we did not automatically execute it when loading files.

4

@simon.zhao
Could you please elaborate on why the row height stored is not correct and should match the size of the default font? Is this a corrupt file?

5

@Vaidas,

There are rows in your Excel file whose height are set to auto (which means MS Excel will auto-fit the rows when opening the Excel file into it to display the contents properly). In Aspose.Cells, we do not do this when reading the file as this is time consuming task, rather we provide Worksheet.autoFitRows(true) API for it, so setting “true” would auto-fit the rows whose height are auto.

6

@Vaidas

It’s not a corrupt file.
1, If the file is created by MS Excel,
a) Default font will change when region of OS is different,so automatic row height will be changed.
b) Different display scale setting will change automatic row height too.
2,The row height is set by API(such as Openxml sdk)

7

@amjad.sahi @simon.zhao
Thank you for your answers. Please, if you could help us understand the problem a bit further:

  1. You mention that the Excel file contains rows that have their height set to auto. Are these specific rows? If so, could you let us know which rows specifically are set to auto? We were unable to find such setting in Excel for specific rows.
  2. In addition to chart comparison, we also perform a comparison of other named ranges, such as tables. There seems to be no difference in any of the other components of the file while comparing, except for charts. Is this expected?
8

@Vaidas
1, All row heights default to automatic in Excel.And all row height are auto in your template file.
You can adjust row height by dragging row in Excel or format all row height.
And you can use Cells.IsDefaultRowHeightMatched | Aspose.Cells for .NET API Reference to change it.
2, Do you want to compare all cells of named ranges or the setting of named ranges?