3f:null 40:["$","title",null,{"children":"XLSX Accessibility Checker | James Trimble's ONS work | Observable"}] 46:T740,heading1CellsInfo = { let heading1XfId = null; let stylesDoc = parseXml(stylesXml); let cellStyles = stylesDoc.getElementsByTagName("cellStyle"); for (let i = 0; i < cellStyles.length; i++) { let cellStyle = cellStyles[i]; let name = cellStyle.getAttribute("name"); let xfId = cellStyle.getAttribute("xfId"); if (name === "Heading 1") { heading1XfId = xfId; } } if (heading1XfId == null) return null; let heading1Styles = {}; // a set of style numbers let cellXfs = stylesDoc .getElementsByTagName("cellXfs") .item(0) .getElementsByTagName("xf"); for (let i = 0; i < cellXfs.length; i++) { let xf = cellXfs[i]; if (xf.getAttribute("xfId") === heading1XfId) { heading1Styles["" + i] = 1; } } let result = []; for (let sheet of sheets) { let sheetFileName = "xl/" + sheet.filename; if (z.filenames.includes(sheetFileName)) { let sheetDoc = parseXml(await z.file(sheetFileName).text()); let hasA1Heading1 = false; let otherHeading1 = false; let cells = sheetDoc.getElementsByTagName("c"); for (let i = 0; i < cells.length; i++) { let cell = cells[i]; if ( cell.hasAttribute("r") && cell.hasAttribute("s") && cell.getAttribute("s") in heading1Styles ) { if (cell.getAttribute("r") === "A1") { hasA1Heading1 = true; } else { otherHeading1 = cell.getAttribute("r"); } } } let problems = ""; if (!hasA1Heading1) problems += "(Cell A1 does not have Heading 1 style)"; if (otherHeading1) problems += " (At least one cell other than A1 has Heading 1 style: cell " + otherHeading1 + ")"; if (problems) result.push({ sheet, problems }); } } return result; }47:T5c8,tables = { let result = []; for (let sheet of sheets) { let relsFiles = z.filenames.filter((f) => f.endsWith(sheet.filename.replace("worksheets/", "_rels/") + ".rels") ); if (relsFiles.length === 0) continue; let relsDoc = parseXml(await z.file(relsFiles[0]).text()); let relationships = relsDoc.getElementsByTagName("Relationship"); for (let rel of relationships) { if ( rel.getAttribute("Type") === "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" ) { let target = rel.getAttribute("Target"); target = target.replace("..", "xl"); let tableDoc = parseXml(await z.file(target).text()); let tableElem = tableDoc.getElementsByTagName("table").item(0); let tableName = tableElem.getAttribute("name"); let tableRef = tableElem.getAttribute("ref"); result.push({ sheetName: sheet.name, tableName, tableRef, range: XLSX.utils.decode_range(tableRef), hasEmptyCells: rangeHasEmptyCells( sheet.name, XLSX.utils.decode_range(tableRef) ), hasFilterButtons: tableHasFilterButtons(tableDoc), hasDefaultName: new RegExp("^Table_?[0-9]*$").test(tableName), hasHeaderRow: tableElem.hasAttribute("headerRowCount") && tableElem.getAttribute("headerRowCount") === "0" }); } } } return result; }48:T416,activeCellsNotA1 = { let result = []; for (let sheet of sheets) { let sheetFileName = "xl/" + sheet.filename; if (z.filenames.includes(sheetFileName)) { let sheetDoc = parseXml(await z.file(sheetFileName).text()); let selections = sheetDoc.getElementsByTagName("selection"); // If freeze panes are switched on, there may be multiple active cells. // If there are no active cells A1, we assume there is an issue. let activeCellA1 = false; let otherActiveCells = []; for (let i = 0; i < selections.length; i++) { let selection = selections[i]; if (selection.hasAttribute("activeCell")) { let activeCell = selection.getAttribute("activeCell"); if (activeCell === "A1") { activeCellA1 = true; } else { otherActiveCells.push(activeCell); } } } if (!activeCellA1 && otherActiveCells.length > 0) { result.push({ sheet, activeCell: otherActiveCells[0] }); } } } return result; }49:[] 44:{"id":"3c45b6f263fa88dc","slug":"xlsx-accessibility-checker","title":"XLSX Accessibility Checker","thumbnail":"6f464ee7e40762f0e048d1c56b4f8e1f624e8b94bf70c27c29eb92f0652055e4","access_level":"public","create_time":"$D2021-12-02T10:40:46.330Z","update_time":"$D2023-07-10T14:27:58.301Z","view_time":null,"first_public_time":"$D2021-12-02T12:18:42.851Z","has_unpublished_edits":null,"fork":false,"stars":3,"starred":false,"owner":{"type":"team","id":"30965c406219b62a","login":"jtrim-ons","name":"James Trimble's ONS work","avatar_url":"https://avatars-next.observableusercontent.com/5c661298568698f51736e0dabf77961904e0e40d91facf41b6d85eb71d6ea3df","tier":"starter_2024","bio":"Dataviz at Office for National Statistics (UK)","following":false},"version":826,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"72cc4aad069a0c71","login":"jtrim-ons","name":"James Trimble's ONS work","avatar_url":"https://avatars-next.observableusercontent.com/5c661298568698f51736e0dabf77961904e0e40d91facf41b6d85eb71d6ea3df","tier":null,"bio":"Dataviz at Office for National Statistics (UK)","following":false}],"members":[{"type":"individual","id":"72cc4aad069a0c71","login":"jtrim-ons","name":"James Trimble's ONS work","avatar_url":"https://avatars-next.observableusercontent.com/5c661298568698f51736e0dabf77961904e0e40d91facf41b6d85eb71d6ea3df","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"titling":"auto","title":"XLSX Accessibility Checker","cells":[{"id":0,"value":"# XLSX Accessibility Checker\n\n**News: A version of this checker is now available on GitHub Pages at https://onsvisual.github.io/xlsx-accessibility-checker/**\n\nThis is an attempt to check some of the rules in [this checklist for accessible spreadsheets](https://gss.civilservice.gov.uk/policy-store/making-spreadsheets-accessible-a-brief-checklist-of-the-basics/). It's not complete and may give some false positives and false negatives.\n\nTo use the checker, click the \"Browse\" button below to upload an XLSX file from your computer.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":30,"value":"**Choose an XLSX file:**","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"viewof file = Inputs.file()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":586,"value":"## Tables","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":73,"value":"### Mark up tables","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":79,"value":"{\n if (sheetsWithoutTables.length < workbook.SheetNames.length) {\n let result =\n '

✓ Looks goodThe following tables were found:

${s}: ${tables.map(\n (d) => \" \" + d.tableName\n )}

\";\n return html`${result}`;\n } else {\n return html``;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":319,"value":"{\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Changes are neededNo tables were found in the workbook. Some of the following checks rely on tables being marked up. See the guidance for instructions.

`;\n } else if (sheetsWithoutTables.length) {\n return html`

Changes may be neededNo tables were found in the following sheets: ${sheetsWithoutTables.join(\n \", \"\n )}.

`;\n } else {\n return html``;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":514,"value":"### Give tables meaningful names","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":518,"value":"{\n let tablesWithDefaultNames = tables.filter((t) => t.hasDefaultName);\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (tablesWithDefaultNames.length === 0) {\n return html`

✓ Looks goodNo tables with default names were found

`;\n } else {\n let result =\n '

Changes are neededThe following tables should be given descriptive names:

${t.tableName}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":652,"value":"### Each table should have a header row","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":656,"value":"{\n let tablesWithoutHeader = tables.filter((t) => t.hasHeaderRow);\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (tablesWithoutHeader.length === 0) {\n return html`

✓ Looks goodTables have header rows

`;\n } else {\n let result =\n '

Changes are neededThe following tables do not have header rows:

${t.tableName}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":141,"value":"### Remove blank rows and columns","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":151,"value":"{\n let tablesWithEmptyCells = tables.filter((t) => t.hasEmptyCells);\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (tablesWithEmptyCells.length === 0) {\n return html`

✓ Looks goodNo tables with empty cells were found

`;\n } else {\n let result =\n '

Changes may be neededTables should have no blank rows or columns, and there should be no blank cells in the header row. However, cells with no data may be left empty if certain conditions are met.

The following tables have at least one empty cell:

${t.tableName} in sheet ${t.sheetName}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":379,"value":"### The first table on each sheet should use column A","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":390,"value":"{\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (sheetsWithoutColumnATable.length === 0) {\n return html`

✓ Looks goodNo problems detected.

`;\n } else {\n let result =\n '

Changes are neededSheets without a table in column A:

${s}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":452,"value":"### Tables should have filter buttons switched off","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":462,"value":"{\n let tablesWithFilterButtons = tables.filter((t) => t.hasFilterButtons);\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (tablesWithFilterButtons.length === 0) {\n return html`

✓ Looks goodNo tables with filter buttons were found

`;\n } else {\n let result =\n '

Changes are neededThe following tables appear to have filter buttons switched on:

${t.tableName} in sheet ${t.sheetName}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":788,"value":"### Avoid putting any information below a table","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":792,"value":"{\n if (sheetsWithoutTables.length === workbook.SheetNames.length) {\n return html`

Not checkedNo tables were found.

`;\n } else if (sheetsWithContentBelowTables.length === 0) {\n return html`

✓ Looks goodNo problems detected.

`;\n } else {\n let result =\n '

Changes may be neededThe following sheets may have content below a table:

${s}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":590,"value":"## Worksheets","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":157,"value":"### Remove blank worksheets","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":161,"value":"{\n if (possiblyBlankWorksheets.length === 0) {\n return html`

✓ Looks goodNo blank sheets were found

`;\n } else {\n let result =\n '

Changes are neededBlank worksheets:

${s}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":548,"value":"### Each sheet should have its title in cell A1","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":551,"value":"{\n if (sheetsWithEmptyCellA1.length === 0) {\n return html`

✓ Looks goodCell A1 is non-empty in each sheet

`;\n } else {\n let result =\n '

Changes are neededCell A1 is empty in the following sheets:

${item}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":177,"value":"### Cell A1 should use Heading 1 style","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":182,"value":"{\n if (heading1CellsInfo == null) {\n return html`

Changes are neededNo cells using the Heading 1 style were detected.

`;\n } else if (heading1CellsInfo.length === 0) {\n return html`

✓ Looks goodCell A1 in each sheet uses the Heading 1 style.

`;\n } else {\n let result =\n '

Changes are neededChanges may be required on the following sheets:

${item.sheet.name} ${item.problems}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":484,"value":"### A1 should be the active cell in each sheet","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":488,"value":"{\n if (activeCellsNotA1.length === 0) {\n return html`

✓ Looks goodA1 is the active cell in each sheet

`;\n } else {\n let result =\n '

Changes are neededThe cursor should be moved to A1 in these sheets:

${item.sheet.name} (currently cell ${item.activeCell})

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":186,"value":"### Rows and columns should not be hidden","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":190,"value":"{\n if (sheetsWithHiddenContent.length === 0) {\n return html`

✓ Looks goodNo hidden content was detected.

`;\n } else {\n let result =\n '

Changes are neededThe following sheets may have hidden rows or columns.

${item.name}

✓ Looks goodNo frozen rows or columns were detected.

`;\n } else {\n return html`

Changes may be neededThe workbook may have frozen rows or columns. If this feature is needed, make sure the sheet has instructions for turning freeze panes off.

✓ Looks goodNo headers or footers were detected.

`;\n } else {\n return html`

Changes are neededThe workbook may have headers or footers.

✓ Looks goodNo merged cells were found

`;\n } else {\n let result =\n '

Changes are neededMerged cells:

${c}

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":610,"value":"## Cell contents","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":221,"value":"### Do not use superscript to signpost to notes","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":225,"value":"{\n if (!workbookHasSuperscript) {\n return html`

✓ Looks goodNo superscript text was detected.

`;\n } else {\n return html`

Changes are neededThe workbook may have superscript text. (This sometimes gives false positives.)

✓ Looks goodNo plain-URL hyperlink descriptions were found. (Hyperlink text should also be checked manually for descriptiveness.)

`;\n } else {\n let result =\n '

Changes are neededThe following links may not have descriptive text. (This check sometimes gives incorrect results!)

${item.title} (on ${item.sheet})

\";\n return html`${result}`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":602,"value":"## Workbook","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":502,"value":"### The workbook should have a title","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":506,"value":"{\n if (documentTitle !== \"\") {\n return html`

✓ Looks goodThe workbook has the following title: ${documentTitle}

`;\n } else {\n return html`

Changes are neededThe workbook needs a title (in Excel: File > Info).

✓ Looks goodThe first sheet is open.

`;\n } else {\n return html`

Changes are neededThe first sheet is not open. Click on cell A1 of the first sheet, then save the workbook.

`;\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":711,"value":"## Things to check manually\n\nThe following list covers many, but not all, of the [checklist](https://gss.civilservice.gov.uk/policy-store/making-spreadsheets-accessible-a-brief-checklist-of-the-basics/) items that have not been checked above.\n\n- See the Footnotes section of the checklist.\n- See the Formatting section of the checklist, most of which is not covered by this spreadsheet checker.\n- Ensure all text is visible and clearly spaced out.\n- The cell in column A directly below the title should describe the contents of the worksheet.\n- Run Excel's spelling and grammar checker and accessibility checker.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":11,"value":"---\n\n## Appendix\n\nEverything below this point can be ignored.\n\n---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":229,"value":"workbookHasSuperscript = sharedStringsXml.includes('val=\"superscript\"')","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":241,"value":"workbookHasHeadersOrFooters = {\n for (let filename of z.filenames) {\n if (filename.startsWith(\"xl/worksheets/\")) {\n let xml = await z.file(filename).text();\n if (\n xml.includes(\"\") &&\n !xml.includes(\"\")\n )\n return true;\n if (\n xml.includes(\"\") &&\n !xml.includes(\"\")\n )\n return true;\n }\n }\n return false;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":209,"value":"workbookHasFrozenContent = {\n for (let filename of z.filenames) {\n if (filename.startsWith(\"xl/worksheets/\")) {\n let xml = await z.file(filename).text();\n if (xml.includes('state=\"frozen\"')) return true;\n }\n }\n return false;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":194,"value":"sheetsWithHiddenContent = {\n let result = [];\n for (let sheet of sheets) {\n let sheetFileName = \"xl/\" + sheet.filename;\n let xml = await z.file(sheetFileName).text();\n if (xml.includes('hidden=\"true\"')) result.push(sheet);\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":315,"value":"sheetsWithoutTables = workbook.SheetNames.filter(\n (d) => sheetToTables[d].length === 0\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":383,"value":"sheetsWithoutColumnATable = {\n function hasAColumnATable(tables) {\n for (let table of tables) {\n if (table.range.s.c === 0) return true;\n }\n return false;\n }\n let result = [];\n for (let sheet of workbook.SheetNames) {\n if (sheetToTables[sheet].length === 0) continue;\n if (!hasAColumnATable(sheetToTables[sheet])) {\n result.push(sheet);\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":779,"value":"sheetsWithContentBelowTables = {\n let result = [];\n for (let sheet of workbook.SheetNames) {\n let tables = sheetToTables[sheet];\n if (tables.length === 0) continue;\n let lastTableRow = -1;\n for (let table of tables) {\n lastTableRow = Math.max(lastTableRow, table.range.e.r);\n }\n for (let cell in workbook.Sheets[sheet]) {\n if (cell.startsWith(\"!\")) continue;\n if (XLSX.utils.decode_cell(cell).r > lastTableRow) {\n result.push(sheet);\n break;\n }\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":312,"value":"sheetToTables = {\n let result = {};\n for (let sheet of workbook.SheetNames) {\n let tt = [];\n for (let table of tables) {\n if (table.sheetName === sheet) {\n tt.push(table);\n }\n }\n result[sheet] = tt;\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":61,"value":"possiblyBlankWorksheets = {\n let result = [];\n for (let sheet of workbook.SheetNames) {\n if (\n !(\"!ref\" in workbook.Sheets[sheet]) ||\n XLSX.utils.decode_range(workbook.Sheets[sheet][\"!ref\"]).e.r < 2\n ) {\n result.push(sheet);\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":546,"value":"sheetsWithEmptyCellA1 = {\n let result = [];\n for (let sheet of workbook.SheetNames) {\n if (!(\"A1\" in workbook.Sheets[sheet])) {\n result.push(sheet);\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":40,"value":"mergedCells = {\n let result = [];\n for (let sheet of workbook.SheetNames) {\n if (\"!merges\" in workbook.Sheets[sheet]) {\n for (let range of workbook.Sheets[sheet][\"!merges\"]) {\n result.push(sheet + \"!\" + XLSX.utils.encode_range(range));\n }\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":741,"value":"$46","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":9,"value":"a = new Uint8Array(await file.arrayBuffer())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":41,"value":"workbook = XLSX.read(a, { type: \"array\" })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":14,"value":"XLSX = require(\"xlsx@0.17.4/dist/xlsx.full.min.js\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"z = file.zip()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":231,"value":"sharedStringsXml = z.file(\"xl/sharedStrings.xml\").text()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":277,"value":"$47","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":457,"value":"tableHasFilterButtons = function (tableDoc) {\n let autoFilterElems = tableDoc.getElementsByTagName(\"autoFilter\");\n if (autoFilterElems.length === 0) return false;\n let filterColumnElems = tableDoc.getElementsByTagName(\"filterColumn\");\n if (filterColumnElems.length === 0) return true;\n for (let i = 0; i < filterColumnElems.length; i++) {\n let col = filterColumnElems[i];\n if (\n !col.hasAttribute(\"hiddenButton\") ||\n col.getAttribute(\"hiddenButton\") != \"1\"\n ) {\n return true;\n }\n }\n return false;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":340,"value":"rangeHasEmptyCells = function (sheetName, range) {\n let sheet = workbook.Sheets[sheetName];\n for (let r = range.s.r; r <= range.e.r; r++) {\n for (let c = range.s.c; c <= range.e.c; c++) {\n let cellRef = XLSX.utils.encode_cell({ c, r });\n if (!(cellRef in sheet) || sheet[cellRef] === \"\") {\n return true;\n }\n }\n }\n return false;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":473,"value":"$48","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":525,"value":"untitledHyperlinks = {\n let result = [];\n for (let sheet of sheets) {\n let sheetFileName = \"xl/\" + sheet.filename;\n if (z.filenames.includes(sheetFileName)) {\n let sheetDoc = parseXml(await z.file(sheetFileName).text());\n let hyperlinks = sheetDoc.getElementsByTagName(\"hyperlink\");\n for (let i = 0; i < hyperlinks.length; i++) {\n let hyperlink = hyperlinks[i];\n let title = \"\";\n if (hyperlink.hasAttribute(\"display\"))\n title = hyperlink.getAttribute(\"display\");\n if (title.startsWith(\"http\")) {\n result.push({ sheet: sheet.name, title });\n }\n }\n }\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":255,"value":"sheets = {\n let workbookRelsXmlDoc = parseXml(\n await z.file(\"xl/_rels/workbook.xml.rels\").text()\n );\n let sheetFilenames = {};\n let rels = workbookRelsXmlDoc.getElementsByTagName(\"Relationship\");\n for (let i = 0; i < rels.length; i++) {\n let rel = rels[i];\n sheetFilenames[rel.getAttribute(\"Id\")] = rel.getAttribute(\"Target\");\n }\n let xmlDoc = parseXml(workbookXml);\n let sheets = xmlDoc.getElementsByTagName(\"sheet\");\n let result = [];\n for (let i = 0; i < sheets.length; i++) {\n let sheet = sheets[i];\n result.push({\n rId: sheet.getAttribute(\"r:id\"),\n filename: sheetFilenames[sheet.getAttribute(\"r:id\")],\n name: sheet.getAttribute(\"name\")\n });\n }\n return result;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":496,"value":"documentTitle = {\n let coreDoc = parseXml(await z.file(\"docProps/core.xml\").text());\n let titleFields = coreDoc.getElementsByTagName(\"dc:title\");\n if (titleFields.length === 0) return \"\";\n return titleFields[0].textContent;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":18,"value":"workbookXml = z.file(\"xl/workbook.xml\").text()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":21,"value":"stylesXml = z.file(\"xl/styles.xml\").text()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":77,"value":"html``","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":273,"value":"parseXml = function (xmlString) {\n return new DOMParser().parseFromString(xmlString, \"text/xml\");\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W49","stdlib":"1"}} 45:"$44:body"