2b:null 2c:["$","title",null,{"children":"Excel to Plot | Data Workflows | Observable"}] 31:[{"id":"968a63e07b7c595340f0138fa33e75705d6c65cb661279ec1e2a2ef933cfd49cffe52fb7431f3ff7a38b21eab7afe0b73c1c933b15054f9ab29a9420d51c9a12","href":"https://static.observableusercontent.com/files/968a63e07b7c595340f0138fa33e75705d6c65cb661279ec1e2a2ef933cfd49cffe52fb7431f3ff7a38b21eab7afe0b73c1c933b15054f9ab29a9420d51c9a12","name":"plot.umd.js","type":"application/javascript","lastModified":1609987271771.064,"size":70528},{"id":"06d7e285d599cc1b9d0c90a92e0877edb7833c99ddfeff917bb3d64c9e387859d8692494bb18158143745d67540d26dab4a27c1f998f6ea6d272af52cc0768a1","href":"https://static.observableusercontent.com/files/06d7e285d599cc1b9d0c90a92e0877edb7833c99ddfeff917bb3d64c9e387859d8692494bb18158143745d67540d26dab4a27c1f998f6ea6d272af52cc0768a1","name":"xlsx-0.14.1.js","type":"application/javascript","lastModified":1611359432924.141,"size":903608},{"id":"06ba22fd484d09dd5d192c481ef70682dcd63581fbdee736d4ba64e4d717de0241f7226c7f17f7f8462bba978710c156cd524c0f1a53ff20b641fb162382e19d","href":"https://static.observableusercontent.com/files/06ba22fd484d09dd5d192c481ef70682dcd63581fbdee736d4ba64e4d717de0241f7226c7f17f7f8462bba978710c156cd524c0f1a53ff20b641fb162382e19d","name":"51135-2019-08-economicprojections_1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1609956691386.918,"size":110794}] 2f:{"id":"c93b05609691d7bb","slug":null,"title":"Excel to Plot","thumbnail":"492cc4c2395a05cfc32f12dd3cd8ffb8e1a12598f868c2bf2370eff3020c8731","access_level":"unlisted","create_time":"$D2021-01-06T16:53:37.954Z","update_time":"$D2021-07-22T17:15:54.046Z","view_time":null,"first_public_time":null,"has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"1d0137e37000f1b4","login":"data-workflows","name":"Data Workflows","avatar_url":"https://avatars-next.observableusercontent.com/3aaff8d085c0b4abbe490c04cb2a744977911c6b8c3c1b5bfb087ab76df8df22","tier":"pro_2024","bio":""},"version":1479,"publish_version":1479,"role":null,"authored":false,"authors":[{"type":"individual","id":"0bb2842b01e0c652","login":"tophtucker","name":"Toph Tucker","avatar_url":"https://avatars-next.observableusercontent.com/3706c31a8f9c59bb716c022c04b9c073144cd1368b78ac85f49139be6c93c34e","tier":null,"bio":"Learned D3 at Businessweek, started using Observable to do my job at Kensho, took time off to write notebooks about Mayans and Galton, moved to San Francisco to work for Observable. Now work for it remotely from the Berkshires where my wife runs Avaloch."}],"members":[{"type":"team","id":"1d0137e37000f1b4","login":"data-workflows","name":"Data Workflows","avatar_url":"https://avatars-next.observableusercontent.com/3aaff8d085c0b4abbe490c04cb2a744977911c6b8c3c1b5bfb087ab76df8df22","tier":"pro_2024","role":"editor"},{"type":"individual","id":"0bb2842b01e0c652","login":"tophtucker","name":"Toph Tucker","avatar_url":"https://avatars-next.observableusercontent.com/3706c31a8f9c59bb716c022c04b9c073144cd1368b78ac85f49139be6c93c34e","tier":null,"role":"editor"}],"comments":[{"id":"a1439b4e7ee7a857","reply_to_id":null,"content":"excel date parsing is notorious for that 4 year shift https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system.","create_time":"$D2021-05-17T22:22:36.329Z","update_time":null,"resolved":false,"user":{"type":"individual","id":"dbf69c13a14d2f62","login":"visnup","name":"Visnu Pitiyanuvath","avatar_url":"https://avatars-next.observableusercontent.com/b847c4c49993b1df7599d355f47dc0ad9c2dcf43439ce9e8f3410e6368b3aac9","tier":null}}],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Excel to Plot","cells":[{"id":0,"value":"md`# Excel to Plot\n\nUsing [SheetJS](https://github.com/SheetJS/sheetjs) (\\`xlsx\\` on npm), like [@elmisback/working-with-excel-notebooks](https://next.observablehq.com/@elmisback/working-with-excel-notebooks) and [@severo/hello-sheetjs](https://next.observablehq.com/@severo/hello-sheetjs). Fil notes that the library “doesn’t parse dates correctly (there is sometimes a 32 seconds shift… resulting in the wrong day, and sometimes a 4 years shift, depending on the excel format).”\n\nThe motivation here is that (1) any finance artifact will quickly run into the question of Excel interoperability and (2) real-world Excel workbooks are super messy; every sheet contains many tables. So you gotta be able to extract arbitrary ranges easily, and it’s nice to do it in the coordinate system you know.\n\n---`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":64,"value":"md`**Method #1: Upload and save** (private on Observable servers)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":7,"value":"file1 = FileAttachment(\"51135-2019-08-economicprojections_1.xlsx\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":66,"value":"md`**Method #2: No upload, no saving** (no data touches our servers)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":60,"value":"viewof file2 = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":27,"value":"// Uncomment next line to use Method #1\nparsed = file1.arrayBuffer()\n\n// Uncomment next line to use Method #2\n// parsed = Files.buffer(file2)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":119,"value":"md`---`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"value":"viewof tabs = radio({options: workbook.SheetNames, value: workbook.SheetNames[2]})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":696,"value":"table = html`${style}\n

\n \n \n \n \n ${d3.range(rows[0].length).map(n => html`\n \n `)}\n \n \n \n ${rows.map((row, i) => html`\n \n \n ${row.map((cell, j) => html`\n \n `)}\n \n `)}\n \n

	${numToLetters(n + 1)}
${i + 1}	\n ${cell \|\| \"\"}\n

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":780,"value":"viewof ref = html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":877,"value":"selection = slice(...parseRef(ref))","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1183,"value":"md`---\n\n## Plotting`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1039,"value":"Plot.plot({\n y: {label: get(\"B9\")},\n marks: [\n Plot.barY(get(\"E15:Q15\"), {x: get(\"E7:Q7\"), fill: \"gray\"}),\n Plot.line(get(\"E9:Q9\"), {x: get(\"E7:Q7\"), y: d => d, stroke: \"steelblue\"})\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1247,"value":"Plot.plot({\n x: {label: get(\"B9\")},\n y: {label: get(\"D10\")},\n inset: 20,\n marks: [\n Plot.dot(get(\"E7:Q7\"), {x: get(\"E9:Q9\"), y: get(\"E10:Q10\")}),\n Plot.text(get(\"E7:Q7\"), {x: get(\"E9:Q9\"), y: get(\"E10:Q10\"), text: d => d, dy: -6})\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1279,"value":"md`One gripe is sometimes I want to pass an array to \\`x\\` and \\`y\\` and not specify anything for the mark’s data, as in \\`Plot.dot(???, {x: get(\"E9:Q9\"), y: get(\"E10:Q10\")})\\`. Then again I guess it’s easy to do \\`Plot.dot(d3.zip(get(\"E9:Q9\"), get(\"E10:Q10\")))\\`. Example with that zipping syntax:`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1269,"value":"Plot.plot({\n x: {label: get(\"B9\")},\n y: {label: get(\"D10\")},\n inset: 20,\n marks: [\n Plot.dot(d3.zip(get(\"E9:Q9\"), get(\"E10:Q10\"))),\n Plot.text(d3.zip(get(\"E9:Q9\"), get(\"E10:Q10\")), {text: get(\"E7:Q7\"), dy: -6})\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":954,"value":"md`---\n\n## Appendix\n\n### Load workbook`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":106,"value":"workbook = xlsx.read(parsed, {type: \"array\"})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":138,"value":"tab = workbook.Sheets[tabs]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":141,"value":"rows = {\n const rows = xlsx.utils.sheet_to_json(tab, {header: 1})\n // normalize so that every row has same number of columns\n const maxCol = d3.max(rows.map(cols => cols.length))\n return rows.map(row => d3.range(0, maxCol).map(i => row[i]))\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1444,"value":"md`### Render table`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":891,"value":"isSelected = (ref, i, j) => {\n const [[r0, c0], [r1, c1]] = ref\n return i >= r0 && i <= r1\n && j >= c0 && j <= c1\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":731,"value":"style = {\n const colWidth = rows[0].length > 6 ? 40 : 240\n return html``\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1408,"value":"md`### Bijective numeration`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":903,"value":"parseRef = ref => {\n try {\n if (ref.indexOf(\":\") !== -1) {\n return ref.split(\":\").map(fromRef)\n } else {\n return [fromRef(ref), fromRef(ref)]\n }\n } catch {\n return [[0, 0], [0, 0]]\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":145,"value":"// [1, 0] to \"A2\"\ntoRef = (i, j) => numToLetters(j + 1) + (i + 1)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":629,"value":"// \"A2\" to [1, 0]\nfromRef = str => [\n str.match(/([0-9])+/)[0] - 1,\n lettersToNum(str.match(/([A-Z])+/)[0]) - 1\n]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":147,"value":"// 1 to A\nnumToLetter = n => String.fromCharCode(\"A\".charCodeAt(0) + (n - 1))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":560,"value":"// A to 1\nletterToNum = str => str.charCodeAt(0) - \"A\".charCodeAt(0) + 1","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":576,"value":"// AA to 27\nlettersToNum = str => fromBij(str.split(\"\").map(letterToNum))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":161,"value":"// 27 to AA\nnumToLetters = n => toBij(n).map(numToLetter).join(\"\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":362,"value":"function toBij(n, base = 26, arr = []) {\n if (n < 0 || !n || n % 1) return null\n const q = Math.ceil(n / base) - 1\n arr.unshift(n - q * base)\n return q ? toBij(q, base, arr) : arr\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":534,"value":"function fromBij(arr, base = 26) {\n return arr.reverse().map((d, i) => d * base ** i).reduce((acc, cur) => acc + cur, 0)\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1212,"value":"md`### Getting row and column ranges`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1059,"value":"// tries to do the right thing based on shape of range reference:\n// \"A1\" => plain value\n// \"A1:A10\" => 1D array of rows\n// \"A1:R1\" => 1D array of cols\n// \"A1:R10\" => 2D array of rows, cols\nget = (ref) => {\n const [start, stop] = parseRef(ref)\n if (start[0] === stop[0] && start[1] === stop[1]) {\n return getRC(ref)[0][0]\n } else if (start[0] === stop[0]) {\n return getR(ref)\n } else if (start[1] === stop[1]) {\n return getC(ref)\n } else {\n return getRC(ref)\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1135,"value":"getRC = (ref) => slice(...parseRef(ref))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1142,"value":"getCR = ref => d3.transpose(getRC(ref))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1138,"value":"getR = ref => getRC(ref)[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1140,"value":"getC = ref => getCR(ref)[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":791,"value":"slice = ([r0, c0], [r1, c1]) => \n rows\n .slice(r0, r1 + 1)\n .map(row => row.slice(c0, c1 + 1))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1054,"value":"md`To-do: These functions probably shouldn't use the same tab selection as preview above; gotta be more stable, wanna let people flip between workbook tabs above without destroying their plots`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":87,"value":"md`### Imports`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":102,"value":"import {radio} from \"@jashkenas/inputs\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":2,"value":"xlsx = require(await FileAttachment(\"xlsx-0.14.1.js\").url())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":131,"value":"d3 = require(\"d3@6\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":957,"value":"Plot = require(await FileAttachment(\"plot.umd.js\").url())","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W31","stdlib":"1"}} 30:"$2f:body"