将Excel转换为嵌套数组

huangapple
go评论112阅读模式
英文:

How to convert excel to nested array

问题

需要帮助将Excel文件转换成嵌套数组，类似于这样：

  1. {
  2.    'a': [{
  3.        'aa': [
  4.            'aaa',
  5.            'aab',
  6.            'aac'
  7.        ],
  8.        'ab': [
  9.            'aba',
  10.            'abc',
  11.            'abd'
  12.        ]
  13.    }],
  14.    'b': [{
  15.        'ba': [
  16.            'baa'
  17.        ],
  18.        'bb': [
  19.            'bba',
  20.            'bbb'
  21.        ]
  22.    }]
  23. }

尝试将大量数据转换成这种格式，谢谢。

英文:

need help converting an Excel file to a nested array

将Excel转换为嵌套数组

to something like this

  1.    {
  2.     'a': [{
  3.         'aa': [
  4.             'aaa',
  5.             'aab',
  6.             'aac'
  7.         ],
  8.         'ab': [
  9.             'aba',
  10.             'abc',
  11.             'abd'
  12.         ]
  13.     }],
  14.     'b': [{
  15.         'ba': [
  16.             'baa'
  17.         ],
  18.         'bb': [
  19.             'bba',
  20.             'bbb'
  21.         ]
  22.     }]
  23.   }

trying to convert a large amount of data to this format, thank you

答案1

得分: 1

你可以将数据导出为CSV并在循环中将其转换为对象：

  1. const csv = `a,aa,aaa
  2.   ,,aab
  3.   ,,aac
  4.   ,ab,aba
  5.   ,,abc
  6.   ,,abd
  7.   b,ba,baa
  8.   ,bb,bba
  9.   ,,bbb`;
  11. const rows = csv.split('\n').map(row => row.split(',').map(col => col.trim()));
  13. const obj = {};
  14. let currentKey = null;
  16. rows.forEach(([k, subKey, value]) => {
  17.     if (k !== "") {
  18.         currentKey = k;
  19.         obj[currentKey] = obj[currentKey] || [];
  20.     }
  22.     if (subKey !== "") {
  23.         const o = { [subKey]: [value] };
  24.         obj[currentKey].push(o);
  25.     } else {
  26.         const lastSubObj = obj[currentKey][obj[currentKey].length - 1];
  27.         const lastSubKey = Object.keys(lastSubObj)[0];
  28.         lastSubObj[lastSubKey].push(value);
  29.     }
  30. });
  32. console.log(obj);
英文:

