wpsjs学习——获取单元格批注

1.获取第一个单元格的值：

wps.Application.ActiveSheet.Range('A1').Value2;

2.1.获取第一个单元格的批注：

wps.Application.ActiveSheet.Range('A1').Comment.Text();

<div class="global">
    获取表格信息
    <div class="divItem">
      <button style="margin: 3px" @click="onbuttonclick('fileName')">
        表格名称4
      </button>
      <button style="margin: 3px" @click="onbuttonclick('sheetName')">
        表格当前页签名称
      </button>
      <button style="margin: 3px" @click="onbuttonclick('bookCount')">
        页签数量
      </button>
      <button style="margin: 3px" @click="onbuttonclick('cellComent')">
        第一个单元格批注
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getCellComent')">
        手动选中单元格获取批注
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getCellValue')">
        获取选中区域的值
      </button>
      <button
        style="margin: 3px"
        @click="onbuttonclick('getSelectCellComment')"
      >
        获取选中区域的批注
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getRowCount')">
        获取表格已使用行数
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getColumnCount')">
        获取表格已使用列数
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getSheetComment')">
        获取当前sheet页中所有批注信息
      </button>
      <button style="margin: 3px" @click="onbuttonclick('getAllComment')">
        获取所有sheet页中所有批注信息
      </button>
    </div>
    <hr />
    <div class="divItem">
      信息：<span>{{ info }}</span>
    </div>
  </div>

function onbuttonclick(idStr, param) {
  console.log(param);
  switch (idStr) {
    case "fileName": {
      return wps.Application.ActiveWorkbook.Name;
    }
    case "sheetName": {
      return wps.Application.ActiveWorkbook.ActiveSheet.Name;
    }
    case "bookCount": {
      return wps.Application.ActiveWorkbook.Worksheets.Count;
    }
    case "cellComent": {
      // 第一个单元格批注
      return wps.Application.Worksheets.Item("Sheet1")
        .Range("A1")
        .Comment.Text();
    }
    case "getCellComent": {
      let arr = wps.Application.InputBox(
        "选中单元格",
        undefined,
        undefined,
        undefined,
        undefined,
        undefined,
        undefined,
        8
      ).Comment;
      if (arr) {
        alert(`获取到的批注是：${arr.Text()}`);
      } else {
        alert("无效单元格或未设置批注信息");
      }
      break;
    }
    // 获取选中区域的值
    case "getCellValue": {
      let value = wps.Application.Selection.Value2;
      if (value) {
        if (typeof value == "string") {
          // alert('值:'+value);
          return `值: ${value}`;
        } else if (typeof value == "number") {
          // alert('值:'+value.join(','));
          return `值:${value}`;
        } else if (Array.isArray(value)) {
          return `值:${value.join(",")}`;
        }
      } else {
        // alert('未设值')
        return "未设值";
      }
      break;
    }
    // 获取选中区域的批注
    case "getSelectCellComment": {
      let str = getComment(wps.Application.Selection.Address());
      return `批注信息：${str}`;
    }
    // 获取总行数
    case "getRowCount": {
      let count = wps.Application.ActiveSheet.UsedRange.Rows.Count;
      return count;
    }
    // 获取总列数
    case "getColumnCount": {
      let count = wps.Application.ActiveSheet.UsedRange.Columns.Count;
      return count;
    }
    // 获取当前sheet页所有批注
    case "getSheetComment": {
      let str = getSheetComment();
      return str;
    }
    // 获取所有sheet页所有批注
    case "getAllComment": {
      let str = "";
      let count = wps.Application.Worksheets.Count;
      for (let i = 1; i <= count; i++) {
        str += `页签${
          wps.Application.Worksheets.Item(i).Name
        }:${getSheetComment(i)}\n`;
      }

      return str;
    }
  }
}

