Image Output Extracted using AsposeCells - related questions

Aspose.Cells Product Family
1

Hi,

We have recently upgraded Aspose.Cells from 20.2.4.0 to 24.12.0.0. Our application uses Aspose.cells to extract the image from excel files (uploaded by clients) and display in the reports.

After the upgrade we are facing multiple issues in our production after this. We see that in production, the images are broken like table content gets compressed/cropped, charts getting cropped. (refer attached images). But when we test these issues in our ‘Dev environment’ we are not able to replicate them even though the ‘entire application code base’ is in sync.

After spending some time, we found that ‘MS Office Version’ being installed in ‘Production Server’ and ‘Development Server’ are different as mentioned below. Slight difference in the build numbers.

In Production
Microsoft Excel 2016 (16.0.5483.1000) MSO (16.0.5483.1000) 32 bit in production

Dev VM
Microsoft Excel 2016 (16.0.5469.1000) MSO (16.0.5465.1000) 32 bit in production

So we have some questions to help our analysis,

  1. Is this difference in version, affect the ‘image output extracted from excel file’ using Aspose.Cells., Is this the reason we are not able to replicate these issues in our ‘Dev Environment’?

  2. We are assuming that ‘Aspose.cells’ is a standalone library and doesn’t depend on/requires MS Office? Is our assumption correct?

  3. ‘Does the version used to create the source excel file’ on different machines/MS Office version affect the ‘image output extracted from excel file’ using Aspose.Cells. ? If yes, how and why?
    For ex: Customer X is using ‘MS Office 2013’, Customer Y is using ‘MS Office 365’, they both create the same excel file in their respective machines and upload it our ‘production server’ , Production server has been installed with ‘MS Office 2016’. So, considering this scenario, what are all the factors that might affect the Aspose.cells output.

  4. Could you also please explain if any other factors that might affect the ‘image output extracted from excel file’ using Aspose.Cells ?

FYI, below are the configurations we set during our image extraction code base.

  private static void ApplyWorkSheetSettings(Worksheet sourceWorksheet)
        {
            sourceWorksheet.PageSetup.LeftMargin =
            sourceWorksheet.PageSetup.RightMargin =
            sourceWorksheet.PageSetup.TopMargin =
            sourceWorksheet.PageSetup.BottomMargin = 0;
            // Resets column's width to fit as per cell values.
            sourceWorksheet.AutoFitColumns(GetAutoFitterOptions());
        }

        public static ImageOrPrintOptions GetImageOrPrintOptionsSettings(Worksheet sourceWorksheet)
        {
            ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
            imageOrPrintOptions.OnePagePerSheet = true;
            // Always use Png as its file size is 8 times lesser than of Jpeg.
            imageOrPrintOptions.ImageType = ImageType.Png;
            imageOrPrintOptions.HorizontalResolution = 200;
            imageOrPrintOptions.VerticalResolution = 200;
            AutoFitterOptions autoFitterOptions = GetAutoFitterOptions();
            sourceWorksheet.AutoFitColumns(autoFitterOptions);
            sourceWorksheet.AutoFitRows(autoFitterOptions);

            return imageOrPrintOptions;
        }

        private static AutoFitterOptions GetAutoFitterOptions()
        {
            return new AutoFitterOptions()
            {
                AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
                AutoFitWrappedTextType = AutoFitWrappedTextType.Default,
                DefaultEditLanguage = DefaultEditLanguage.Auto,
                FormatStrategy = CellValueFormatStrategy.DisplayStyle,
                ForRendering = true,
                IgnoreHidden = true
            };
        }

Thanks,
Prathap

Attachments:
chart issues screenshot.png (104.2 KB)

table issue screenshot.png (85.8 KB)

2

@PrathapSV

Hi Prathap,

