记工具开发

excel工具

依赖

• exceljs
• node-xlsx
• axios

核心代码

/**
 * 绘制表格
*/
function drawTable() {
  const sourcePath = 'src/public/demo.xlsx'
  const targetPath = config.version + '-版本发布方案.xlsx'

  // 读取本地Excel文件作为模版
  const oldworkbook = xlsx.parse(fs.readFileSync(sourcePath))

  const workbook = new ExcelJS.Workbook()
  createSheet0(workbook, oldworkbook)

  const isExist = fs.existsSync('dist');
  if (isExist) {
    workbook.xlsx.writeFile('dist/' + targetPath)
  } else {
    fs.mkdir('dist', async (err) => {
      if (err) {
          console.log(err);
      } else {
        workbook.xlsx.writeFile('dist/' + targetPath)
      }
  });
  }

}

function createSheet0(workbook, oldworkbook) {

    const header = [
      { key: 'version', name: '版本' },
      { key: 'time', name: '生效时间' },
      { key: 'updateDesc', name: '变更该要' },
      { key: 'author', name: '作者' },
      { key: 'approve', name: '审核', width: 90 },
    ]

  const contentLists = []

  const data = oldworkbook[0]
  let sheet = workbook.addWorksheet(data.name)
  let getFileObj = new GetFileStyle(sheet)
  getFileObj
    .setGeneralValue(2, 1, header, COLOR.GREEN)
    .setGeneralValue(3, 2, contentLists)
    .setGeneralValue(1, 6, desc)
    .setBorderStyle('thin', 'thin', 'thin', 'thin', '000000', header.length, 9)
    .setMerge('A7', 'E9')

  sheet.getCell('A7').alignment = { wrapText: true }
  sheet.getRow(7).height = 45
}

常用方法

