Hi,
We are trying to expose all the properties of ‘AutoFitterOptions’ for end users in our app, but there is a property called ‘MaxRowHeight’, I believe it make sense for AutoFitRows(), but how this will affect the ‘AutoFitColumns()’? Below is our code for your review.
Also, what about ‘OnlyAuto’ property, does it have impact on both AutoFitRows and AutoFitColumns?
if (reportLinkedExcelObject.Settings.AutoFitRows)
sourceWorksheet.AutoFitRows(GetAutoFitterOptions(reportLinkedExcelObject, true));
if (reportLinkedExcelObject.Settings.AutoFitColumns)
sourceWorksheet.AutoFitColumns(GetAutoFitterOptions(reportLinkedExcelObject, false));
private static AutoFitterOptions GetAutoFitterOptions(ReportsExcelLinkObject reportLinkedExcelObject, bool isRowAutoFitter)
{
return isRowAutoFitter ? new AutoFitterOptions()
{
AutoFitMergedCellsType = reportLinkedExcelObject.Settings.RowAutoFitterOptions.AutoFitMergedCellsType,
AutoFitWrappedTextType = reportLinkedExcelObject.Settings.RowAutoFitterOptions.AutoFitWrappedTextType,
DefaultEditLanguage = reportLinkedExcelObject.Settings.RowAutoFitterOptions.DefaultEditLanguage,
FormatStrategy = reportLinkedExcelObject.Settings.RowAutoFitterOptions.FormatStrategy,
ForRendering = reportLinkedExcelObject.Settings.RowAutoFitterOptions.ForRendering,
IgnoreHidden = reportLinkedExcelObject.Settings.RowAutoFitterOptions.IgnoreHidden,
MaxRowHeight = reportLinkedExcelObject.Settings.RowAutoFitterOptions.MaxRowHeight,
OnlyAuto = reportLinkedExcelObject.Settings.RowAutoFitterOptions.OnlyAuto,
}
:
new AutoFitterOptions()
{
AutoFitMergedCellsType = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.AutoFitMergedCellsType,
AutoFitWrappedTextType = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.AutoFitWrappedTextType,
DefaultEditLanguage = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.DefaultEditLanguage,
FormatStrategy = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.FormatStrategy,
ForRendering = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.ForRendering,
IgnoreHidden = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.IgnoreHidden,
MaxRowHeight = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.MaxRowHeight,
OnlyAuto = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.OnlyAuto,
};
}
@PrathapSV
The MaxRowHeight property in the AutoFitterOptions class is specifically designed to limit the maximum height of rows when using the AutoFitRows() method. This property is particularly useful when you want to ensure that rows do not exceed a certain height, which can be important for maintaining the layout of your spreadsheet.
However, when it comes to the AutoFitColumns() method, the MaxRowHeight property does not have a direct impact. The AutoFitColumns() method is focused on adjusting the width of columns based on the content within the cells, and it does not consider row height. Therefore, setting MaxRowHeight will not affect how columns are auto-fitted.
In your implementation, you can safely use the MaxRowHeight property when calling AutoFitRows(), but you should not expect it to influence the behavior of AutoFitColumns(). Here’s a brief overview of how you might structure your code:
if (reportLinkedExcelObject.Settings.AutoFitRows)
sourceWorksheet.AutoFitRows(GetAutoFitterOptions(reportLinkedExcelObject, true));
if (reportLinkedExcelObject.Settings.AutoFitColumns)
sourceWorksheet.AutoFitColumns(GetAutoFitterOptions(reportLinkedExcelObject, false));
In the GetAutoFitterOptions method, you are correctly distinguishing between row and column options. Just ensure that when you are setting options for columns, you do not include properties that are irrelevant to column auto-fitting, such as MaxRowHeight.
For further reference, you might find examples of using AutoFitterOptions in the Aspose documentation helpful, particularly those that demonstrate how to handle merged cells and text wrapping, as these can also affect how auto-fitting behaves in your application.
If you have any more specific questions or need further clarification, feel free to ask!
@PrathapSV
MaxRowHeight: When you call AutoFitRows() method to automatically adjust the row height, it will automatically increase the row height based on the data in the cell by default. If you set the MaxRowHeight property, the adjusted row height will not exceed the set maximum row height.
OnlyAuto: The setting of row height is divided into custom row height and automatic row height. When the OnlyAuto parameter is true, when calling the AutoFitRows() method, only rows with automatic row height set will be adjusted, while rows with custom row height set will maintain their original row height. When the OnlyAuto parameter is set to false, all rows will automatically adjust their row height.
Please refer to the following document for the meanings of the various attributes of AutoFitterOptions.
You didn’t answer my original question,
It makes sense with AutoFitRows(), but when we call ‘AutoFitColumns()’, does the ‘MaxRowHeight’ and ‘OnlyAuto’ has impact/affect the functionality in any way?
to be more precise: refer the below code. how these props ‘MaxRowHeight’ and ‘OnlyAuto’ affect - AutoFitColumns here?
worksheet.AutoFitColumns(new AutoFitterOptions()
{
AutoFitMergedCellsType = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.AutoFitMergedCellsType,
AutoFitWrappedTextType = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.AutoFitWrappedTextType,
DefaultEditLanguage = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.DefaultEditLanguage,
FormatStrategy = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.FormatStrategy,
ForRendering = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.ForRendering,
IgnoreHidden = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.IgnoreHidden,
MaxRowHeight = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.MaxRowHeight,
OnlyAuto = reportLinkedExcelObject.Settings.ColumnAutoFitterOptions.OnlyAuto,
});
@PrathapSV
Thank you for your feedback. The MaxRowHeight and OnlyAuto properties only affect row height. It has no impact on column width. Similarly, calling the AutoFitColumns method will only adjust the column width and will not affect the row height.
Great,
We are planning to have our own implementation for a property - ‘MaxColWidth’, and below is our code, Is this the right way to achieve the max column width based on user input?
Also, could you give us the code for ‘OnlyAuto’ with respect to ‘MaxColWidth’ , meaning how can we limit the ‘MaxColWidth’ setting only for ‘auto column widths’ but not the ‘custom column width’?
// Auto-fit columns
worksheet.AutoFitColumns(autoFitterOptions);
// Define a maximum column width (e.g., 50 points)
double maxColWidth = 50.0;
// Iterate through columns and adjust widths
for (int col = 0; col < worksheet.Cells.MaxColumn; col++)
{
double currentWidth = worksheet.Cells.Columns[col].Width;
if (currentWidth > maxColWidth)
{
worksheet.Cells.Columns[col].Width = maxColWidth;
}
}
@PrathapSV
In Excel, there are automatic row height and custom row height for rows. But on the column, there is only custom column width, not automatic column width.
So there is no mention of OnlyAuto in terms of column width. You can refer to the following example code to set the maximum column width.
// Auto-fit columns
worksheet.AutoFitColumns(autoFitterOptions);
// Define a maximum column width (e.g., 50 points)
double maxColWidth = 50.0;
ColumnCollection columns = worksheet.Cells.Columns;
IEnumerator iter = columns.GetEnumerator();
while (iter.MoveNext())
{
Column column = (Column)iter.Current;
double colWidth = column.Width;
if (colWidth > maxColWidth)
{
column.Width = maxColWidth;
}
}
Thank you for the code.
Is there a way to apply the ‘AutoFitColumns’ or ‘AutoFitRows’ for a selected range instead of the entire column or row?
We see that both of the auto fit functions allows to send parameters for the range via index of the row/column, but we are specifically trying to handle when user sets - ‘Print_Area’ or other ways of setting the range like - ‘a1:j12’ and so on … etc.
@PrathapSV
After obtaining the range, you can use CellsHelper.CellNameToIndex method to obtain the start row, end row, start column, and end column of the range.
Then call Worksheet.AutoFitRow(int startRow, int endRow, int startColumn, int endColumn) method to adjust the row height of the range, and then call Worksheet.AutoFitColumns(int firstRow, int firstColumn, int lastRow, int lastColumn) method to adjust the column width of the range.
That’s great,
Could you please add me some sample code here to demo that. I don’t see in the documentation to get all the 4 indexes, gives only row and column indexes.
public static void CellNameToIndex(string cellName, out int row, out int column)
@PrathapSV
Please refer to the following documents.
What about ‘CreateRange’ function, is this okay to use it for getting the range like we are doing in below code?
// Access the print area
string printArea = sourceWorksheet.PageSetup.PrintArea;
// Parse the print area (e.g., "A1:J12")
Range range = sourceWorksheet.Cells.CreateRange(printArea);
// Auto-fit rows and columns within the print area
sourceWorksheet.AutoFitRows(range.FirstRow, range.FirstRow + range.RowCount - 1);
sourceWorksheet.AutoFitColumns(range.FirstRow, range.FirstColumn, range.FirstRow + range.RowCount - 1, range.FirstColumn + range.ColumnCount - 1);
// Define a custom range
Range customRange = sourceWorksheet.Cells.CreateRange("A1:J12");
// Auto-fit rows and columns within the custom range
sourceWorksheet.AutoFitRows(customRange.FirstRow, customRange.FirstRow + customRange.RowCount - 1);
sourceWorksheet.AutoFitColumns(customRange.FirstRow, customRange.FirstColumn, customRange.FirstRow + customRange.RowCount - 1, customRange.FirstColumn + customRange.ColumnCount - 1);
@PrathapSV
Thank you for your feedback. Certainly. You can obtain the row and column index through any means and pass them into the methods of automatically adjusting rows and columns.