将样式从一个范围复制到另一个范围? [英] Copying styles from a range to another range?
问题描述
我有一个excel文件,用作模板,可以在其中添加所需的信息.
I have an excel file that I'm using as a template where I add information in as it's needed.
我需要在几个范围的单元格上完成特殊的样式和合并,但是当有大量数据时,我现在的处理方式(强制执行)非常慢.
I have special styles and merges that need to be done on a couple of ranges of cells, but the way I'm doing it right now (bruteforcing it) is very slow when there is a lot of data.
有没有一种方法可以使我做得更好?
Is there a way I could be doing this better?
for a in xrange(1, len(plans)):
offset = 3 * a
# Create blank templates for plans
for x in xrange(5, 549):
first_col_cell = recommended.cell(row=x, column=4)
second_col_cell = recommended.cell(row=x, column=5)
third_col_cell = recommended.cell(row=x, column=6)
new_first_col_cell = recommended.cell(row=x, column=4 + offset)
new_second_col_cell = recommended.cell(row=x, column=5 + offset)
new_third_col_cell = recommended.cell(row=x, column=6 + offset)
if third_col_cell.has_style and x != 42:
new_third_col_cell.font = copy(third_col_cell.font)
new_third_col_cell.border = copy(third_col_cell.border)
new_third_col_cell.fill = copy(third_col_cell.fill)
new_third_col_cell.number_format = copy(third_col_cell.number_format)
new_third_col_cell.protection = copy(third_col_cell.protection)
new_third_col_cell.alignment = copy(third_col_cell.alignment)
new_third_col_cell.value = copy(third_col_cell.value)
if second_col_cell.has_style and x != 42:
new_second_col_cell.font = copy(second_col_cell.font)
new_second_col_cell.border = copy(second_col_cell.border)
new_second_col_cell.fill = copy(second_col_cell.fill)
new_second_col_cell.number_format = copy(second_col_cell.number_format)
new_second_col_cell.protection = copy(second_col_cell.protection)
new_second_col_cell.alignment = copy(second_col_cell.alignment)
new_second_col_cell.value = copy(second_col_cell.value)
if first_col_cell.has_style and x != 42:
new_first_col_cell.font = copy(first_col_cell.font)
new_first_col_cell.border = copy(first_col_cell.border)
new_first_col_cell.fill = copy(first_col_cell.fill)
new_first_col_cell.number_format = copy(first_col_cell.number_format)
new_first_col_cell.protection = copy(first_col_cell.protection)
new_first_col_cell.alignment = copy(first_col_cell.alignment)
new_first_col_cell.value = copy(first_col_cell.value)
if (x >= 6 and x <= 33) or x == 36 or x == 41 or x == 44:
recommended.merge_cells(start_row=x, start_column=4 + offset, end_row=x, end_column=6 + offset)
if first_col_cell.has_style:
recommended_merge = colnum_string(4 + offset) + str(x) + ':' + colnum_string(6 + offset) + str(x)
style_border(recommended, recommended_merge, border)
second_col_cell.border = copy(first_col_cell.border)
third_col_cell.border = copy(first_col_cell.border)
如果需要的话,这里也是calnum_string函数:
Here's the calnum_string function too, in case that's needed:
def colnum_string(n):
div=n
string=""
while div>0:
module=(div-1)%26
string=chr(65+module)+string
div=int((div-module)/26)
return string
推荐答案
您的工作量用于复制这六个样式,例如
new_third_col_cell.font = copy(third_col_cell.font)
Your workload are to copy the six styles, e.g.
new_third_col_cell.font = copy(third_col_cell.font)
例如,尝试仅复制样式参考,而不要分配新样式
Try to copy only the style reference, instead of assigning new styles, for instance
target_cell._style = copy(source_cell._style)
您可以为每种样式节省〜10%,如果不是全部,则在所有单元格中都使用样式.
仅复制使用的样式,例如:
You can save ~10 %, per Style, if not all Styles are are used in all cells.
Copy only used styles, for instance:
if source_cell._style.fontId: target_cell.font = copy(s_cell.font)
...
除此之外,请考虑苗条您的代码,例如:
Besides this, consider to slim your code, for instance:
def copy_style(s_cell, t_cell):
...
def merge_cells(ws, row, offset, columns_456):
...
def main(recommended, plans):
column_456 = [4,5,6]
for a in xrange(1, len(plans)):
offset = 3 * a
# Create blank templates for plans
for x in xrange(5, 549):
if x != 42:
for column in column_456:
cell = recommended.cell(row=x, column=column)
if cell.has_style:
copy_style(cell, recommended.cell(row=x, column=column+offset))
if (x >= 6 and x <= 33) or x == 36 or x == 41 or x == 44:
merge_cells(recommended, x, offset, column_456)
这篇关于将样式从一个范围复制到另一个范围?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!