function GetFileStyle(sheet) {
  // 合并单元格
  this.setMerge = function (start, end) {
    // B1:E7
    // console.log(`${start}:${end}`);
    sheet.mergeCells(`${start}:${end}`)
    return this
  }

  // 设置通用值 type = 1 desc, type = 2 header, 3 type
  this.setGeneralValue = function (type, numberLine, params, bg) {
    switch (type) {
      case 1:
        this.setDesc(numberLine, params)
        break
      case 2:
        this.setHeader(numberLine, params, bg)
        break
      case 3:
        this.setContent(numberLine, params)
      default:
        break
    }
    return this
  }

  // 垂直插值
  this.setDesc = function (numberLines = 0, value) {
    let item = null
    for (let i = 0, length = value.length; i < length; i++) {
      item = value[i]
      const cellNum = `A${numberLines + i + 1}`
      sheet.getCell(cellNum).value = item
      this.setFontSize(cellNum, 14, COLOR.RED)
    }
    return this
  }

  // 设置header
  this.setHeader = function (numberLines, value, bg, colNum = 1) {
    let keys = []
    for (let i = 0, length = value.length; i < length; i++) {
      let currentLines = numberLines ? numberLines : sheet.rowCount + 1
      let columnsChar = String.fromCharCode(64 + i + colNum)
      let cellNumbers = `${columnsChar}${currentLines}`
      // console.log(cellNumbers, value[i].name);
      let item = value[i]
      if (colNum > i + 1) {
        keys.push({
          width: 18,
        })
      }
      keys.push({
        key: item.key,
        width: item.width || 18,
      })
      sheet.getCell(cellNumbers).value = item.name
      this.setFontSize(cellNumbers, 14)
      this.setBackgroundColor(cellNumbers, bg)
    }
    // console.log(keys);
    sheet.columns = keys
    return this
  }

  this.setCellValue = function (cellNumber, value) {
    sheet.getCell(cellNumber).value = value
    return this
  }

  // 设置单元格宽度
  this.setWidth = function (arr) {
    for (let i = 0; i < arr.length; i++) {
      const dobCol = sheet.getColumn(i + 1)
      dobCol.width = arr[i]
    }
    return this
  }

  // 设置内容
  this.setContent = function (numberLines, value) {
    sheet.addRows(value)
    return this
  }

  // 设置字体是否居中
  this.setAlignment = function (cell, verticalValue, horizontalValue) {
    sheet.getCell(cell).alignment = {
      vertical: verticalValue,
      horizontal: horizontalValue,
    }
    return this
  }

  // 设置字体大小
  this.setFontSize = function (cell, size, color) {
    sheet.getCell(cell).font = {
      color: { argb: color || '000000' },
      family: 2,
      size: size || 14,
      bold: true,
    }
    return this
  }

  // 设置背景色
  this.setBackgroundColor = function (cell, color) {
    // console.log("color", color)
    sheet.getCell(cell).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: {
        argb: `FF${color}`,
      },
    }
    return this
  }

  // 设置表格边框
  this.setBorderStyle = function (
    top,
    right,
    bottom,
    left,
    color,
    columns,
    rows,
    startCol = 1,
    startRow = 1
  ) {
    for (let i = startCol; i <= columns; i++) {
      let char = String.fromCharCode(64 + i)
      for (let j = startRow; j <= rows; j++) {
        // console.log(`fdsjklfjdskfjdk${char}${j}`)
        sheet.getCell(`${char}${j}`).border = {
          top: {
            style: top,
            color: {
              argb: `FF${color}`,
            },
          },
          left: {
            style: top,
            color: {
              argb: `FF${color}`,
            },
          },
          bottom: {
            style: top,
            color: {
              argb: `FF${color}`,
            },
          },
          right: {
            style: top,
            color: {
              argb: `FF${color}`,
            },
          },
        }
      }
    }
    return this
  }

  this.setBorderRightStyle = function () {
    for (let i = 1; i <= 5; i++) {
      sheet.getCell(`A${i}`).border = {
        right: {
          style: 'thin',
          color: {
            argb: `FF000000`,
          },
        },
      }
    }
    return this
  }

  // 将二维数组的数据值填入表格
  this.setValueToSheet = function (arr, maxColNum) {
    for (let i = 0; i < arr.length; i++) {
      for (let j = 0; j < maxColNum; j++) {
        let columnsChar = String.fromCharCode(64 + j + 1)
        let cellNumbers = `${columnsChar}${i + 1}`
        if (arr[i][j]) {
          sheet.getCell(cellNumbers).value = arr[i][j] + ''
        }
      }
    }
    return this
  }

  // 设置加粗
  this.setBold = function (arr) {
    for (let i = 0; i < arr.length; i++) {
      const dobCol = sheet.getCell(arr[i])
      if (!dobCol.font) {
        dobCol.font = {}
      }
      dobCol.font.bold = true
    }
    return this
  }

  // 设置标题样式
  this.setHeaderStyle = function (rowNum, startCol, endCol) {
    for (let i = startCol; i <= endCol; i++) {
      let columnsChar = String.fromCharCode(64 + i)
      let cellNumbers = `${columnsChar}${rowNum}`
      this.setFontSize(cellNumbers, 14)
      this.setBackgroundColor(cellNumbers, COLOR.GREEN)
    }
    return this
  }

  return this
}

puputeer

依赖

• puppeteer-core
• chrome-launcher
• axios

const puppeteer = require('puppeteer-core');

/**
 * 打开网站开始截图
*/
async function startScreenshot(data) {
    const browser = await puppeteer.launch({
        executablePath: getChromePath(), // 浏览器地址 chrome://version/  复制可执行文件路径
        headless: false,
        timeout: 15000,
    })
    
    const page = await browser.newPage();

    // 打开xxx
    await page.goto('http://www.xxx.com',{ timeout: 30000 }).then(async () => {
        // 登录gitlab
        console.log('1. 登录xxx');
        await page.type('#_umname', config.umName, {delay: 100});
        await page.type('#test', config.umPwd, {delay: 100});
        await page.click('#submit');

        await page.waitForNavigation({
            waitUntil: 'domcontentloaded',
            timeout: 100000
        });

        // 完成登录后关闭页面
        await page.close();

        console.log('2. 开始截图');
        data.forEach(async (each) => {
            await screenshot(browser, each);
        });

        // await browser.close();

    }, (err) => {
        console.log('登陆页超时', err);
    })
}

获取chrome路径通用方法

const puppeteer = require('puppeteer-core');
const ChromeLauncher = require('chrome-launcher');

// 获取本地chrome可执行路径
function getChromePath() {
  const chromePath = ChromeLauncher.Launcher.getInstallations()[0];
  console.log('本地Chrome的可执行路径为：', chromePath);
  return chromePath;
}