脚本经典案例

例子1：选中区域快速批量填值

function main(){
  const time = getNowTime()
  const date = getNowDate()
  ActiveView.Selection(null, ["@日期", "@时间", "@分类"]).Value = [date, time, 'B']
}
// 获取当前时间，格式为 "hh:mm:ss"
function getNowTime() {
  return (new Date()).toTimeString().split(" ")[0]
}
// 获取当前日期，格式为 "yyyy:MM:dd"
function getNowDate() {
  const date = new Date()
  return date.getFullYear() + '/' + (date.getMonth() + 1) + '/' + date.getDate()
}

main()

例子2：快速实现“一键归档”

下面代码实现了一个文件中两张数据结构相同的表 把表一中的已完成的数据插入到表二中，并删除表一中数据 表结构如下图所示：

function main() {
  const criterias = []
  criterias.push(Criteria("@分类",  "Equals", ["B"]))
  criterias.push(Criteria("@完成",  "Equals", ["1"]))
  // 创建filters
  const filters = []
  const filter = {Criterias: criterias, Op: "AND"}
  filters.push(filter)
  const range1 = Sheets(1).Views(1).RecordRange.Condition(filters, "AND")
  if(!range1){
    return
  }
  const length = range1.Count
  const range2 = Sheets(2).Views(1).RecordRange.Add(1, undefined, length)
  range2.Value = range1.Value
  range1.Delete()
}

main()

结合上面两个例子，可以实现自动设置归档日期和时间，或者选中记录一键归档等等功能

例子3：快速删除空白数据

删除名称字段中 值为空的数据

function deleteRecords() {
  const criterias = []
  criterias.push(Criteria("@名称",  "Equals",['']))
  const filters = []
  const filter = {Criterias: criterias, Op: "AND"}
  filters.push(filter)
  Sheets(3).Views(1).RecordRange.Condition(filters, "AND").Delete()
}
deleteRecords()

例子4：快速创建一张表

function main() {
  Application.Sheets.Add(
    {
        Type:'xlEtDataBaseSheet',
        Config:{
            fields:
                [
                    {fieldType:'SingleLineText',args:{fieldName:'文本',fieldWidth:15}},
                    {fieldType:'MultiLineText',args:{fieldName:'多行文本',fieldWidth:15}},
                    {fieldType:'Date',args:{fieldName:'日期',numberFormat:'yyyy/mm/dd;@',fieldWidth:15}},
                    {fieldType:'SingleSelect',args:{fieldName:'单选项',fieldWidth:15,listItems:[{value: '选项1', color: 4283466178},{value: '选项2',color: 4281378020}]}},
                    {fieldType:'Number',args:{fieldName:'数字',fieldWidth:15}},
                    {fieldType:'Rating',args:{fieldName:'等级',maxRating:6,fieldWidth:15}},
                ],
            name:'数据表',
            views:
                [
                    {name:'表格视图',type:'Grid'},
                    {name:'表单视图',type:'Form'}
                ]
        }
    })
}
main()

例子5：格式化数据批量插入

function main(){
    const range = Application.Sheets(4).Views(1).RecordRange.Add(1, undefined, 300)// 在第1行，向上方添加300条记录
    const template = ["商品", 10]
    const range1 = []
    // 给1-300行赋值
    for (let i = 1; i < 301; i++ ) {
        if(i<101){
          range1.push([template[0]+i,template[1],'A'])
        }else if(i<201){
          range1.push([template[0]+i,template[1]+10,'B'])
        }else{
          range1.push([template[0]+i,template[1]+10,'C'])
        }
    }
    range.Value = range1
}
main()

例子 6：自动双向关联

先来看一下表结构

• 客户表

• 拜访记录表

对 【客户表】 中的"拜访记录" 和 【拜访记录表】 中的 "客户详情"做自动关联

通过客户名称来进行匹配

