WPS版Excel常用JS宏记录

Adam · · ·

WPS宏开发文档

https://open.wps.cn/docs/client/wpsLoad

PS 本文只讨论宏的实现，不讨论其他实现方式，例如某些场景也可用函数来解决

1. 自动生成序号，支持合并单元格

作用：支持对选中的单一列的单元格生成连续的序号，可包含合并单元格。
用法：选中要生成序号的单元格（同一列），然后运行函数即可。

      function 生成序号() {
          let range = Application.Selection;
          let lastRowIdx = null;
          let idx = 1;
          let lastIsMerged = false;
          for (let cell of range) {
              if (cell.MergeCells) {
                  let ma = cell.MergeArea;
                  let current = ma.Item(1,1);
                  current.Value2 = idx;
                  if (lastIsMerged && lastRowIdx != null && lastRowIdx != current.Row) {
                      idx++;
                  }
                  lastRowIdx = current.Row;
                  lastIsMerged = true;
              } else {
                  if (lastIsMerged) {
                      idx++;
                  }
                  cell.Value2 = idx;
                  idx++;
                  lastRowIdx = cell.Row;
                  lastIsMerged = false;
              }
          }
          
          alert("执行完成");
      }

2. 从不同sheet之间复制匹配数据

适用于如下情况，想把两个表格的数据合到一个表格中。例如：

想把sheet3中的语文分数、数学分数、英语分数，填入到 sheet2 中对应的单元格中。就可以使用如下代码。

function 从不同sheet之间匹配提取数据_fast() {
    let conf = {
        sourceSheet: "复制567月合并数据",// 源sheet名称，
        targetSheet: "ETC7月",//目标sheet名称，即 要将 targetSheet 中对应的数据，copy到 sourceSheet 中去
        sourceTitleRow: 1, //标题行号，第一行为1， 依次类推
        targetTitleRow: 1,
        markMatchedTargetRow: true,//是否要将目标sheet中匹配上的数据标记颜色
        markColor: 4,// 1 黑色，2白色，3红色,4浅绿色，5蓝色，6黄色，7粉红，8青色，靛蓝，9褐色，10深绿，11 深蓝
        idColumns: ["运单号"],//用哪一列数据作为唯一标记来对比
        matchColumns: [//匹配列标题， 源sheet标题 -> 目标sheet标题
            {
                src: "ETC金额2",//源sheet标题
                dest: "金额", //目标sheet标题
                multiStrategy: "sum",//多条记录的策略， sum为求和， first为取第一个, join 为多条拼接
                joinChar: "," 
            }
        ]
    };

    // // =================实现逻辑=============================
    let rowCount = 0;
    let columnCount = 0;
    for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择目标sheet
        if (Application.Worksheets.Item(i).Name == conf.targetSheet) {
            Application.Worksheets.Item(i).Select();
            Application.Sheets.Item(i).Activate();
            rowCount = Application.Worksheets.Item(i).UsedRange.Cells.Rows.Count;
            columnCount = Application.Worksheets.Item(i).UsedRange.Cells.Columns.Count;
        }
    }

    let targetSheetData = {};//读取目标sheet数据
    /**
    *targetSheetData = {
        "keys": [
            {
                data: {
                    "title1":xxx
                },
                idData: {
                    "title":xxx
                },
                metaInfo: {
                    rowNum: r,
                    matched: false
                }
            }
        ]
    }
    */
    let targetTitleInfo = {};
