解析XLSX并使用Node创建json

Parse XLSX and create json with Node

本文关键字：Node 创建 json XLSX 解析      更新时间：2023-09-26

我使用的是js-xlsx包link:https://github.com/SheetJS/js-xlsx
问题:我如何解析合并输出json的xlsx ?下面是excel表格的样子:最后json应该是这样的:

    [
   {
   "Nuber": 1,
   "Department": "part1",
   "Unit": ["check","second","cable"],
   "BeginTime": "1/7:00",
   "EndTime": "2/20:00",
   "Worker": ["Lucy","Jussa","Peter"]
   },
   {
   "Nuber": 2,
   "Department": "part2",
   "Unit": "check",
   "BeginTime": "1/7:00",
   "EndTime": "1/20:00",
   "Worker": "Yu"
   }
]

. js: 

XLSX = require('xlsx');
var workbook = XLSX.readFile('test.xlsx');
var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function(y) { /* iterate through sheets */
  var worksheet = workbook.Sheets[y];
  for (z in worksheet) {
    /* all keys that do not begin with "!" correspond to cell addresses */
    if(z[0] === '!') continue;
  }
});

var XLSX = require('xlsx');
var workbook = XLSX.readFile('iris_small.xlsx');
var sheetNames = workbook.SheetNames;
var sheetIndex = 1;
var df = XLSX.utils.sheet_to_json(workbook.Sheets[sheetNames[sheetIndex-1]]);
console.log(df);

给:

[ { Id: '1',
    'Petal.Length': '1.4',
    'Petal.Width': '0.2',
    Species: 'setosa' },
  { Id: '2',
    'Petal.Length': '1.4',
    'Petal.Width': '0.2',
    Species: 'setosa' },
  { Id: '3',
    'Petal.Length': '1.3',
    'Petal.Width': '0.2',
    Species: 'setosa' },
  { Id: '4',
    'Petal.Length': '3.9',
    'Petal.Width': '1.4',
    Species: 'versicolor' },
  { Id: '5',
    'Petal.Length': '3.5',
    'Petal.Width': '1',
    Species: 'versicolor' },
  { Id: '6',
    'Petal.Length': '4.2',
    'Petal.Width': '1.5',
    Species: 'versicolor' },
  { Id: '7',
    'Petal.Length': '5.4',
    'Petal.Width': '2.3',
    Species: 'virginica' },
  { Id: '8',
    'Petal.Length': '5.1',
    'Petal.Width': '1.8',
    Species: 'virginica' } ]

可以看到，数字被转换为字符串。我不知道是否有保留数字作为数字的选项。使用Papa Parse的变通方法:

var Papa = require('babyparse');
var csv = XLSX.utils.sheet_to_csv(workbook.Sheets[sheetNames[sheetIndex-1]]);
var json = Papa.parse(csv, 
                {
                    header: true,
                    skipEmptyLines: true,
                    dynamicTyping: true
                }
            );
console.log(json.data);

现在的结果是:

[ { Id: 1,
    'Petal.Length': 1.4,
    'Petal.Width': 0.2,
    Species: 'setosa' },
  { Id: 2,
    'Petal.Length': 1.4,
    'Petal.Width': 0.2,
    Species: 'setosa' },
  { Id: 3,
    'Petal.Length': 1.3,
    'Petal.Width': 0.2,
    Species: 'setosa' },
  { Id: 4,
    'Petal.Length': 3.9,
    'Petal.Width': 1.4,
    Species: 'versicolor' },
  { Id: 5,
    'Petal.Length': 3.5,
    'Petal.Width': 1,
    Species: 'versicolor' },
  { Id: 6,
    'Petal.Length': 4.2,
    'Petal.Width': 1.5,
    Species: 'versicolor' },
  { Id: 7,
    'Petal.Length': 5.4,
    'Petal.Width': 2.3,
    Species: 'virginica' },
  { Id: 8,
    'Petal.Length': 5.1,
    'Petal.Width': 1.8,
    Species: 'virginica' } ]
相关文章：