OpenOffice Export to JSON

'OpenOffice Basic macro for exporting a .ods spreadsheet to JSON (and CSV)
'Project-specific formating I implemented: cull empty or "0" entries, '^' as separator value for the csv

REM  *****  BASIC  *****

Sub Main

'First export to CSV which we will later use as the source for the export to json
Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="94,34,0,1,1"
Doc = ThisComponent
Dim FileName as String
FileName = Doc.getURL()
splitName = split(FileName, ".")
FileName = splitName(0) + ".csv"
Doc.StoreToURL(FileName, Propval())

'Export to JSON

'Get the number of keys in the header of the csv
Dim csv As Integer
Dim len As Integer
csv = FreeFile
Open FileName For Input As csv
Line Input #csv, first
keys = split(first, "^")
len = 0
For Each i in keys
	len = len + 1
Next i

'Need to count the number of lines in the csv (after header line)
Dim lines as Integer
lines = 0
Do While not eof(csv)
	Line Input #csv, line   
	If line <>"" Then
		lines = lines + 1
	End If
Loop
Close #csv

'Need to re-open the csv again for a fresh read from just after the header
Open FileName For Input As csv
Line Input #csv, line

'Open the JSON file for writing
Dim json As Integer
Dim CurrentLine As String
Dim fn As String
fn = splitName(0) + ".json" 
json = Freefile

Open fn For Output As json
Print #json, "["

For line = 0 to lines-1
	Line Input #csv, CurrentLine   
	If CurrentLine <>"" Then
    	values = split(CurrentLine, "^")
    	
    	'Find the last non-empty or non-zero line for each entry
    	lastValidLine = 0
    	For j = 0 to len-1
    		If values(j) = "" OR values(j) = "0" Then
    			'NOT
    		Else
    			lastValidLine = j
    		End If
    	Next j
    		
    	Print #json, "    {"
    	
    	For i = 0 To len-1
    		keyValString = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34) & ","
    		keyValLastEntry = "        " & CHR(34) & LCase(keys(i)) & CHR(34) & ": " & CHR(34) & values(i) & CHR(34)
    		    		
	    	If i = len-1 Then
	    		'Don't include comma after last entry
	    		Print #json, keyValLastEntry
	    	Else
	    		If i = lastValidLine Then
	    			Print #json, keyValLastEntry
	    		Else
	    			Print #json, keyValString
	    		End If
	    		
	    	End If
    	Next i
    	
    	'Next object
    	If line = lines-1 Then
    		Print #json, "    }"
    	Else
    		Print #json, "    },"
    	End If 	
	End If
Next line
 
 
' Close file 
Print #json, "]"
Close #json
Close #csv          

End Sub

This macro exports the "Active" sheet as JSON.
So to export any particular sheet, set that sheet as active sheet just after declaring Sub

e.g. [Rename "Sheet1" with your required sheet Name]

Sub export_as_json REM ------ Set Sheet1 as active sheet ------------- ThisComponent.getcurrentController.setActiveSheet(ThisComponent.Sheets.getByName("Sheet1"))

Otherwise manually go to the sheet of your choice and run the macro from there. It will export only that sheet.

I've made a version for LibreOffice (I think this also works for OpenOffice?) that doesn't rely on a CSV export if anyone's interested.
This can also export all sheets in the file.
https://gist.github.com/ceruleancerise/de1f6a7a9bf285e033e9135c5049216b

@ceruleancerise
Good Work ! especially for getting rid of CSV !
Thanks

Just a few suggestions ... [ As Mentioned in https://gist.github.com/pharmankur/773fcb940338d304b13d6e4f93f3ac3f ]

As Many JSON specifications requires to be in that way , and JSON has specific for parameters, kindly consider :-

1. Let Boolean get expressed as TRUE / FALSE , your output represents as 1 / 0 ... [ I know , You have clarified it specifically ]
2. Let blank cell get expressed as null , your output represents it as "" [ as you know in JSON, "" <> null ]