//	let rowCount = Range('a65536').End(xlUp).Row;//获取sheet的行数
//	let columnCount = Range('IV1').End(xlToLeft).Column;//获取sheet的列数
    for (let r = 1; r <= rowCount; r++) {
        if (r == conf.targetTitleRow) {
            for (let c = 1; c <= columnCount; c++) {
                targetTitleInfo[Application.Cells.Item(r, c).Value2] = c;
            }

            //校验标题是否在sheet中存在
            let checkTitles = [];
            for (let idColumn of conf.idColumns) {
                checkTitles.push(idColumn);
            }

            for (let column of conf.matchColumns) {
                checkTitles.push(column.dest);
            }

            for (let column of checkTitles) {
                if (!targetTitleInfo.hasOwnProperty(column)) {
                    alert("Sheet [" + conf.targetSheet + "] 中未找到标题 [" + column + "] !");
                    return;
                }
            }

        } else {
            if (Object.keys(targetTitleInfo).length == 0) {
                continue;
            }

            let rowData = {
                data: {},
                idData: {},
                metaInfo: {
                    rowNum: r,
                    matched: false
                }
            };

            for (let title in targetTitleInfo) {
                rowData.data[title] = Application.Cells.Item(r, targetTitleInfo[title]).Value2;
            }

            let key = "";
            for (let idColumn of conf.idColumns) {
                rowData.idData[idColumn] = Application.Cells.Item(r, targetTitleInfo[idColumn]).Value2;
                key = key + "-" + Application.Cells.Item(r, targetTitleInfo[idColumn]).Value2;
            }
            if (!targetSheetData.hasOwnProperty(key)) {
                targetSheetData[key] = [];
            }
            targetSheetData[key].push(rowData);
        }
    }

    console.log(targetSheetData);

    for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择源sheet
        if (Application.Worksheets.Item(i).Name == conf.sourceSheet) {
            Application.Worksheets.Item(i).Select();
            Application.Sheets.Item(i).Activate();
            rowCount = Application.Worksheets.Item(i).UsedRange.Cells.Rows.Count;
            columnCount = Application.Worksheets.Item(i).UsedRange.Cells.Columns.Count;
        }
    }

    let sourceTitleInfo = {};
//	rowCount = Range('a65536').End(xlUp).Row;
//	columnCount = Range('IV1').End(xlToLeft).Column;
//  遍历源sheet数据，然后从targetSheetData中挑选出匹配数据
    for (let r = 1; r <= rowCount; r++) {
        if (r == conf.sourceTitleRow) {//解析标题
            for (let c = 1; c <= columnCount; c++) {
                sourceTitleInfo[Application.Cells.Item(r, c).Value2] = c;
            }	

            //校验标题是否在sheet中存在
            let checkTitles = [];
            for (let idColumn of conf.idColumns) {
                checkTitles.push(idColumn);
            }

            for (let column of conf.matchColumns) {
                checkTitles.push(column.src);
            }

            for (let column of checkTitles) {
                if (!sourceTitleInfo.hasOwnProperty(column)) {
                    alert("Sheet [" + conf.targetSheet + "] 中未找到标题 [" + column + "] !");
                    return;
                }
            }

        } else {
            if (Object.keys(sourceTitleInfo).length == 0) {
                continue;
            }


            //从目标sheet数据中筛选匹配数据
            let key = "";
            for (let idColumn of conf.idColumns) {
                key = key + "-" + Application.Cells.Item(r, sourceTitleInfo[idColumn]).Value2; 
            }
            let matchedTargetRowData = targetSheetData[key];

            //根据不同策略，填入数据
            if (matchedTargetRowData != null && matchedTargetRowData.length > 0) {
                for (let matchColumn of conf.matchColumns) {
                    let finalVal = null;
                    switch (matchColumn.multiStrategy) {
                        case "sum":
                            finalVal = 0;
                            for (let targetRowData of matchedTargetRowData) {
                                targetRowData.metaInfo.matched = true;
                                let tempVal = targetRowData.data[matchColumn.dest];
                                if (tempVal != null && tempVal != '' && tempVal != undefined) {
                                    finalVal = finalVal + tempVal;
                                }
                            }
                            break;
                        case "join": 
                            finalVal = "";
                            let matchedValues = [];
                            for (let targetRowData of matchedTargetRowData) {
                                targetRowData.metaInfo.matched = true;
                                let tempVal = targetRowData.data[matchColumn.dest];
                                if (tempVal != null && tempVal != '' && tempVal != undefined) {
                                    matchedValues.push(tempVal);
                                }
                            }
                            finalVal = matchedValues.join(matchColumn.joinChar);
                            if (matchedValues.length > 1) {
                                Application.Cells.Item(r, sourceTitleInfo[matchColumn.src]).Interior.ColorIndex = conf.markColor;
                            }
                            break;
                        case "first":
                            targetLoop: for (let targetRowData of matchedTargetRowData) {
                                let tempVal = targetRowData.data[matchColumn.dest];
                                if (tempVal != null && tempVal != '' && tempVal != undefined) {
                                    finalVal = tempVal;
                                    targetRowData.metaInfo.matched = true;
                                    break targetLoop;
                                }
                            }
                    }

                    Application.Cells.Item(r, sourceTitleInfo[matchColumn.src]).Value2 = finalVal;
                }
            }
        }
    }

    if (conf.markMatchedTargetRow) {
        for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择目标sheet
            if (Application.Worksheets.Item(i).Name == conf.targetSheet) {
                Application.Worksheets.Item(i).Select();
                Application.Sheets.Item(i).Activate();
                rowCount = Application.Worksheets.Item(i).UsedRange.Cells.Rows.Count;
                columnCount = Application.Worksheets.Item(i).UsedRange.Cells.Columns.Count;
            }
        }

        for (let key in targetSheetData) {
            for (let arr of targetSheetData[key]) {
                if (arr.metaInfo.matched) {
                    for (let c = 1; c <= columnCount; c++) {
                        Application.Cells.Item(arr.metaInfo.rowNum, c).Interior.ColorIndex = conf.markColor;// 1 黑色，2白色，3红色,4浅绿色，5蓝色，6黄色，7粉红，8青色，靛蓝，9褐色，10深绿，11 深蓝
                    }
                }
            }
        }
    }

    for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择目标sheet
          if (Application.Worksheets.Item(i).Name == conf.sourceSheet) {
              Application.Worksheets.Item(i).Select();
              Application.Sheets.Item(i).Activate();
          }
      }


    alert("执行完成");
}

