英文:
Can Pandas write excel files with merged cells?
问题
我有一些数据，想要将其导出到Excel电子表格中。这些数据包含有不同数量元素的嵌套字典。
数据的结构如下：
[{ "12345" :
{ "cup" : "123456789",
"spoon" : "234567891",
}
},
{ "23456" :
{ "plate" : "345678912",
}
}
]
我想要将这些数据导出到一个看起来像这样的Excel电子表格中：
我的数据更加复杂，但是如果我知道如何完成这个任务，我可以自己应用到我的数据上。
所以我考虑使用Python的xlsxwriter模块，但是我需要遍历数据来创建单元格。
然后我想起Pandas有一种简单的方法可以将这样的数据导入到一个数据框中，并且可以很好地导出到Excel中。
但是我不知道Pandas是否支持合并单元格。
在这种情况下，你会建议我使用什么？
英文:
I have some data that I want to export in an excel spread sheet. The data contains nested dictionaries with variable number of elements.
It looks like this
[{ "12345" :
{ "cup" : "123456789",
"spoon" : "234567891",
}
},
{ "23456" :
{ "plate" : "345678912",
}
}
]
I want to export this data in an Excel spreadsheet that looks like this:
My data is more complex, but I guess if I understand how to get this done I can apply it myself.
So I was thinking about using the xlsxwriter python module, but I would have to loop thru the data to create the cells.
Then I remembered that Pandas has an easy way to import such data in a dataframe and has a nice excel export.
But I don't now if Pandas supports something like merged cells.
What would you suggest to use in such case?
答案1
得分: 1
这可以通过使用Xlsxwriter来完成。
这是一个示例，我在列表中包含了一些额外的数据以供展示；
import xlsxwriter
data_list = [
{"12345":
{"cup": "123456789",
"spoon": "234567891",
}
},
{"23456":
{"plate": "345678912",
}
},
{"11111":
{"knife": "12121212",
"fork": "23232323",
"spoon": "34343434"
}
},
{"22222":
{"cup": "56565656",
"saucer": "67676767"
}
}
]
### 新建工作簿和工作表
wb = xlsxwriter.Workbook('xlsxwriter_merge_example.xlsx')
ws = wb.add_worksheet()
### 单元格格式化
header_format = wb.add_format({"bold": True, "num_format": "0", "align": "center", "valign": "vcenter", "border": 1})
cell_format_str = wb.add_format({"border": 1})
cell_format_num = wb.add_format({"num_format": "0", "border": 1})
ws.write(0, 0, 'Order', header_format)
ws.merge_range("B1:C1", "Item", header_format)
row_index = 1
for row_data in data_list:
for col_index, (cell_key, cell_value) in enumerate(row_data.items()):
if len(cell_value) >= 2:
ws.merge_range(row_index,
col_index,
(row_index + len(cell_value)) - 1,
col_index,
int(cell_key),
header_format)
else:
ws.write(row_index, col_index, int(cell_key), header_format)
for item_key, item_value in cell_value.items():
ws.write(row_index, col_index + 1, item_key, cell_format_str)
ws.write(row_index, col_index + 2, int(item_value), cell_format_num)
row_index += 1
ws.autofit()
wb.close()
输出带有额外数据的工作表。已应用最少量的格式化。
英文:
This can be done with just Xlsxwriter.<br>
This is an example, I have included some extra data in the list for show;
import xlsxwriter
data_list = [
{"12345":
{"cup": "123456789",
"spoon": "234567891",
}
},
{"23456":
{"plate": "345678912",
}
},
{"11111":
{"knife": "12121212",
"fork": "23232323",
"spoon": "34343434"
}
},
{"22222":
{"cup": "56565656",
"saucer": "67676767"
}
}
]
### New workbook and sheet
wb = xlsxwriter.Workbook('xlsxwriter_merge_example.xlsx')
ws = wb.add_worksheet()
### Cell formatting
header_format = wb.add_format({"bold": True, "num_format": "0", "align": "center", "valign": "vcenter", "border": 1})
cell_format_str = wb.add_format({"border": 1})
cell_format_num = wb.add_format({"num_format": "0", "border": 1})
ws.write(0, 0, 'Order', header_format)
ws.merge_range("B1:C1", "Item", header_format)
row_index = 1
for row_data in data_list:
for col_index, (cell_key, cell_value) in enumerate(row_data.items()):
if len(cell_value) >= 2:
ws.merge_range(row_index,
col_index,
(row_index + len(cell_value)) - 1,
col_index,
int(cell_key),
header_format)
else:
ws.write(row_index, col_index, int(cell_key), header_format)
for item_key, item_value in cell_value.items():
ws.write(row_index, col_index + 1, item_key, cell_format_str)
ws.write(row_index, col_index + 2, int(item_value), cell_format_num)
row_index += 1
ws.autofit()
wb.close()
Output sheet with the extra data. <br>Minimal amount of formatting has been applied. <br>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论