如何将Excel中存储JSON格式数据的列转换为JavaScript对象

核心原因 #

问题本质是Excel单元格中存储的JSON字符串存在格式污染，导致JSON.parse校验不通过，无需中转CSV格式，直接处理xlsx解析后的原始数据即可。常见污染场景包括：

• Excel自动给包含特殊字符的单元格外层包裹额外双引号
• 单元格内的JSON字符串存在多余换行、空格、制表符
• JSON内部的双引号被Excel自动转义为""
• 存在全角引号、全角冒号等非标准JSON符号

注意事项 #

原代码中存在笔误：最终写入文件时使用的validJsonData变量未定义，实际应为转换后的结果对象。

解决代码 #

let xlsx = require("xlsx")
let path = require("path")
let fs = require("fs");

// 安全解析JSON的工具函数
function safeJsonParse(str) {
  if (typeof str !== 'string') return str
  try {
    // 1. 清理首尾空白字符
    let cleaned = str.trim()
    // 2. 移除Excel自动添加的外层多余双引号
    if (cleaned.startsWith('"') && cleaned.endsWith('"')) {
      cleaned = cleaned.slice(1, -1)
    }
    // 3. 处理Excel转义产生的重复双引号
    cleaned = cleaned.replace(/""/g, '"')
    // 4. 移除换行、制表符等无效空白
    cleaned = cleaned.replace(/[\n\t\r]/g, '')
    // 5. 替换全角符号为半角
    cleaned = cleaned.replace(/“|”/g, '"').replace(/：/g, ':')
    return JSON.parse(cleaned)
  } catch (e) {
    console.warn('JSON解析失败，原始内容：', str)
    return str
  }
}

const inputFilePath = path.join(__dirname, './mastersheet.xlsx');
const workbook = xlsx.readFile(inputFilePath);
// 直接读取sheet为JSON数组，无需中转CSV避免额外转义问题
const jsonArr = xlsx.utils.sheet_to_json(workbook.Sheets['Sheet6']);

// 遍历处理目标JSON列，示例中存储JSON的列名为jsonContent，可替换为实际列名
const processedJson = jsonArr.map(item => {
  item.jsonContent = safeJsonParse(item.jsonContent)
  return item
})

// 写入最终文件
fs.writeFileSync("./mastersheet.json", JSON.stringify(processedJson), 'utf8', (err) => {
  if (err) console.log(err);
})

后续排查 #

如果仍存在解析失败的情况，可根据控制台打印的原始字符串内容，针对性调整safeJsonParse中的清洗规则即可。

内容的提问来源于stack exchange，提问作者Maharshi