How to Find Hidden Sheets in Excel Files using XLSX

In this tutorial, You are going to learn how to find hidden sheets in excel using XLSX Sheet.js. The approach that we are going to take is applicable to frontend browser-based applications (Angular, React, Vue, etc) and also server-side applications using NodeJS as well.

Install XLSX Package

Use the following command to install the XLSX package latest version in your application.

npm install xlsx --save

// for nodejs
npm install node-xlsx --save

Import the XLSX package into your file

You can import the XLSX package using the ES6 import method or NodeJS require module method.

import * as XLSX from 'xlsx';

// for nodejs module method
const xlsx = require('xlsx');

File Upload HTML Template

In your HTML file, Add Input with type file to upload excel files, here I am adding different versions of HTML tags for Angular, React, and NodeJS/JavaScript

/** React **/
<input type="file" style="display: inline-block;" onChange="onFileChange"
 placeholder="Upload file" accept=".xlsx">

/** Angular **/
<input type="file" style="display: inline-block;" (change)="onFileChange($event)"
 placeholder="Upload file" accept=".xlsx">

On the input file change event, we are triggering onFileChange function which reads the file using XLSX and then gets workbooks out of the excel file.

Find Hidden Sheets in Excel

For front-end applications (Angular, React, JavaScript), use the following function to read and find the hidden sheets in excel.

onFileChange(event: any) {    
    const target: DataTransfer = <DataTransfer>(event.target);
    if (target.files.length !== 1) {
      throw new Error('Cannot use multiple files');
    }
    const reader: FileReader = new FileReader();
    reader.readAsBinaryString(target.files[0]);
    reader.onload = (e: any) => {
      /* create workbook */
      const binarystr: string = e.target.result;
      const wb: any = XLSX.read(binarystr, { type: 'binary' });
      const hiddenSheets = wb.Workbook?.Sheets?.filter((sheet) => sheet.Hidden === 1);


      console.log(hiddenSheets, "Hidden Sheets");
    };
 }

GitHub Angular Repo for Finding Hidden Sheets in Excel: https://github.com/bearnithi/angular-xlsx

For NodeJS, you can use the node-xlsx package and follow the same approach as above instead of reading using FileReader, you will be just passing the file buffer or path to XLSX.read or XLSX.parse and then use the following code to find the hidden sheet in the excel file.

// XLSX 
const wb: any = XLSX.read(binarystr, { type: 'binary' });
      const hiddenSheets = wb.Workbook?.Sheets?.filter((sheet) => sheet.Hidden === 1);

// node-xlsx
 const wb: any = XLSX.parse(binarystr, { type: 'binary' });
      const hiddenSheets = wb.Workbook?.Sheets?.filter((sheet) => sheet.Hidden === 1);

Understanding Event Emitters in NodeJS

The XLSX.read method is used to read the excel files that output the workbook object. Each sheet object in the workbook object has a property called Hidden. If the value of Hidden is 1, then it’s a hidden sheet, if it’s 0 then it’s a visible one.

Using Array.filter method in the Sheets array, we can get the array of hidden sheets from the workbook. For brevity, I just logged the hiddenSheets array. You can also use Array.some method to get the boolean whether there’s a hidden sheet in excel or not.

Conclusion

How to Implement Angular User Idle Timeout Detection

Hope in this guide, you got a solution on how to find hidden sheets inside the excel file using XLSX in Angular, React, and NodeJS as well. Let me know in the comments if this worked for you. If you face any issues feel free to reach out to me.