Tutorials
JavaScript
Spire.XLS for JavaScript
React
Cells

Hide or Unhide Excel Rows and Columns with JavaScript in React

Hiding and unhiding rows and columns is a common feature in daily office work. It helps protect sensitive information, simplify data views, or temporarily conceal unnecessary data. Spire.XLS for JavaScript runs entirely in the browser via WebAssembly, using a virtual file system (VFS) to manage input and output files — no backend server required. It provides a simple API for controlling the visibility of rows and columns.

This article covers four core features:

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

Hide Specific Rows and Columns in Excel

Hiding specific rows and columns can keep your worksheet cleaner and more readable without compromising data integrity. Spire.XLS for JavaScript supports hiding a specific row or column using the HideRow() and HideColumn() methods. The steps are as follows:

  1. Create a Workbook object and load an existing Excel file containing data.
  2. Use worksheet.HideRow() to hide a specific row.
  3. Use worksheet.HideColumn() to hide a specific column.
  4. Save the workbook to an Excel file using SaveToFile().

Below is a complete code example demonstrating how to hide specific rows and columns in Excel in React:

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

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

    // Load the sample file into the virtual file system (VFS)
    let excelFileName = 'Sample.xlsx';
    await window.spire.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a workbook object and load the existing file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: excelFileName });
    let sheet = workbook.Worksheets.get(0);

    // Hide a specific row (row 4)
    sheet.HideRow(4);

    // Hide a specific column (column 2, i.e., column B)
    sheet.HideColumn(2);

    // Save the workbook
    const outputFileName = 'HideSpecificRowsColumns.xlsx';
    workbook.SaveToFile(outputFileName);
    workbook.Dispose();

    // Read the file from VFS and trigger 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>Hide Specific Rows and Columns</h1>
      <button onClick={hideSpecificRowsAndColumns}>
        Generate
      </button>
    </div>
  );
}

export default App;

Specific rows and columns hidden with Spire.XLS for JavaScript

Specific rows and columns hidden with Spire.XLS for JavaScript

Unhide Specific Rows and Columns in Excel

When you need to view or edit specific hidden data, you can unhide a particular row or column individually. Spire.XLS for JavaScript supports unhiding a specific row or column using the ShowRow() and ShowColumn() methods. The steps are as follows:

  1. Create a Workbook object and load an existing Excel file containing hidden rows/columns.
  2. Use sheet.ShowRow() to unhide a specific row.
  3. Use sheet.ShowColumn() to unhide a specific column.
  4. Save the workbook to an Excel file using SaveToFile().

Below is a complete code example demonstrating how to unhide specific rows and columns in Excel in React:

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

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

    // Load the sample file into the virtual file system (VFS)
    let excelFileName = 'HideSpecificRowsColumns.xlsx';
    await window.spire.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a workbook object and load the existing file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: excelFileName });

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

    // Unhide a specific row (row 4)
    sheet.ShowRow(4);

    // Unhide a specific column (column 2, i.e., column B)
    sheet.ShowColumn(2);

    // Save the workbook
    const outputFileName = 'UnhideSpecificRowsColumns.xlsx';
    workbook.SaveToFile(outputFileName);
    workbook.Dispose();

    // Read the file from VFS and trigger 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>Unhide Specific Rows and Columns</h1>
      <button onClick={unhideSpecificRowsAndColumns}>
        Generate
      </button>
    </div>
  );
}

export default App;

Specific rows and columns unhidden with Spire.XLS for JavaScript

Specific rows and columns unhidden with Spire.XLS for JavaScript

Hide Multiple Rows and Columns at Once in Excel

When there are multiple rows or columns that you don't need to display, hiding them one by one is inefficient. Spire.XLS for JavaScript supports hiding multiple rows and columns at once through loops, greatly improving operational efficiency. The steps are as follows:

  1. Create a Workbook object and load an existing Excel file containing data.
  2. Use a loop to call worksheet.HideRow() to hide multiple rows at once.
  3. Use a loop to call worksheet.HideColumn() to hide multiple columns at once.
  4. Save the workbook to an Excel file using SaveToFile().

