首页

安装 Vue-Codemirror6

bash

npm i vue-codemirror6 --registry https://registry.npmmirror.com/

安装 Hyperformula

bash

npm install hyperformula --registry https://registry.npmmirror.com/

示例代码

vueexcelLang.ts

vue

<template>
  <div style="height: 155px;overflow:auto;">
    <code-mirror ref="codeMirrorRef" :tab-size="8" v-model="value" :dark="isDark" :extensions="[placeholders, baseTheme, basicSetup, excelLang()]" />

  </div>
  <button style="margin-top: 10px" class="exc-button" @click="insert('变量')">点击我插入一个变量</button>
  <input style="margin-left: 10px" class="exc-input" v-model="va"></input>
  <button style="margin-top: 10px;margin-left: 10px" class="exc-button" @click="test">计算结果:{{calculationResult.testValue}}</button>
  <br>
</template>

<script setup lang="ts">
// 使用hyperFormula 和 codemirror6 进行构建
// hyperFormula-> https://hyperformula.handsontable.com/guide/built-in-functions.html#list-of-available-functions
// codemirror6 -> https://codemirror.net/
import { ref } from 'vue';
const { isDark } = useData()
import CodeMirror from 'vue-codemirror6';
import { baseTheme, excelLang, placeholders } from './excelLang';
import { basicSetup } from 'codemirror';
import { DetailedCellError, HyperFormula } from 'hyperformula';
import {useData} from "vitepress";

// 接受父组件参数
const props = defineProps<{
  modelValue: string, // 公式
}>();

const va =ref(70)

// 定义子组件向父组件传值/事件
const emit = defineEmits(['update:modelValue', 'operateSuccess']);

const codeMirrorRef = ref();
const hyperFormulaOptions = {
  licenseKey: 'gpl-v3'
};

const value = ref('IFS(AND([[变量]]>=0,[[变量]]<60),77,AND([[变量]]>=60,[[变量]]<80),88,AND([[变量]]>=80,[[变量]]<=100),99)\n\n\n\n\n');

const calculationResult = ref({
  testValue: '88',
});

const insert = (object) => {
  const editor = codeMirrorRef.value;
  editor.view.dispatch({
    changes: { from: editor.getCursor(), to: editor.getCursor(), insert: `[[${object}]]`, },
    // 光标位置
    selection: { anchor: editor.getCursor() + object.length + 4, },
  });
  // 获取焦点
  editor.view.focus();
}

const test = () => {
  const thresholdRegex = /\[\[变量\]\]/g;
  // 使用 replace 方法进行替换
  let formula = '=' + value.value.trim();
  if (va.value || va.value === 0) {
    formula = formula.replace(thresholdRegex, va.value + '');
  }
  const data = [[formula]];
  const hfInstance = HyperFormula.buildFromArray(data, hyperFormulaOptions);
  const result: any = hfInstance.getCellValue({ col: 0, row: 0, sheet: 0 });
  if (result instanceof DetailedCellError) {
    calculationResult.value.testValue = '公式错误';
  } else {
    if (isNaN(Number(result))) {
      calculationResult.value.testValue = '公式正确，但是结果必须为数字';
    } else {
      calculationResult.value.testValue = result;
    }
  }
}

</script>


<style scoped>

/* 基础输入框样式 */
.exc-input {
  width: 100%; /* 宽度自适应 */
  max-width: 200px; /* 最大宽度 */
  padding: 2px 4px; /* 内边距 */
  font-size: 16px; /* 字体大小 */
  line-height: 1.5; /* 行高 */
  border: 2px solid #ddd; /* 边框颜色 */
  border-radius: 8px; /* 圆角 */
  background-color: rgba(76, 175, 80, 0.65); /* 背景颜色 */
  color: #fff; /* 字体颜色 */
  box-sizing: border-box; /* 盒模型，包含padding和border */
  transition: border-color 0.3s, box-shadow 0.3s; /* 平滑过渡效果 */
}