function getComment(addr) {
  console.log("选中区域：", wps.Application.Selection.Address());
  addr = addr.replaceAll("$", "");
  if (!addr.includes(",") && !addr.includes(":")) {
    let arr = wps.Application.ActiveWorkbook.ActiveSheet.Range(addr).Comment;
    if (arr) {
      // alert('获取到的批注是：'+arr.Text());
      return `获取到的批注是：${arr.Text()}`;
    } else {
      return "无效单元格或未设置批注信息";
      // alert('无效单元格或未设置批注信息');
    }
  } else {
    let list = addr.split(",");
    let str = "";
    list.forEach((item) => {
      if (!item.includes(":")) {
        let c = wps.Application.ActiveWorkbook.ActiveSheet.Range(item).Comment;
        if (c) {
          str += `${c.Text()} `;
        }
      } else {
        let aa = item.split(":");
        console.log("1", aa[1].substr(0, 1), "2", aa[0].substr(0, 1));
        let length =
          aa[1].substr(0, 1).charCodeAt() - aa[0].substr(0, 1).charCodeAt();
        let height =
          aa[1].substr(1, aa[1].length) - aa[0].substr(1, aa[0].length);
        console.log("len", length);
        console.log("hei", height);
        // 拼接成二维数组
        for (let i = 0; i <= height; i++) {
          for (let j = 0; j <= length; j++) {
            let char = String.fromCharCode(aa[0].charCodeAt() + j);
            let b = Number(aa[0].substr(1, aa[0].length)) + i;
            let c = wps.Application.ActiveWorkbook.ActiveSheet.Range(
              char + b
            ).Comment;
            console.log("char", char + b);
            if (c) {
              str += `${c.Text()} `;
            }
          }
        }
      }
    });
    return str;
  }
}
function getSheetComment(index) {
  let rowCount = 1;
  let columnCount = 1;
  let cells = null;
  let range = "";
  if (!index) {
    // 获取表格已用行和列
    rowCount = wps.Application.ActiveSheet.UsedRange.Rows.Count;
    columnCount = wps.Application.ActiveSheet.UsedRange.Columns.Count;
    cells = wps.Application.ActiveSheet.Cells;
    range = Application.ActiveSheet.UsedRange.Address(); // 获取表格数据范围，如'$C$3:$E$3'
  } else {
    rowCount = wps.Application.Worksheets.Item(index).UsedRange.Rows.Count;
    columnCount =
      wps.Application.Worksheets.Item(index).UsedRange.Columns.Count;
    cells = wps.Application.Worksheets.Item(index).Cells;
    range = wps.Application.Worksheets.Item(index).UsedRange.Address();
  }
  console.log(rowCount, columnCount);

  let str = "";
  let aa = range.replaceAll("$", "").split(":");
  console.log("1", aa[1].substr(0, 1), "2", aa[0].substr(0, 1));
  let length =
    aa[1].substr(0, 1).charCodeAt() - aa[0].substr(0, 1).charCodeAt();
  let height = aa[1].substr(1, aa[1].length) - aa[0].substr(1, aa[0].length);
  console.log("len", length);
  console.log("hei", height);
  // 拼接成二维数组,依次遍历每个单元格
  for (let i = 0; i <= height; i++) {
    for (let j = 0; j <= length; j++) {
      let char = String.fromCharCode(aa[0].charCodeAt() + j); // 获取对应的字母，如A
      let b = Number(aa[0].substr(1, aa[0].length)) + i; // 获取行
      let comment = null;
      if (index) {
        // 获取单元格批注
        comment = wps.Application.Worksheets.Item(index).Range(
          char + b
        ).Comment; // Range('A3')
      } else {
        comment = wps.Application.ActiveWorkbook.ActiveSheet.Range(
          char + b
        ).Comment;
      }
      console.log("char", char + b);
      if (comment) {
        str += `${comment.Text()} `;
        if (comment.Text().includes("RangeData")) {
          comment = JSON.parse(comment.Text().replaceAll("\n", ""));
          console.log("getRangeData", comment.RangeData);
          getRangeData(index, comment.RangeData);
        }
      }
    }
  }
  return str;
}

// 请求接口获取范围数据
function getRangeData(index, range) {
  console.log("获取范围数据", index, range);
  request
    .post("https://aa.com/api/common/getinfo")
    .then((res) => {
      console.log(`${range}:范围数据`, res);
      if (res && res.data) {
        setRangeData(index, range, res.data);
      }
    });
  // 设置默认数据
  let list = [
    [1, 2, 3, 4, 5, 6, 7],
    [1, 2, 3, 4, 5, 6, 7],
  ];
  setRangeData(index, range, list);
}

// 向序号页签表内range范围内插入数据
function setRangeData(index, range, data) {
  if (index) {
    wps.Application.Worksheets.Item(index).Range(range).Value2 = data;
  } else {
    // 当前sheet页
    wps.Application.ActiveSheet.Range(range).Value2 = data;
  }
}

?