Autofit Row Heights and Column Widths in React with JavaScript

Once the data is in, a table usually needs one last pass before it is usable: a product name squeezed into a sliver of a column, a paragraph of remarks running along a single line until the next non-empty cell cuts it off. Dragging column borders and row dividers by hand is slow, and once there are enough columns it is easy to miss a few. Spire.XLS for JavaScript performs this work directly in the browser through WebAssembly, managing input and output files with a virtual file system (VFS) and requiring no backend service.

This article covers two key features:

• Autofit the Height of a Single Row and the Width of a Single Column
• Autofit the Heights of Multiple Rows and the Widths of Multiple Columns

For installation and project setup, see Integrating Spire.XLS for JavaScript in a React Project. The examples below assume Spire.XLS is already installed and the WebAssembly module has been initialized.

Autofit the Height of a Single Row and the Width of a Single Column

AutoFitRow works out the height of the given row from its content, and AutoFitColumn works out the width of the given column the same way. Each one affects only the row or the column it is pointed at and leaves the rest of the table untouched, which is what you want when a single overflow is the only thing in the way.

Note that row height autofit only means anything for content that needs to wrap: with wrapping turned off the text always sits on one line and the height simply follows the font size, so there is no taller value to calculate. The steps are:

1. Load the workbook and get the first worksheet.
2. Autofit the height of that row with AutoFitRow.
3. Autofit the width of that column with AutoFitColumn.
4. Save the workbook.

Here is a complete code example that autofits the height of a single row and the width of a single column in React:

function App() {
  const autoFitSingleRowColumn = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into the VFS
    const inputFileName = 'AutoFitRowsAndColumns.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Autofit the height of row 2
    sheet.AutoFitRow(2);

    // Autofit the width of column 4
    sheet.AutoFitColumn(4);

    // Save the workbook
    const outputFileName = "AutoFitSingleRowColumn.xlsx";
    workbook.SaveToFile(outputFileName);

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Autofit Row Height and Column Width</h1>
      <button onClick={autoFitSingleRowColumn}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of autofitting a single row height and a single column width:

Autofit the Heights of Multiple Rows and the Widths of Multiple Columns

When the whole table needs tidying, calling the methods row by row is not realistic. Calling AutoFitRows or AutoFitColumns on a range recalculates every row and every column the range covers from its own content, so a single call lines up the whole block — the kind of pass you want before exporting a report. The steps are:

1. Load the workbook and get the first worksheet.
2. Autofit the heights of the rows with AutoFitRows.
3. Autofit the widths of the columns with AutoFitColumns.
4. Save the workbook.

Here is a complete code example that autofits the heights of multiple rows and the widths of multiple columns in React:

function App() {
  const autoFitMultipleRowsColumns = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

    // Load the Excel file into the VFS
    const inputFileName = 'AutoFitRowsAndColumns.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);

    // Load the workbook
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile(inputFileName);

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Get the used range of the worksheet
    const range = sheet.AllocatedRange;

    // Autofit the height of every row in the range
    range.AutoFitRows();

    // Autofit the width of every column in the range
    range.AutoFitColumns();

    // Save the workbook
    const outputFileName = "AutoFitMultipleRowsColumns.xlsx";
    workbook.SaveToFile(outputFileName);

    // Dispose of the workbook object to free resources
    workbook.Dispose();

    // Read the result file from the VFS and trigger the download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Autofit Row Height and Column Width</h1>
      <button onClick={autoFitMultipleRowsColumns}>Start</button>
    </div>
  );
}

export default App;

After running, the effect of autofitting multiple row heights and multiple column widths:

FAQ

I called AutoFitRow() and the row height did not change at all?

Cause: Row height autofit only applies to content that needs to wrap. With wrapping turned off the text stays on a single line and the height follows the font size, so autofit arrives at the same value as the existing height and appears to have done nothing.

Solution: Set WrapText to true first, then autofit the row height:

// With wrapping off, autofitting the row height changes nothing
sheet.Range.get("D2").Style.WrapText = false;
sheet.AutoFitRow(2);

// With wrapping on, the height is recalculated from the wrapped line count
sheet.Range.get("D2").Style.WrapText = true;
sheet.AutoFitRow(2);

AutoFitColumns() has no effect on merged cells?

Cause: Column width autofit measures the content of individual cells. In a merged range only the top-left cell actually holds text and every other position in the range is empty, so the width it works out is only enough for that top-left content.

Solution: Set the width of a merged range by hand with ColumnWidth:

// A7:D7 is a merged range, so autofit cannot work out its combined width
sheet.Range.get("A7:D7").Merge();
sheet.Range.get("A7:D7").AutoFitColumns();

// Set the column width by hand so the merged text fits
sheet.Range.get("A7").ColumnWidth = 40;

Get a Free License

Spire.XLS for JavaScript offers a 30-day full-featured free trial license with no functional limitations. Apply here to evaluate before purchasing.