/* 聚焦状态 */
.exc-input:focus {
  border-color: #4CAF50; /* 聚焦时的边框颜色 */
  outline: none; /* 去掉默认的聚焦轮廓 */
  box-shadow: 0 0 5px rgba(76, 175, 80, 0.3); /* 聚焦时的阴影效果 */
}

/* 悬停效果 */
.exc-input:hover {
  border-color: #888; /* 悬停时的边框颜色 */
}

/* 禁用状态 */
.exc-input:disabled {
  background-color: #f5f5f5; /* 禁用状态背景色 */
  border-color: #ccc; /* 禁用状态边框颜色 */
  color: #aaa; /* 禁用状态字体颜色 */
  cursor: not-allowed; /* 禁用状态光标 */
}

/* 错误状态 */
.exc-input.error {
  border-color: #f44336; /* 错误时的边框颜色 */
  background-color: rgba(244, 67, 54, 0.1); /* 错误时的背景颜色 */
}

.exc-input.error:focus {
  border-color: #f44336; /* 错误聚焦时的边框颜色 */
  box-shadow: 0 0 5px rgba(244, 67, 54, 0.3); /* 错误聚焦时的阴影 */
}

/* 输入框的标签 */
.exc-input-label {
  font-size: 14px; /* 标签字体大小 */
  font-weight: 600; /* 标签加粗 */
  color: #555; /* 标签字体颜色 */
  margin-bottom: 5px; /* 标签与输入框的间距 */
  display: inline-block; /* 标签为行内块元素 */
}


.exc-button {
  display: inline-block;
  padding: 2px 4px;
  font-size: 14px;
  font-weight: 400;
  text-align: center;
  border-radius: 8px;
  border: 2px solid #4CAF50; /* 边框颜色 */
  background-color: #4CAF50; /* 背景颜色 */
  color: white; /* 字体颜色 */
  text-decoration: none; /* 去除下划线 */
  transition: background-color 0.3s ease, transform 0.3s ease; /* 添加平滑过渡 */
}

/* 悬停效果 */
.exc-button:hover {
  background-color: #45a049; /* 悬停时背景色 */
  cursor: pointer;
  transform: scale(1.05); /* 放大效果 */
}

/* 点击效果 */
.exc-button:active {
  background-color: #397d3a; /* 点击时背景色 */
  transform: scale(1.02); /* 点击时轻微缩小效果 */
}

/* 禁用状态 */
.exc-button:disabled {
  background-color: #ccc; /* 禁用状态背景色 */
  border-color: #999; /* 禁用状态边框颜色 */
  color: #666; /* 禁用状态字体颜色 */
  cursor: not-allowed; /* 禁用状态光标 */
  transform: none; /* 禁用状态去掉点击效果 */
}

</style>

