There is no predefined method in Excel to convert the Excel data to JSON. You can either use online Excel to JSON conversion software or download an add-in from the Microsoft store for this task to get done.
You can try this tool I made:
Mr. Data Converter
It converts to JSON, XML and others.
It's all client side, too, so your data never leaves your computer.
This worked perfectly for me and does NOT require a file upload:
https://github.com/cparker15/csv-to-json?files=1
Since Powershell 3.0 (shipped with Windows 8, available for Windows 7 and windows Server 2008 but not Windows Vista ) you can use the built-in convertto-json commandlet:
PS E:> $topicsjson = import-csv .\itinerary-all.csv | ConvertTo-Json
PS E:\> $topicsjson.Length
11909
PS E:\> $topicsjson.getType()
IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True True Object[] System.Array
Online Help Page on Technet
If you can't find an existing solution it's pretty easy to build a basic one in Java. I just wrote one for a client and it took only a couple hours including researching tools.
Apache POI will read the Excel binary. http://poi.apache.org/
JSONObject will build the JSON
After that it's just a matter of iterating through the rows in the Excel data and building a JSON structure. Here's some pseudo code for the basic usage.
FileInputStream inp = new FileInputStream( file );
Workbook workbook = WorkbookFactory.create( inp );
// Get the first Sheet.
Sheet sheet = workbook.getSheetAt( 0 );
// Start constructing JSON.
JSONObject json = new JSONObject();
// Iterate through the rows.
JSONArray rows = new JSONArray();
for ( Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext(); )
{
Row row = rowsIT.next();
JSONObject jRow = new JSONObject();
// Iterate through the cells.
JSONArray cells = new JSONArray();
for ( Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext(); )
{
Cell cell = cellsIT.next();
cells.put( cell.getStringCellValue() );
}
jRow.put( "cell", cells );
rows.put( jRow );
}
// Create the JSON.
json.put( "rows", rows );
// Get the JSON text.
return json.toString();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With