Converting Excel File (Xlsx) into Json

What is the easiest way to convert an Excel spreadsheet with tabular data to JSON?

Assuming you really mean easiest and are not necessarily looking for a way to do this programmatically, you can do this:

Add, if not already there, a row of "column Musicians" to the spreadsheet. That is, if you have data in columns such as:
```
Rory Gallagher      Guitar
Gerry McAvoy        Bass
Rod de'Ath          Drums
Lou Martin          Keyboards
Donkey Kong Sioux   Self-Appointed Semi-official Stomper
```
Note: you might want to add "Musician" and "Instrument" in row 0 (you might have to insert a row there)
Save the file as a CSV file.
Copy the contents of the CSV file to the clipboard
Go to http://www.convertcsv.com/csv-to-json.htm
Verify that the "First row is column names" checkbox is checked
Paste the CSV data into the content area

Mash the "Convert CSV to JSON" button

With the data shown above, you will now have:

[
  {
    "MUSICIAN":"Rory Gallagher",
    "INSTRUMENT":"Guitar"
  },
  {
    "MUSICIAN":"Gerry McAvoy",
    "INSTRUMENT":"Bass"
  },
  {
    "MUSICIAN":"Rod D'Ath",
    "INSTRUMENT":"Drums"
  },
  {
    "MUSICIAN":"Lou Martin",
    "INSTRUMENT":"Keyboards"
  }
  {
    "MUSICIAN":"Donkey Kong Sioux",
    "INSTRUMENT":"Self-Appointed Semi-Official Stomper"
  }
]

With this simple/minimalistic data, it's probably not required, but with large sets of data, it can save you time and headache in the proverbial long run by checking this data for aberrations and abnormalcy.

Go here: http://jsonlint.com/
Paste the JSON into the content area
Pres the "Validate" button.

If the JSON is good, you will see a "Valid JSON" remark in the Results section below; if not, it will tell you where the problem[s] lie so that you can fix it/them.

Converting Excel file (xlsx) into Json

Thanks for the other answers, with that information i have tried and found the solution.

Code:

FileInputStream file = new FileInputStream(new File("C:\\Users\\SomeExcel.xlsx"));

// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

SomePojo somePojo = new SomePojo();

Map<Object, Object> x = new LinkedHashMap<>();
// ignoring header for that I've +1 in loop
for(int i = sheet.getFirstRowNum() + 1; i<=sheet.getLastRowNum(); i++)
{

    Row row = sheet.getRow(i);
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
        Cell ce = row.getCell(j);
        if (j == 0) {
            // If you have Header in text It'll throw exception because it won't get
            // NumericValue
            somePojo.setName(ce.getStringCellValue());
        }
        if (j == 1) {
            somePojo.setValue(ce.getStringCellValue());
        }
        // Same for third column
    }
    x.put(somePojo.getName(), somePojo.getValue());
}

// Object to JSON in String
ObjectMapper mapper = new ObjectMapper();
// Object to JSON in file
mapper.writeValue(new File("C:\\some.json"),x);
// Print in console
String jsonFromMap = mapper.writeValueAsString(x);
file.close();

SomePojo.java

public String name;
public String value;

How to convert Excel file to json using pandas?

I would just create a sheet level dict and loop through each of the sheets. Something like this:

import pandas
import json

sheets = ['sheet1','sheet2','sheet3']
output = dict()
# Read excel document
for sheet in sheets:
    excel_data_df = pandas.read_excel('data.xlsx', sheet_name=sheet)

# Convert excel to string 
# (define orientation of document in this case from up to down)
    thisisjson = excel_data_df.to_json(orient='records')
    
# Print out the result
    print('Excel Sheet to JSON:\n', thisisjson)

# Make the string into a list to be able to input in to a JSON-file
    thisisjson_dict = json.loads(thisisjson)
    output[sheet] = thisisjson_dict
# Define file to write to and 'w' for write option -> json.dump() 
# defining the list to write from and file to write to
    with open('data.json', 'w') as json_file:
        json.dump(output, json_file)

Converting Excel Data to JSON using SHEETJS

