表格区域操作相关 API #

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

  const app = instance.Application

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

//@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')

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

//@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('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: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 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.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')

  // 设置对齐方式：底部对齐
  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')

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

//@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:B2')

  // 【只读】读取单元格格式化文本
  const text = await range.Text
  console.log(text)
}

//@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)
  window.cells = cells

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

//@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:D2')

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

//@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')

  // 区域中第一列的列号
  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')

  // 获取该区域的行
  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: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')

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

//@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('A4:D5')

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

  // 设置公式
  range.Formula = 'WebOffice'

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

  // 添加条件格式
  formatConditions.Add(
    app.Enum.XlFormatConditionType.xlExpression,
    undefined,
    '=D1=1'
  )
}

//@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('A1')

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

  // 添加数据验证
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5'
  })
}

//@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')

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

  // 添加数据验证
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5'
  })

  // 5 秒后删除规则
  setTimeout(async () => {
    await validation.Delete()
  }, 10000)
}

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

  const app = instance.Application

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

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

  // 添加数据验证
  await validation.Add({
    Type: app.Enum.XlDVType.xlValidateWholeNumber,
    Operator: app.Enum.XlFormatConditionOperator.xlBetween,
    Formula1: '1',
    Formula2: '5'
  })

  // 10 秒后修改规则
  setTimeout(async () => {
    await validation.Modify({
      Type: app.Enum.XlDVType.xlValidateWholeNumber,
      Operator: app.Enum.XlFormatConditionOperator.xlNotBetween,
      Formula1: '1',
      Formula2: '5'
    })
  }, 10000)
}

//@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('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('A1')

  // 边框对象
  const borders = await range.Borders
}

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

  const app = instance.Application

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

  // 边框对象
  const borders = await range.Borders

  // 单个边框对象
  const border = await borders.Item(app.Enum.XlBordersIndex.xlOutside)

  // 获取边框颜色
  const color = await border.Color
  console.log(color)
}

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

  const app = instance.Application

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

  // 边框对象
  const borders = await range.Borders

  // 单个边框对象
  const border = await borders.Item(1)
}

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

  const app = instance.Application

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

  // 边框对象
  const borders = await range.Borders

  // 单个边框对象
  const border = await borders.Item(app.Enum.XlBordersIndex.xlOutside)

  // 设置边框的线条样式
  border.LineStyle = app.Enum.XlLineStyle.xlDash
}

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

  const app = instance.Application

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

  // 边框对象
  const borders = await range.Borders

  // 单个边框对象
  const border = await borders.Item(app.Enum.XlBordersIndex.xlOutside)

  // 设置边框的粗细
  border.Weight = app.Enum.XlBorderWeight.xlThick
}

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

  const app = instance.Application

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

  // 内部属性对象
  const interior = await range.Interior

  // 设置内部颜色
  interior.Color = '#e1ff02'

  setTimeout(async () => {
    // 获取内部颜色
    const color = await interior.Color
    console.log(color)
  }, 3000)
}

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

  const app = instance.Application

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

  // 内部属性对象
  const interior = await range.Interior
}

//@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: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.ClearContents()
}

//@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 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('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.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, -4150)
  console.log('address3：', address3)
}

//@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: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 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')

  // 区域中第一行的行号
  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')

  // 获取该区域的行
  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: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')

  // 字体对象
  const font = range.Font

  // 获取字体加粗
  const isBold = await font.Bold
  console.log('字体是否加粗：', isBold)

  // 设置字体加粗
  font.Bold = true
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font

  // 获取字体颜色
  const color = await font.Color
  console.log('字体颜色：', color)

  // 设置字体颜色
  font.Color = '#eb5451'
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font

  // 获取字体斜体
  const italic = await font.Italic
  console.log('字体是否为斜体：', italic)

  // 设置字体斜体
  font.Italic = true
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font

  // 设置字体大小
  font.Size = 30

  // 获取字体大小
  const size = await font.Size
  console.log(size)
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font

  // 获取字体删除线
  const strikethrough = await font.Strikethrough
  console.log('字体是否有删除线：', strikethrough)

  // 设置字体删除线
  font.Strikethrough = true
}

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

  const app = instance.Application

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

  // 字体对象
  const font = range.Font

  // 获取字体是否设置下划线
  const underline = await font.Underline
  console.log('字体是否有下划线：', underline)

  // 设置字体下划线
  font.Underline = app.Enum.XlUnderlineStyle.xlUnderlineStyleSingle
}