function main() {
  const clientsView = Application.Sheets(1).Views(1)
  const clientsCount = clientsView.RecordRange.Count
  
  const visitsView = Application.Sheets(2).Views(1)
  const visitsCount = visitsView.RecordRange.Count

  const clients = clientsView.RecordRange('1:'+clientsCount)
  const visits = visitsView.RecordRange('1:'+visitsCount)

  linkVisits(clients, visits)
  linkClients(clients, visits)
}
// 关联拜访记录表中的客户
function linkClients(clients,visits){
  let name1 = ''
  let name2 = ''
  let id = 0
  // {"value":[{"id":"XP","str":"金山办公"}]}
  for(let i=1;i<visits.Count+1;i++){
    name1 = visits.Item(i,['@客户名称']).Value
    if(name1!==''){
      for(let j=1;j<clients.Count+1;j++){
            name2 = clients.Item(j,['@客户名称']).Value
            id = clients.Item(j).Id
            if(name1 === name2){
              // console.log({id:`${id}`,str:`${name2}`})
              visits.Item(j,['@客户详情']).Value = Application.DBCellValue([id])
              break
            }
      }
    }
    
  }
}
// 关联客户表中的拜访记录
function linkVisits(clients,visits){
  let name1 = ''
  let name2 = ''
  let id = 0
  
  // {"value":[{"id":"XP","str":"金山办公"}]}
  for(let i=1;i<clients.Count+1;i++){
    let vits = []
    name1 = clients.Item(i,['@客户名称']).Value
    if(name1!==''){
      for(let j=1;j<visits.Count+1;j++){
        name2 = visits.Item(j,['@客户名称']).Value
        id = visits.Item(j).Id
        if(name1 === name2){
          vits.push({id:`${id}`,str:`${name2}`})
          // visits.Item(i,['@客户详情']).Value = Application.DBCellValue([{id:`${id}`,str:`${name2}`}])
        }
      }
    }
    // console.log(vits)
    clients.Item(i,['@拜访记录']).Value = Application.DBCellValue(vits)
  }
}

main()

例子7：同步主表数据到其他表

表结构如下

第一张表中存着所有数据，其他几张表中存着分类数据 当主表中数据更新的时候，期望其他表数据也更新

// 读取整个表
function getAllRecords(sheetIndex) {
  const view = Application.Sheets(sheetIndex).Views(1)
  const count = view.RecordRange.Count
  return view.RecordRange("1:"+count)
}
// 获取子表中匹配的同步字段数组
function getMatchFields(sheetIndex,toSyncFields){
  const matchFields = []
  const fieldDescs = Application.Sheets(sheetIndex).FieldDescriptors
  for(let i=0;i<toSyncFields.length;i++){
    const fieldName = toSyncFields[i]
    for(let i=1;i<fieldDescs.Count+1;i++){
      if(fieldDescs.Item(i).Name === fieldName){
        matchFields.push('@'+fieldName)
        break
      }
    }
  }
  return matchFields
}
// 检查记录值相同与否, 返回需要更新的记录
function checkFields(mainRecord, record, params) {
  const v1 = mainRecord.Item(1,params).Value
  const v2 = record.Item(1,params).Value
  if(v1===v2) return null
  // console.log(record.Item(1,params).Id,v1)
  return {id:record.Item(1,params).Id[0],val: params.length>1?v1:[v1]}
}

// 同步副表数据
function syncSheet(sheetIndex, mainRecordMap, {
  keyField,
  toSyncFields,
}) {
  const records = getAllRecords(sheetIndex)
  const matchFields = getMatchFields(sheetIndex,toSyncFields)
  // console.log(matchFields)
  const toUpdateIds = []
  const toUpdateVals = []
  for (let i = 1; i < records.Count+1; i++) {
    const record = records.Item(i)
    const name = records.Item(i,['@'+keyField]).Value
    if(name === '') continue
    const mainRecord = mainRecordMap[name]
    if (mainRecord) {
      const updatedResult = checkFields(mainRecord, record, matchFields)
      if (updatedResult) {
        toUpdateIds.push(updatedResult.id)
        toUpdateVals.push(updatedResult.val)
      }
    } else {
      console.error('没有在主表里面找到此条记录： ', name)
    }
  }
  if (toUpdateIds.length > 0) {
    // console.log(toUpdateVals)
    records(toUpdateIds,matchFields).Value = toUpdateVals
  }
}

