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

蜂巢糖FCT

已于 2024-02-26 10:02:43 修改

阅读量942

点赞数 8

分类专栏：前端 vue wpsjs 文章标签：学习

于 2024-01-04 14:56:52 首次发布

本文链接：https://blog.csdn.net/qq_36093530/article/details/135386739

版权

前端同时被 3 个专栏收录

27 篇文章 0 订阅

订阅专栏

vue

17 篇文章 0 订阅

订阅专栏

wpsjs

1 篇文章 0 订阅

订阅专栏

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(":");
  //如果页签内只有一个单元格有数据或者一个单元格数据都没有则跳过遍历
  if (aa.length == 1) {
    if (index) {
      return wps.Application.Worksheets.Item(index).Range(aa[0]).Comment;
    } else {
      return wps.Application.ActiveWorkbook.ActiveSheet.Range(aa[0]).Comment;
    }
  }  
  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;
  }
}