英文:
Set Header colour and border for all sheets in an excel file with python
问题
我有一个包含多个工作表的Excel文件。我尝试一次性设置所有Excel工作表的标题背景颜色和边框。
这些Excel工作表是我通过ExcelWriter创建的输出数据框架。
writer = pd.ExcelWriter(os.path.join(path, 'file.xlsx'), engine='xlsxwriter')
df1.to_excel(writer, sheet_name='df1', index=False)
df2.to_excel(writer, sheet_name='df2', index=False)
# 以此类推
我只是试图找出如何在这些工作表中添加格式。
英文:
I have an Excel file with multiple sheets. I am trying to set the header background colour and border for all the Excel sheets at once.
This excel sheets are output dataframes which I am creating through ExcelWriter.
writer = pd.ExcelWriter(os.path.join(path, 'file.xlsx'), engine = 'xlsxwriter'
df1.to_excel(writer, sheetname = 'df1', index = False)
df2.to_excel(writer, sheetname = 'df2', index = False)
.
.
.
.
and so on
I'm just trying to figure out how to add the formatting in these sheets.
答案1
得分: 1
You can use add_format along with write to format cells in Excel using the xlsxwriter library. Here's the code you provided:
import pandas as pd
import xlsxwriter
dmap = {"bg_color": "#00D100", "border": 2, "bold": True}
df1 = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]})
df2 = pd.DataFrame({"C": [7, 8], "D": [9, 10], "E": [11, 12]})
list_dfs = [df1, df2]
with pd.ExcelWriter("output.xlsx", engine="xlsxwriter") as writer:
for idx1, df in enumerate(list_dfs, start=1):
ws_name = f"Sheet{idx1}" # change the sheet name here (df for example)
df.to_excel(writer, sheet_name=ws_name, index=False)
for idx2, col in enumerate(df.columns):
writer.sheets[ws_name].write(0, idx2, col, writer.book.add_format(dmap))
Output:
英文:
IIUC, you can use add_format along with write :
import pandas as pd
import xlsxwriter
dmap = {"bg_color": "#00D100", "border": 2, "bold": True}
df1 = pd.DataFrame({"A": [1, 2, 3], "B": [4, 5, 6]})
df2 = pd.DataFrame({"C": [7, 8], "D": [9, 10], "E": [11, 12]})
list_dfs = [df1, df2]
with pd.ExcelWriter("output.xlsx", engine="xlsxwriter") as writer:
for idx1, df in enumerate(list_dfs, start=1):
ws_name = f"Sheet{idx1}" # change the sheet name here (df for example)
df.to_excel(writer, sheet_name=ws_name, index=False)
for idx2, col in enumerate(df.columns):
writer.sheets[ws_name].write(0, idx2, col, writer.book.add_format(dmap))
Output :
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论