Pandas可以将单元格合并后写入Excel文件吗？

huangapple
go评论96阅读模式
英文:

Can Pandas write excel files with merged cells?

问题

我有一些数据，想要将其导出到Excel电子表格中。这些数据包含有不同数量元素的嵌套字典。

数据的结构如下：

  1. [{ "12345" :
  2.     { "cup" : "123456789",
  3.       "spoon" : "234567891",
  4.     }
  5.  },
  6.  { "23456" :
  7.     { "plate" : "345678912",
  8.     }
  9.  }
  10. ]

我想要将这些数据导出到一个看起来像这样的Excel电子表格中：

Pandas可以将单元格合并后写入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

  1. [{ "12345" :
  2.     { "cup" : "123456789",
  3.       "spoon" : "234567891",
  4.     }
  5.  },
  6.  { "23456" :
  7.     { "plate" : "345678912",
  8.     }
  9.  }
  10. ]

I want to export this data in an Excel spreadsheet that looks like this:

Pandas可以将单元格合并后写入Excel文件吗？

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来完成。

这是一个示例，我在列表中包含了一些额外的数据以供展示；

  1. import xlsxwriter
  3. data_list = [
  4.     {"12345":
  5.          {"cup": "123456789",
  6.           "spoon": "234567891",
  7.           }
  8.      },
  9.     {"23456":
  10.          {"plate": "345678912",
  11.           }
  12.      },
  13.     {"11111":
  14.          {"knife": "12121212",
  15.           "fork": "23232323",
  16.           "spoon": "34343434"
  17.           }
  18.      },
  19.     {"22222":
  20.          {"cup": "56565656",
  21.           "saucer": "67676767"
  22.           }
  23.      }
  24. ]
  26. ### 新建工作簿和工作表
  27. wb = xlsxwriter.Workbook('xlsxwriter_merge_example.xlsx')
  28. ws = wb.add_worksheet()
  30. ### 单元格格式化
  31. header_format = wb.add_format({"bold": True, "num_format": "0", "align": "center", "valign": "vcenter", "border": 1})
  32. cell_format_str = wb.add_format({"border": 1})
  33. cell_format_num = wb.add_format({"num_format": "0", "border": 1})
  35. ws.write(0, 0, 'Order', header_format)
  36. ws.merge_range("B1:C1", "Item", header_format)
  38. row_index = 1
  39. for row_data in data_list:
  40.     for col_index, (cell_key, cell_value) in enumerate(row_data.items()):
  41.         if len(cell_value) >= 2:
  42.             ws.merge_range(row_index,
  43.                            col_index,
  44.                            (row_index + len(cell_value)) - 1,
  45.                            col_index,
  46.                            int(cell_key),
  47.                            header_format)
  48.         else:
  49.             ws.write(row_index, col_index, int(cell_key), header_format)
  50.         for item_key, item_value in cell_value.items():
  51.             ws.write(row_index, col_index + 1, item_key, cell_format_str)
  52.             ws.write(row_index, col_index + 2, int(item_value), cell_format_num)
  53.             row_index += 1
  55. ws.autofit()
  56. wb.close()

输出带有额外数据的工作表。已应用最少量的格式化。

Pandas可以将单元格合并后写入Excel文件吗？

英文:

This can be done with just Xlsxwriter.<br>

This is an example, I have included some extra data in the list for show;

  1. import xlsxwriter
  4. data_list = [
  5.     {&quot;12345&quot;:
  6.          {&quot;cup&quot;: &quot;123456789&quot;,
  7.           &quot;spoon&quot;: &quot;234567891&quot;,
  8.           }
  9.      },
  10.     {&quot;23456&quot;:
  11.          {&quot;plate&quot;: &quot;345678912&quot;,
  12.           }
  13.      },
  14.     {&quot;11111&quot;:
  15.          {&quot;knife&quot;: &quot;12121212&quot;,
  16.           &quot;fork&quot;: &quot;23232323&quot;,
  17.           &quot;spoon&quot;: &quot;34343434&quot;
  18.           }
  19.      },
  20.     {&quot;22222&quot;:
  21.          {&quot;cup&quot;: &quot;56565656&quot;,
  22.           &quot;saucer&quot;: &quot;67676767&quot;
  23.           }
  24.      }
  25. ]
  27. ### New workbook and sheet
  28. wb = xlsxwriter.Workbook(&#39;xlsxwriter_merge_example.xlsx&#39;)
  29. ws = wb.add_worksheet()
  31. ### Cell formatting
  32. header_format = wb.add_format({&quot;bold&quot;: True, &quot;num_format&quot;: &quot;0&quot;, &quot;align&quot;: &quot;center&quot;, &quot;valign&quot;: &quot;vcenter&quot;, &quot;border&quot;: 1})
  33. cell_format_str = wb.add_format({&quot;border&quot;: 1})
  34. cell_format_num = wb.add_format({&quot;num_format&quot;: &quot;0&quot;, &quot;border&quot;: 1})
  36. ws.write(0, 0, &#39;Order&#39;, header_format)
  37. ws.merge_range(&quot;B1:C1&quot;, &quot;Item&quot;, header_format)
  40. row_index = 1
  41. for row_data in data_list:
  42.     for col_index, (cell_key, cell_value) in enumerate(row_data.items()):
  43.         if len(cell_value) &gt;= 2:
  44.             ws.merge_range(row_index,
  45.                            col_index,
  46.                            (row_index + len(cell_value)) - 1,
  47.                            col_index,
  48.                            int(cell_key),
  49.                            header_format)
  50.         else:
  51.             ws.write(row_index, col_index, int(cell_key), header_format)
  52.         for item_key, item_value in cell_value.items():
  53.             ws.write(row_index, col_index + 1, item_key, cell_format_str)
  54.             ws.write(row_index, col_index + 2, int(item_value), cell_format_num)
  55.             row_index += 1
  57. ws.autofit()
  58. wb.close()

Output sheet with the extra data. <br>Minimal amount of formatting has been applied. <br>
Pandas可以将单元格合并后写入Excel文件吗？

huangapple
  • 本文由 发表于 2023年7月27日 14:33:03
  • 转载请务必保留本文链接：https://go.coder-hub.com/76777047.html
  • pandas
  • python
  • xlsxwriter
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定