OpenOffice / LibreOffice: .ods Export To JSON (BASIC Macro)

ExportToJSON.bas

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

'Exports sheet(s) to JSON in this file's directory under their sheet name.'
'Booleans are exported as int (1 = true, 0 = false).'

SUB JSONExportAll

    sheets = ThisComponent.Sheets
    numSheets = sheets.Count
    
    FOR i = 0 TO (numSheets - 1)
    	sheet = Sheets(i)
    	ThisComponent.getCurrentController.setActiveSheet(sheet)
    	JSONExport
    NEXT i

END SUB

'--------------------------------------'

SUB JSONExport
	sheet = ThisComponent.getCurrentController.getActiveSheet
	
	numCols = 0
	cell = sheet.getCellByPosition(0, 0)
	WHILE cell.Type <> com.sun.star.table.CellContentType.EMPTY
		numCols = numCols + 1
		cell = sheet.getCellByPosition(numCols, 0)
	WEND
	
	numRows = 0
	cell = sheet.getCellByPosition(0, 0)
	WHILE cell.Type <> com.sun.star.table.CellContentType.EMPTY
		numRows = numRows + 1
		cell = sheet.getCellByPosition(0, numRows)
	WEND
	
	'Properties is the keys for the values.'
	propertyRange = sheet.getCellRangeByPosition(0, 0, numCols - 1, 0)
	propertyArray = propertyRange.getDataArray()(0) '2D Array, but only need row.'
	cells = sheet.getCellRangeByPosition(0, 0, numCols - 1, numRows - 1).getDataArray()

	fileName = GetDirectory + sheet.Name + ".json"
	file = Freefile
	OPEN fileName FOR OUTPUT AS file
	
	tabChr = CHR(9)
	doubleQuoteChr = CHR(34)
	
	PRINT #file, ("[")
	FOR r = 1 TO (numRows - 1):
		
		PRINT #file, (tabChr + "{")
		
		FOR c = 0 TO (numCols - 1):
			
			_key = propertyArray(c)
			keyStr = doubleQuoteChr + _key + doubleQuoteChr
			
			'Don't add quotes if value is a number.'
			_val = cells(r)(c)
			valStr = _val
			IF (NOT IsNumeric(_val)) THEN
				valStr = doubleQuoteChr + _val + doubleQuoteChr
			ENDIF
			
			line = tabChr + tabChr + keyStr + ": " + valStr
			IF (c < numCols - 1) THEN
				line = line + ","
			ENDIF
			
			PRINT #file, (line)
			
		NEXT c
		
		endLine = tabChr + "}"
		IF (r < numRows - 1) THEN
			endLine = endLine + ","
		ENDIF
		PRINT #file, (endLine)
	
	NEXT r 
	PRINT #file, ("]")
	CLOSE #file
	
END SUB

'--------------------------------------'

FUNCTION GetDirectory() AS STRING

	fileName = ThisComponent.getUrl()
	fileNameSegments = Split(fileName, "/")
	numFileNameSegments = UBound(fileNameSegments) + 1
	
	dirName = ""
	
	FOR i = 0 TO (numFileNameSegments - 2)
		dirName = dirName + fileNameSegments(i) + "/"
	NEXT i
	
	GetDirectory = dirName

END FUNCTION

basic sucks

For those in a hurry: remove any leading empty rows (or columns) to make this work, and the exported file will be named after the sheet (e.g., Sheet1.json).

basic sucks

Thank you for your sacrifice!

This is GREAT! Thanks for sharing 🙇

Thank you.
This worked great. I was considering getting YLC utils just for the JSON export, but this worked so much better.

Keep the great work.