import pandas as pd data = { 'Name': ['Alice', 'Bob', 'Charlie'], 'Age': [25, 30, 35], 'Salary': [50000, 60000, 45000], 'Location': ['NY', 'CA', 'TX'], 'Department': ['HR', 'IT', 'Finance'] } df = pd.DataFrame(data) # 创建一个 Pandas ExcelWriter 对象 file_path = "excel.xlsx" writer = pd.ExcelWriter(file_path, engine='xlsxwriter') # 将 DataFrame 写入到 Excel 文件中,不保存索引列 df.to_excel(writer, index=False) # 获取 Excel 文件的工作簿和工作表对象 workbook = writer.book worksheet = writer.sheets['Sheet1'] # 根据 Sheet 名称获取工作表对象 # 根据内容宽度设置列宽 for col_num, col_value in enumerate(df.columns, start=0): column_len = max(df[col_value].astype(str).map(len).max(), len(col_value)) + 3 worksheet.set_column(col_num, col_num, column_len) # 创建标题行格式对象 header_format = workbook.add_format({ 'bold': True, # 设置标题行为粗体 'bg_color': '#95B3D7', # 设置标题行的背景颜色 'align': 'center', # 设置标题行的文本居中对齐 }) # 获取标题行的列数 num_cols = df.shape[1] # 设置标题行的格式 for col_num in range(num_cols): worksheet.write(0, col_num, df.columns[col_num], header_format) # 创建微软雅黑字体样式对象 yahei_format = workbook.add_format({ 'font_name': '微软雅黑', 'align': 'center'}) # 设置标题行的文本居中对齐 # 设置工作表的默认单元格格式为微软雅黑字体样式 worksheet.set_column(first_col=0, last_col=df.shape[1] - 1, width=None, cell_format=yahei_format) worksheet.set_column(1, 1, 10) # 创建右对齐的单元格格式对象 right_align_format = workbook.add_format({'align': 'right', 'font_name': '微软雅黑'}) # 设置第一列的宽度和样式 worksheet.set_column(0, 0, 15, cell_format=right_align_format) # 保存 Excel 文件 writer.save()
标签:format,df,worksheet,excel,DataFrame,存为,column,num,col From: https://www.cnblogs.com/nicole-zhang/p/17614024.html