// 同步主表数据到其他表
function syncMainSheetToOthers(mainSheetName, keyField, toSyncFields) {
  const sheets = Application.Sheet.GetSheets()
  const mainIndex = sheets.findIndex(item => item.name === mainSheetName)+1
  const mainRecords = getAllRecords(mainIndex)
  const recordsMap = {}
  for (let i = 1; i < mainRecords.Count+1; i++) {
    const name = mainRecords.Item(i,'@'+keyField).Value
    if(name === '') continue
    if (recordsMap[name]) {
      console.error('有重复的记录', name)
    }
    recordsMap[name] = mainRecords.Item(i)
  }
  for(let index=1;index<sheets.length+1;index++){
    if(index === mainIndex) continue
    syncSheet(index, recordsMap, {
      keyField,
      toSyncFields,
    })
  }
}

syncMainSheetToOthers('关爱清单', '产品名', ['机制', '价格'])

例子8：获取日期筛选后记录

表结构如下

使用日期筛选进行获取日期为本月（2023年2月）的记录，即将筛选参数改为thisMonth，如"dynamicType": "thisMonth",筛选得到的记录可查看all变量

function main(){
  const criterias = []
  criterias.push(Criteria("@日期",  "Equals", [{"dynamicType": "thisMonth","type": "DynamicSimple"}]))
  // 创建filters
  const filters = []
  const filter = {Criterias: criterias, Op: "AND"}
  filters.push(filter)
  const range1 = Application.Sheets(5).Views(1).RecordRange.Condition(filters, "AND")
  console.log(range1.Value)
  return range1
}
main()

例子9：获取联系人筛选后记录

表结构如下

使用联系人筛选进行获取联系人为Lin的记录，即更改筛选参数value改为“Lin”，如 "values": [ "Lin" ]，注意value值为数组形式,筛选得到的记录可查看all变量

function filterContact() {
    const criterias = [Criteria("@联系人", "Equals", ["WPS_1719228187"])]
    // 创建filters
    const filters = [{Criterias: criterias, Op: "AND"}]
    const all = Application.Sheets(7).Views(1).RecordRange.Condition(filters)
    console.log(all.Value)
    return all
}

filterContact()

例子10：批量将获取的图片url进行文字识别

• 可以通过获取的图片url来进行调用百度云文字识别接口，识别图片中的文字

获取所有记录 ，获取该表所有图片URL，调用百度云文字识别接口，识别图片中的文字，并将结果生成在相应记录的“识别文字”字段下。

const access_token="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

function main() {
    const view = Application.ActiveSheet.Views(1)
    const count = view.RecordRange.Count
    // 获取所有记录的图片附件值
    const attachments =  view.RecordRange('1:'+count, ["@图片和附件"]).Value
    // console.log(attachments)
    // 获取对应的url
    const urls = []
    let attachment = []
    // 这里的attachments是一个二维数组
    for(let i=0;i<attachments.length;i++){
        urls[i] = []
        attachment = attachments[i][0].Value
        for(let j = 0;j<attachment.length;j++){
          urls[i].push(attachment[j].LinkUrl || null)
        }
    }
    // console.log(urls)
    let words = []
    let url = []
    for(let i=0;i<urls.length;i++){
      url = urls[i]
      if(url){
        words[i] = [getText(url)]
      }else{
        words[i] = ['']
      }
      // console.log(words[i])
    }
    // console.log(recordIds,words)
    // 赋值到对应的 ‘识别文字’ 这一栏
    view.RecordRange('1:'+count,["@识别文字"]).Value = words
}
// 调用接口把所有的url转换为文字
function getText(urls){
    const word = urls.reduce((word,url)=>{
      // console.log('调用1次')
        let resp = HTTP.fetch('https://aip.baidubce.com/rest/2.0/ocr/v1/general_basic?access_token='+access_token,{
                method:"POST",
                timeout:2000,
                headers:{
                  'Content-Type': 'application/x-www-form-urlencoded',
                  'Accept': 'application/json',
                  'Authorization': access_token
            },
                body: `url=${encodeURIComponent(url)}`
            })
        if(resp.status !== 200){
           throw new Error("fetch err! status is "+resp.status)
        }
        return word + resp.text()
    },'')
    return word
}
main()

脚本示例（08/26更新）