You can export your data to CSV and transform it to the object in a loop:

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

  1. const csv = `a,aa,aaa
  2.   ,,aab
  3.   ,,aac
  4.   ,ab,aba
  5.   ,,abc
  6.   ,,abd
  7.   b,ba,baa
  8.   ,bb,bba
  9.   ,,bbb`;
  11. const rows = csv.split(&#39;\n&#39;).map(row =&gt; row.split(&#39;,&#39;).map(col =&gt; col.trim()));
  13. const obj = {};
  14. let currentKey = null;
  16. rows.forEach(([k, subKey, value]) =&gt; {
  17.     if (k !== &quot;&quot;) {
  18.         currentKey = k;
  19.         obj[currentKey] = obj[currentKey] || [];
  20.     }
  22.     if (subKey !== &quot;&quot;) {
  23.         const o = { [subKey]: [value] };
  24.         obj[currentKey].push(o);
  25.     } else {
  26.         const lastSubObj = obj[currentKey][obj[currentKey].length - 1];
  27.         const lastSubKey = Object.keys(lastSubObj)[0];
  28.         lastSubObj[lastSubKey].push(value);
  29.     }
  30. });
  33. console.log(obj);

<!-- end snippet -->

答案2

得分: 1

这是一种改进的形式 @protob answer。只需使用库 xlsx 将Excel转换为2D数组，然后使用一些逻辑将2D数组转换为嵌套的JSON格式。

  1. document.querySelector('input').addEventListener('change', function () {
  2.   var reader = new FileReader();
  3.   reader.onload = function () {
  4.     var arrayBuffer = this.result,
  5.       array = new Uint8Array(arrayBuffer),
  6.       binaryString = String.fromCharCode.apply(null, array);
  7.     var workbook = XLSX.read(binaryString, {
  8.       type: "binary"
  9.     });
  10.     var first_sheet_name = workbook.SheetNames[0];
  11.     var worksheet = workbook.Sheets[first_sheet_name];
  12.     let data = XLSX.utils.sheet_to_json(worksheet, {
  13.       raw: true,
  14.       header: 1
  15.     });
  16.     data = data.map(d => Array.from(d, e => e ?? ''));
  17.     const obj = {};
  18.     let currentKey = null;
  20.     data.forEach(([k, subKey, value]) => {
  21.       if (k !== "") {
  22.         currentKey = k;
  23.         obj[currentKey] = obj[currentKey] || [];
  24.       }
  26.       if (subKey !== "") {
  27.         const o = { [subKey]: [value] };
  28.         obj[currentKey].push(o);
  29.       } else {
  30.         const lastSubObj = obj[currentKey][obj[currentKey].length - 1];
  31.         const lastSubKey = Object.keys(lastSubObj)[0];
  32.         lastSubObj[lastSubKey].push(value);
  33.       }
  34.     });
  35.     console.log(obj);
  36.   }
  37.   reader.readAsArrayBuffer(this.files[0]);
  38. }, false);
  1. <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
  2. <input type="file" id="data" accept=".xlsx" />
英文:

This is a form of refinement @protob answer. Just use library xlsx to convert Excel to a 2d array, then use some logic to convert a 2d array into a nested JSON form.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

  1. document.querySelector(&#39;input&#39;).addEventListener(&#39;change&#39;, function () {
  2.   var reader=new FileReader();
  3.   reader.onload= function () {
  4.     var arrayBuffer=this.result,
  5.       array=new Uint8Array(arrayBuffer),
  6.       binaryString=String.fromCharCode.apply(null, array);
  7.     var workbook=XLSX.read(binaryString, {
  8.       type: &quot;binary&quot;
  9.     });
  10.     var first_sheet_name=workbook.SheetNames[0];
  11.     var worksheet=workbook.Sheets[first_sheet_name];
  12.     let data=XLSX.utils.sheet_to_json(worksheet, {
  13.       raw: true,
  14.       header: 1
  15.     });
  16.     data = data.map(d =&gt; Array.from(d, e =&gt; e??&#39;&#39;));
  17.     const obj={};
  18.     let currentKey=null;
  20.     data.forEach(([k, subKey, value]) =&gt; {
  21.       if (k!==&quot;&quot;) {
  22.         currentKey=k;
  23.         obj[currentKey]=obj[currentKey]||[];
  24.       }
  26.       if (subKey!==&quot;&quot;) {
  27.         const o={ [subKey]: [value] };
  28.         obj[currentKey].push(o);
  29.       } else {
  30.         const lastSubObj=obj[currentKey][obj[currentKey].length-1];
  31.         const lastSubKey=Object.keys(lastSubObj)[0];
  32.         lastSubObj[lastSubKey].push(value);
  33.       }
  34.     });
  35.     console.log(obj);
  36.   }
  37.   reader.readAsArrayBuffer(this.files[0]);
  38. }, false);

<!-- language: lang-html -->

  1. &lt;script src=&quot;https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js&quot;&gt;&lt;/script&gt;
  2. &lt;input type=&quot;file&quot; id=&quot;data&quot; accept=&quot;.xlsx&quot; /&gt;

<!-- end snippet -->

huangapple
  • 本文由 发表于 2023年4月13日 15:26:53
  • 转载请务必保留本文链接：https://go.coder-hub.com/76002723.html
  • arrays
  • excel
  • javascript
  • nested
  • object
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定