Below is a complete code example demonstrating how to hide multiple rows and columns at once in Excel in React:

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

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

    // Load the sample file into the virtual file system (VFS)
    let excelFileName = 'Sample.xlsx';
    await window.spire.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a workbook object and load the existing file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: excelFileName });
    let sheet = workbook.Worksheets.get(0);

    // Hide multiple rows at once (rows 6 through 10)
    for (let i = 6; i <= 10; i++) {
      sheet.HideRow(i);
    }

    // Hide multiple columns at once (columns 4 through 5, i.e., columns D to E)
    for (let j = 4; j <= 5; j++) {
      sheet.HideColumn(j);
    }

    // Save the workbook
    const outputFileName = 'HideMultipleRowsColumns.xlsx';
    workbook.SaveToFile(outputFileName);
    workbook.Dispose();

    // Read the file from VFS and trigger 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>Hide Multiple Rows and Columns</h1>
      <button onClick={hideMultipleRowsAndColumns}>
        Generate
      </button>
    </div>
  );
}

export default App;

Multiple rows and columns hidden at once with Spire.XLS for JavaScript

Multiple rows and columns hidden at once with Spire.XLS for JavaScript

Unhide All Hidden Rows and Columns in Excel

To unhide all hidden rows and columns, iterate through the rows and columns in the worksheet, use GetRowIsHide() and GetColumnIsHide() to find hidden ones, then call ShowRow() and ShowColumn() to unhide them. The steps are as follows:

  1. Create a Workbook object and load the Excel file.
  2. Get the worksheet.
  3. Iterate through rows, use GetRowIsHide() to find hidden rows, use ShowRow() to unhide.
  4. Iterate through columns, use GetColumnIsHide() to find hidden columns, use ShowColumn() to unhide.
  5. Save the result file using SaveToFile().

Below is a complete code example demonstrating how to unhide all hidden rows and columns in Excel in React:

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

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

    // Load the sample file into the virtual file system (VFS)
    let excelFileName = 'HideMultipleRowsColumns.xlsx';
    await window.spire.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a workbook object and load the existing file
    const workbook = new xlsModule.Workbook();
    workbook.LoadFromFile({ fileName: excelFileName });

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

    for (let i = 1; i <= sheet.Rows.length; i++) {
      if (sheet.GetRowIsHide(i)) {
        // Unhide row
        sheet.ShowRow(i);
      }
    }

    for (let j = 1; j <= sheet.Columns.length; j++) {
      if (sheet.GetColumnIsHide(j)) {
        // Unhide column
        sheet.ShowColumn(j);
      }
    }
    
    // Save the workbook
    const outputFileName = 'UnhideAllRowsColumns.xlsx';
    workbook.SaveToFile(outputFileName);
    workbook.Dispose();

    // Read the file from VFS and trigger 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>Unhide All Rows and Columns</h1>
      <button onClick={unhideAllRowsAndColumns}>
        Generate
      </button>
    </div>
  );
}

export default App;

All hidden rows and columns unhidden with Spire.XLS for JavaScript

All hidden rows and columns unhidden with Spire.XLS for JavaScript

FAQ

How to check whether a row or column is hidden?

Solution: Use the GetRowIsHide() and GetColumnIsHide() methods to check:

// Check if row 3 is hidden
let rowIsHidden = sheet.GetRowIsHide(3);

// Check if column 2 (column B) is hidden
let columnIsHidden = sheet.GetColumnIsHide(2);

Are row heights or column widths preserved after hiding?

Solution: When hiding rows or columns, the original row height and column width values are preserved. After unhiding with ShowRow() or ShowColumn(), the original dimensions are automatically restored without any additional configuration.

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.

See Also

Products
Products
Purchase
Partnership
Support
Corporation
Copyright ©2024 E-ICEBLUE. All Rights Reserved.