Sheet 可见性

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.

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

Excel 可以隐藏下部选项卡栏中的工作表选项卡。工作表数据存储在文件中，但选项卡只有在取消隐藏后才会显示。

¥Excel can hide sheet tabs from the lower tab bar. The sheet data is stored in the file but the tabs are not displayed until they are unhidden.

标准 "hidden" 工作表列在 "取消隐藏" 菜单中。

¥Standard "hidden" sheets are listed in the "Unhide" menu.

Excel "非常隐蔽" 工作表无法在菜单中显示。它们仅在 Visual Basic 编辑器中可见！

¥Excel "very hidden" sheets cannot be revealed in the menu. They are only visible in the Visual Basic Editor!

贮存

¥Storage

可见性设置存储在相应 wb.Workbook.Sheets 数组中的元数据 的 Hidden 属性中

¥The visibility setting is stored in the Hidden property of the corresponding metadata in the wb.Workbook.Sheets array

公认的值如下：

¥The recognized values are listed below:

值	定义	VB 编辑器 "可见的" 属性
0	可见的	-1 - xlSheetVisible
1	隐藏	0 - xlSheetHidden
2	非常隐蔽	2 - xlSheetVeryHidden

如果相应的工作表条目不存在或者未设置 Hidden 属性，则工作表可见。

¥If the respective Sheet entry does not exist or if the Hidden property is not set, the worksheet is visible.

解析

¥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;
}

通常，"hidden" 和 "非常隐蔽" 之间的区别与应用无关。选择这些值是为了使逻辑否定按预期工作：

¥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" 非常隐蔽

  ¥"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>);
}

实时代码块通过以下方式测试可见性：

¥The live codeblock tests for visibility with:

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

对于现代 JS，这可以写成

¥With modern JS, this can be written as

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