WPS表格 JS宏常用工具函数分享

在宏开发过程中我们会有一些非常常见的操作，WPS并没有为我们提供原生的方法，因此，我将我开发过程中经常使用到的一些操作封装成了函数供大家直接使用。代码不一定是最佳实践，水平有限敬请谅解。

1. 将列名转化为数字，将数字转化为列名

//列名数字互相转换
//列名转化为数字
/**
* @param {string} col 
* @returns {number}
*/
function col2Num(col){
    const alphabeta = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    let num = 0
    for(let i = col.length - 1;i>=0;i --){
        const char = col[i]
        let index = alphabeta.split('').findIndex(item => item === char)
        num += (index+1) * (26 ** (col.length - 1 - i))
    }
    return num
}
//数字转化为列名
/**
* @param {number} num 
* @returns {string}
*/
function num2Col(num){
    const alphabeta = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    let col = []
    while(num >= 1){
        num --
        if(!num){
            col.unshift('A')
            continue
        }
        let word = num % 26
        col.unshift(alphabeta[word])
        num = Math.floor(num / 26)
    }
    return col.join('')
}

2. 找到表格的最后一行

我们传入对表单的引用（sheet对象）,判断基于的列(支持通过多列判断，返回这些列最底部的一个行号) 以及开始扫描的行号，函数就会返回该表格存在数据的最后一个行号。需要注意的是，该函数要求表格数据一行一行之间没有空白行，否则会将该空白行作为最后表格末尾。

/**
 * 
 * @param {sheet} sht 
 * @param {string[]} cols 
 * @param {number} begin 
 * @returns 
 */
function touchBottom(sht,cols,begin = 1){
    let base = 1
    while(base < begin) base *= 2
    function ifEmpty(row){
        let empty = true
        for(const col of cols){
            if(sht.Range(`${col}${row}`).Value2){ empty = false; break}
        }
        return empty
    }
    while(!ifEmpty(base)) base *= 2
    let start = base - 1 ? base / 2 : 1
    let move = start
    while(start < base - 1){
        if(!ifEmpty(start)){
            if(!ifEmpty(start + 1)){
                move = start
                start = (start + base) / 2
            }else{
                break
            }
        }else{
            base = start
            start = move
        }
    }
    return start
}

例如，我希望从第5行开始查询sht1表单，找到它A列的最后一条数据的行号，那么我们调用：

touchBottom(sht1, ["A"], 5)

3. 检测单元格是否为合并单元格的第一格

该函数可以通过传入的表单引用，列名，行号判断该单元格是否为合并单元格的第一格。

合并单元格的数据通常存储于合并单元格的最上方单元格中，该函数可以很方便地应用于需要遍历存在合并单元格的表格的情况。

/**
 * 
 * @param {sheet} sht 
 * @param {string} col 
 * @param {number|string} row 
 * @returns 
 */
function ifCellIsFirstCell(sht,col,row){
    return row === 1 || !(sht.Range(`${col}${row - 1}:${col}${row}`).MergeCells)
}

4. 在进行数据写入操作时，关闭数据刷新和自动计算功能，数据写入操作完毕后在打开，以大幅提升JS宏执行速度

 //运行函数时暂停wps刷新机制和自动计算以加快运行速度
function fastExec(callback,...args){
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = false
    const output = callback(...args)
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = true
    return output
}

5. 用于表格数据处理的工具类

//数据表处理
class DataHandler{
    /**
     * @param {Sheet} raw 
     * @param {number} titleLine 
     */
    constructor(raw,titleLine = 1,defaultMaxCol='Z'){
        Object.assign(this,{raw,titleLine,len:touchBottom(raw,["A"]),defaultMaxCol})
        this.filteredData = null
        this.filteredDataCols = null
    }
    /**
     * @param {string[]} cols 
     * @param {function} condition 
     * @param {object} params
     */
    //params = {formatTime:string,...} formatTime:日期所在列; defaultMaxCol:截取表格的最大列
    //condition function(row:any[]):boolean
    filter(cols,condition = () => true, params={}){
        ////---处理params额外参数---
        //格式化日期时间
        if(params.formatTime) this.raw.Range(`${params.formatTime}:${params.formatTime}`).TextToColumns(Range(`${params.formatTime}1`), xlDelimited, xlTextQualifierDoubleQuote, true, true, false, false, true, false, undefined, Array(Array(1, 5)), undefined, undefined, true)
        //设定默认最大列
        let defaultMaxCol = params.defaultMaxCol || 'Z'
        let data = this.raw.Range(`A1:${defaultMaxCol}${this.len}`).Value2

        ////---筛选表格---
        //按标题筛选列, 筛选结果列遵从参数cols的顺序
        let filteredData = []
        const titleMap = {}
        for(const t of cols) {
            let titleIndex = data[0].findIndex(i => i === t)
            if(titleIndex === -1) throw new Error(`筛选数据表时发现所提供的数据表缺少字段：${t}!`)
            else titleMap[t] = titleIndex
        }
        data.shift()
        data.forEach(row => {
            const filteredRow = cols.map( t => row[titleMap[t]])
            filteredData.push(filteredRow)
        })
        data = null
        //按条件筛选行，row代表每一行数据,condition回调函数对每一行数据进行判断，留下符合条件的数据
        filteredData = filteredData.filter(row => condition(row))
        this.filteredData = filteredData
        this.filteredDataCols = cols
        return filteredData
    }
}

使用时，我们先使用表单的引用实例化DataHandler类：

const dh = new DataHandler(sheetRef)

默认以表格第一行作为标题行，引用范围默认为A-Z列。如需修改，请在实例化的时候传入对应参数。

例如，我希望以第二行为标题行，引用至AZ列：

const dh = new DataHandler(sheetRef, 2, "AZ")

实例化完成后，我们需要筛选表格，调用实例的filter方法，我们可以非常方便地筛选表格数据：

const data = dh.filter(["国家","仓库","产品","数量"], row => row[1] === "仓库A") 
//只保留表格的 "国家","仓库","产品","数量" 4列，并筛选仓库名为“仓库A”的记录