https://github.com/SheetJS/sheetjs/issues/418

This github issue seems to discuss issues around this error. Most commonly seems to be that the readFile function does not access local file system and it seems like you are trying to load from a local file system.

Here is a potential solution that I have used in the past to load an excel file then parse each sheet into json data. NOTE: this was in an angular 12 project but the same readExcel function can be used.

component.html code:

<input type="file" id="file" multiple (change)="readExcel($event)">

component.ts code:

import { Component } from '@angular/core';
import * as XLSX from 'xlsx';

@Component({
  selector: 'my-app',
  styleUrls: ['./app.component.scss'],
  templateUrl: './app.component.html',
})
export class AppComponent {
 
  readExcel(event) {

    const file = event.target.files[0];
    const reader: FileReader = new FileReader();

    reader.readAsArrayBuffer(file);
    reader.onload = (e: any) => {

      // upload file
      const binarystr = new Uint8Array(e.target.result);
      const wb: XLSX.WorkBook = XLSX.read(binarystr, { type: 'array', raw: true, cellFormula: false });
      console.log(wb.Sheets)

      const wsname = wb.SheetNames[0];
      const data = XLSX.utils.sheet_to_json(wb.Sheets[wsname]);
      console.log(data)
    }
  }
}

Below is a screenshot of the test data I used from a test excel file that had two sheets in it with a 3x3 matrix of data on each sheet.

Sample Image

Below is a screenshot of the .json output form the .ts and .html code above when I uploaded the test excel file.

Sample Image

Convert Excel to JSON using LightweightExcelReader

If you don't mind a dependency on Newtonsoft JSON you could do something like:

public static class ExcelJsonExtensionMethods
{
    public static string ToJson(this SheetReader sheetReader)
    {
        IDictionary<int, string> HeaderNames = GetHeaderNames(sheetReader);
        var jArray = new JArray();
        while (sheetReader.ReadNext())
        {
            var jObject = new JObject();
            do
            {
                var propertyName = HeaderNames[new CellRef(sheetReader.Address).ColumnNumber];
                jObject[propertyName] = sheetReader.Value?.ToString();
            } while (sheetReader.ReadNextInRow());
            jArray.Add(jObject);
        }

        return jArray.ToString();
    }

    private static IDictionary<int, string> GetHeaderNames(SheetReader sheetReader)
    {
        var headerNames = new Dictionary<int, string>();
        while (sheetReader.ReadNextInRow())
        {
            headerNames.Add(new CellRef(sheetReader.Address).ColumnNumber, sheetReader.Value?.ToString());
        }

        return headerNames;
    }
}

Use it like:

var excelReader = new ExcelReader(@"path\to\file\test.xlsx");
var sheetReader = excelReader[0];
var sheetJson = sheetReader.ToJson();

Bear in mind that for this code to work:

The headers need to be on the first row
The header names need to be valid JSON property names
You can't have duplicate header names
There can't be any blank columns in the header

How to convert Excel to JSON and append it to existing JSON file?

This works

# Importing dependencies 
import pandas
import json

# Reading xlsx into pandas dataframe
df = pandas.read_excel('../Data/18-12-21.xlsx')

# Encoding/decoding a Dataframe using 'columns' formatted JSON
jsonfile = df.to_json(orient='columns')

# Print out the result
print('Excel Sheet to JSON:\n', jsonfile)

# Make the string into a list to be able to input in to a JSON-file
json_dict = json.loads(jsonfile)

# write from and file to write to
with open('data.json', 'w') as json_file:
    json.dump(json_dict, json_file)

Output

{
    "myValue": {
        "0": 1,
        "1": 2,
        "2": 4,
        "3": 5,
        "4": 6,
        "5": 7,
        "6": 8
    },
    "myValue2": {
        "0": "A",
        "1": "B",
        "2": "C",
        "3": "D",
        "4": "E",
        "5": "F",
        "6": "G"
    },
    "myValue3": {
        "0": "AA",
        "1": "BB",
        "2": "CC",
        "3": "DD",
        "4": "EE",
        "5": "FF",
        "6": "GG"
    }
}