3. 在多个sheet中查找重复数据

用途：根据指定的匹配列，查找重复数据，并在第一个sheet中将重复数据用背景色标记出来。JS代码如下：

function 从不同Sheet中查找重复数据() {
    let conf = {
        sheetNames: ["ETC6月", "ETC7月"],
        idColumns: ["运单号"],//用哪一列数据作为唯一标记来对比
        titleRow: 1, //目标行号，从1开始
        markColor: 4,// 将目标sheet中匹配上的数据标记颜色, 1 黑色，2白色，3红色,4浅绿色，5蓝色，6黄色，7粉红，8青色，靛蓝，9褐色，10深绿，11 深蓝
    };

    /**
    *  
    {
        "key1": [
            {
                data: {
                    "title1":"xxx"
                },
                idData: {
                    "title2":"xxx"
                },
                metaInfo: {
                    rowNum: r,
                    matched: false
                }	
            }
        ]
    }
    */
    function readSheetData(sheetName) {
        let rowCount = 0;
        let columnCount = 0;
        for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择目标sheet
            if (Application.Worksheets.Item(i).Name == sheetName) {
                Application.Worksheets.Item(i).Select();
                Application.Sheets.Item(i).Activate();
                rowCount = Application.Worksheets.Item(i).UsedRange.Cells.Rows.Count;
                columnCount = Application.Worksheets.Item(i).UsedRange.Cells.Columns.Count;
            }
        }

        let ret = {};
        let titleInfo = {};
        for (let r = 1; r <= rowCount; r++) {
            if (r == conf.titleRow) {
                for (let c = 1; c <= columnCount; c++) {
                    titleInfo[Application.Cells.Item(r, c).Value2] = c;
                }

                //校验标题是否在sheet中存在
                for (let idColumn of conf.idColumns) {
                    if (!titleInfo.hasOwnProperty(idColumn)) {
                        alert("Sheet [" + sheetName + "] 中未找到标题 [" + idColumn + "] !");
                        return;
                    }
                }
            } else {
                if (Object.keys(titleInfo).length == 0) {
                    continue;
                }

                let rowData = {
                    data: {},
                    idData: {},
                    metaInfo: {
                        rowNum: r,
                        matched: false
                    }
                };

                for (let title in titleInfo) {
                    rowData.data[title] = Application.Cells.Item(r, titleInfo[title]).Value2;
                }

                let key = "";
                for (let idColumn of conf.idColumns) {
                    rowData.idData[idColumn] = Application.Cells.Item(r, titleInfo[idColumn]).Value2;
                    key = key + "-" + Application.Cells.Item(r, titleInfo[idColumn]).Value2;
                }
                if (!ret.hasOwnProperty(key)) {
                    ret[key] = [];
                }
                ret[key].push(rowData);
            }
        }

        return ret;
    }

    let sheetDatas = [];
    for (let sheetName of conf.sheetNames) {
        sheetDatas.push(readSheetData(sheetName));
    }

    let rowCount  = 0;
    let columnCount = 0;
    for (let i = 1; i <= Application.Worksheets.Count; i++) {//选择目标sheet
        if (Application.Worksheets.Item(i).Name == conf.sheetNames[0]) {
            Application.Worksheets.Item(i).Select();
            Application.Sheets.Item(i).Activate();
            rowCount = Application.Worksheets.Item(i).UsedRange.Cells.Rows.Count;
            columnCount = Application.Worksheets.Item(i).UsedRange.Cells.Columns.Count;
        }
    }

    let firstSheetDatas = sheetDatas[0];
    for (let key in firstSheetDatas) {
        let match = true;
        for (let i = 1; i < sheetDatas.length; i++) {
            if (!sheetDatas[i].hasOwnProperty(key)) {
                match = false;
            }
        }
        if (match) {
            let rowDatas = firstSheetDatas[key];
            for (let rowData of rowDatas) {
                for (let c = 1; c <= columnCount; c++) {
                    Application.Cells.Item(rowData.metaInfo.rowNum, c).Interior.ColorIndex = conf.markColor;	
                }
            }
        }
    }

    alert("执行完成");
}

