区域(Range) #

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 单元格对象
  const cells = await range.Cells

  // 指定单元格
  const cell = await cells.Item(1)

  // 选中第 1 个单元格
  await cell.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域中第一列的列号
  const column = await range.Column
  console.log(column)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域中最后一列的列号
  const columnEnd = await range.ColumnEnd
  console.log(columnEnd)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 列对象
  const columns = await range.Columns

  // 指定列
  const column = await columns.Item(2)

  // 选中第 2 列
  await column.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域宽度
  range.ColumnWidth = 30
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域中单元格的数量
  const count = await range.Count
  console.log(count)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取该区域的行
  const entireColumn = range.EntireColumn

  // 选择这列
  await entireColumn.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取该区域的行
  const entireRow = range.EntireRow

  // 选择这一行
  await entireRow.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 条件格式对象
  const formatConditions = await range.FormatConditions
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A2')

  // 设置公式：A2 = A1 + B1
  range.Formula = '=A1+B1'
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A2:B2')

  // 设置公式：A2 到 B2 的值为 A1 + B1 的和
  range.FormulaArray = '=Sum(A1:B1)'

  // 获取该公式
  const formulaArray = await range.FormulaArray
  console.log(formulaArray)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取整列
  const entireColumn = await range.Rows.EntireColumn
  console.log(entireColumn)

  // 隐藏该列
  entireColumn.Hidden = true
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 设置对齐方式：居中
  range.HorizontalAlignment = await app.Enum.XlHAlign.xlHAlignCenter

  setTimeout(async () => {
    // 获取对齐方式
    const horizontalAlignment = await range.HorizontalAlignment
    console.log(horizontalAlignment)
  }, 3000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 写入值到单元格中
  range.IndentLevel = 10
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B2')

  // 左边距
  const left = await range.Left
  console.log(left)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 合并单元格
  await range.Merge()

  // 页面加载合并后，5 秒内点击空白区域，5 秒后自动选择该区域
  setTimeout(async () => {
    // 单元格的合并区域
    const mergeArea = await range.MergeArea()
    mergeArea.Select()
  }, 5000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 合并单元格
  await range.Merge()

  // 区域内是否存在合并的单元格
  const mergeCells = await range.MergeCells
  console.log(mergeCells)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取区域数字格式
  const numberFormat = await range.NumberFormat
  console.log('当前区域数字格式：', numberFormat)

  // 设置区域数字格式为文本
  range.NumberFormat = '@'
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 区域子项：B2
  const item1 = await range.Item(2, 2)
  await item1.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域中第一行的行号
  const row = await range.Row
  console.log(row)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域中最后一行的行号
  const rowEnd = await range.RowEnd
  console.log(rowEnd)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 区域高度
  range.RowHeight = 100
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 行对象
  const rows = await range.Rows

  // 指定行
  const row = await rows.Item(2)

  // 选中第 2 行
  await row.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 【只读】读取range区域的第1行，第2列单元格格式化文本
  const text = await range.Cells(1, 2).Text
  console.log(text)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B2')

  // 上边距
  const top = await range.Top
  console.log(top)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 数据有效性对象
  const validation = await range.Validation
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 写入值到单元格中
  range.Value = 'WebOffice'
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 设置对齐方式：底部对齐
  range.VerticalAlignment = await app.Enum.XlVAlign.xlVAlignBottom

  setTimeout(async () => {
    // 获取对齐方式
    const horizontalAlignment = await range.VerticalAlignment
    console.log(horizontalAlignment)
  }, 3000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取区域自动换行
  const wrapText = await range.WrapText
  console.log('区域是否自动换行：', wrapText)

  // 设置区域自动换行
  range.WrapText = true
}

//@file=base.xlsx
async function example() {
  await instance.ready();
  const app = instance.Application;
  // 前置条件存在选中的选区
  // 选区对象
  const range = await app.Selection;
  // 获取选中的所有区域
  const areas = await range.Areas;
  // 选中区域的数量
  const count = await areas.Count;
  // 选中的第一个区域
  const area = await areas.Item(1)
  const address = await area.Address()
  console.log(area,address)
}

//@file=base.xlsx
async function example() {
  await instance.ready();

  const app = instance.Application;
  
  // 选区对象
  const range = await app.Selection;

  // 返回超链接对象
  const hyperlinks = await range.Hyperlinks;
  
  // 获取超链接单个对象
  const hyperlink = await hyperlinks.Item(1)

  console.log(await hyperlink.Address)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 激活区域 A1
  range.Activate()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 给 A1 区域添加评论
  range.AddComment('WebOffice')
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 获取宏语言的区域引用的 String 值
  const address1 = await range.Address()
  console.log('address1：', address1)

  const address2 = await range.Address(false, false)
  console.log('address2：', address2)

  const address3 = await range.Address(
    true,
    true,
    app.Enum.XlReferenceStyle.xlR1C1
  )
  console.log('address3：', address3)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:A2')

  // 要填充的单元格
  const fillRange = await app.Range('A1:A20')

  // 对指定区域中的单元格执行自动填充
  await range.AutoFill(fillRange)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 清除区域的评论
  await range.ClearComments()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 清除区域的内容
  await range.ClearContents()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:B2')

  // 清除区域的样式
  await range.ClearFormats()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 第二块区域对象
  const newRange = await app.Range('A1:B4')

  // 判断是否重叠
  const contain = await range.Contain(newRange)
  console.log(contain)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B5:D10')

  // 选择区域的所有行并删除
  await range.EntireRow.Delete()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B5:D10')

  // 选择区域的所有行并删除
  await range.EntireColumn.Delete()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B5:D10')

  // 选择区域的所有行并在该范围新增
  await range.EntireRow.Insert()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('B5:D10')

  // 选择区域的所有行并在该范围新增
  await range.EntireColumn.Insert()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 合并单元格
  await range.Merge()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 合并单元格
  await range.Merge()

  setTimeout(async () => {
    // 取消合并单元格
    await range.UnMerge()
  }, 3000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')
  await range.Select()

  // 对指定区域进行迁移操作
  const newRange = await range.Offset(2, 2)
  await newRange.Select()
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 选区
  const range = await app.Selection

  // 粘贴到range格，并相加
  await range.PasteSpecial(
    app.Enum.XlPasteType.xlPasteValues,
    app.Enum.XlPasteSpecialOperation.xlPasteSpecialOperationAdd
  )
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 选择区域 A1
  range.Select()

  // 3 秒后选择区域 A1:D2
  setTimeout(async () => {
    const newRange = await app.Range('B1:D2')
    newRange.Select()
  }, 5000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1')

  // 选择区域 A1 并且设置闪烁动画
  range.Select(null, true)

  // 3 秒后选择区域 B1:D2 并设置选中区域第二行第二列为活动单元格
  setTimeout(async () => {
    const newRange = await app.Range('B1:D2')
    newRange.Select({ row: 2, col: 2 }, true)
  }, 5000)
}

//@file=base.xlsx
async function example() {
  await instance.ready()

  const app = instance.Application

  // 区域对象
  const range = await app.Range('A1:D2')

  // 将区域导出成图片
  const img = await range.ToImageDataURL()
  console.log(img)
}

//@file=base.xlsx
async function example() {
  await instance.ready();

  const app = instance.Application;

  // 单元格对象，包含所有单元格
  const cells = await app.Cells;
  
  // 查找
  let range = await cells.Find('123');

  // 查找下一个
  range = await cells.Find('123', range);

  // 选中查到的单元格
  await range.Select();
}

//@file=base.xlsx
async function example() {
  await instance.ready();

  const app = instance.Application;

  // 单元格对象，包含所有单元格
  const cells = await app.Cells;
  
  // 查找，返回所有包含'123'的单元格地址
  let results = await cells.FindAll('123');

  // 查到的第一个单元格对象
  const range = await app.Range(results[0]);
  
  // 选中单元格
  await range.Select();
}

//@file=base.xlsx
async function example() {
  await instance.ready();

  const app = instance.Application;

  // 区域对象
  const range = await app.Range('A1:D2');

  //将区域的内容复制到剪贴板
  await range.Copy();
  // 内容已复制到剪切板，通过 Ctrl + V 可以粘贴
}

//@file=base.xlsx
async function example() {
    await instance.ready();

    const app = instance.Application;

    // 区域对象
    const range = await app.Range('A1:D2');
    
    //删除重复值
    await range.RemoveDuplicates([1,2]);
}