Skip to content

Instantly share code, notes, and snippets.

@SehgalDivij
Created December 22, 2017 09:31
Show Gist options
  • Save SehgalDivij/8e06b4658de6d0365e6a83b9a5b2613b to your computer and use it in GitHub Desktop.
Save SehgalDivij/8e06b4658de6d0365e6a83b9a5b2613b to your computer and use it in GitHub Desktop.
Convert an excel workbook to a dictionary using python's xlrd module
import xlrd
def make_json_from_data(column_names, row_data):
"""
take column names and row info and merge into a single json object.
:param data:
:param json:
:return:
"""
row_list = []
for item in row_data:
json_obj = {}
for i in range(0, column_names.__len__()):
json_obj[column_names[i]] = item[i]
row_list.append(json_obj)
return row_list
def xls_to_dict(workbook_url):
"""
Convert the read xls file into JSON.
:param workbook_url: Fully Qualified URL of the xls file to be read.
:return: json representation of the workbook.
"""
workbook_dict = {}
book = xlrd.open_workbook(workbook_url)
sheets = book.sheets()
for sheet in sheets:
if sheet.name == 'PortHoles & Discrete Appurtenan':
continue
workbook_dict[sheet.name] = {}
columns = sheet.row_values(0)
rows = []
for row_index in range(1, sheet.nrows):
row = sheet.row_values(row_index)
rows.append(row)
sheet_data = make_json_from_data(columns, rows)
workbook_dict[sheet.name] = sheet_data
return workbook_dict
# Sample Call:
sample = xls_to_dict('/home/root/workbook.xls')
print(sample)
@mbjallow6
Copy link

Hello can some help me figure out why am i have index out of range error!

columns = sheet.row_values(0)
return self._cell_values[rowx][start_colx:]
IndexError: list index out of range

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment