Dec-05-2018, 01:38 AM
Hi there,
It seems that I have an issue with this python 2 script (from https://github.com/paceaux/xlsx-to-json) translated to py3.
Any idea on how I could improve it?
It return the following
It seems that I have an issue with this python 2 script (from https://github.com/paceaux/xlsx-to-json) translated to py3.
Any idea on how I could improve it?
It return the following
Quote:Enter the path to the filename -> C:\......\MS1.xls
Traceback (most recent call last):
File "C:\......xls2json.py", line 69, in <module>
main()
File "C:\......\xls2json.py", line 63, in main
output.write(json.dumps(workbookdata, sort_keys=True, indent=2, separators=(',', ": ")))
TypeError: a bytes-like object is required, not 'str'
import xlrd import xlwt import json import os.path import datetime def getColNames(sheet): rowSize = sheet.row_len(0) colValues = sheet.row_values(0, 0, rowSize ) columnNames = [] for value in colValues: columnNames.append(value) return columnNames def getRowData(row, columnNames): rowData = {} counter = 0 for cell in row: # check if it is of date type print in iso format if cell.ctype==xlrd.XL_CELL_DATE: rowData[columnNames[counter].lower().replace(' ', '_')] = datetime.datetime(*xlrd.xldate_as_tuple(cell.value,0)).isoformat() else: rowData[columnNames[counter].lower().replace(' ', '_')] = cell.value counter +=1 return rowData def getSheetData(sheet, columnNames): nRows = sheet.nrows sheetData = [] counter = 1 for idx in range(1, nRows): row = sheet.row(idx) rowData = getRowData(row, columnNames) sheetData.append(rowData) return sheetData def getWorkBookData(workbook): nsheets = workbook.nsheets counter = 0 workbookdata = {} for idx in range(0, nsheets): worksheet = workbook.sheet_by_index(idx) columnNames = getColNames(worksheet) sheetdata = getSheetData(worksheet, columnNames) workbookdata[worksheet.name.lower().replace(' ', '_')] = sheetdata return workbookdata def main(): filename = input("Enter the path to the filename -> ") if os.path.isfile(filename): workbook = xlrd.open_workbook(filename) workbookdata = getWorkBookData(workbook) output = \ open((filename.replace("xlsx", "json")).replace("xls", "json"), "wb") output.write(json.dumps(workbookdata, sort_keys=True, indent=2, separators=(',', ": "))) output.close() print("%s was created" %output.name) else: print("Sorry, that was not a valid filename") main()