WPS 本地表与在线表交互_本地表代码

// ==============================// 配置常量（仅在此处修改即可全局生效）// ==============================// ---- API 接口配置 ----const API_URL = "XXX";const AIR_SCRIPT_TOKEN = "XXX";const CONTENT_TYPE = "application/json";// ---- 系统基础配置 ----const sysDataSheetName = "sysData";       // 系统数据存储工作表名const MAIN_SHEET_NAME = "Sheet1";         // 业务工作表名// ---- 账号相关配置 ----let user = "";                            // 当前登录用户let INITIAL_PASSWORD = "XXX";           // 初始密码// ---- 全局变量 ----let rib;let wb = ThisWorkbook;let sh1 = wb.Sheets(MAIN_SHEET_NAME);// ---- sysData 工作表配置 ----const SYS_USER_COL = 1;      // sysData工作表：用户名列const SYS_PWD_COL = 2;       // sysData工作表：密码列const SYS_DATA_ROW = 2;      // sysData工作表：数据起始行，第几行开始存储const SYS_TITLE_ROW = 1;     // sysData工作表：标题行const SYS_USERNAME_HEADER = "UserName"; // sysData工作表：用户名表头const SYS_PASSWORD_HEADER = "Password"; // sysData工作表：密码表头// ---- Sheet1 工作表配置 ----const SHEET1_RECORD_ID_COL = 1;      // Sheet1工作表：记录ID列const SHEET1_RECORD_TIME_COL = 2;    // Sheet1工作表：记录时间列const SHEET1_RECORD_ACCOUNT_COL = 3; // Sheet1工作表：记录账号列const SHEET1_UNIT_COL = 4;           // Sheet1工作表：记录单位列const SHEET1_MAJOR_COL = 6;          // Sheet1工作表：报读专业列const SHEET1_AUTO_FILL_COLS = [SHEET1_RECORD_ID_COL, SHEET1_RECORD_TIME_COL, SHEET1_RECORD_ACCOUNT_COL]; // Sheet1工作表：自动填充列const SHEET1_DATA_ROW = 2;           // Sheet1工作表：数据起始行const SHEET1_START_COL = "a";        // Sheet1工作表：自动填充起始列字母const SHEET1_END_COL = "c";          // Sheet1工作表：自动填充结束列字母const SHEET1_DATA_RANGE = "a:h";     // Sheet1工作表：数据范围const SHEET1_START_CELL = "a1";      // Sheet1工作表：起始单元格// ---- 功能开关 ----let config = {};let isRegisterEnabled = () => {return config.isRegisterEnabled};   // 启用注册，由在线表决定let isSubmitEnabled = () => {return config.isSubmitEnabled};       // 启用提交，由在线表决定// ---- 数据验证下拉列表 ----const validateList1 = "单位1,单位2,单位3,单位4,单位5";const validateList2 = "JSA开发,VBA开发,会计实务";// ---- 固定提示信息 ----const MSG_WELCOME_TITLE = "说明";let MSG_WELCOME = () => {return `1.须连接互联网；\n2.须使用本地WPS程序打开工作簿，JS脚本才能正常运行；\n3.使用的账号是XXX为每个使用者主动分配的账号，初始密码${INITIAL_PASSWORD}。不是采购网、政采云、wps账号；\n4.流程：【打开文件】  ->  【登录】（自动查询下载记录，未登录禁用查询下载）  ->  【增、改、删】（未登录禁用增改删）  ->  【保存】（自动提交上传记录，未登录禁用提交上传，未成功提交上传记录取消保存）  ->  【关闭文件】（未成功提交上传记录取消关闭）。\n\n本次${isRegisterEnabled() ? "启用" : "禁用"}注册账号。\n本次${isSubmitEnabled() ? "启用" : "禁用"}提交记录。`};const MSG_RETRY_LATER = "请稍后重试！";const MSG_SERVER_ERROR = "在线表AirScript脚本运行错误！";const MSG_CONFIG_GET_FAILED_RETRY = "获取配置失败，请稍后重试！";const MSG_CONFIG_GET_FAILED_SCRIPT = "获取配置失败，在线表AirScript脚本运行错误！";const MSG_NO_LOGIN = "未登录账号！请登录账号后再增加、修改、删除记录！";const MSG_NO_LOGIN_QUERY = "未登录账号！请登录账号后再查询记录！";const MSG_NO_LOGIN_SUBMIT = "未登录账号！请登录账号后再提交记录！";const MSG_NO_LOGIN_MODIFY = "未登录账号！请登录账号后再修改密码！";const MSG_CONTINUE_TITLE = "是否继续？";let MSG_SWITCH_ACCOUNT = () => {return `工作簿当前账号${isSubmitEnabled() ? "将" : "不会"}提交记录，工作簿将用于存放其他账号记录，是否继续？`};const MSG_INPUT_EMPTY = "账号或密码不能为空！";const MSG_INVALID_CREDENTIALS = "账号或密码不正确！";const MSG_LOGIN_FAILED_RETRY = "登录失败，请稍后重试！";const MSG_LOGIN_FAILED_SERVER = "登录失败，在线表AirScript脚本运行错误！";const MSG_SUCCESS_LOGIN = "登录成功！";const MSG_ACCOUNT_EXISTS = "账号已存在！";const MSG_REGISTER_FAILED_RETRY = "注册失败，请稍后重试！";const MSG_REGISTER_FAILED_SERVER = "注册失败，在线表AirScript脚本运行错误！";const MSG_SUCCESS_REGISTER = "注册成功！";const MSG_PWD_EMPTY = "密码不能为空！";const MSG_PWD_MISMATCH = "新密码两次输入不相同，请重新输入！";const MSG_CURRENT_PWD_WRONG = "当前密码不正确，请重新输入！";const MSG_MODIFY_FAILED_RETRY = "修改密码失败，请稍后重试！";const MSG_MODIFY_FAILED_SERVER = "修改密码失败，在线表AirScript脚本运行错误！";const MSG_SUCCESS_MODIFY = "修改密码成功！";const MSG_ERROR_TITLE = "错误";const MSG_SAVE_FAILED = "保存账号和密码失败！";const MSG_QUERY_FAILED_RETRY = "查询记录失败，请稍后重试！";const MSG_QUERY_FAILED_SERVER = "查询记录失败，在线表AirScript脚本运行错误！";const MSG_SUCCESS_QUERY = "查询记录成功！";const MSG_NO_DATA = "没有记录，不需要提交记录！";const MSG_INCOMPLETE_RECORD = "不能提交记录！每条记录的“记录ID、记录时间、记录账号、记录单位”不能为空。请填写完整后重新提交记录！";const MSG_SUBMIT_FAILED_RETRY = "提交记录失败，请稍后重试！";const MSG_SUBMIT_FAILED_SERVER = "提交记录失败，在线表AirScript脚本运行错误！";const MSG_SUCCESS_SUBMIT = "提交记录成功！";const MSG_SUBMIT_DISABLED = "已禁用提交记录。本次增改删不会提交记录，只保存在工作簿中，下次打开工作簿，查询下载的记录将会覆盖工作簿记录。";const MSG_SAVE_CONFIRM_TITLE = "是否保存文档？";let MSG_SAVE_CONFIRM = `是否保存对 “${wb.Name}” 的更改？`;// ---- 模块名称常量 ----const GETCONFIG = "获取配置";const MODULE_LOGIN = "登录";const MODULE_REGISTER = "注册";const MODULE_MODIFY_PWD = "修改密码";const MODULE_QUERY = "查询记录";const MODULE_SUBMIT = "提交记录";// ---- 格式与通用常量 ----const ACCOUNT_LABEL_PREFIX = "当前登录账号：";const TEXT_FORMAT = "@";const DATE_FORMAT = "yyyy-mm-dd hh:mm:ss";const ID_PREFIX = "id";                          // 记录ID前缀const RANDOM_MAX = 999999;                       // 随机数最大值，需要几位就设置几个9let RANDOM_DIGITS = String(RANDOM_MAX).length; // 随机数位数// ==============================// 工具函数// ==============================function onLoad(ribbon) {    rib = ribbon;}function createHighOrderNo() { // 标识符    const time = new Date().getTime();    const random = String(Math.floor(Math.random() * RANDOM_MAX)).padStart(RANDOM_DIGITS, "0");    return `${ID_PREFIX}${time}${random}`;}function encodeText(text) { // 混淆（位移 +3）    let result = "";    for (let i = 0; i < text.length; i++) {        result += String.fromCharCode(text.charCodeAt(i) + 3);    }    return result;}function decodeText(text) { // 解除混淆（位移 -3）    let result = "";    for (let i = 0; i < text.length; i++) {        result += String.fromCharCode(text.charCodeAt(i) - 3);    }    return result;}async function conn(body) {    let res = await fetch(API_URL, {        method: "post",        headers: {            "content-type": CONTENT_TYPE,            "AirScript-Token": AIR_SCRIPT_TOKEN,        },        body: JSON.stringify(body)    });    let data = await res.json();    return data.data.result;}async function getConfig() { // 获取配置模块    let body = {        Context: {            argv: {                module: GETCONFIG            }        }    };    let result = await conn(body);    if (result == MSG_RETRY_LATER) {        alert(MSG_CONFIG_GET_FAILED_RETRY);        return;    }    if (result == MSG_SERVER_ERROR) {        alert(MSG_CONFIG_GET_FAILED_SCRIPT);        return;    }    return result;}// ==============================// 工作簿事件// ==============================async function Workbook_Open() { // 该工作簿打开事件    Application.Visible = false; // wb.Windows(1).Visible = false;    config = await getConfig();    MsgBox(MSG_WELCOME(), 0, MSG_WELCOME_TITLE);    UserForm1.Show(false);}async function Workbook_BeforeSave(SaveAsUI, Cancel) { // 该工作簿保存之前事件    if (isSubmitEnabled()) {        let result = await button3_onAction();        if (result !== MSG_SUCCESS_SUBMIT && result !== MSG_NO_DATA) { // 提交失败 不保存            Cancel.Value = true; // 取消保存            return;        }    } else {        alert(MSG_SUBMIT_DISABLED);    }}async function Workbook_BeforeClose(Cancel) { // 该工作簿关闭之前事件    if (wb.Saved) return;    let ret = MsgBox(MSG_SAVE_CONFIRM, jsYesNoCancel + jsQuestion, MSG_SAVE_CONFIRM_TITLE);    if (ret === 6) { // 是        Cancel.Value = true; // 避免系统二次弹窗        if (isSubmitEnabled()) {            let result = await button3_onAction();            if (result == MSG_SUCCESS_SUBMIT || result == MSG_NO_DATA) { // 提交成功（保存 + 关闭）                Application.EnableEvents = false;                wb.Save();                Application.EnableEvents = true;                wb.Close(false);                return;            } else { // 提交失败（不保存 + 不关闭）                Cancel.Value = true; // 取消关闭                return;            }        } else {            alert(MSG_SUBMIT_DISABLED);            Application.EnableEvents = false;            wb.Save();            Application.EnableEvents = true;            wb.Close(false);            return;        }    }    if (ret === 7) { // 否        wb.Saved = true; // 标记为已保存        return;    }    if (ret === 2) { // 取消        Cancel.Value = true; // 取消关闭        return;    }}// ==============================// 工作表事件// ==============================function Workbook_SheetSelectionChange(Sh, Target) { // 单元格选择事件 主要设置：填充已知列、设置数据有效性    if (user == "") {        UserForm1.Show(false);        alert(MSG_NO_LOGIN);        return MSG_NO_LOGIN;    }    if (Sh.Name != sh1.Name || Target.CountLarge > 1 || Target.Row < SHEET1_DATA_ROW) return;    if (SHEET1_AUTO_FILL_COLS.includes(Target.Column)) { // ABC列 填充已知列        let r = Target.Row;        const rangeStr = `${SHEET1_START_COL}${r}:${SHEET1_END_COL}${r}`;        for (let cell of sh1.Range(rangeStr).Cells) {            let vlt = cell.Validation;            vlt.Delete();        }        sh1.Cells.Item(r, SHEET1_RECORD_ID_COL).Value2 ??= createHighOrderNo();        sh1.Cells.Item(r, SHEET1_RECORD_TIME_COL).Value2 ??= WorksheetFunction.Text(new Date().toLocaleString(), DATE_FORMAT);        sh1.Cells.Item(r, SHEET1_RECORD_ACCOUNT_COL).Value2 ??= user;    }    if (Target.Column == SHEET1_RECORD_ID_COL && Target.Value2 == undefined) { // A列 记录ID 设置数据有效性        let vlt = Target.Validation;        vlt.Delete();        vlt.Add(xlValidateList, xlValidAlertStop, undefined, createHighOrderNo());    }    if (Target.Column == SHEET1_RECORD_TIME_COL && Target.Value2 == undefined) { // B列 记录时间 设置数据有效性        let vlt = Target.Validation;        vlt.Delete();        vlt.Add(xlValidateList, xlValidAlertStop, undefined, "'" + WorksheetFunction.Text(new Date().toLocaleString(), DATE_FORMAT));    }    if (Target.Column == SHEET1_RECORD_ACCOUNT_COL && Target.Value2 == undefined) { // C列 记录账号 设置数据有效性        let vlt = Target.Validation;        vlt.Delete();        vlt.Add(xlValidateList, xlValidAlertStop, undefined, user);    }    if (Target.Column == SHEET1_UNIT_COL && Target.Value2 == undefined) { // D列 记录单位 设置数据有效性        let vlt = Target.Validation;        vlt.Delete();        vlt.Add(xlValidateList, xlValidAlertStop, undefined, validateList1);    }    if (Target.Column == SHEET1_MAJOR_COL && Target.Value2 == undefined) { // F列 报读专业 设置数据有效性        let vlt = Target.Validation;        vlt.Delete();        vlt.Add(xlValidateList, xlValidAlertStop, undefined, validateList2);    }}// ==============================// 功能区按钮事件// ==============================async function button_onAction() { // 切换账号模块    let ret = MsgBox(MSG_SWITCH_ACCOUNT(), jsYesNo + jsInformation + jsDefaultButton1, MSG_CONTINUE_TITLE);    if (ret == 7) return; // 7 否    if (isSubmitEnabled()) {        let result = await button3_onAction();        if (result == MSG_SUCCESS_SUBMIT || result == MSG_NO_DATA) { // 提交成功            sh1.Range(SHEET1_DATA_RANGE).ClearContents();            UserForm1.Show(false);            return;        }    } else {        alert(MSG_SUBMIT_DISABLED);        sh1.Range(SHEET1_DATA_RANGE).ClearContents();        UserForm1.Show(false);    }}async function button2_onAction() { // 查询记录模块    if (user == "") {        UserForm1.Show(false);        alert(MSG_NO_LOGIN_QUERY);        return MSG_NO_LOGIN_QUERY;    }    let body = {        Context: {            argv: {                module: MODULE_QUERY,                user: user            }        }    };    let arrFilter = await conn(body);    if (arrFilter == MSG_RETRY_LATER) {        alert(MSG_QUERY_FAILED_RETRY);        return;    }    if (arrFilter == MSG_SERVER_ERROR) {        alert(MSG_QUERY_FAILED_SERVER);        return;    }    sh1.Range(SHEET1_DATA_RANGE).ClearContents();    sh1.Range(SHEET1_DATA_RANGE).Validation.Delete();    sh1.Range(SHEET1_START_CELL).Resize(arrFilter.length, arrFilter[0].length).Value2 = arrFilter;    alert(MSG_SUCCESS_QUERY);}async function button3_onAction() { // 提交记录模块    if (isSubmitEnabled()) {        if (user == "") {            UserForm1.Show(false);            alert(MSG_NO_LOGIN_SUBMIT);            return MSG_NO_LOGIN_SUBMIT;        }        let arr = sh1.Range(SHEET1_START_CELL).CurrentRegion.Value2;        if (arr == undefined) {            alert(MSG_NO_DATA);            return MSG_NO_DATA;        }        arr.shift();        if (arr.length == 0) {            alert(MSG_NO_DATA);            return MSG_NO_DATA;        }        let bool = arr.every(ar => {            return ar[SHEET1_RECORD_ID_COL - 1] != undefined &&                   ar[SHEET1_RECORD_TIME_COL - 1] != undefined &&                    ar[SHEET1_RECORD_ACCOUNT_COL - 1] != undefined &&                    ar[SHEET1_UNIT_COL - 1] != undefined;        });        if (bool) {            let body = {                Context: {                    argv: {                        module: MODULE_SUBMIT,                        user: user,                        arr: arr                    }                }            };            let result = await conn(body);            if (result == MSG_RETRY_LATER) {                alert(MSG_SUBMIT_FAILED_RETRY);                return;            }            if (result == MSG_SERVER_ERROR) {                alert(MSG_SUBMIT_FAILED_SERVER);                return;            }            if (result) {                alert(MSG_SUCCESS_SUBMIT);                return MSG_SUCCESS_SUBMIT;            }        } else {            alert(MSG_INCOMPLETE_RECORD);            return;        }    } else {        alert(MSG_SUBMIT_DISABLED);    }}// ==============================// 窗体及控件事件// ==============================function UserForm1_Initialize() {    UserForm1.Label3.Caption = `${ACCOUNT_LABEL_PREFIX}${user}`;    UserForm1.Label4.Visible = !!user;    UserForm1.CommandButton2.Enabled = isRegisterEnabled();    const info = loadRememberedLogin();    if (info && info.user) {        UserForm1.TextEdit1.Value = info.user;        UserForm1.TextEdit2.Value = info.pwd;        UserForm1.CheckBox1.Value = true;    } else {        UserForm1.CheckBox1.Value = false;    }}async function UserForm1_CommandButton1_Click() { //登录模块    let body = {        Context: {            argv: {                module: MODULE_LOGIN,                user: UserForm1.TextEdit1.Value,                psd: UserForm1.TextEdit2.Value            }        }    };    if (UserForm1.TextEdit1.Value == "" || UserForm1.TextEdit2.Value == "") {        alert(MSG_INPUT_EMPTY);        return;    }    let bool = await conn(body);    if (bool == MSG_RETRY_LATER) {        alert(MSG_LOGIN_FAILED_RETRY);        return;    }    if (bool == MSG_SERVER_ERROR) {        alert(MSG_LOGIN_FAILED_SERVER);        return;    }    if (bool) {        user = UserForm1.TextEdit1.Value;        const pwd = UserForm1.TextEdit2.Value;        if (UserForm1.CheckBox1.Value) {            saveRememberedLogin(user, pwd);        } else {            clearRememberedLogin();        }        UserForm1.Close();        Application.Visible = true; // wb.Windows(1).Visible = true;        alert(MSG_SUCCESS_LOGIN);        await button2_onAction();    } else {        alert(MSG_INVALID_CREDENTIALS);    }}async function UserForm1_CommandButton2_Click() { // 注册模块    let body = {        Context: {            argv: {                module: MODULE_REGISTER,                user: UserForm1.TextEdit1.Value,                psd: UserForm1.TextEdit2.Value            }        }    };    if (UserForm1.TextEdit1.Value == "" || UserForm1.TextEdit2.Value == "") {        alert(MSG_INPUT_EMPTY);        return;    }    let bool = await conn(body);    if (bool == MSG_RETRY_LATER) {        alert(MSG_REGISTER_FAILED_RETRY);        return;    }    if (bool == MSG_SERVER_ERROR) {        alert(MSG_REGISTER_FAILED_SERVER);        return;    }    if (bool) {        alert(MSG_ACCOUNT_EXISTS);    } else {        UserForm1.TextEdit1.Value = "";        UserForm1.TextEdit2.Value = "";        alert(MSG_SUCCESS_REGISTER);    }}function UserForm1_Label4_Click() {    UserForm2.Show(false);}async function UserForm2_CommandButton1_Click() { // 修改密码模块    if (user == "") {        UserForm1.Show(false);        alert(MSG_NO_LOGIN_MODIFY);        return MSG_NO_LOGIN_MODIFY;    }    if (UserForm2.TextEdit1.Value == "" || UserForm2.TextEdit2.Value == "" || UserForm2.TextEdit3.Value == "") {        alert(MSG_PWD_EMPTY);        return;    }    if (UserForm2.TextEdit2.Value != UserForm2.TextEdit3.Value) {        alert(MSG_PWD_MISMATCH);        return;    }    let body = {        Context: {            argv: {                module: MODULE_MODIFY_PWD,                user: user,                psd: UserForm2.TextEdit1.Value,                newPsd: UserForm2.TextEdit2.Value            }        }    };    let bool = await conn(body);    if (bool == MSG_RETRY_LATER) {        alert(MSG_MODIFY_FAILED_RETRY);        return;    }    if (bool == MSG_SERVER_ERROR) {        alert(MSG_MODIFY_FAILED_SERVER);        return;    }    if (bool) {        UserForm2.Close();        alert(MSG_SUCCESS_MODIFY);    } else {        alert(MSG_CURRENT_PWD_WRONG);    }}// ==============================// 系统数据管理（sysData工作表）// ==============================function getSysSheet() { // 获取/创建系统工作表    try {        return wb.Sheets(sysDataSheetName);    } catch (e) {        const sh = wb.Sheets.Add();        sh.Name = sysDataSheetName;        sh.Columns(SYS_USER_COL).NumberFormat = TEXT_FORMAT;        sh.Columns(SYS_PWD_COL).NumberFormat = TEXT_FORMAT;        sh.Cells(SYS_TITLE_ROW, SYS_USER_COL).Value2 = SYS_USERNAME_HEADER;        sh.Cells(SYS_TITLE_ROW, SYS_PWD_COL).Value2 = SYS_PASSWORD_HEADER;        sh.Visible = false;        return sh;    }}function loadRememberedLogin() { // 读取 保存的账号和密码    try {        const sh = getSysSheet();        const userVal = sh.Cells(SYS_DATA_ROW, SYS_USER_COL).Value2;        const pwdVal = sh.Cells(SYS_DATA_ROW, SYS_PWD_COL).Value2;        if (userVal !== undefined && userVal !== "") {            return {                user: String(userVal),                pwd: decodeText(String(pwdVal || ""))            };        }        return null;    } catch (e) {        return null;    }}function saveRememberedLogin(user, pwd) { // 保存 账号和密码    try {        const sh = getSysSheet();        sh.Cells(SYS_DATA_ROW, SYS_USER_COL).NumberFormat = TEXT_FORMAT;        sh.Cells(SYS_DATA_ROW, SYS_PWD_COL).NumberFormat = TEXT_FORMAT;        sh.Cells(SYS_DATA_ROW, SYS_USER_COL).Value2 = String(user);        sh.Cells(SYS_DATA_ROW, SYS_PWD_COL).Value2 = encodeText(String(pwd));        sh.Visible = false;    } catch (e) {        MsgBox(MSG_SAVE_FAILED, 0, MSG_ERROR_TITLE);    }}function clearRememberedLogin() { // 清除 账号和密码    try {        const sh = getSysSheet();        sh.Cells(SYS_DATA_ROW, SYS_USER_COL).NumberFormat = TEXT_FORMAT;        sh.Cells(SYS_DATA_ROW, SYS_PWD_COL).NumberFormat = TEXT_FORMAT;        sh.Cells(SYS_DATA_ROW, SYS_USER_COL).Value2 = "";        sh.Cells(SYS_DATA_ROW, SYS_PWD_COL).Value2 = "";    } catch (e) {}}