Thank you for your detailed inquiry regarding the issues you’re facing after upgrading to Aspose.Cells 24.12.0.0. Let’s address your questions one by one:

  1. Version Differences and Image Output: The difference in Microsoft Excel versions between your production and development environments could potentially affect the image output extracted from Excel files. Different builds of Excel may handle rendering and formatting differently, which could lead to discrepancies in how images and content are displayed. This might explain why you are unable to replicate the issues in your development environment.

  2. Aspose.Cells Dependency on MS Office: Your assumption is correct. Aspose.Cells is a standalone library and does not require Microsoft Office to function. It operates independently to manipulate Excel files, including extracting images.

  3. Impact of Source Excel File Version: Yes, the version of Microsoft Office used to create the source Excel file can affect the image output when using Aspose.Cells. Different versions of Excel may implement features and formatting differently, which can lead to variations in how content is rendered. For example, if Customer X uses MS Office 2013 and Customer Y uses MS Office 365 to create the same Excel file, the way certain elements are formatted or displayed might differ, especially if they utilize features specific to their respective versions.

  4. Other Factors Affecting Image Output: Several factors can influence the image output extracted from Excel files using Aspose.Cells:

    • DPI Settings: The DPI (dots per inch) settings of the environment can affect the clarity and size of the rendered images.
    • Font and Style Differences: Variations in default fonts or styles between different Excel installations can lead to differences in how text and images are rendered.
    • Page Setup and Margins: The settings for page margins and layout can also impact the final output. Ensure that these settings are consistent across environments.
    • AutoFit Options: The way columns and rows are auto-fitted can vary slightly between different versions of Excel and Aspose.Cells, which may lead to differences in spacing and layout.

Your provided code for image extraction looks good, but you might want to ensure that the settings for margins and auto-fitting are consistent with your expectations for the output.

If you continue to experience issues, consider testing with different versions of Aspose.Cells or reviewing the specific Excel files that are causing problems to identify any unique formatting or features that may be contributing to the discrepancies.

Feel free to reach out if you have further questions or need additional assistance!

Best regards.

3

@PrathapSV,

Thanks for the details.

Are you using the same version of Aspose.Cells (24.12.0.0) on both your production server and development environment, but encountering issues only on the production server? If so, there might be factors causing incorrect or broken images in your worksheets/spreadsheets. Consider the following:

  1. Display Settings: These can impact image accuracy and quality. What display settings are configured on your production server? Ensure that the display/scale settings are set to 100% to achieve accurate and correct images.
  2. Fonts: Missing fonts can lead to distorted images. Verify that all fonts used in your Excel spreadsheets are installed on the production server. Fonts are essential for rendering PDFs or images, so install any necessary fonts if they are missing. Additionally, specify the fonts folder in your code at the start of your application if it is not the default folder. For guidance, refer to the topic on configuring fonts for rendering spreadsheets: Configuring Fonts for Rendering Spreadsheets|Documentation

If you’ve addressed these two points and still face issues with incorrect images, please provide your template Excel file and the resulting images for further investigation.

Regarding your queries:

  1. Aspose.Cells does not depend on the MS Excel application or office automation APIs, so there should be no effect.
  2. Your understanding is correct.
  3. It won’t have any effect or be applicable.
  4. Refer to the two points mentioned above.
4

Thank you for the advise.

In our older version(20.2.4.0), we had the below settings.

private static void ApplyWorkSheetSettings(Worksheet sourceWorksheet)
        {
            sourceWorksheet.PageSetup.LeftMargin =
            sourceWorksheet.PageSetup.RightMargin =
            sourceWorksheet.PageSetup.TopMargin =
            sourceWorksheet.PageSetup.BottomMargin = 0;
            // Resets column's width to fit as per cell values.
            sourceWorksheet.AutoFitColumns();
        }

        public static ImageOrPrintOptions GetImageOrPrintOptionsSettings()
        {
            ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
            imageOrPrintOptions.OnePagePerSheet = true;
            // Always use Png as its file size is 8 times lesser than of Jpeg.
            imageOrPrintOptions.ImageType = ImageType.Png;
            imageOrPrintOptions.HorizontalResolution = 200;
            imageOrPrintOptions.VerticalResolution = 200;
            imageOrPrintOptions.IsCellAutoFit = true;

            return imageOrPrintOptions;
        }

In newer version(24.12.0.0) we had the below settings. (after the upgrade).

 private static void ApplyWorkSheetSettings(Worksheet sourceWorksheet)
        {
            sourceWorksheet.PageSetup.LeftMargin =
            sourceWorksheet.PageSetup.RightMargin =
            sourceWorksheet.PageSetup.TopMargin =
            sourceWorksheet.PageSetup.BottomMargin = 0;
            // Resets column's width to fit as per cell values.
            sourceWorksheet.AutoFitColumns(GetAutoFitterOptions());
        }

        public static ImageOrPrintOptions GetImageOrPrintOptionsSettings(Worksheet sourceWorksheet)
        {
            ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
            imageOrPrintOptions.OnePagePerSheet = true;
            // Always use Png as its file size is 8 times lesser than of Jpeg.
            imageOrPrintOptions.ImageType = ImageType.Png;
            imageOrPrintOptions.HorizontalResolution = 200;
            imageOrPrintOptions.VerticalResolution = 200;
            AutoFitterOptions autoFitterOptions = GetAutoFitterOptions();
            sourceWorksheet.AutoFitColumns(autoFitterOptions);
            sourceWorksheet.AutoFitRows(autoFitterOptions);

            return imageOrPrintOptions;
        }

        private static AutoFitterOptions GetAutoFitterOptions()
        {
            return new AutoFitterOptions()
            {
                AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
                AutoFitWrappedTextType = AutoFitWrappedTextType.Default,
                DefaultEditLanguage = DefaultEditLanguage.Auto,
                FormatStrategy = CellValueFormatStrategy.DisplayStyle,
                ForRendering = true,
                IgnoreHidden = true
            };
        }