import { LRParser } from '@lezer/lr';
import { LRLanguage, indentNodeProp, delimitedIndent, foldNodeProp, foldInside, LanguageSupport } from '@codemirror/language';
import { styleTags, tags } from '@lezer/highlight';
import { completeFromList, snippetCompletion } from '@codemirror/autocomplete';
import { Decoration, DecorationSet, MatchDecorator, ViewPlugin, ViewUpdate, WidgetType } from '@codemirror/view';
import { EditorView } from 'codemirror';
import { ref, reactive } from 'vue';
// 自动生成的,如何生成看官方示例 https://github.com/codemirror/lang-example
const parser = LRParser.deserialize({
    version: 14,
    states: "!WQYQPOOOhQPO'#CdOOQO'#Ci'#CiOOQO'#Ce'#CeQYQPOOOOQO,59O,59OOyQPO,59OOOQO-E6c-E6cOOQO1G.j1G.j",
    stateData: '![~O[OSPOS~ORQOSQOTQOVPO~ORQOSQOTQOUTOVPO~ORQOSQOTQOUWOVPO~O',
    goto: 'u^PPPPPPPP_ePPPoXQOPSUQSOQUPTVSUXROPSU',
    nodeNames: '⚠ LineComment Program String Boolean Keyword ) ( Application',
    maxTerm: 13,
    nodeProps: [
        ['openedBy', 6, '('],
        ['closedBy', 7, ')']
    ],
    skippedNodes: [0, 1],
    repeatNodeCount: 1,
    tokenData: "0o~RkXY!vYZ!v]^!vpq!vrs#Xst$uxy%Tyz%Y!]!^%_!c!d%v!e!f'T!k!l'{!n!o(Z!o!p(p!q!r)Y!r!s)`!t!u*e!u!v*w#T#U+^#V#W,f#]#^-^#`#a-l#a#b.R#c#d.k#d#e.q#f#g/v#g#h0Y~!{S[~XY!vYZ!v]^!vpq!v~#[VOr#Xrs#qs#O#X#O#P#v#P;'S#X;'S;=`$o<%lO#X~#vOR~~#yRO;'S#X;'S;=`$S;=`O#X~$VWOr#Xrs#qs#O#X#O#P#v#P;'S#X;'S;=`$o;=`<%l#X<%lO#X~$rP;=`<%l#X~$xQ#Y#Z%O#h#i%O~%TOS~~%YOV~~%_OU~~%dSP~OY%_Z;'S%_;'S;=`%p<%lO%_~%sP;=`<%l%_~%yR!d!e&S!p!q&_!x!y&e~&VP!u!v&Y~&_OT~~&bP!f!g&Y~&hP!g!h&k~&nP!t!u&q~&tP!c!d&w~&zP!i!j&}~'QP!g!h&Y~'WP!q!r'Z~'^P!w!x'a~'dP!p!q'g~'jP!v!w'm~'rPT~!k!l'u~'xP!h!i&Y~(OP!h!i(R~(WPT~!u!v&Y~(^Q!c!d(d!q!r(j~(gP!t!u&w~(mP!i!j&Y~(sR!c!d(|!k!l)S!q!r&_~)PP!z!{&Y~)VP!p!q&Y~)]P!t!u&Y~)cQ!q!r)i!t!u)u~)lP!y!z)o~)rP!g!h)Y~)xP!q!r){~*OP!f!g*R~*UP!w!x*X~*[P!e!f*_~*bP!v!w&Y~*hP!q!r*k~*nP!w!x*q~*tP!p!q&_~*zQ!s!t+Q!w!x+W~+TP!t!u*_~+ZP!o!p&Y~+aR#U#V+j#b#c+p#j#k+v~+mP#g#h&Y~+sP#W#X&Y~+yP#X#Y+|~,PP#f#g,S~,VP#T#U,Y~,]P#Z#[,`~,cP#X#Y&Y~,iP#c#d,l~,oP#i#j,r~,uP#b#c,x~,{P#h#i-O~-TPT~#]#^-W~-ZP#Y#Z&Y~-aP#Y#Z-d~-iPT~#g#h&Y~-oQ#T#U-u#c#d-{~-xP#f#g,Y~.OP#Z#[&Y~.UR#T#U._#]#^.e#c#d+p~.bP#l#m&Y~.hP#b#c&Y~.nP#f#g&Y~.tQ#c#d.z#f#g/W~.}P#k#l/Q~/TP#X#Y.k~/ZP#c#d/^~/aP#W#X/d~/gP#i#j/j~/mP#V#W/p~/sP#h#i&Y~/yP#c#d/|~0PP#i#j0S~0VP#b#c+p~0]Q#e#f0c#i#j0i~0fP#f#g/p~0lP#a#b&Y",
    tokenizers: [0],
    topRules: { 'Program': [0, 2] },
    tokenPrec: 0
});

/*
自定义语言配置
*/
const myLanguage = LRLanguage.define({
    parser: parser.configure({
        props: [
            indentNodeProp.add({
                Application: delimitedIndent({ closing: ')', align: false })
            }),
            foldNodeProp.add({
                Application: foldInside
            }),
            styleTags({
                Identifier: tags.variableName,
                Boolean: tags.bool,
                String: tags.string,
                Keyword: tags.keyword,
                LineComment: tags.lineComment,
                '( )': tags.paren
            })
        ]
    }),
    languageData: {
        commentTokens: { line: ';' }
    }
});

