Vue 3 blog post code (spreadsheet example)

vue3_spreadsheet.vue

<template>
  <div>
    <div style="margin: 1ex;">Calculations: {{ calculations }}</div>
    <table class="table" border="0">
      <tr class="row">
        <td id="empty_first_cell"></td>

        <td class="column"
            v-for="(_, j) in cols" :key="'header' + j"
        >
          {{ letters[j] }}
        </td>
      </tr>

      <tr class="row"
          v-for="(_, i) in rows" :key="i"
      >
        <td class="column">
          {{ i + 1 }}
        </td>

        <td class="column"
            v-for="(__, j) in cols" :key="i + '-' + j"
            :class="{ column_selected: active(i, j), column_inactive: !active(i, j),  }"
            @click="activate(i, j)"
        >
          <div v-if="active(i, j)">
            <input :ref="'input' + i + '-' + j"
                   v-model="raw_values[i][j]"
                   @keydown.enter.prevent="ui_enter()"
                   @keydown.esc="ui_esc()"
            />
          </div>
          <div v-else v-html="computed_value_formatter(computed_values[i][j].value)"/>
        </td>
      </tr>
    </table>
  </div>
</template>

<script>
import {ref, reactive, computed, watchEffect, toRefs, nextTick, onUpdated} from "vue";

export default {
  name: 'App',
  components: {},
  data() {
    return {
      ui_editing_i: null,
      ui_editing_j: null,
    }
  },
  methods: {
    get_dom_input(i, j) {
      return this.$refs['input' + i + '-' + j];
    },
    activate(i, j) {
      this.ui_editing_i = i;
      this.ui_editing_j = j;
      nextTick(() => this.get_dom_input(i, j).focus());
    },
    active(i, j) {
      return this.ui_editing_i === i && this.ui_editing_j === j;
    },
    unselect() {
      this.ui_editing_i = null;
      this.ui_editing_j = null;
    },
    computed_value_formatter(str) {
      if (str === undefined || str === null)
        return 'none';
      return str;
    },
    ui_enter() {
      if (this.ui_editing_i < this.rows - 1)
        this.activate(this.ui_editing_i + 1, this.ui_editing_j);
      else
        this.unselect();
    },
    ui_esc() {
      this.unselect();
    },
  },
  setup() {
    const rows = ref(30), cols = ref(26);

    /* if a string codes a number, return the number, else return a string */
    const as_number = raw_cell => /^[0-9]+(\.[0-9]+)?$/.test(raw_cell)  
        ?  Number.parseFloat(raw_cell)  :  raw_cell;

    const make_table = (val = '', _rows = rows.value, _cols = cols.value) =>
        Array(_rows).fill(null).map(() => Array(_cols).fill(val));

    const raw_values = reactive(make_table('', rows.value, cols.value));
    const computed_values = reactive(make_table(undefined, rows.value, cols.value));

    /* a useful metric for debugging: how many times did cell (re)computations occur? */
    const calculations = ref(0);

    const letters = Array(26).fill(0)
        .map((_, i) => String.fromCharCode("A".charCodeAt(0) + i));

    const transpile = str => {
        let cell_replacer = (match, prepend, col, row) => {
            col = letters.indexOf(col);
            row = Number.parseInt(row) - 1;
            return prepend + ` computed_values[${row}][${col}].value `;
        };
        return str.replace(/(^|[^A-Z])([A-Z])([0-9]+)/g, cell_replacer);
    };

    const computed_cell_generator = (i, j) => {
        const computed_cell = computed(() => {
            // we don't want Vue to think that the value of a computed_cell depends on the value of `calculations`
            nextTick(() => ++calculations.value);

            let raw_cell = raw_values[i][j].trim();
            if (!raw_cell || raw_cell[0] != '=') 
                return as_number(raw_cell);

            let user_code = raw_cell.substring(1);
            let code = transpile(user_code);
            try {
                // the constructor of a Function receives the body of a function as a string
                let fn = new Function(['computed_values'], `return ${code};`);
                return fn(computed_values);
            } catch (e) {
                return "ERROR";
            }
        });
        return computed_cell;
    };

    for (let i = 0; i < rows.value; ++i)
        for (let j = 0; j < cols.value; ++j)
            computed_values[i][j] = computed_cell_generator(i, j);

    return {raw_values, computed_values, rows, cols, letters, calculations};   
  },
}
</script>

<style>
.table {
  margin-left: auto;
  margin-right: auto;
  margin-top: 1ex;
  border-collapse: collapse;
}

.column {
  box-sizing: border-box;
  border: 1px lightgray solid;
}

.column:first-child {
  background: #f6f6f6;
  min-width: 3em;
}

.column:not(:first-child) {
  min-width: 4em;
}

.row:first-child {
  background: #f6f6f6;
}

#empty_first_cell {
  background: white;
}

.column_selected {
  border: 2px cornflowerblue solid !important;
  padding: 0px;
}

.column_selected input, .column_selected input:active, .column_selected input:focus {
  outline: none;
  border: none;
}
</style>

Hi @luka-mikec,

Thanks for the article! I found an issue that the spreadsheet won't process multiple references like =A1 + B1 (in case when both of this cells are filled with numbers). But it should work like this:

I fixed it with /(^|[^A-Z])([A-Z]{1,2})([0-9]+)/g regexp in transpile() function and I'd report this fix to you:) I don't mind this is a bulletproof solution, but that works.

Thanks for the article and this challenge!)

Hi @vasiliy0s!

Thanks for reading the article and for reporting the issue! I didn't quite catch what the issue is, could you please explain it in more detail?

I tried setting A1 to 1, B1 to 11 and C1 to 111, and I get correct results for "=A1 + B1" (should be 12) and for "=A1+B1+C1" (should be 123). I tested with the latest Firefox and Chrome on Linux.

Hi @luka-mikec,

Yes, in my case in Chrome (Version 86.0.4240.198 (Official Build) (x86_64)) on macOS Catalina (Version 10.15.7), the calculation doesn't work as expected. For example, in the scenario, you proposed above, it showed 1 for =A1 + B1 (seemed the transpile() wasn't able to use g flag in the regexp).

But, I rechecked it now and the bug doesn't re-appear in my environment, and that's strange. I was thought that this was a bug in the regexp (despite it seemed ok for me), and tried to fix, and get the result.

(My proposal is also should work if cols will have two letters (like AA, AB, and so forth))

But the problem, why I get the computation, is my curious for now :) Assume it could be something related to the Vue 3, or my browser, or something else, I don't know.

Sorry for wasting your time!

Hi @vasiliy0s, thanks again and please let me know if it occurs again. I wouldn't be surprised if Chrome's regex implementation was buggy. As a matter of a fact I got carried away in my original implementation and had dynamic row/column additions, range functions (e.g. SUM(A1:A30)), autofill etc., but removed it for the article in order not to make it too long...