Created
December 22, 2017 09:31
-
-
Save SehgalDivij/8e06b4658de6d0365e6a83b9a5b2613b to your computer and use it in GitHub Desktop.
Convert an excel workbook to a dictionary using python's xlrd module
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hello can some help me figure out why am i have index out of range error!