Aspose.Cells - generating images from Excel file - related settings input range?

#5666:

Hi

We are using Aspose.Cells in our application to generate images from Excel File.
Below are the settings we are using along with other settings.

In a particular combination of these settings especially when we set all the margins(left, right, top and bottom) to 9.99
and Horizontal and Vertical Resolution to 600, we get the
exception thrown from Aspose.Cells ‘SheetRender’ object’s .ToImage(0) function. Error says - Invalid parameters.

SheetRender sr = new SheetRender(sourceWorksheet, imageOrPrintOptions);
Image imageToReturn = sr.ToImage(0);// this throws exception.

But when we set the ‘Horizontal and Vertical Resolution to 400’, it works fine.

So,

  1. Do you have any insights on this behavior?
  2. Do you have any standard input range for the ‘Horizontal and Vertical Resolution’? If yes, please do let us know.
  3. We are planning to set new input range for all our Aspose.Cells related settings. Below are our current limits, could you please let us know that are we giving a valid input range or do you suggest any new limits for the below, if Yes please mention the same?

Worksheet>PageSetup related properties:
Left Margin - Minimum value: 0.01 || Maximum value: 9.99
Right Margin - Minimum value: 0.01 || Maximum value: 9.99
Top Margin - Minimum value: 0.01 || Maximum value: 9.99
Bottom Margin - Minimum value: 0.01 || Maximum value: 9.99

ImageOrPrintOptions related properties:
Horizontal Resolution - Minimum value: 72 || Maximum value: 600
Vertical Resolution - Minimum value: 72 || Maximum value: 600

imageOrPrintOptions.SetDesiredSize function for setting custom width and height:
Width - Minimum value: 50 || Maximum value: 5000
Height - Minimum value: 50 || Maximum value: 5000

AutoFitterOptions>MaxRowHeight property:
Max Row Height - Minimum value: 10 || Maximum value: 200

Column > Width property:
Max Column Width - Minimum value: 5 || Maximum value: 200

We are planning to release this in this quarter and we don’t want to allow end users to give invalid input range and end up with bugs , which again we need to replicate and post to the Aspose support. Thus planning to reset the input range, whichever seems invalid or a potential candidate to create issues in future. Hope you understood our objective.

Please let us know as soon as you can.

Thanks in advance,
Prathap

@PrathapSV

Can you please specify the exact exception message you are receiving when the parameters are invalid? Additionally, could you clarify what specific insights you are looking for regarding the behavior of the ‘SheetRender’ object?

@PrathapSV,

Based on your description, it appears that setting both the margins and high resolution simultaneously leads to the Invalid parameters exception when using SheetRender.ToImage(0).

See the possible causes which are obvious for your scenario/case:

  1. When setting margins to 9.99 inches, it consumes a significant portion of the printable area, leaving insufficient space for the content. This could lead to rendering issues, especially at higher resolutions.
  2. A resolution of 600 DPI would produce a large image size. When combined with large margins, it may exceed memory or processing limits, causing the Invalid parameters exception.
  3. The combination of large margins and high resolution likely exacerbates the issue, leading to the exception. There may not be any standard or specifications to be set for a range.

You provided limits for different aspects and elements seem OK for your case as long as it works for your scenario. Anyways, to ensure stable performance and avoid exceptions, we will further evaluate your scenario/case thoroughly and may provide more details on how should you set worksheet Page Setup (margins), ImageOrPrintOptions (resolution), Image Size (desired width & height), max row height and column widths accordingly.

That is awesome, Thanks a ton. Please let us know as soon as you can.

Thanks,
Prathap

@PrathapSV,

Please note, the above options will effect the output image size.
Generally, setting Image Horizontal and vertical resolution to 600 alone will not cause exception. It seems you are also using OnePagePerSheet option as well, is not it?

We appreciate and it will be good if you could share us a standalone runnable project/app with resource files to reproduce the issue on our end. If sample project is not possible for you, please share the code segment of converting Excel to image and resource files at least. We will evaluate your issue thoroughly soon.

Yes, you are correct.

Our approach of generating the images is pretty basic, nothing fancy. Below is the core function which we use to generate the images.

 public Tuple<Image, bool> GenerateImageFromExcelCellRange(ReportsExcelLinkObject reportLinkedExcelObject, CellRangeObject selectedCellRangeObject, bool isFromReports)
        {
            Image imageToReturn = null;

            if (reportLinkedExcelObject == null)
                return null;

            bool isImageLoadedFromDatabase = false;

            Workbook workbook = null;
            try
            {
                workbook = new Workbook(reportLinkedExcelObject.ExcelFilePath);
            }
            catch (DirectoryNotFoundException ex)
            {
                isImageLoadedFromDatabase = true;

                if (isFromReports)
                    ex.LogException("Reports Excel Link Warning: Excel data could not be loaded directly from the file but from database, since file directory could not be found!");
            }
            catch (FileNotFoundException ex)
            {
                isImageLoadedFromDatabase = true;

                if (isFromReports)
                    ex.LogException("Reports Excel Link Warning: Excel data could not be loaded directly from the file but from database, since file could not be found!");
            }
            catch (Exception ex)
            {
                // Under Observation: Earlier we were catching any aspose related exceptions here, as we already handled filenotfound and directorynotfound exceptions, 
                // but in production some other exceptions like user access restrictions or others might come, so will be loading the file from db, 
                // in such cases as we are not sure of how many kinds of exceptions may come for UNC paths in production.
                isImageLoadedFromDatabase = true;
                // throw new Exception("Aspose Error Occured: Unable to read excel workbook/sheet using aspose!, please contact administrator", asposeException);

                if (isFromReports)
                    ex.LogException("Reports Excel Link Warning: Excel data could not be loaded directly from the file but from database, since an error occured while trying to fetch the excel file!");
            }

            if (isImageLoadedFromDatabase)
            {
                if (reportLinkedExcelObject.ExcelWorkSheetByteData.Length > 0)
                    workbook = AsposeCellsCommonFunction.BytesToWorkbook(reportLinkedExcelObject.ExcelWorkSheetByteData);
            }

            try
            {
                if (workbook != null && reportLinkedExcelObject.CellRangeList != null && reportLinkedExcelObject.CellRangeList.Count > 0)
                {
                    // For reports, whenever user adds the report link, if file is found in the file system, we are updating the workbook to db.
                    if (!isImageLoadedFromDatabase)
                        UpdateExcelData(reportLinkedExcelObject.ReportLinkId, workbook);

                    // Required, when user is still editing one excel sheet which has dependency on other sheets within the same workbook.
                    workbook.CalculateFormula(true);
                    if (selectedCellRangeObject != null)
                    {
                        Worksheet sourceWorksheet = workbook.Worksheets[selectedCellRangeObject.SheetName];

                        // If worksheet is not there in the system file, then just fetch the file from database and load the workbook again.
                        if (!isImageLoadedFromDatabase && sourceWorksheet == null)
                        {
                            workbook = null;

                            if (reportLinkedExcelObject.ExcelWorkSheetByteData.Length > 0)
                                workbook = AsposeCellsCommonFunction.BytesToWorkbook(reportLinkedExcelObject.ExcelWorkSheetByteData);

                            if (workbook != null)
                            {
                                workbook.CalculateFormula(true);
                                sourceWorksheet = workbook.Worksheets[selectedCellRangeObject.SheetName];
                            }
                        }

                        if (sourceWorksheet != null)
                        {
                            AsposeCellsFontWarningCallback asposeCellsWarnings = null;
                            reportLinkedExcelObject.WarningsForUser = new StringBuilder();
                            reportLinkedExcelObject.WarningsCount = 0;

                            #region image options and worksheet settings

                            // Set all margins as 0
                            ApplyWorkSheetSettings(sourceWorksheet, reportLinkedExcelObject, selectedCellRangeObject, isFromReports);

                            ImageOrPrintOptions imageOrPrintOptions = GetImageOrPrintOptionsSettings(sourceWorksheet, reportLinkedExcelObject);

                            if (!isFromReports)
                            {
                                asposeCellsWarnings = new AsposeCellsFontWarningCallback();
                                imageOrPrintOptions.WarningCallback = asposeCellsWarnings;// Show warning, if customer font not found in Dev/QA/Prod instances.
                            }

                            #endregion

                            ExcelDataRangeType rangeType = GetExcelDataRangeType(sourceWorksheet, selectedCellRangeObject.CellRange);

                            // This generates the image from excellink and also stores the image and workbook bytes in respective class properties.
                            // If the user has already set Print_Area in excel sheet then ExcelDataRangeType will be None, but still image has to be shown for the print area.

                            bool isPrintAreaSpecified = (rangeType == ExcelDataRangeType.None && !string.IsNullOrEmpty(sourceWorksheet.PageSetup.PrintArea) && selectedCellRangeObject.CellRange.ToLower() == "print_area");

                            if (rangeType == ExcelDataRangeType.TabularData || isPrintAreaSpecified)
                            {
                                bool isInvalidRange = false;
                                try
                                {
                                    // When both user defined cellRange and print area seems valid, cell range should be considered for creating image.
                                    if (rangeType == ExcelDataRangeType.TabularData)
                                        sourceWorksheet.PageSetup.PrintArea = selectedCellRangeObject.CellRange;
                                }
                                catch (Exception)
                                {
                                    isInvalidRange = true;
                                }

                                if (!isInvalidRange)
                                {
                                    SheetRender sr = new SheetRender(sourceWorksheet, imageOrPrintOptions);
                                    try
                                    {
                                        if (isPrintAreaSpecified)
                                        {
                                            string[] printAreas = sourceWorksheet.PageSetup.PrintArea.Split(',');
                                            if (printAreas.Length > 0)
                                            {
                                                if (printAreas.Length > 1)
                                                    imageToReturn = MergeMultiplePrintAreaImages(printAreas, sr, sourceWorksheet);
                                                else
                                                    imageToReturn = sr.ToImage(0);
                                            }
                                        }
                                        else
                                            imageToReturn = sr.ToImage(0);
                                    }
                                    catch
                                    {
                                        // if invalid range is given, then aspose fails to convert the range into image.
                                    }
                                }
                            }
                            else if (rangeType == ExcelDataRangeType.ChartData)
                            {
                                Aspose.Cells.Charts.Chart sourceChart = sourceWorksheet.Charts[selectedCellRangeObject.CellRange];
                                sourceChart.Calculate();
                                try
                                {
                                    // Aspose.Cells Bug Fix[CELLSNET-47202-chart legends overlapping issues in previous v19.4.5.0]:- Fixed in latest v20.2.4.0.
                                    // Note: Vertical legends in MS Excel chart when printed using Print command, by default MS Excel aligns the legends horizontally, 
                                    // same behavior can be seen in Aspose.Cells as they rely on MS excel's print command internally while converting chart to image.
                                    imageToReturn = sourceChart.ToImage(imageOrPrintOptions);
                                }
                                catch
                                {
                                    // if invalid range is given, then aspose fails to convert the range into image.
                                }

                            }

                            if (!isFromReports && asposeCellsWarnings != null && asposeCellsWarnings.FontWarningDetails != null)
                            {
                                foreach (string fontWarning in asposeCellsWarnings.FontWarningDetails)
                                {
                                    if (!string.IsNullOrEmpty(fontWarning))
                                        reportLinkedExcelObject.WarningsForUser.AppendLine(fontWarning);
                                }

                                reportLinkedExcelObject.WarningsCount += asposeCellsWarnings.FontWarningDetails.Count;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error: Generating Image from Cell Range failed!, please contact administrator", ex);
            }

            return new Tuple<Image, bool>(imageToReturn, isImageLoadedFromDatabase);
        }

The exception is thrown with any kind of excel files with some content like a table or chart.
Below is the list of our other settings, which made the Aspose.cells to throw the exception.

All Margins set to => 9.99
Horizontal and Vertical Resolution set to : 600
OnePagePerSheet = true;
ImageType = ImageType.Png;
Also calling → Worksheet.AutoFitColumns();

As you said, Individually when we apply these settings, there won’t be any issue, but when we combine these settings, we get the exception.

This is a very weird test case in our QA env, The exception is thrown randomly, ex: if we generate the image 10 times, out of 10 , exception is thrown 3 or 4 times. Another observation, When we debug the solution, we see this behavior only in our ‘release’ versions but when we debug the same app in our visual studio IDE in debug mode, it throws the exception consistently.

Thanks,
Prathap

@PrathapSV,

Thanks for sharing further details and code snippet.

We require thorough investigation of your issue. We will conduct some tests for your scenario/case and evaluate the exception (if occurred) when setting margins to 9.99 with horizontal and vertical resolution to 600. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58486

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

@PrathapSV

1).

The margins should be less than the size of Paper.
We do not check margins when setting margins.

2).

The row height should between 0 and 409.5 points . An Exception was thrown when invalid width was set with Aspose.Cells.

3).

The column width should be 0 and 255 in unit of characters.
An Exception was thrown when invalid width was set with Aspose.Cells.

@PrathapSV ,

The Invalid parameters exception is thrown by .Net while initilizing a Bitmap object with huge image width and height.

new Bimap(int width, int height)

You’re using OnePagePerSheet option, it will convert all the content in a sheet into one image. If the content in the sheet is huge, the width and height of output page/image is huge. Also, the large margins(9.99) and the bigger resolution(600) make it worse.
The limit of width and height while initilizing Bitmap may be different for different machines.

The API SheetRender.GetPageSizeInch(int pageIndex) can get width and height in inch of a page(for your case, the pageIndex is 0) before rendering to image.

SheetRender sr;
...

float[] whInch = sr.GetPageSizeInch(0);
float widthPixel = whInch[0] * imageOrPrintOptions.HorizontalResolution;
float heightPixel = whInch[1] * imageOrPrintOptions.VerticalResolution;
Console.WriteLine($"{widthPixel}, {heightPixel}");