python+excel⼯资条⾃动⽣成
年终绩效分配结果出来了，领导要求每⼈要清楚地知道⾃⼰的情况。要求：总绩效和各分类都要清楚。这就表⽰我们要给每⼈六个纸条，⼀个总的，五个分的。打出来，裁开，分发给每个⼈！累死⼈。所以，我就想能否每⼈⽣成⼀个表，直接A4打印！
表的结构⼤致是这个样⼦。总绩效表和五个分绩效表。我的想法是：根据每个⼈的姓名⽣成⼀张表，把每个绩效表中对应姓名的数据⾏写⼊到该表中。调整格式打印输出即可。尝试的过程中，填了不少坑，但是把OpenPyXl这个库倒是⼤体上整明⽩了。不废话，直接上代码：
# open a excel file
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Border, Side, Alignment
border_a = Border(
left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
)
# 如果字体变化，适当调整这两个参数即可
# ⼀个汉字在Excel中的⼤致列宽
h_w = 2.1
# ⼀个英⽂字符在Excel中的⼤致列宽
n_w = 1.1
# 姓名列在你的Excel⽂件中的列标签
label_name = '姓名'
wb_read = openpyxl.load_workbook(filename='绩效.xlsx')
wb_write = openpyxl.Workbook()
sheet_list = wb_read.sheetnames
wsr = wb_read[sheet_list[0]]
# get where is the label about '姓名'
def get_position_name(label_str=label_name, ws_obj=wsr):
for i in range(1, ws_obj.max_row + 1):
for j in range(1, ws_obj.max_column + 1):
if ll(i, j).value == label_str:
return i, j
return 0, 0
# move a row from one sheet object to another
def move_row(s_sheet_obj, s_row_no, d_sheet_obj, d_row_no):
if s_sheet_obj.max_column >= 1:
d_w_dimensions[d_row_no].height = 25
for i0 in range(1, s_sheet_obj.max_column + 1):
d_ll(d_row_no, i0).value = s_ll(s_row_no, i0).value
print("successful")
else:
print("There is not any data in the source obj")
# set the width of column of one sheet
def set_width(s_s_obj):
max_col = s_s_obj.max_column
for i in range(1, max_col + 1):
width_col = get_max_col_width(s_s_obj[get_column_letter(i)])
s_lumn_dimensions[get_column_letter(i)].width = width_col
def set_height(s_s_obj, start_r, end_r):
for i in range(start_r, end_r + 1):
s_w_dimensions[i].height = 25
for j in range(1, s_s_obj.max_column + 1):
s_ll(i, j).alignment = Alignment(horizontal='center', vertical='center')
# 得到⼀列中的最⼤列宽
def get_max_col_width(col_obj):
length_max = 0
for ce in col_obj:
ce = str(ce.value)
ce_char = count_char(ce)
lenth_t = int(ce_char[0] * h_w + ce_char[1] * n_w + 0.9)
if lenth_t > length_max:
length_max = lenth_t
return length_max
# 给特定区域内的单元格加上框线
def draw_lines(ss_obj, start_r, start_c, end_r, end_c):
for i in range(start_r, end_r + 1):
for j in range(start_c, end_c + 1):
ll(i, j).border = border_a
# 为了设置列宽的精确，需要知道单元格中有⼏个汉字⼏个英⽂字符
def count_char(s):
ch_h = 0
ch_n = 0
excel自动生成排名
for c in s:
if ord(c) > 255:
ch_h = ch_h + 1
else:
ch_n = ch_n + 1
return ch_h, ch_n
name_pos = get_position_name(label_str='姓名')
for n in range(name_pos[0] + 1, wsr.max_row + 1):
# for n in range(name_pos[0] + 1, 5):
t_name = ll(n, name_pos[1]).value
if n == name_pos[0] + 1:
wsw = wb_write.active
wsw.title = t_name
else:
wsw = ate_sheet(title=t_name)
for i in range(0, len(sheet_list)):
wsr_temp = wb_read[sheet_list[i]]
pos_temp = get_position_name(t_name, wsr_temp)
print(t_name)
print(pos_temp)
# write the data of object line into object sheet
move_row(wsr_temp, pos_temp[0], wsw, 4 * i + 4)
# write the title data
move_row(wsr_temp, name_pos[0], wsw, 4 * i + 3)
# setup the column width
set_width(wsw)
draw_lines(wsw, 4 * i + 2, 1, 4 * i + 4, wsw.max_column)
<_cells(start_row=4 * i + 2, start_column=1, end_row=4 * i + 2, end_column=wsw.max_column)        ll(4 * i + 2, 1).value = wsr_temp.title
set_height(wsw, 1, 100)
wb_write.save('ends.xlsx')
运⾏后⽣成的结果，⼤致是这个样⼦，虽然不够完美，但能不⽤剪⼑，我就觉得很开⼼了！