javascript - Convert .xlsx file with merged cells to JSON with SheetJS - Stack Overflow

admin管理员组

文章数量:1327939

I'm trying to read a .xlsx file with SheetJS and convert it into JSON. I'm able to read and convert a simple (without merged cells) .xlsx file to JSON, but I'm not getting it right with merged cells.

The problem is that I have a .xlsx file with products in different languages.

I tried searching for a solution on different websites and the github repository of SheetJS with mediocre success. Mostly addressing how to write merged cells.

function handleFile(e) {
   var files = e.target.files, f = files[0];
   var reader = new FileReader();
   reader.onload = function(e) {
      var data = new Uint8Array(e.target.result);
      var workbook = XLSX.read(data, {type: 'array'});
      let result = '';
      let sheet_name_list = workbook.SheetNames;
      // iterate through sheets
      sheet_name_list.forEach(function (y) {

         workbook.Sheets[y]['!merges'].map(r => {
             let startChar = XLSX.utils.encode_range(r).split(':')[0].replace(/[^a-zA-Z]+/g, '');
             let endChar = XLSX.utils.encode_range(r).split(':')[1].replace(/[^a-zA-Z]+/g, '');
             let number = XLSX.utils.encode_range(r).split(':')[0].match(/[+-]?\d+(?:\.\d+)?/g)[0];
             for (let i = numbersFromLetters(startChar); i < numbersFromLetters(endChar); i++) {
                workbook.Sheets[y][numbersToLetters(i + 1) + number] = {t:'s'};
                workbook.Sheets[y][numbersToLetters(i + 1) + number].v = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].h = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].w = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].r = '<t>' + workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v + '</t>';
             }

         });   
         // Convert the cell value to JSON
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {range: 1});
         let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header: 1});
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {skipHeader: true});
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header: 1, range: 1});
         if (roa.length > 0) result = roa;

      });
      console.log(result);
   };
   reader.readAsArrayBuffer(f);
}

For better perception this is an example excel file with merged language cell:

and I want this result:

[
   { "languages": 
      [
         {
            "language": "US", 
            "name": "blue pants"
         },
         {
            "language": "DE", 
            "name": "blaue Hose"
         },             
         {
            "language": "ES", 
            "name": "pantalones azules"
         }
      ],
      "price": 29.9,
      "category": "pants"
   },
   ...
]

Depending on which part of the mented code I execute I get different results which look kind of similar to the code that I want to achieve or at least can further work with. But at each oute I need to fiddle around further which appears to me that there might be a better solution.

I'm trying to read a .xlsx file with SheetJS and convert it into JSON. I'm able to read and convert a simple (without merged cells) .xlsx file to JSON, but I'm not getting it right with merged cells.

The problem is that I have a .xlsx file with products in different languages.

I tried searching for a solution on different websites and the github repository of SheetJS https://github./sheetjs/js-xlsx/#json with mediocre success. Mostly addressing how to write merged cells.

function handleFile(e) {
   var files = e.target.files, f = files[0];
   var reader = new FileReader();
   reader.onload = function(e) {
      var data = new Uint8Array(e.target.result);
      var workbook = XLSX.read(data, {type: 'array'});
      let result = '';
      let sheet_name_list = workbook.SheetNames;
      // iterate through sheets
      sheet_name_list.forEach(function (y) {

         workbook.Sheets[y]['!merges'].map(r => {
             let startChar = XLSX.utils.encode_range(r).split(':')[0].replace(/[^a-zA-Z]+/g, '');
             let endChar = XLSX.utils.encode_range(r).split(':')[1].replace(/[^a-zA-Z]+/g, '');
             let number = XLSX.utils.encode_range(r).split(':')[0].match(/[+-]?\d+(?:\.\d+)?/g)[0];
             for (let i = numbersFromLetters(startChar); i < numbersFromLetters(endChar); i++) {
                workbook.Sheets[y][numbersToLetters(i + 1) + number] = {t:'s'};
                workbook.Sheets[y][numbersToLetters(i + 1) + number].v = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].h = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].w = workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v;
                workbook.Sheets[y][numbersToLetters(i + 1) + number].r = '<t>' + workbook.Sheets[y][XLSX.utils.encode_range(r).split(':')[0]].v + '</t>';
             }

         });   
         // Convert the cell value to JSON
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {range: 1});
         let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header: 1});
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {skipHeader: true});
         //let roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header: 1, range: 1});
         if (roa.length > 0) result = roa;

      });
      console.log(result);
   };
   reader.readAsArrayBuffer(f);
}

For better perception this is an example excel file with merged language cell:

and I want this result:

[
   { "languages": 
      [
         {
            "language": "US", 
            "name": "blue pants"
         },
         {
            "language": "DE", 
            "name": "blaue Hose"
         },             
         {
            "language": "ES", 
            "name": "pantalones azules"
         }
      ],
      "price": 29.9,
      "category": "pants"
   },
   ...
]

Depending on which part of the mented code I execute I get different results which look kind of similar to the code that I want to achieve or at least can further work with. But at each oute I need to fiddle around further which appears to me that there might be a better solution.

• javascript
• excel
• sheetjs

Share Improve this question Follow edited Apr 20, 2019 at 11:13 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Apr 17, 2019 at 17:13 DoodleDoodle 16122 silver badges99 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 6

const workbook = XLSX.readFile(fileName, readOptions);
    const sheetNames = workbook.SheetNames;
    for (let worksheet of sheetNames) {
      if (workbook.Sheets[worksheet]['!merges'])
        workbook.Sheets[worksheet]['!merges'].map((merge) => {
          const value = XLSX.utils.encode_range(merge).split(':')[0];
          for (let col = merge.s.c; col <= merge.e.c; col++)
            for (let row = merge.s.r; row <= merge.e.r; row++)
              workbook.Sheets[worksheet][String.fromCharCode(65 + col) + (row + 1)] = workbook.Sheets[worksheet][value];
        });

本文标签： javascriptConvert xlsx file with merged cells to JSON with SheetJSStack Overflow