Dataviz at Office for National Statistics (UK)
Public
Edited
Jul 10, 2023
1 fork
3 stars
workbookHasSuperscript = sharedStringsXml.includes('val="superscript"')
workbookHasFrozenContent = {
for (let filename of z.filenames) {
if (filename.startsWith("xl/worksheets/")) {
let xml = await z.file(filename).text();
if (xml.includes('state="frozen"')) return true;
}
}
return false;
}
sheetsWithHiddenContent = {
let result = [];
for (let sheet of sheets) {
let sheetFileName = "xl/" + sheet.filename;
let xml = await z.file(sheetFileName).text();
if (xml.includes('hidden="true"')) result.push(sheet);
}
return result;
}
sheetsWithoutTables = workbook.SheetNames.filter(
(d) => sheetToTables[d].length === 0
)
sheetsWithoutColumnATable = {
function hasAColumnATable(tables) {
for (let table of tables) {
if (table.range.s.c === 0) return true;
}
return false;
}
let result = [];
for (let sheet of workbook.SheetNames) {
if (sheetToTables[sheet].length === 0) continue;
if (!hasAColumnATable(sheetToTables[sheet])) {
result.push(sheet);
}
}
return result;
}
sheetsWithContentBelowTables = {
let result = [];
for (let sheet of workbook.SheetNames) {
let tables = sheetToTables[sheet];
if (tables.length === 0) continue;
let lastTableRow = -1;
for (let table of tables) {
lastTableRow = Math.max(lastTableRow, table.range.e.r);
}
for (let cell in workbook.Sheets[sheet]) {
if (cell.startsWith("!")) continue;
if (XLSX.utils.decode_cell(cell).r > lastTableRow) {
result.push(sheet);
break;
}
}
}
return result;
}
sheetToTables = {
let result = {};
for (let sheet of workbook.SheetNames) {
let tt = [];
for (let table of tables) {
if (table.sheetName === sheet) {
tt.push(table);
}
}
result[sheet] = tt;
}
return result;
}
possiblyBlankWorksheets = {
let result = [];
for (let sheet of workbook.SheetNames) {
if (
!("!ref" in workbook.Sheets[sheet]) ||
XLSX.utils.decode_range(workbook.Sheets[sheet]["!ref"]).e.r < 2
) {
result.push(sheet);
}
}
return result;
}
sheetsWithEmptyCellA1 = {
let result = [];
for (let sheet of workbook.SheetNames) {
if (!("A1" in workbook.Sheets[sheet])) {
result.push(sheet);
}
}
return result;
}
mergedCells = {
let result = [];
for (let sheet of workbook.SheetNames) {
if ("!merges" in workbook.Sheets[sheet]) {
for (let range of workbook.Sheets[sheet]["!merges"]) {
result.push(sheet + "!" + XLSX.utils.encode_range(range));
}
}
}
return result;
}
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;
}
a = new Uint8Array(await file.arrayBuffer())
workbook = XLSX.read(a, { type: "array" })
XLSX = require("xlsx@0.17.4/dist/xlsx.full.min.js")
z = file.zip()
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;
}
tableHasFilterButtons = function (tableDoc) {
let autoFilterElems = tableDoc.getElementsByTagName("autoFilter");
if (autoFilterElems.length === 0) return false;
let filterColumnElems = tableDoc.getElementsByTagName("filterColumn");
if (filterColumnElems.length === 0) return true;
for (let i = 0; i < filterColumnElems.length; i++) {
let col = filterColumnElems[i];
if (
!col.hasAttribute("hiddenButton") ||
col.getAttribute("hiddenButton") != "1"
) {
return true;
}
}
return false;
}
rangeHasEmptyCells = function (sheetName, range) {
let sheet = workbook.Sheets[sheetName];
for (let r = range.s.r; r <= range.e.r; r++) {
for (let c = range.s.c; c <= range.e.c; c++) {
let cellRef = XLSX.utils.encode_cell({ c, r });
if (!(cellRef in sheet) || sheet[cellRef] === "") {
return true;
}
}
}
return false;
}
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;
}
untitledHyperlinks = {
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 hyperlinks = sheetDoc.getElementsByTagName("hyperlink");
for (let i = 0; i < hyperlinks.length; i++) {
let hyperlink = hyperlinks[i];
let title = "";
if (hyperlink.hasAttribute("display"))
title = hyperlink.getAttribute("display");
if (title.startsWith("http")) {
result.push({ sheet: sheet.name, title });
}
}
}
}
return result;
}
sheets = {
let workbookRelsXmlDoc = parseXml(
await z.file("xl/_rels/workbook.xml.rels").text()
);
let sheetFilenames = {};
let rels = workbookRelsXmlDoc.getElementsByTagName("Relationship");
for (let i = 0; i < rels.length; i++) {
let rel = rels[i];
sheetFilenames[rel.getAttribute("Id")] = rel.getAttribute("Target");
}
let xmlDoc = parseXml(workbookXml);
let sheets = xmlDoc.getElementsByTagName("sheet");
let result = [];
for (let i = 0; i < sheets.length; i++) {
let sheet = sheets[i];
result.push({
rId: sheet.getAttribute("r:id"),
filename: sheetFilenames[sheet.getAttribute("r:id")],
name: sheet.getAttribute("name")
});
}
return result;
}
documentTitle = {
let coreDoc = parseXml(await z.file("docProps/core.xml").text());
let titleFields = coreDoc.getElementsByTagName("dc:title");
if (titleFields.length === 0) return "";
return titleFields[0].textContent;
}
workbookXml = z.file("xl/workbook.xml").text()
stylesXml = z.file("xl/styles.xml").text()
html`<style>
.bg-green { background: #e4ffe0; border: 1px solid #5ca352; padding: 4px; border-radius: 4px}
.bg-red { background: #ffebe8; border: 1px solid #ab4f41; padding: 4px; border-radius: 4px}
.bg-amber { background: #fff9de; border: 1px solid #ab9c59; padding: 4px; border-radius: 4px}
.bg-neutral { background: #f3f3f4; border: 1px solid #444; padding: 4px; border-radius: 4px}
.badge {border: 1px solid #888; color #888; border-radius: 20px; background: white; padding:1px 6px 1px 6px; margin-right:4px}
</style>`
parseXml = function (xmlString) {
return new DOMParser().parseFromString(xmlString, "text/xml");
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.