在 Python 中使用 XLRD 迭代行和列 [英] Iterating rows and columns using XLRD in Python
问题描述
我正在使用 python xlrd 模块来解析 Excel 文件.这是excel文件的样子:
标题 A B C属性 1 1 2 3属性 2 4 5 6属性 3 7 8 9
而且我想要以下格式的输出:
<预><代码>[{"name": "A",属性1":{价值":1},属性2":{价值":4},属性3":{价值":7}},{"name": "B",属性1":{价值":2},属性2":{价值":5},属性3":{价值":8}},{"name": "C",属性1":{价值":3},属性2":{价值":6},属性3":{价值":9}}]我尝试了以下操作,但不知道如何以上述格式创建输出.将不胜感激!
from xlrd import open_workbookwb = open_workbook('D:\abc_Template.xlsx', 'r')wb_sheet = wb.sheet_by_index(0)值 = []对于范围内的 row_idx(7,wb_sheet.nrows):col_value = []rowval = str((wb_sheet.cell(row_idx, 1)))对于范围 (1, 5) 中的 col_idx:if(col_idx != 2 和 col_idx != 1):cellVal = wb_sheet.cell(row_idx, col_idx)cellObj = {rowval: {"value" : cellVal}}col_value.append(cellObj)values.append(col_value)打印值
range(7, wb_sheet.nrows) 和 range(1, 5) 中的迭代器值不等价于输入表的维度.先按列再按行解析数据似乎更容易.我对您的解析器有以下代码建议:
from xlrd import open_workbook导入jsonwb = open_workbook('abc_Template.xlsx', 'r')wb_sheet = wb.sheet_by_index(0)值 = []对于范围内的 col_idx(1,wb_sheet.ncols):cellObj = {"name": str(wb_sheet.cell(0, col_idx).value)}对于范围内的 row_idx(1,wb_sheet.nrows):attrib = str(wb_sheet.cell(row_idx, 0).value)cellObj[str(attrib)] = {"value": int(wb_sheet.cell(row_idx, col_idx).value)}values.append(cellObj)打印(json.dumps(值))
OBS:本示例运行python版本> 3,确保导入json库并更改.xlsx文件的输入路径.
I'm using python xlrd module to parse an Excel file. Here is how the excel file looks like:
Title A B C
attribute 1 1 2 3
attribute 2 4 5 6
attribute 3 7 8 9
And I want the output in the following format:
[
{
"name": "A",
"attribute1": {
"value": 1
},
"attribute2": {
"value": 4
},
"attribute3": {
"value": 7
}
},
{
"name": "B",
"attribute1": {
"value": 2
},
"attribute2": {
"value": 5
},
"attribute3": {
"value": 8
}
},
{
"name": "C",
"attribute1": {
"value": 3
},
"attribute2": {
"value": 6
},
"attribute3": {
"value": 9
}
}
]
I have tried the following but can't figure out how to create the output in the above format. Would appreciate any help on this!
from xlrd import open_workbook
wb = open_workbook('D:\abc_Template.xlsx', 'r')
wb_sheet = wb.sheet_by_index(0)
values = []
for row_idx in range(7, wb_sheet.nrows):
col_value = []
rowval = str((wb_sheet.cell(row_idx, 1)))
for col_idx in range(1, 5):
if(col_idx != 2 and col_idx != 1):
cellVal = wb_sheet.cell(row_idx, col_idx)
cellObj = {rowval: {"value" : cellVal}}
col_value.append(cellObj)
values.append(col_value)
print values
The iterator values in range(7, wb_sheet.nrows) and range(1, 5) are not equivalent with the dimension of input table . It seems more easy to parse the data first by column and after by row. I have a code suggestion to your parser below:
from xlrd import open_workbook
import json
wb = open_workbook('abc_Template.xlsx', 'r')
wb_sheet = wb.sheet_by_index(0)
values = []
for col_idx in range(1, wb_sheet.ncols):
cellObj = {"name": str(wb_sheet.cell(0, col_idx).value)}
for row_idx in range(1, wb_sheet.nrows):
attrib = str(wb_sheet.cell(row_idx, 0).value)
cellObj[str(attrib)] = {"value": int(wb_sheet.cell(row_idx, col_idx).value)}
values.append(cellObj)
print(json.dumps(values))
OBS: This example run with python version > 3, make sure to import json library and change the input path of the .xlsx file.
这篇关于在 Python 中使用 XLRD 迭代行和列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!