代码之家 › 专栏 › 技术社区 › r.user.05apr

定义不同的pandas xlsxwriter边框类型

xlsxwriter pandas python

r.user.05apr · 技术社区 · 7 年前

我想用xlsxwriter定义下边框的不同优势。我的问题是:在下面的示例中,如何防止覆盖厚的底部标题边框? 我想指出的是,如果我稍后使用条件格式开始一行,我会得到一行没有底部边框。

import numpy as np
import pandas as pd

dates = pd.DataFrame(pd.date_range('2000-01-01', periods = 15), columns = ['date'])
d = pd.DataFrame(np.random.randint(1, 5, (15, 3)), columns = list('abc'))
d = pd.concat([dates, d], axis = 1)

writer = pd.ExcelWriter('dates_border.xlsx',
                    #OR: 'c:\\users\\USER_NAME\\desktop\\dates_border.xlsx',
                    datetime_format='yyyy.mm') 
d.to_excel(writer, 'Sheet1', index = False)
worksheet = writer.sheets['Sheet1']
workbook  = writer.book

# header: I LOSE THE THICK BOTTOM BORDER OF THE HEADER
header_format = workbook.add_format({'bottom': 2, 'bg_color': '#F9DA04'})
for col_num, value in enumerate(d.columns.values):
worksheet.write(0, col_num, value, header_format)

# data with bottom borders
format1 = workbook.add_format()
format1.set_bottom(7)

worksheet.conditional_format(1, 0, d.shape[0], d.shape[1] -1,
{'type': 'cell',
 'criteria': '<>',
 'value': '""',
 'format': format1})

writer.save()

Thx和;亲切的问候

1 回复 | 直到 7 年前

patrickjlong1 7 年前

我在下面提供了一个解决方法。我为第二行添加了另一种条件格式,称为“format2”。它用粗边框设置单元格的顶部,用虚线设置单元格的底部。

import numpy as np
import pandas as pd

dates = pd.DataFrame(pd.date_range('2000-01-01', periods = 15), columns = ['date'])
d = pd.DataFrame(np.random.randint(1, 5, (15, 3)), columns = list('abc'))
d = pd.concat([dates, d], axis = 1)

writer = pd.ExcelWriter('dates_border.xlsx',
                    #OR: 'c:\\users\\USER_NAME\\desktop\\dates_border.xlsx',
                    datetime_format='yyyy.mm') 
d.to_excel(writer, 'Sheet1', index = False)
worksheet = writer.sheets['Sheet1']
workbook  = writer.book

# header: I LOSE THE THICK BOTTOM BORDER OF THE HEADER
header_format = workbook.add_format({'bottom': 2, 'bg_color': '#F9DA04'})
for col_num, value in enumerate(d.columns.values):
    worksheet.write(0, col_num, value, header_format)

# data with bottom borders
format1 = workbook.add_format()
format1.set_bottom(7)

#snippet added to op script
format2 = workbook.add_format()
format2.set_top(2)
format2.set_bottom(7)

worksheet.conditional_format(1, 0, 1, d.shape[1] -1,
{'type': 'cell',
 'criteria': '<>',
 'value': '""',
 'format': format2})
#end of snippet added to op script

worksheet.conditional_format(1, 0, d.shape[0], d.shape[1] -1,
{'type': 'cell',
 'criteria': '<>',
 'value': '""',
 'format': format1})

writer.save()

具有预期输出: