Sheet Visibility工作表可见性

File Format Support (click to show)

By default, all sheets in a workbook are "Visible". The standard "Hidden" state is controlled through the context menu in the sheet tab bar. The "Very Hidden" state is controlled through the "Visibility" property in the VBA editor.默认情况下，工作簿中的所有工作表都是“可见”的。标准的“隐藏”状态通过图纸选项卡栏中的关联菜单进行控制。“非常隐藏”状态通过VBA编辑器中的“可见性”属性进行控制。

Formats	Hidden	Very Hidden
XLSX/XLSM	✔	✔
XLSB	✔	✔
XLML	✔	✔
BIFF8 XLS	✔	✔
BIFF5 XLS	✔	✔

Excel enables hiding sheets in the lower tab bar. The sheet data is stored in the file but the UI does not readily make it available.Excel允许在较低的选项卡栏中隐藏工作表。图纸数据存储在文件中，但UI不容易使其可用。

Standard "hidden" sheets are revealed in the "Unhide" menu.标准的“隐藏”工作表显示在“取消隐藏”菜单中。

Excel also has "very hidden" sheets which cannot be revealed in the menu. They are only accessible in the VB Editor!Excel也有“非常隐藏”的工作表，无法在菜单中显示。它们只能在VB编辑器中访问！

Storage存储

The visibility setting is stored in the Hidden property of the corresponding metadata in the wb.Workbook.Sheets array可见性设置存储在wb.Workbook.Sheets数组中相应元数据的Hidden属性中

The recognized values are listed below:公认的价值如下所示：

Value	Definition释义	VB Editor "Visible" PropertyVB编辑器“可见”属性
0	Visible	-1 - xlSheetVisible
1	Hidden	0 - xlSheetHidden
2	Very Hidden	2 - xlSheetVeryHidden

If the respective Sheet entry does not exist or if the Hidden property is not set, the worksheet is visible.如果相应的工作表条目不存在，或者未设置Hidden特性，则工作表可见。

Parsing分析

Since worksheet visibility is stored in the workbook, both the workbook object and the sheet name must be known to determine visibility setting.由于工作表可见性存储在工作簿中，因此必须知道工作簿对象和工作表名称才能确定可见性设置。

function get_sheet_visibility(workbook, sheet_name) {
  // if the metadata does not exist for the sheet, the sheet is visible如果该工作表的元数据不存在，则该工作表可见
  if(!workbook.Workbook) return 0;
  if(!workbook.Workbook.Sheets) return 0;

  var idx = workbook.SheetNames.indexOf(sheet_name);
  if(idx == -1) throw new Error(`Sheet ${sheet_name} missing from workbook`);

  var meta = workbook.Workbook.Sheets[idx];
  return meta && meta.Hidden || 0;
}

Typically the distinction between "hidden" and "very hidden" is not relevant for applications. The values were chosen to make logical negation work as expected:通常，“隐藏”和“非常隐藏”之间的区别与应用程序无关。选择这些值是为了使逻辑否定按预期工作：

function is_sheet_visible(workbook, sheet_name) {
  return !get_sheet_visibility(workbook, sheet_name); // true if visible
}

Writing写入

When assigning, the entire workbook metadata structure should be tested and constructed if necessary:分配时，如有必要，应测试并构建整个工作簿元数据结构：

function set_sheet_visibility(workbook, sheet_name, Hidden) {
  var idx = workbook.SheetNames.indexOf(sheet_name);
  if(idx == -1) throw new Error(`Sheet ${sheet_name} missing from workbook`);

  // if the metadata does not exist for the sheet, create it如果图纸的元数据不存在，请创建它
  if(!workbook.Workbook) workbook.Workbook = {};
  if(!workbook.Workbook.Sheets) workbook.Workbook.Sheets = [];
  if(!workbook.Workbook.Sheets[idx]) workbook.Workbook.Sheets[idx] = {};

  // set visibility
  workbook.Workbook.Sheets[idx].Hidden = Hidden;
}

Demo演示

This test file此测试文件 has three sheets:有三张个工作表：

• "Visible" is visible是可见的
• "Hidden" is hidden是隐藏的
• "VeryHidden" is very hidden是进一步隐蔽的

The live demo fetches the test file and displays visibility information.实时演示获取测试文件并显示可见性信息。

Result

Loading...

Live Editor

function Visibility(props) { const [wb, setWB] = React.useState({SheetNames:[]}); const [sheets, setSheets] = React.useState([]); const vis = [ "Visible", "Hidden", "Very Hidden" ]; React.useEffect(async() => { const f = await fetch("/files/sheet_visibility.xlsx"); const ab = await f.arrayBuffer(); const wb = XLSX.read(ab); setWB(wb); /* State will be set to the `Sheets` property array */ setSheets(wb.Workbook.Sheets); }, []); return (<table> <thead><tr><th>Name</th><th>Value</th><th>Hidden</th></tr></thead> <tbody>{wb.SheetNames.map((n,i) => { const h = ((((wb||{}).Workbook||{}).Sheets||[])[i]||{}).Hidden||0; return ( <tr key={i}> <td>{n}</td> <td>{h} - {vis[h]}</td> <td>{!h ? "No" : "Yes"}</td> </tr> ); })}</tbody></table>); }

function Visibility(props) {
  const [wb, setWB] = React.useState({SheetNames:[]});
  const [sheets, setSheets] = React.useState([]);
  const vis = [ "Visible", "Hidden", "Very Hidden" ];

  React.useEffect(async() => {
    const f = await fetch("/files/sheet_visibility.xlsx");
    const ab = await f.arrayBuffer();
    const wb = XLSX.read(ab);
    setWB(wb);
    /* State will be set to the `Sheets` property array */
    setSheets(wb.Workbook.Sheets);
  }, []);

  return (<table>
    <thead><tr><th>Name</th><th>Value</th><th>Hidden</th></tr></thead>
    <tbody>{wb.SheetNames.map((n,i) => {
      const h = ((((wb||{}).Workbook||{}).Sheets||[])[i]||{}).Hidden||0;
      return ( <tr key={i}>
        <td>{n}</td>
        <td>{h} - {vis[h]}</td>
        <td>{!h ? "No" : "Yes"}</td>
      </tr> );
    })}</tbody></table>);
}

The live codeblock tests for visibility with:实时代码块测试可见性，使用：

const h = ((((wb||{}).Workbook||{}).Sheets||[])[i]||{}).Hidden||0;

With modern JS, this can be written as使用现代JS，这可以写成

const h = wb?.Workbook?.Sheets?.[i]?.Hidden||0;