一键把 Excel 中的图片链接替换成真实图片

前言

日常使用 Excel 中，经常会出现其中一列是图片链接，查看的时候需要点开链接到浏览器里才能查看到具体的图片内容，反复切换十分不便，因此就想着在 Excel 中把图片链接转成真实的图片，这样查看就方便多了…

奈何对 Excel 中的宏函数了解甚少，所以就写个 node 脚本来处理吧 :)

说干就干

思路

遍历每一个单元格，如果是图片链接就下载图片然后在 Excel 中替换对应的链接。（听起来好像很简单…

exceljs & request

安装 exceljs 和 request

yarn add exceljs --save
yarn add request --save

处理 Excel

const fdirs = await fs.readdirSync(path.join(process.cwd(), "./"));
const f = fdirs.find((f) => !f.startsWith(".") && f.endsWith(".xlsx"));
await console.info(`\n找到 excel 文件：${f}\n`);
const xlsx_file = path.join(process.cwd(), `./${f}`);
const success_file = path.join(process.cwd(), `./result_${f}`);

let img_arr = [];
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.readFile(xlsx_file);
await workbook.eachSheet((sheet) => {
  sheet.eachRow((row, row_num) => {
    row.eachCell((cell, col_num) => {
      if (cell.value.toString().startsWith("http")) {
        const img_url = cell.value.toString();
        cell.value = null;
        // style.alignment maybe undefined
        try {
          cell.style.alignment.horizontal = "center";
          cell.style.alignment.vertical = "justify";
        } catch {}
        const obj = {
          sheet: sheet,
          img_url,
          position: {
            col: col_num,
            row: row_num,
          },
        };
        img_arr.push(obj);
      }
    });
  });
});

处理图片

const getBuffer = (img_url) => {
  return new Promise((resolve, reject) => {
    request(
      {
        url: img_url,
        encoding: null,
      },
      (error, resp, body) => {
        if (body) {
          resolve(body);
        } else {
          resolve();
        }
      }
    );
  });
};

const saveImg = async (workbook, img_obj_arr) => {
  const len = img_obj_arr.length;
  for (let i = 0; i < img_obj_arr.length; i++) {
    const { sheet, img_url, position } = img_obj_arr[i];
    const buff = await getBuffer(img_url);
    if (buff) {
      log(`正在处理第 ${i + 1} / ${len} 个图片 \n\n`);
      const img_base64 = buff.toString("base64");
      const img_id = workbook.addImage({
        base64: img_base64,
        extension: "jpeg",
      });
      const sheet_row = sheet.getRow(position.row);
      sheet_row.height = 100;

      sheet.addImage(img_id, {
        tl: { col: position.col - 0.5, row: position.row - 0.5 },
        ext: { width: 100, height: 100 },
        hyperlinks: {
          hyperlink: img_url,
          tooltip: `${img_url}`,
        },
      });
    }
  }
};

完整代码

url2pic.js

end