// 关键词提醒
export const exampleCompletion = myLanguage.data.of({
    autocomplete: completeFromList([
        snippetCompletion('SUM(${})', { label: 'sum', type: 'keyword' }),
        snippetCompletion('SQRT(${})', { label: 'sqrt', type: 'keyword' }),
        snippetCompletion('LOG(${})', { label: 'log', type: 'keyword' }),
        snippetCompletion('MAX(${})', { label: 'max', type: 'keyword' }),
        snippetCompletion('MIN(${})', { label: 'min', type: 'keyword' }),
        snippetCompletion('ABS(${})', { label: 'abs', type: 'keyword' }),
        snippetCompletion('LARGE(${})', { label: 'large', type: 'keyword' }),
        snippetCompletion('COUNT(${})', { label: 'count', type: 'keyword' }),
        snippetCompletion('COUNTIF(${})', { label: 'countif', type: 'keyword' }),
        snippetCompletion('AVERAGE(${})', { label: 'average', type: 'keyword' }),
        snippetCompletion('MOD(${})', { label: 'mod', type: 'keyword' }),
        snippetCompletion('POWER(${})', { label: 'power', type: 'keyword' }),
        snippetCompletion('PRODUCT(${})', { label: 'product', type: 'keyword' }),
        snippetCompletion('IF(${})', { label: 'if', type: 'keyword' }),
        snippetCompletion('IFS(${})', { label: 'ifs', type: 'keyword' }),
        snippetCompletion('OR(${})', { label: 'or', type: 'keyword' }),
        snippetCompletion('AND(${})', { label: 'and', type: 'keyword' }),
    ])
});

// 自定义 Atomic Ranges https://codemirror.net/examples/decoration/
const placeholderMatcher = new MatchDecorator({
    // regexp: /\[\[(\w+)\]\]/g, // 原有逻辑
    regexp: /\[\[(.+?)\]\]/g, // 支持中文
    decoration: (match) =>
        Decoration.replace({
            widget: new PlaceholderWidget(match[1]),
        }),
});

export const placeholders = ViewPlugin.fromClass(
    class {
        placeholders: DecorationSet;
        constructor(view: EditorView) {
            this.placeholders = placeholderMatcher.createDeco(view);
        }
        update(update: ViewUpdate) {
            this.placeholders = placeholderMatcher.updateDeco(
                update,
                this.placeholders
            );
        }
    },
    {
        decorations: (instance) => instance.placeholders,
        provide: (plugin) =>
            EditorView.atomicRanges.of((view) => {
                return view.plugin(plugin)?.placeholders || Decoration.none;
            }),
    }
);

// 自定义主题
export const baseTheme = EditorView.baseTheme({
    '.cm-mywidget': {
        paddingLeft: '6px',
        paddingRight: '6px',
        paddingTop: '3px',
        paddingBottom: '3px',
        marginLeft: '3px',
        marginRight: '3px',
        backgroundColor: '#409eff',
        borderRadius: '4px',
    },
    '.ͼb':{
        color: 'rgb(99, 226, 183)'
    },
});

class PlaceholderWidget extends WidgetType {
    private name: any;
    constructor(name) {
        super();
        this.name = name;
    }
    eq(other) {
        return this.name === other.name;
    }
    toDOM() {
        const elt = document.createElement('span');
        elt.style.cssText = `
      padding: 0 3px;
      background: rgba(112, 192, 232, 0.16);
      color:rgb(112, 192, 232)`;
        elt.textContent = this.name;
        return elt;
    }
    ignoreEvent() {
        return false;
    }
}

function excelLang() {
    return new LanguageSupport(myLanguage, [exampleCompletion]);
}

export { excelLang, myLanguage };

思维导图

Excel公式计算

安装 Vue-Codemirror6

安装 Hyperformula

示例代码

安装 Vue-Codemirror6 ​

安装 Hyperformula ​

示例代码 ​

安装 Vue-Codemirror6

安装 Hyperformula

示例代码