scripts

random scripts
git clone https://git.parazyd.org/scripts
Log | Files | Refs

xlstojson.py (1453B)

      1 #!/usr/bin/env python3
      2 import xlrd
      3 import json
      4 import os.path
      5 import sys
      6 
      7 def getColNames(sheet):
      8 	rowSize = sheet.row_len(0)
      9 	colValues = sheet.row_values(0, 0, rowSize )
     10 	columnNames = []
     11 
     12 	for value in colValues:
     13 		columnNames.append(value)
     14 
     15 	return columnNames
     16 
     17 def getRowData(row, columnNames):
     18 	rowData = {}
     19 	counter = 0
     20 
     21 	for cell in row:
     22 		rowData[columnNames[counter]] = cell.value
     23 		counter +=1
     24 
     25 	return rowData
     26 
     27 def getSheetData(sheet, columnNames):
     28 	nRows = sheet.nrows
     29 	sheetData = []
     30 	counter = 1
     31 
     32 	for idx in range(1, nRows):
     33 		row = sheet.row(idx)
     34 		rowData = getRowData(row, columnNames)
     35 		sheetData.append(rowData)
     36 
     37 	return sheetData
     38 
     39 def getWorkBookData(workbook):
     40 	nsheets = workbook.nsheets
     41 	counter = 0
     42 	workbookdata = {}
     43 
     44 	for idx in range(0, nsheets):
     45 		worksheet = workbook.sheet_by_index(idx)
     46 		columnNames = getColNames(worksheet)
     47 		sheetdata = getSheetData(worksheet, columnNames)
     48 		workbookdata[worksheet.name] = sheetdata
     49 
     50 	return workbookdata
     51 
     52 def main():
     53 	filename = sys.argv[1]
     54 
     55 	if os.path.isfile(filename):
     56 		workbook = xlrd.open_workbook(filename, encoding_override="cp1252")
     57 		workbookdata = getWorkBookData(workbook)
     58 		output = \
     59 		open((filename.replace("xlsx", "json")).replace("xls", "json"), "w")
     60 		output.write(json.dumps(workbookdata, sort_keys=True, indent=4,  separators=(',', ": ")))
     61 		output.close()
     62 		print("%s was created" % output.name)
     63 	else:
     64 		print("Sorry, that was not a valid filename")
     65 
     66 
     67 
     68 main()