Column spacing issue - when any row in the column has a long text

Aspose.Cells Product Family
1

Hi,

We found an issue in aspose.cells v24.10. The issue is when we export a “range” in the excel, even though the “range selected” doesn’t has any “long text/string” for any of the row for a given column. The output image “shows the space between the columns”. Refer attached screenshots.

This happens when we place “long string/text” in any one of the rows of a given column. But if there are no “long string” then it works fine.

We presume that aspose does this to accommodate the “long string”, which is totally fine if we “select the row which has that long string” . But here the issue is even though the “range selected” doesn’t have the “row with long string”, still it resizes to have more space between the columns.

We have replicated the issue and attached here with excel files and screenshots of two different scenarios (with space and without space). Use the “sheet name” and “range” in the respective screenshots when running the sample to see the difference.

Please provide a fix as soon as you can.

Below are the attachments.

AsposeCells_Sample_ZenDesk-111853.zip (33.4 KB)

excel file and issue screenshots.zip (97.5 KB)

Thanks
Prathap

2

@PrathapSV,

I evaluated your scenario/case using your template Excel file and sample code snippet. I also checked your template Excel file and found D7 has long text. Since you are auto-fitting columns in code so obviously the D column’s width would be expanded enough to accommodate the long text value. That’s why you get column spacing in the rendered image. Please do not auto-fit columns as your rendered range (“B8:E11”) don’t cover the long text cell(s). In short, please comment or remove the following lines and it will give you expected results:

sourceWorksheet.AutoFitColumns();

and

sourceWorksheet.AutoFitColumns(autoFitterOptions);

Let us know if you still have any issue or confusion.

3

We are aware of this code and behavior. We already tried commenting those lines before posting here and we can see that it will work. But if user selects “B7:E11” then we need the columns to autofit.

Basically, what we are asking is " Can aspose has any option or setting, where If the selected range has a long text, then only let it apply ‘autofit columns’, if the “selected range” doesn’t has any long text then do not apply ‘autofit columns’.

So, we are expecting that aspose should have a way to control the ‘autofit’ based on the “SELECTED RANGE” ? Please let us know.

4

@PrathapSV,

I think you may use the relevant AutoFitColumns overload4 or overload5 to support your needs. These methods will auto-fit columns for specific cells range only.

For example, you may try the line to auto fit columns in B8:E11 range.

sourceWorksheet.AutoFitColumns(7, 1, 10, 4);//B8:E11

Hope, this helps a bit.

5

Thanks for the update.

Is there a way where I can pass the “range value” directly, in this case “B8: E11”?

6

@PrathapSV
Thank you for your feedback. Worksheet.AutoFitColumns does not support directly passing Range value. You can obtain the corresponding row and column values through the Range API.

Please refer to the following APIs:
Range.ColumnCount
Range.FirstColumn
Range.FirstRow
Range.RowCount

7

Thanks for the update, could you please explain how do we initialize the “Range” object properly, I have tried the below approach to get the “range” object. But it is always null. FYI, We are passing “B8:E11” as cell range string here.

public static ImageOrPrintOptions GetImageOrPrintOptionsSettings(Worksheet sourceWorksheet, string cellRange)
        {
            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; //Obsolete in v24, need to use worksheet.AutoFitColumns(AutoFitterOptions) and worksheet.AutoFitRows(AutoFitterOptions)
            AutoFitterOptions autoFitterOptions = new AutoFitterOptions()
            {
                AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine,
                AutoFitWrappedTextType = AutoFitWrappedTextType.Default,
                DefaultEditLanguage = DefaultEditLanguage.Auto,
                FormatStrategy = CellValueFormatStrategy.DisplayStyle,
                ForRendering = true,
                IgnoreHidden = true
            };

            if (ApplyAutoFitColumnsToRangeOnly)
            {
                //   sourceWorksheet.AutoFitColumns(7, 1, 10, 4, autoFitterOptions);//"B8:E11"
                Range range = sourceWorksheet.Workbook.Worksheets.GetRangeByName(cellRange); // **here this object is always null?**
                if (range != null)
                    sourceWorksheet.AutoFitColumns(range.FirstRow, range.FirstColumn, range.RowCount, range.ColumnCount);
            }
            else
                sourceWorksheet.AutoFitColumns(autoFitterOptions);

            sourceWorksheet.AutoFitRows(autoFitterOptions);

            return imageOrPrintOptions;
        }
8

@PrathapSV
By testing with the following sample code, we found that there is no range in the sample file you provided.

Workbook wb = new Workbook(filePath + "aspose cells upgrade v1 - Chart and Table.xlsx");
WorksheetCollection sheets = wb.Worksheets;
ArrayList names = new ArrayList();
foreach (Worksheet sheet in sheets)
{
    Console.WriteLine(sheet.Name + "   range count: " + sheet.Cells.Ranges.Count);
               
}

