Create with JavaScript "Excel → JSON conversion application"
Tags: javascript, app, excel, json
Published: 2018-01-23 08:01:18

---
title: Create with JavaScript "Excel → JSON conversion application"
published: true
description: Create with JavaScript "Excel → JSON conversion application"
tags: javascript, app, excel, json
---

> Author:[@yama_cqw](https://qiita.com/yama_cqw)
> Source:[Qiita](https://qiita.com/yama_cqw/items/fbdd069703c2a5798fcf)

## Introduction
For development, use "script editor" which is included as standard in Mac (OS X Yosemite or later).

![](https://thepracticaldev.s3.amazonaws.com/i/fdufe1f7k1mj1le8p63w.png)


It's a very simple editor.

Launch the script editor.
Select "JavaScript" in the language menu in the upper left. (You can choose AppleScript or JavaScript)
Write the code.
File -> Export (file format: application).
You can create an application by this alone. Easy.

## Application

The code of the created application is here.
When you drag and drop an Excel file, the JSON file is exported.
(Note: Microsoft Excel must be installed for execution.)

``` javascript
function analyzeExcel(path) {
  var app, worksheets;
  app = Application('Microsoft Excel');
  app.includeStandardAdditions = true;
  app.activate();
  app.open(path);
  worksheets = app.worksheets;

  // Create JSON for each worksheet.
  for (var i = 0; i < worksheets.length; i++) {
    makeJSON(worksheets[i]);
  }

  function makeJSON(ws) {
    var data = {};
    var title = [];
    var first_row = ws.rows[0];
    var worksheet_name = ws.name();

    // Acquire line 1 as heading.
    for (var col_i = 0; ; col_i++) {
       var check = first_row.columns[col_i].value();
       if (!check) {
          break;
       }
       title.push(check);
    }

    // Acquire as data from the second line
    for (var row_i = 1; ; row_i++) {
       var row = ws.rows[row_i];
       var row_data = {};
       // Determine the presence or absence of data in the first column.
       var id = row.columns[0].value();
       if (!id) {
          break;
       } else {
          data[id] = {};
       }
       // Combine data row by row.
       // If it has the same heading, put it in an array.
       for (var i = 1; i < title.length; i++) {
          if (row_data[title[i]]) {
             // 配列化
             if (!Array.isArray(row_data[title[i]])) {
                row_data[title[i]] = [row_data[title[i]]];
             }
             // Add
             if (row.columns[i].value()) {
                row_data[title[i]].push(row.columns[i].value());
             }
          } else {
             row_data[title[i]] = row.columns[i].value();
          }
       }
       data[id] = row_data;
    }

    // Export settings
    var filePath = app.chooseFileName({
       defaultName: worksheet_name + '.json',
       defaultLocation: app.pathTo('desktop')
    });

    // Write JSON data (convert character code to UTF-8)
    ObjC.import('Cocoa');
    var text = JSON.stringify(data, null, '  ');
    string = $.NSString.stringWithString(text);
    string.writeToFileAtomicallyEncodingError(
      filePath.toString(),
      true,
      $.NSUTF8StringEncoding,
      $()
    );
  }
}

// Processing when dragging and dropping a file to an application.
var SystemEvents = Application("System Events");
var fileTypesToProcess = ["ELSX"];
var extensionsToProcess = ["xlsx"];
var typeIdentifiersToProcess = [];
function openDocuments(droppedItems) {
  for (var item of droppedItems) {
    var alias = SystemEvents.aliases.byName(item.toString());
    var extension = alias.nameExtension();
    var fileType = alias.fileType();
    var typeIdentifier = alias.typeIdentifier();
    if (
       fileTypesToProcess.includes(fileType) 
       || extensionsToProcess.includes(extension)
       || typeIdentifiersToProcess.includes(typeIdentifier)
    ) {
      var path = Path(item.toString().slice(1));
      analyzeExcel(path);
    }
  }
}

// Describe how to use when double-clicking the application icon
function run() {
  var sys = Application("System Events");
  sys.includeStandardAdditions = true;
  sys.displayDialog("Please drag and drop the Excel file (xlsx). Convert to JSON file.");
}

```
Here is the conversion rule.

* Create a JSON file for each Excel worksheet.
* Heading the first line, treat the second and subsequent lines as values.
* Use the value of the first column as the key and group the data for each row.
* If the headings are the same, make it an array.
* Convert to UTF-8 (garbled characters if not converted).


For example, if you drag and drop such an Excel file...

![](https://thepracticaldev.s3.amazonaws.com/i/o4ugkr1c6ewf74hs0emn.png)

Such JSON will be exported!

``` json
{
  "a": {
    "name": "John",
    "num": [
      11,
      12,
      13
    ]
  },
  "b": {
    "name": "Paul",
    "num": [
      21,
      22
    ]
  },
  "c": {
    "name": "George",
    "num": [
      31
    ]
  },
  "d": {
    "name": "Ringo",
    "num": ""
  }
}
```