function main(){
  addSheetViewField()
  setDemoValue()
  testRecordRange()
  testFilters()
  testSorts()
  testGroups()
  testGridView()
}
function addSheetViewField(){
    const sheet = Application.Sheets.Add({After:1,Type:"xlEtDataBaseSheet"})
    sheet.Views.Add("Grid",'新建的表格视图')
    addField("MultiLineText","富文本")
    addField("Time","时间")
    addField("Currency","货币")
    addField("Percentage","百分比")
    addField("ID","身份证")
    addField("Phone","电话")
    addField("Email","邮箱")
}
function setDemoValue(){
  setValue("@文本","100")
  setValue("@文本","200",'4:6')
  setValue(2,123)
  setValue(2,456,'4:6')
  setValue(3,"2024/07/13")
  setValue(3,"2024/08/13","4:6")
  setValue("@单选项","选项1")
  setValue("@单选项","选项2","4:6")
  setValue(6,3)
  setValue(6,4,"4:6")
  setValue("@富文本","dfsafdasfdasf")
  setValue("@富文本","fdsfagzgzgfgvcbc","4:6")
  setValue("@时间","8:48:16")
  setValue("@时间","10:48:16","4:6")
  setValue("@货币","123213")
  setValue("@货币","1434434","4:6")
  // setValue(10,1.23)
  // setValue(10,2.35,"4:6")
  setValue("@身份证","43211620050917691X")
  setValue("@身份证","43211620050917691X","4:6")
  setValue("@电话","18064038091")
  setValue("@电话","18064838691","4:6")
  setValue("@邮箱","1437252712@qq.com")
  setValue("@邮箱","1437257777@qq.com","4:6")
}
function addField(type,name,index,valueUnique,defaultValue,defaultValueType,numberFormat,arg1,arg2,arg3,arg4){
    const desc = Application.Sheets(2).FieldDescriptors.FieldDescriptor(type, name)
    if (type === 'SingleSelect' || type === 'MultipleSelect') {
        desc.Items = arg1
    }
    if(type === 'Rating'){
        desc.MaxRating = arg1
    }
    if(type === 'Formula'){
        if (arg1) {
          desc.ValueUnique = arg1
         }if (arg2) {
             desc.ValueType =arg2
         }if (arg3) {
             desc.ShowPercentAsProgress = arg3
         }
    }
    if(type === 'Cascade'){
        const options = Application.CascadeOptions()
         if(typeof arg1 ==='object'){
            const o1 =  options.Add(arg1[0])
            const children = arg1[1]
            for(let i=0;i<children.length;i++){
              o1.Children.Add(children[i])
            }
          }
    if(typeof arg2 ==='object'){
        const o2 =  options.Add(arg2[0])
        const children = arg2[1]
        for(let i=0;i<children.length;i++){
           o2.Children.Add(children[i])
        }
    }
        desc.AllCascadeOption = options
    }
    if (type === 'OneWayLink') {
        desc.LinkSheet = arg1
        desc.IsAutoLink = arg2
    }
    if (valueUnique) {
        desc.ValueUnique = valueUnique
    }
    if (defaultValue) {
        desc.DefaultValue = defaultValue
    }
    if (defaultValueType) {
        desc.DefaultValue = defaultValueType
    }
    if (numberFormat) {
        desc.NumberFormat = numberFormat
    }
    const result =  Application.Sheets(2).FieldDescriptors.AddField(desc, index)
    if(result.Code !== 0){
      console.error(result.Message)
    }
}
function setValue(field,value,index="1:3"){
   if(typeof value === 'object'){
      value = Application.DBCellValue(value)
   }
   Application.Sheets(2).Views(1).RecordRange(index, field).Value = value
   const newVal = Application.Sheets(2).Views(1).RecordRange(index, field).Value
   let setSuccess = true
   if(typeof newVal === 'object'){
     for(let i=0;i<newVal.length;i++){
       if(newVal[i][0] === value){
          setConfirm = false
          break
       }
     }
   }else if(newVal !== value){
     setConfirm = false
   }
   if(!setSuccess)   console.error('setValue异常！')
}
function testRecordRange(){
  // RecordRange: Add、Item、Condition、Delete
  const rr = Application.Sheets(2).Views(1).RecordRange.Add(1,undefined,2)
  rr.Item(1).Value = ["300",789,"2024/09/13","选项1",[],3,"fadfadsf","10:33:33"]
  rr.Item(2).Value = ["400",789,"2024/09/13","选项1",[],3,"fadfadsf","10:33:33"]
  const criterias = [Criteria("@文本", "Equals",['400'])]
  const filters = [{Criterias: criterias, Op: "AND"}]
  const recordRange = Application.Sheets(2).Views(1).RecordRange.Condition(filters)
  if(recordRange){
    recordRange.Delete()
  }
}
function testFilters(){
  //Filters: Add、Item、Clear
  //Filter:  Delete
  Application.Sheets(2).Views(1).Filters.Clear()
  const filters = Application.Sheets(2).Views(1).Filters;
  const criteria = Criteria({
      Field: 1,
      CriteriaOp: 'Equals',
      Values: ["200"]
  })
  const filter = filters.Add(criteria);
  const delResult = filter.Delete()
  if(delResult.Code !== 0) {
    console.error(delResult.Message)
  } 
  const criteria1 = Criteria({
      Field: 1,
      CriteriaOp: 'Equals',
      Values: ["300"]
  })
  Application.Sheets(2).Views(1).Filters.Add(criteria1)
  const clsResult = Application.Sheets(2).Views(1).Filters.Clear()
  if(clsResult.Code !== 0) {
    console.error(clsResult.Message)
  } 
  const criteria2 = Criteria({
      Field: 1,
      CriteriaOp: 'Equals',
      Values: ["100"]
  })
  Application.Sheets(2).Views(1).Filters.Add(criteria2)
  //console.log(Application.Sheets(2).Views(1).Filters.Item(1).Criteria)
}
function testSorts(){
  //Sorts: Add Item Move 
  //Sort:  Delete ChangeField
  const sort= Application.Sheets(2).Views(1).Sorts.Add(2,true)
  if(sort.IsAscending !== true) {
    console.error('Add Sort时 设置IsAscending 异常')
  }
  Application.Sheets(2).Views(2).Sorts.Add(3,false)
  const changeRes = Application.Sheets(2).Views(1).Sorts.Item(1).ChangeField(1)
  if(changeRes.Code !== 0) {
    console.error(changeRes.Message)
  }
  Application.Sheets(2).Views(1).Sorts.Move([3,1])
  Application.Sheets(2).Views(1).Sorts.Add(2,false)
  const delRes = Application.Sheets(2).Views(1).Sorts.Item(2).Delete()
  if(delRes.Code !== 0) {
    console.error(delRes.Message)
  }
}
function testGroups(){
  // Groups: Add FoldAll UnFoldAll Item StatisticResult
  // Group:  ChangeField Delete
  const group = Application.Sheets(2).Views(1).Groups.Add(1,true)
  if(group.IsAscending !== true) {
    console.error('groups add 方法设置IsAscending 异常')
  }
  Application.Sheets(2).Views(1).Groups.Add(2,false)
  Application.Sheets(2).Views(1).Groups.FoldAll()
  const changeResult = Application.Sheets(2).Views(1).Groups.Item(2).ChangeField(3)
  if(changeResult.Code !== 0) {
    console.error(changeResult.Message)
  } 
  Application.Sheets(2).Views(1).Groups.Add(2)
  const delResult = Application.Sheets(2).Views(1).Groups.Item(2).Delete()
  if(delResult.Code !== 0) {
    console.error(delResult.Message)
  } 
  // console.log(Application.Sheets(2).Views(1).Groups.StatisticResult(1))
  Application.Sheets(2).Views(1).Groups.UnFoldAll()
}
function testGridView(){
    const gridView = Application.Sheets(2).Views.Add('Grid', '表格视图');
    if(!gridView.RowHeight){
       console.error('Views.Add表格视图时，返回值异常')
    }
    const itemGridView = Application.Sheets(2).Views(1)
    if(!itemGridView.RowHeight){
        console.error('Views.Item获取表格视图时，返回值异常')
    }
    const activeGridView = Application.ActiveView
    if(!activeGridView.RowHeight){
        console.error('ActiveView获取表格视图时，返回值异常')
    }
    Application.ActiveView.RowHeight = 'Medium';
    if(Application.ActiveView.RowHeight !== 'Medium'){
        console.error('设置GridView的RowHeight结果时异常')
    }
}
main()

更多API用法可参考多维表API