The only difference we see is that "IsCellAutoFit " property was not public in Newer version(24.12.0.0), so after asking the support earlier(at the time of upgrade), we ended up with the above settings to replace the older one, hoping not to affect the functionality.

Do you see any concerns, with our older and new versions of settings? Please advise if you see any, also when I debug the ‘newer version’ - I see that the flag ‘imageOrPrintOptions.IsCellAutoFit’ is ‘false’, I believe internally your DLLs doesn’t update it as ‘true’. Is there a way to update it ? We are just suspecting these settings might be creating issues in production and trying to match the older and newer settings.

Thanks,
Prathap

5

@PrathapSV,

Thanks for the code snippets and further details

You have not provided details on what display/scale settings you are using on the production server? Is it not 100% display setting? Also, kindly provide your template Excel file and the resulting images (by older and newer version) to evaluate your issue on our end.

6

We are able to figure out the issue, it’s not related to display or resolution, but it was related to ‘Fonts’.

Our production had a font ’ Avenir LT 55 Roman Regular’ and our ‘Dev’ didn’t, so aspose cells was replacing the font with some default font in our Dev, thus we couldn’t replicate it in Dev.

But after replicating the issue in Dev, we found that the setting ‘AutoFitRows()’ is the biggest culprit and causing all these issues. If we just comment out the below code, pretty much 98% of the issues goes away in production.

sourceWorksheet.AutoFitRows(autoFitterOptions);

If you compare our ‘code snippet’ I sent earlier for the settings between ‘before upgrade’ vs ‘after upgrade’. We replaced the IsCellAutoFit with AutoFitColumns() and AutoFitRows() as per the advise from the Aspose support itself, after confirming that it would be equivalent replacement and won’t break any existing functionalities. Refer the previous ticket here

Before upgrade:

imageOrPrintOptions.IsCellAutoFit = true;

After upgrade:

 AutoFitterOptions autoFitterOptions = GetAutoFitterOptions();
            sourceWorksheet.AutoFitColumns(autoFitterOptions);
            sourceWorksheet.AutoFitRows(autoFitterOptions);

So, now our question is

  1. Why this is breaking our existing functionalities?
  2. Tell us more about the AuotFitRows() and it’s behavior, ?
  3. We are planning to remove this setting from the code and send to production , but would it create other issues if we do so?
  4. Let us know more about ‘IsCellAutoFit’ vs ‘AutoFitRows’, how the earlier setting ‘IsCellAutoFit’ handling the ‘AutoFit’ without creating any issues, and why the new ‘AutoFitRows’ creates issues, is it not the exact equivalent of ‘IsCellAutoFit’?
  5. what is the exact property used in AuotFitRows that is replaced with IsCellAutoFit?

This is creating a lot of noise in the production and we are just trying to understand the issue in depth and it’s potential risks when we change these kind of configuration before doing a production release.

I have also attached the the excel file and images (old vs new), please review along with our previous (old vs new configuration settings) and let us know how to effectively address this issue without causing any havoc in production.

after upgrade.png (27.7 KB)

before upgrade.png (7.0 KB)

psv new test for support.zip (6.7 KB)

Thanks in advance,
Prathap

7

@PrathapSV
We can obtain the correct results by testing on the latest version v25.4 using the following sample code. Please refer to the attachment. out_net.png (15.8 KB)

