node：脚本提取Excel 和 json输出Excel

提取Excel

const fs = require('fs')
const path = require('path')
const xlsx = require('node-xlsx')

// 根据要存储的路径,递归创建文件夹
const createDirSync = pathName => {
  if (fs.existsSync(pathName)) {
    return true
  } else {
    if (createDirSync(path.dirname(pathName))) {
      fs.mkdirSync(pathName)
      return true
    }
  }
}

// 写入到指定路径的文件
const writeToFile = (path, content) => {
  fs.writeFile(path, content, err => {
    console.log(`err`, err)
  })
}

const convertToJson = (excelPath, dstFilePath) => {
  // 读取Excel文件
  const workSheetsFromFile = xlsx.parse(excelPath)

  let result = []
  for (const item of workSheetsFromFile[0]['data']) {
    let [, name, , , address, , lng, lat] = item
    // 组装obj
    const obj = {
      name,
      address,
      location: { lng, lat }
    }
    result.push(obj)
  }
  result.splice(0, 1)

  if (!path.extname(dstFilePath)) {
    dstFilePath += '/default.json'
  }
  const dirPath = path.dirname(dstFilePath)
  if (createDirSync(dirPath)) {
    writeToFile(dstFilePath, JSON.stringify(result))
  }
}

// 使用
convertToJson('./党群服务中心.xls', './mine/jsons/abc.json')

输出Excel

输出表格的格式

const fs = require('fs')
const path = require('path')
const xlsx = require('node-xlsx')

const titles = ['地点名称', '详细地址', '联系电话', '经纬度', '标签']
const result = []

const filenames = fs.readdirSync(__dirname, { encoding: 'utf-8' })
for (const filename of filenames) {
  if (filename.endsWith('.json')) {
    const data = []
    const filePath = path.resolve(__dirname, filename)
    const content = JSON.parse(fs.readFileSync(filePath, { encoding: 'utf-8' }))
    data.push(titles)
    for (const {
      name,
      address,
      tel,
      location: { lng, lat },
      tag
    } of content) {
      const element = []
      element.push(name)
      element.push(address)
      element.push(tel)
      element.push(`${lng},${lat}`)
      element.push(tag)
      data.push(element)
    }
    result.push({ name: filename, data})
  }
}

const buffer = xlsx.build(result, {
  '!cols': [{ wch: 20 }, { wch: 50 }, { wch: 15 }, { wch: 20 }, { wch: 20 }]
})
fs.writeFileSync(`./最终表格.xlsx`, buffer, { flag: 'w' })