4.纵向合并单元格

用途：可基于某一列数据，对相同列不同行的数据进行合并。
用法：在要合并的sheet中，运行如下代码。

function 纵向合并单元格() {
    let conf = {
        titleRow: 1,//标题行
        baseColumn: "承运人名称",//根据此列数据进行合并，将此列数据相同的 同列不同行的单元格进行合并
        mergeColumn: {//要合并的标题列
            "承运人名称": "first",
            "承运人电话":"first",//如果有多个数据，first 表示取第一个， sum 表示求和， last表示取最后一个
            "承运人运费":"sum",
            "货物重量":"sum",
            "货物体积":"sum"
        }
    };

    let parseNumber = function(val) {
        if (typeof val == 'number' && !isNaN(val)) {
            return val;
        }

        if (typeof val == 'string') {
            if (val.indexOf(".") != -1) {
                return parseFloat(val);
            } else {
                return parseInt(val);
            }
        }

        return 0;
    };

    let mergeCell = function(titleInfo, startIndex, endIndex) {
        for (let prop in conf.mergeColumn) {
            let columnIndex = titleInfo[prop];
            let strategy = conf.mergeColumn[prop];
            if (strategy == null || strategy == "" || strategy == undefined) {
                strategy = "first";
            }

            let finalValue = null;
            switch (strategy) {
                case "first":
                    finalValue = Application.Cells.Item(startIndex, columnIndex).Value2;
                    break;
                case "last":
                    finalValue = Application.Cells.Item(endIndex, columnIndex).Value2;
                    break;
                case "sum": 
                    finalValue = 0;
                    for (let r = startIndex; r <= endIndex; r++) {
                        finalValue += parseNumber(Application.Cells.Item(r, columnIndex).Value2);
                    }
                    break;
                default:
                    finalValue = Application.Cells.Item(startIndex, columnIndex).Value2;
            }

            Application.ActiveSheet.Range(Application.Cells.Item(startIndex,columnIndex), Application.Cells.Item(endIndex,columnIndex)).Merge();
            Application.Cells.Item(startIndex,columnIndex).Value2 = finalValue;
        }
    };

    let rowCount = ActiveSheet.UsedRange.Cells.Rows.Count;
    let columnCount = ActiveSheet.UsedRange.Cells.Columns.Count;

    let titleInfo = {};
    let rangeStartInfo = {
        row: -1,
        val: null
    };
    for (let r = 1; r <= rowCount; r++) {
        if (r == conf.titleRow) {
            for (let c = 1; c <= columnCount; c++) {
                titleInfo[Application.Cells.Item(conf.titleRow, c).Value2] = c;
            }

            continue;
        }

        if (Object.keys(titleInfo).length == 0) {
            continue;
        }

        let cell = Application.Cells.Item(r, titleInfo[conf.baseColumn]);
        if (rangeStartInfo.row == -1 || rangeStartInfo.val == null || rangeStartInfo.val == undefined) {
            rangeStartInfo.row = r;
            rangeStartInfo.val = cell.Value2;
            continue;
        }

        if (rangeStartInfo.val == cell.Value2) {
            continue;
        }

        if (rangeStartInfo.val != cell.Value2 && rangeStartInfo.row != -1 && rangeStartInfo.row < r) {
            if (r - rangeStartInfo.row > 1) {
                mergeCell(titleInfo, rangeStartInfo.row, r - 1);
            }
            rangeStartInfo.row = r;
            rangeStartInfo.val = cell.Value2;
        }
    }

    if (rowCount - rangeStartInfo.row > 1) {
        mergeCell(titleInfo, rangeStartInfo.row, rowCount);
    }
    alert("执行完成");
}

Q.E.D.

openssl: error while loading shared libraries: libssl.so.3: cannot open shared object file:

如何在 Nginx 中为 Cookie 设置 HTTPOnly / Secure / SameSite 属性

Adam

Adam

Hi,Friend

切换主题 | SCHEME TOOL