The output result:

MSSB Req   range count: 0
Sheet4   range count: 0
Sheet1   range count: 0
Chart   range count: 0
Table   range count: 0
Sheet2   range count: 0
Sheet3   range count: 0
colSpacing-Issue   range count: 0
9

Thank you for looking into this.

I realized that I was not using the correct way to get the cell range as it was not a “named range” that our application was using. It was a “cell address string => B8:E11”

After using below approach I am able to work this out.

 
                Range range = sourceWorksheet.Cells.CreateRange("B8:E11");
                if (range != null)
                    sourceWorksheet.AutoFitColumns(range.FirstRow, range.FirstColumn, range.RowCount, range.ColumnCount); // 7, 1,4,4

But in one of your previous answers, to “autofit columns based on a range” , you asked us to use the below overloaded function.

sourceWorksheet.AutoFitColumns(7, 1, 10, 4, autoFitterOptions);//"B8:E11"

Since we don’t want to hardcode or extract the first four parameters above, we are trying to achieve the same using the “Range” object and the function sourceWorksheet.Cells.CreateRange(“B8:E11”); is giving us the range object as well. Now we were expecting that the “range” object here will have the necessary values to pass for the first four parameters of “AutoFitColumns” function above, But when i verified the “range” object in debug mode, instead of “7, 1, 10, 4” we are getting “7, 1, 4, 4”.
Only the third parameter value is different and I know it’s because we use “range.RowCount” which is 4 in this case.

But we are not sure, should the usage of range object properties below for “AutoFitColumns” function is correct in our case, since if we compare with your previous answer parameter values(7,1,10,4), it won’t match.

sourceWorksheet.AutoFitColumns(range.FirstRow, range.FirstColumn, range.RowCount, range.ColumnCount);

Please let us know.

10

@PrathapSV,

You need to adjust the line of code to:

sourceWorksheet.AutoFitColumns(range.FirstRow, range.FirstColumn, range.FirstRow + range.RowCount -1, range.FirstColumn + range.ColumnCount -1);

Hope, this helps a bit.

11

Thank you for the update.

We are aware of defining the ranges in two ways in Microsoft excel sheet,

  1. using “cell address strings” ex: “B8: E11”
  2. using the “named ranges” ex: select the cells and go to formulas tab and define name option.

So, we are handling only these two kinds of “range” inputs in our application. Could you please let us know if there are any other ways that users can define the ranges in excel sheet, so that we start working on implementing the code to handle those “inputs”?

12

@PrathapSV
Please refer to the relevant API documentation.
CreateRange(string address)
CreateRange(string upperLeftCell, string lowerRightCell)
CreateRange(int firstIndex, int number, bool isVertical)
CreateRange(int firstRow, int firstColumn, int totalRows, int totalColumns)

13

@PrathapSV,

Moreover, if you want to create named range in code (similar to using Name Manager (MS Excel)), you may use NameCollection and Name object. See the following sample code for your reference.
e.g.,
Sample code:

            // Create a new workbook
            Workbook workbook = new Workbook();
            
            // Access the worksheet collection
            WorksheetCollection worksheets = workbook.Worksheets;
            
            // Access the name collection
            NameCollection names = worksheets.Names;
            
            // Add a new name to the collection
            int nameIndex = names.Add("MyNamedRange");
            Name name = names[nameIndex];
            
            // Set the refers to property for the name
            name.RefersTo = "=Sheet1!$A$1:$A$10";
            
            // Access and modify properties of the name
            name.Comment = "This is a named range for demonstration.";
            name.IsVisible = true;
            
            // Add another name
            int anotherNameIndex = names.Add("AnotherNamedRange");
            Name anotherName = names[anotherNameIndex];
            anotherName.RefersTo = "=Sheet1!$B$1:$B$10";
            
            // Remove a name by text
            names.Remove("AnotherNamedRange");
            
            // Remove a name by index
            names.RemoveAt(nameIndex);
            
            // Clear all names
            names.Clear();
            
            // Save the workbook
            workbook.Save("NameCollectionExample.xlsx");

Hope, this helps a bit.

14

Thank you for the update.

In the sample I have posted in this thread, if you observe the code, we are using “png” images when generating images from excel data using aspose.cells (refer GetImageOrPrintOptionsSettings() function). Now, Some of our customers are complaining that the image quality is not that good.

We initially came up with “png” since image size was 8 times lesser than the others. But now we are planning an enhancement in our application giving user the control to chose between image quality vs size optimization.

So considering this, Could you please guide us on how we can improve the image quality here? Should we use Other than png like jpeg or emf etc or any other settings to apply?

Please let us know.

15

@PrathapSV,

I think you can try to increase horizontal and vertical resolutions in code, it may enhance the quality of the rendered image. Alternatively, you may use EMF image type which is a vector image format to get better quality images.