Interaction Designerin @ Zeit Online Daten und Visualisierung
Published
Edited
Jan 22, 2021
Importers
1 star
data = readExcel(
await FileAttachment("Impfquotenmonitoring.xlsx").arrayBuffer(),
{
sheetName: "Gesamt_bis_einschl_17.01.21",
range: 'A3:I20'
}
)
table(data)
readExcel = function(
fileContent,
{
sheetName: manualSheetName,
sheetIndex = 0,
range: manualRange,
skip = 0,
colNames: manualColNames,
namesInFirst: manualNamesInFirst = true
} = {}
) {
let wb;
try {
wb = xlsx.read(fileContent, { type: 'array' });
} catch (e) {
throw new Error(
'xlsx.read(fileContent, { type: "array" }) was unsuccessful, ' +
'did you pass a valid file content as an array buffer?'
);
}
if (manualSheetName) {
if (!Object.keys(wb.Sheets).includes(manualSheetName)) {
throw new Error(
`invalid sheetName ${manualSheetName}, no sheet was found with that name`
);
}
if (sheetIndex !== 0) {
console.log(
`sheetName was provided manually, so sheetIndex ${sheetIndex} will be ignored`
);
}
} else {
if (
typeof sheetIndex !== "number" ||
sheetIndex > Object.keys(wb.Sheets).length
) {
throw new Error(
`invalid sheetIndex ${sheetIndex}, must be a number and smaller than ${
Object.keys(wb.Sheets).length
}`
);
}
}
if (
manualRange &&
(typeof manualRange !== "string" ||
!manualRange.match(/[a-z]+\d*:[a-z]*\d*/i))
) {
throw new Error(
`invalid range ${manualRange}, must be string of format "A1:B3" or "A:B"`
);
}
if (manualColNames && !Array.isArray(manualColNames)) {
throw new Error(
`invalid colNames ${JSON.stringify(colNames)}, must be an array`
);
}
// get sheet name or name of sheet at index position (defaults to first)
const sheetName = manualSheetName || Object.keys(wb.Sheets)[sheetIndex];
// read sheet
const ws = wb.Sheets[sheetName];
// create array with rows in range as numbers [2, 3, 4,…]
const rows = getRows(manualRange, ws['!ref']).filter(d => d > skip);
// create array with columns in range as letters [B, C, D,…]
const columns = getCols(manualRange, ws['!ref']);
// create function that returns the cell value or undefined
const getV = (col, row) => ws[col + row] && ws[col + row].v;
// if column names are provided, names are not found in first row either
const namesInFirst =
!!manualNamesInFirst && typeof manualColNames === "undefined";
// create array used for object property names (three scenarios)
const colNames =
typeof manualColNames !== "undefined"
? // first: provided manually
manualColNames
: // second: if names are in first row (default) get them from there
namesInFirst
? columns.map(col => getV(col, rows[0]) || col)
: // third: if no names are provided at all, just use the letters [B, C, D,…]
columns;
return (
rows
// skip first row if it contains column names
.filter((_, i) => i >= (namesInFirst ? 1 : 0))
// map over each cell of the row
.map(row =>
columns.reduce((res, col, i) => {
// use the corresponding column name as a property name or letter as fallback
const colName = i < colNames.length ? colNames[i] : col;
res[colName] = getV(col, row);
return res;
}, {})
)
);
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
