AutoFit Rows and Columns with JavaScript via C++
Contents
[
Hide
]
Microsoft Excel lets users auto-size the width and height of cells according to their content. This feature is also available through Aspose.Cells for JavaScript via C++, so developers can auto-size the dimensions of a cell at runtime.
Auto Fitting
Aspose.Cells provides a Workbook class that represents a Microsoft Excel file. The Workbook class contains a Workbook.worksheets collection that allows access to each worksheet in an 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 straightforward 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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - AutoFit Row</title>
</head>
<body>
<h1>AutoFit Row Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object by opening the Excel file through the buffer
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Auto-fitting the 3rd row of the worksheet (row index 1 is the 2nd row; original code used 1)
worksheet.autoFitRow(1);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Row auto-fitted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to 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.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example - AutoFit Row</title>
</head>
<body>
<h1>Auto-Fit Row Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object by opening the Excel file through the file buffer
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Auto-fitting the 3rd row of the worksheet (row index 1, startColumn 0, endColumn 5)
worksheet.autoFitRow(1, 0, 5);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Row auto-fitted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to 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, the index of the column about to be auto-fitted.
- First row index, the index of the column’s first row.
- Last row index, the index of the column’s last row.
The autoFitColumn method checks the contents of all rows in the column and then auto-fits the column.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells AutoFit Column Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
// Auto-fitting the Column of the worksheet (column index 4)
worksheet.autoFitColumn(4);
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Column auto-fitted successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to 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. The AutoFitterOptions class provides AutoFitterOptions.autoFitMergedCellsType() property that can be used to autofit rows for merged cells. AutoFitterOptions.autoFitMergedCellsType() accepts AutoFitMergedCellsType enumerable which has the following members.
- None: Ignore merged cells.
- FirstLine: Only expands the height of the first row.
- LastLine: Only expands the height of the last row.
- EachLine: Only expands the height of each row.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Autofit Rows for Merged Cells Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, AutoFitterOptions, AutoFitMergedCellsType } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
// Create or load workbook
let wb;
if (fileInput.files && fileInput.files.length) {
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
wb = new Workbook(new Uint8Array(arrayBuffer));
} else {
wb = new Workbook();
}
// Get the first (default) worksheet
const worksheet = wb.worksheets.get(0);
// Create a range A1:B1
const range = worksheet.cells.createRange(0, 0, 1, 2);
// Merge the cells
range.merge();
// Insert value to the merged cell A1
const cell = worksheet.cells.get(0, 0);
cell.value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
// Create a style object
const style = cell.style;
// Set wrapping text on
style.isTextWrapped = true;
// Apply the style to the cell
cell.style = style;
// Create an object for AutoFitterOptions
const options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.autoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
// Autofit rows in the sheet (including the merged cells)
worksheet.autoFitRows(options);
// Save the Excel file
const outputData = wb.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'AutofitRowsforMergedCells.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
You may also try to 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 your desired AutoFitterOptions accordingly.
The signatures of the aforesaid methods are as follows:
- autoFitRows(int startRow, int endRow, AutoFitterOptions options)
- autoFitColumns(int firstColumn, int lastColumn, AutoFitterOptions options)
Important to Know
If a cell is merged then the autoFit methods will not be applied, which is the same behavior as in Microsoft Excel. You can get around this by using the autofilter API. Moreover, if the text in a cell is wrapped, the autoFitColumn method will not be applied either. Another thing you need to know is that the autoFit methods are time-consuming. So, you should call these methods as seldom as possible to ensure the efficiency of your application.