【AirScript工具代码分享】结构化解析表格数据区域
大家平时在代码中是怎么定位表格数据的起始行、终止行的呢?
又是怎么定位具体的字段列所在位置呢?
我在实践中发现一些问题
如果在代码中写死行列,表格结构稍微有些变化,代码就要跟着改,不够灵活。
或者在开头提前定义了一堆列所在位置的配置项,变量的命名管理也很麻烦
我最近突然想到一个认为很优雅的解决方案,工具代码,分享给大家一起使用,有什么问题和建议可以回复讨论(我还没有充分测试各种极端场景下的表现)。
(太长了发不了,部分内容截图,这里是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
另一个使用示例: