Autofit Rows and Columns
Auto Fitting
Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file.
A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of properties and methods for managing a worksheet. This article looks at using the Worksheet class to autofit rows or columns.
AutoFit Row - Simple
The most straight-forward approach to auto-sizing the width and height of a row is to call the Worksheet class' autoFitRow method. The autoFitRow method takes a row index (of the row to be resized) as a parameter.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(AutoFitRowsandColumns.class) + "rows_cloumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Auto-fitting the 2nd row of the worksheet | |
worksheet.autoFitRow(1); | |
// Auto-fitting the 1st column of the worksheet | |
worksheet.autoFitColumn(0); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "AutoFitRowsandColumns_out.xls"); | |
// Print message | |
System.out.println("Row and Column auto fit successfully."); |
AutoFit Row in a Range of Cells
A row is composed of many columns. Aspose.Cells allows developers to auto-fit a row based on the content in a range of cells within the row by calling an overloaded version of the autoFitRow method. It takes the following parameters:
- Row index, the index of the row about to be auto-fitted.
- First column index, the index of the row’s first column.
- Last column index, the index of the row’s last column.
The autoFitRow method checks the contents of all the columns in the row and then auto-fits the row.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(AutoFitRowsinaRangeofCells.class) + "rows_cloumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Auto-fitting the row of the worksheet | |
worksheet.autoFitRow(1, 0, 5); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "AutoFitRowsinaRangeofCells_out.xls"); | |
// Print message | |
System.out.println("Row auto fit successfully."); |
AutoFit Column - Simple
The easiest way to auto-size the width and height of a column is to call the Worksheet class' autoFitColumn method. The autoFitColumn method takes the column index (of the column about to be resized) as a parameter.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(AutoFitRowsandColumns.class) + "rows_cloumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Auto-fitting the 2nd row of the worksheet | |
worksheet.autoFitRow(1); | |
// Auto-fitting the 1st column of the worksheet | |
worksheet.autoFitColumn(0); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "AutoFitRowsandColumns_out.xls"); | |
// Print message | |
System.out.println("Row and Column auto fit successfully."); |
AutoFit Column in a Range of Cells
A column is composed of many rows. It is possible to auto-fit a column based on the content in a range of cells in the column by calling an overloaded version of autoFitColumn method that takes the following parameters:
- Column index, represents the index of the column whose contents need to auto-fit
- First row index, represents the index of the first row of the column
- Last row index, represents the index of the last row of the column
The autoFitColumn method checks the contents of all rows in the column and then auto-fits the column.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(AutoFitColumnsinaRangeofCells.class) + "rows_cloumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Auto-fitting the Column of the worksheet | |
worksheet.autoFitColumn(4, 4, 6); | |
// Saving the modified Excel file in default (that is Excel 2003) format | |
workbook.save(dataDir + "AutoFitColumnsinaRangeofCells_out.xls"); | |
// Print message | |
System.out.println("Columns auto fit successfully."); |
AutoFit Rows for Merged Cells
With Aspose.Cells it is possible to autofit rows even for cells that have been merged using the AutoFitterOptions API. AutoFitterOptions class provides AutoFitMergedCellsType property that can be used to autofit rows for merged cells. AutoFitMergedCellsType accepts AutoFitMergedCellsType enumerable which has the following members.
- NONE: Ignore merged cells.
- FIRST_LINE: Only expands the height of the first row.
- LAST_LINE: Only expands the height of the last row.
- EACH_LINE: Only expands the height of each row.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getSharedDataDir(AutofitRowsforMergedCells.class) + "RowsAndColumns/"; | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Accessing the first worksheet in the Excel file | |
Worksheet worksheet = workbook.getWorksheets().get(0); | |
// Create a range A1:B1 | |
Range range = worksheet.getCells().createRange(0, 0, 1, 2); | |
// Merge the cells | |
range.merge(); | |
// Insert value to the merged cell A1 | |
worksheet.getCells().get(0, 0).setValue("A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"); | |
// Create a style object | |
Style style = worksheet.getCells().get(0, 0).getStyle(); | |
// Set wrapping text on | |
style.setTextWrapped(true); | |
// Apply the style to the cell | |
worksheet.getCells().get(0, 0).setStyle(style); | |
// Create an object for AutoFitterOptions | |
AutoFitterOptions options = new AutoFitterOptions(); | |
// Set auto-fit for merged cells | |
options.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE); | |
// Autofit rows in the sheet(including the merged cells) | |
worksheet.autoFitRows(options); | |
// Save the Excel file | |
workbook.save(dataDir + "AutofitRowsforMergedCells_out.xlsx"); |
You may also use the overloaded versions of autoFitRows & autoFitColumns methods accepting a range of rows/columns and an instance of AutoFitterOptions to auto-fit the selected rows/columns with the desired AutoFitterOptions accordingly.
The signatures of aforesaid methods are as follow:
- autoFitRows(int startRow, int endRow, AutoFitterOptions options)
- autoFitColumns(int firstColumn, int lastColumn, AutoFitterOptions options)
Important to Know