【AirScript工具代码分享】结构化解析表格数据区域

大家平时在代码中是怎么定位表格数据的起始行、终止行的呢?

又是怎么定位具体的字段列所在位置呢?

我在实践中发现一些问题

  1. 如果在代码中写死行列,表格结构稍微有些变化,代码就要跟着改,不够灵活。

  1. 或者在开头提前定义了一堆列所在位置的配置项,变量的命名管理也很麻烦

我最近突然想到一个认为很优雅的解决方案,工具代码,分享给大家一起使用,有什么问题和建议可以回复讨论(我还没有充分测试各种极端场景下的表现)。

(太长了发不了,部分内容截图,这里是findCel的文档)

function findCel(what, ur = ActiveSheet.UsedRange, lookAt = xlWhole) {
    return ur.Find(what, undefined, undefined, lookAt)
}

function findRow(what, ur = ActiveSheet.UsedRange, lookAt = xlWhole) {
    const cel = findCel(what, ur, lookAt)
    if (cel) return cel.Row
}

function findCol(what, ur = ActiveSheet.UsedRange, lookAt = xlWhole) {
    let cel = findCel(what, ur, lookAt)
    if (cel) return cel.Column
}

// 判断 cells 集合是否全空
function isEmpty(cels) {
    for (let i = 1; i <= cels.Count; i++) {
        if (cels.Item(i).Text) return false
    }
    return true
}

// 获取ws实际使用的区域:会裁剪掉四周没有数据的空白区域(之前被使用过的区域或设置过格式等操作,默认ur会得到空白区域干扰数据范围定位)
function getUsedRange(ws = ActiveSheet) {
    // 1 定位默认的UsedRange
    if (typeof ws === 'string') ws = Sheets(ws)
    let ur = ws.UsedRange
    let firstRow = 1, firstCol = 1, lastRow = ur.Rows.Count, lastCol = ur.Columns.Count

    // 2 裁剪四周
    // todo 待官方支持TRIMRANGE后可能有更简洁的解决方案。期望官方底层不是这样暴力检索,应该有更高效的解决方式

    // 找到最后一个非空行
    for (; lastRow >= firstRow; lastRow--)
        if (!isEmpty(ur.Rows(lastRow).Cells))
            break
    // 最后一个非空列
    for (; lastCol >= firstCol; lastCol--)
        if (!isEmpty(ur.Columns(lastCol).Cells))
            break
    // 第一个非空行
    for (; firstRow <= lastRow; firstRow++)
        if (!isEmpty(ur.Rows(firstRow).Cells))
            break
    // 第一个非空列
    for (; firstCol <= lastCol; firstCol++)
        if (!isEmpty(ur.Columns(firstCol).Cells))
            break

    // 3 创建一个新的 Range 对象,它只包含非空的行和列
    return ws.Range(ur.Cells(firstRow, firstCol), ur.Cells(lastRow, lastCol))
}

/**
 * 表格结构化定位工具
 * @param sheet 输入表格名,或表格对象
 * @param dataRow 输入两个值的数组,第1个值标记(不含表头的)数据起始行,第2个值标记数据结束行。
 *  只输入单数值,未传入第2个参数时,默认以0填充,例如:4 -> [4, 0]
 *  起始行标记:
 *      0,智能检测。如果cols有给入字段名,以找到的第1个字段的下一行作为起始行。否则默认设置为ur的第2行。
 *      正整数,人工精确指定数据起始行(输入的是整张表格的绝对行号)
 *      '料理'等精确的字段名标记,以找到的单元格下一行作为数据起始行
 *      负数,比如-2,表示基于第2列(B列),使用.End(xlDown)机制找到第1条有数据的行的下一行作为数据起始行
 *  结束行标记:
 *      0,智能检测。以getUsedRange的最后一行为准。
 *      正整数,人工精确指定数据结束行(有时候数据实际可能有100行,可以只写10,实现少量部分样本的功能测试)
 *      '料理'等精确的字段名标记,同负数模式,以找到的所在列,配合.End(xlUp)确定最后一行有数据的位置
 *      负数,比如-3,表示基于第3列(C列),使用.End(xlUp)对这列的最后一行数据位置做判定,作为数据最后一行的标记
 * @param colNames 后续要使用到的相关字段数据,使用as2.0版本的时候,该参数可以不输入,会在使用中动态检索
 * @return [ur, rows, cols]
 *      ur,表格实际的UsedRange
 *      rows是字典,rows.start、rows.end分别存储了数据的起止行
 *      cols也是字典,存储了个字段名对应的所在列编号,比如cols['料理']
 *      注:返回的行、列,都是相对ur的位置,所以可以类似这样 ur.Cells(rows.start, cols[x]) 取到第1条数据在x字段的值
 */
function as1_locateTableRange(sheet, dataRow = [0, 0], colNames = []) {
    // 1 初步确定数据区域范围
    const ur = getUsedRange(sheet)
    const ws = ur.Parent
    // dataRow可以输入单个数值
    if (typeof dataRow === 'number') dataRow = [dataRow, 0]
    let rows = {
        start: dataRow[0] === 0 ? ur.Row + 1 : dataRow[0],
        end: dataRow[1] === 0 ? ur.Row + ur.Rows.Count - 1 : dataRow[1]
    }

    // 2 获取列名对应的列号
    let cols = {}
    colNames.forEach(colName => {
        const col = findCol(colName, ur)
        if (col) {
            cols[colName] = col
            // 如果此时rows.start还未确定,则以该单元格的下一行作为数据起始行
            if (rows.start === 0) rows.start = findRow(colName, ur) + 1 || 0  // 有可能会找不到,则保持0
        }
    })

    // 3 定位行号
    if (typeof rows.start === 'string') rows.start = findRow(rows.start, ur) + 1
    if (rows.start < 0) {
        const col = -rows.start
        rows.start = 2
        if (isEmpty(ws.Cells(1, col))) rows.start = ws.Cells(1, col).End(xlDown).Row + 1
    }

    if (typeof rows.end === 'string') rows.end = -findCol(rows.end, ur)
    if (rows.end < 0) {
        const cel = ws.Cells(ws.Rows.Count, -rows.end)
        rows.end = cel.Row
        if (isEmpty(cel)) rows.end = cel.End(xlUp).Row
    }

    // 4 转成ur里的相对行号
    rows.start -= ur.Row - 1
    rows.end -= ur.Row - 1
    for (const colName in cols) cols[colName] -= ur.Column - 1

    return [ur, rows, cols]
}


function locateTableRange(sheetName, dataRow = [0, 0], colNames = []) {
    // 1 先获得基础版本的结果
    let [ur, rows, cols] = as1_locateTableRange(sheetName, dataRow, colNames)

    // 2 使用 Proxy 实现动态查找未配置的字段(该功能仅AirScript2.0可用,1.0请使用as1_locateTableRange接口)
    cols = new Proxy(cols, {
        get(target, prop) {
            if (prop in target) {
                return target[prop] // 已配置字段,直接返回
            } else {
                const dynamicCol = findCol(prop, ur) // 动态查找
                if (dynamicCol) {
                    target[prop] = dynamicCol // 缓存动态找到的列
                    return dynamicCol
                }
            }
        }
    })

    // cols支持在使用中动态自增字段
    return [ur, rows, cols]
}

函数里都有参数的用法解释了,这里演示一个最简单的使用场景示例:

使用的示例数据表:

https://kdocs.cn/l/cly1BNKcBNo0

另一个使用示例:

北京
浏览 42
收藏
3
分享
3 +1
+1
全部评论