Workbook workbook = new Workbook(filePath + "psv new test for support.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

ImageOrPrintOptions options = GetImageOrPrintOptionsSettings(worksheet);
SheetRender render = new SheetRender(worksheet, options);
render.ToImage(0, filePath + "out_net.png");

public static ImageOrPrintOptions GetImageOrPrintOptionsSettings(Worksheet sourceWorksheet)
{
    ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
    imageOrPrintOptions.OnePagePerSheet = true;
    // Always use Png as its file size is 8 times lesser than of Jpeg.
    imageOrPrintOptions.ImageType = ImageType.Png;
    imageOrPrintOptions.HorizontalResolution = 200;
    imageOrPrintOptions.VerticalResolution = 200;
    AutoFitterOptions autoFitterOptions = GetAutoFitterOptions();
    sourceWorksheet.AutoFitColumns(autoFitterOptions);
    sourceWorksheet.AutoFitRows(autoFitterOptions);

    return imageOrPrintOptions;
}

private static AutoFitterOptions GetAutoFitterOptions()
{
    return new AutoFitterOptions()
    {
        AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
        AutoFitWrappedTextType = AutoFitWrappedTextType.Default,
        DefaultEditLanguage = DefaultEditLanguage.Auto,
        FormatStrategy = CellValueFormatStrategy.DisplayStyle,
        ForRendering = true,
        IgnoreHidden = true
    };
}

ImageOrPrintOptions.IsCellAutoFit indicates whether the width and height of the cells is automatically fitted by cell value. This member is now obsolete. Instead, please use AutoFitColumns and AutoFitRows.

We recommend you to kindly try using our latest version: Aspose.Cells for .NET v25.4.

If you still find the issue, kindly do share your complete sample (runnable) code to reproduce the issue on our end, we will check it soon.

8

It’s a production issue expecting to be fixed ASAP, We don’t have the time and resources to go through another upgrade at this point. Could you please try to answer our previous questions?

Also, it is not working in the latest version 25.4 as you mentioned, I have attached the sample and excel file here for your review.

AsposeCells_Sample-DC-Fee-TableIssue.zip (98.7 KB)

dc fee table issue for support v2.zip (257.3 KB)

Note: you need to change the ‘file path’ accordingly, in the UI -first text box or in the code, when running the sample.

9

@PrathapSV ，

After reveiwing the code of Aspose.Cells 20.2.4, the ImageOrPrintOptions.IsCellAutoFit is not used at all in our code. So please use the same code as the code used with Aspose.Cells 20.2.4. Just remove the code that uses ImageOrPrintOptions.IsCellAutoFit.

  1. The ImageOrPrintOptions.IsCellAutoFit is not used at all in our code. AutoFitRows/AutoFitColumns are not called.
  2. AutoFitRows is designed to recalculate row height and change row height to fit cell content. But it may not work OK for your font Avenir LT 55 Roman Regular.
  3. Please use the same code as the code used with Aspose.Cells 20.2.4. Just remove the code that uses ImageOrPrintOptions.IsCellAutoFit.
  4. Refer to answer 1.
  5. Refer to answer 1.

We apologize for any inconvenience caused for you. We will update descriptions for ImageOrPrintOptions.IsCellAutoFit that is not used at all.

10

Thanks a ton for finding this information, it was very helpful for us to take next steps.

We cannot remove the code that uses ImageOrPrintOptions.IsCellAutoFit since we have been currently using v24.12 where it is obsolete.

But during the upgrade, we added AutoFitRows() and AutoFitColumns() assuming that it is the equivalent of ‘IsCellAutoFit’.

So, Just to conclude your previous answer -

In v20.2.4 - If ‘ImageOrPrintOptions.IsCellAutoFit’ is not used at all.

and

In v24.12.0 (our recent upgrade) - we added AutoFitRows() and AutoFitColumns() ,

This means, if we have to match the functionality with our old v20.2.4, We SHOULD NOT USE
AutoFitRows() and AutoFitColumns() - these two functions in our v24.12.0 , since ‘IsCellAutoFit’ was never used in your v20.2.4.

Are we correct in our above assumptions, Please confirm.

Thanks a ton in advance.
Prathap

11

@PrathapSV ,

Yes, you’re right. ImageOrPrintOptions.IsCellAutoFit is not used in v20.2.4. Just remove the line for ImageOrPrintOptions.IsCellAutoFit for the code that works with v20.2.4.

12

@PrathapSV ,

A little more about mageOrPrintOptions.IsCellAutoFit

This property only takes effect for the very old Worksheet.SheetToImage method(about year 2009, version 4.8.1.1, the method is not there now). After we separate some functions related to SheetToImage from Worksheetclass and use new implementations, the property takes no effect. It is retained and expired only for compatibility with previous interfaces to prevent compilation errors in user programs.