从 Python 2 到 Python 3 重写 xlsxwriter 框边框的一些函数 [英] Rewriting some functions for xlsxwriter box borders from Python 2 to Python 3
问题描述
我在创建 Excel 工作表时让 xlsxwriter 在多个单元格周围创建框边界时遇到一些问题.经过一番搜索，我在这里找到了一个线程，其中有一个关于如何在 Python 2 中执行此操作的示例.
该线程的链接是:python XlsxWriter 在多个单元格周围设置边框
我尝试使用的答案是 aubaub 给出的答案.我正在使用 Python 3 并试图让它工作，但我遇到了一些问题.
我做的第一件事是将 xrange 更改为
def box(workbook, sheet_name, row_start, col_start, row_stop, col_stop),然后我在
中将 dict.iteritems() 更改为 dict.items()def add_to_format(existing_format, dict_of_properties, workbook):因为从 Python 2 到 3 对此进行了一些更改.
但我正在努力的下一部分，有点不知道该怎么做，这就是
return(workbook.add_format(dict(new_dict.items() + dict_of_properties.items())))部分.我试图通过以另一种方式添加两个字典来改变这一点，在返回部分之前添加它.
dest = dict(list(new_dict.items()) + list(dict_of_properties.items()))返回(workbook.add_format(dest))但是这不起作用，我以前没有经常使用字典，并且对如何使它起作用以及是否对 xlsxwriter 或其他因素进行了一些其他更改阻止了它的工作还有些空白.有没有人对如何解决这个问题有一些好主意?
这里我添加了代码和问题的工作示例.
将pandas导入为pd导入 xlsxwriter将 numpy 导入为 np从 xlsxwriter.utility 导入 xl_range#添加从 Stackoverflow 上的问题复制的 aubaub 中的函数# https://stackoverflow.com/questions/21599809/python-xlsxwriter-set-border-around-multiple-cells/37907013#37907013#并添加了我认为可以使其工作的更改.def add_to_format(existing_format, dict_of_properties, workbook):"""给出你想扩展的格式和你想扩展的属性的字典扩展它，然后您将它们以单一格式返回"""new_dict={}对于键，在 existing_format.__dict__.items() 中的值:if (value != 0) and (value != {}) and (value != None):new_dict[key]=valuedel new_dict['转义']dest = dict(list(new_dict.items()) + list(dict_of_properties.items()))返回(workbook.add_format(dest))def 框(工作簿、sheet_name、row_start、col_start、row_stop、col_stop):"""制作一个 RxC 盒子.使用整数，而不是 'A1' 格式"""行 = row_stop - row_start + 1cols = col_stop - col_start + 1for x in range((rows) * (cols)): # 矩形中的单元格总数box_form = workbook.add_format() # 格式重置每个循环row = row_start + (x//cols)列 = col_start + (x % cols)如果 x <(cols): # 如果它在顶行box_form = add_to_format(box_form, {'top':1}, 工作簿)if x >= ((rows * cols) - cols): # 如果它在底行box_form = add_to_format(box_form, {'bottom':1}, 工作簿)if x % cols == 0: # 如果在左列box_form = add_to_format(box_form, {'left':1}, 工作簿)if x % cols == (cols - 1): # 如果它在右列box_form = add_to_format(box_form, {'right':1}, workbook)sheet_name.write(行，列，"，box_form)#添加带有一些数据的数据框frame1 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)), columns=list('ABCD'))writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')#将框架添加到Excel工作表frame1.to_excel(writer, sheet_name='Sheet1', startcol= 1, startrow= 2)# 获取 xlsxwriter 工作簿和工作表对象.工作簿 = writer.book工作表 = writer.sheets['Sheet1']#在表格中添加一些格式format00 = workbook.add_format()format00.set_bold()format00.set_font_size(14)format00.set_bg_color('#F2F2F2')format00.set_align('center')worksheet.conditional_format(xl_range(2, 1, 2, 5),{'type': 'no_blanks','格式':format00})框(工作簿，'Sheet1', 3, 1, 12, 5)writer.save()我在尝试查看是否有人发布了处理格式的更好方法时偶然发现了这个问题.不要用我的老办法；无论您是否可以使其与 Python 3 一起使用，它都非常糟糕.相反，抓住我刚刚放在这里的内容:https://github.com/Yoyoyoyoyoyoyo/XlsxFormatter.>
如果您使用 sheet.cell_writer() 而不是 sheet.write()，那么它将在一个单元格的基础上保存您要求的格式的记忆，因此在单元格中写入新内容(或添加它周围的边框)不会删除单元格的旧格式，而是添加到它.
一个简单的代码示例:
from format_classes import Book书 = 书(where_to_save)sheet = book.add_book_sheet('Sheet1')sheet.box(3, 1, 12, 5)# 使用 sheet.cell_writer(...) 将数据添加到框中book.close()看代码&README 以了解如何执行其他操作，例如设置框的边框或背景的格式、写入数据、将格式应用于整个工作表等.
I am having some problem getting xlsxwriter to create box borders around a number of cells when creating a Excel sheet. After some searching I found a thread here where there was a example on how to do this in Python 2.
The link to the thread is: python XlsxWriter set border around multiple cells
The answer I am trying to use is the one given by aubaub. I am using Python 3 and is trying to get this to work but I am having some problems with it.
The first thing I did was changing xrange to range in the
def box(workbook, sheet_name, row_start, col_start, row_stop, col_stop),
and then I changed dict.iteritems() to dict.items() in
def add_to_format(existing_format, dict_of_properties, workbook):
Since there have been some changes to this from Python 2 to 3.
But the next part I am struggling with, and kinda have no idea what to do, and this is the
return(workbook.add_format(dict(new_dict.items() + dict_of_properties.items())))
part. I tried to change this by adding the two dictionaries in another way, by adding this before the return part.
dest = dict(list(new_dict.items()) + list(dict_of_properties.items()))
return(workbook.add_format(dest))
But this is not working, I have not been using dictionaries a lot before, and am kinda blank on how to get this working, and if it there have been some other changes to xlsxwriter or other factors that prevent this from working. Does anyone have some good ideas for how to solve this?
Here I have added a working example of the code and problem.
import pandas as pd
import xlsxwriter
import numpy as np
from xlsxwriter.utility import xl_range
#Adding the functions from aubaub copied from question on Stackoverflow
# https://stackoverflow.com/questions/21599809/python-xlsxwriter-set-border-around-multiple-cells/37907013#37907013
#And added the changes I thought would make it work.
def add_to_format(existing_format, dict_of_properties, workbook):
"""Give a format you want to extend and a dict of the properties you want to
extend it with, and you get them returned in a single format"""
new_dict={}
for key, value in existing_format.__dict__.items():
if (value != 0) and (value != {}) and (value != None):
new_dict[key]=value
del new_dict['escapes']
dest = dict(list(new_dict.items()) + list(dict_of_properties.items()))
return(workbook.add_format(dest))
def box(workbook, sheet_name, row_start, col_start, row_stop, col_stop):
"""Makes an RxC box. Use integers, not the 'A1' format"""
rows = row_stop - row_start + 1
cols = col_stop - col_start + 1
for x in range((rows) * (cols)): # Total number of cells in the rectangle
box_form = workbook.add_format() # The format resets each loop
row = row_start + (x // cols)
column = col_start + (x % cols)
if x < (cols): # If it's on the top row
box_form = add_to_format(box_form, {'top':1}, workbook)
if x >= ((rows * cols) - cols): # If it's on the bottom row
box_form = add_to_format(box_form, {'bottom':1}, workbook)
if x % cols == 0: # If it's on the left column
box_form = add_to_format(box_form, {'left':1}, workbook)
if x % cols == (cols - 1): # If it's on the right column
box_form = add_to_format(box_form, {'right':1}, workbook)
sheet_name.write(row, column, "", box_form)
#Adds dataframe with some data
frame1 = pd.DataFrame(np.random.randint(0,100,size=(10, 4)), columns=list('ABCD'))
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
#Add frame to Excel sheet
frame1.to_excel(writer, sheet_name='Sheet1', startcol= 1, startrow= 2)
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
#Add some formating to the table
format00 = workbook.add_format()
format00.set_bold()
format00.set_font_size(14)
format00.set_bg_color('#F2F2F2')
format00.set_align('center')
worksheet.conditional_format(xl_range(2, 1, 2, 5),
{'type': 'no_blanks',
'format': format00})
box(workbook, 'Sheet1', 3, 1, 12, 5)
writer.save()
I stumbled on this when trying to see if anyone else had posted a better way to deal with formats. Don't use my old way; whether you could make it work with Python 3 or not, it's pretty crappy. Instead, grab what I just put here: https://github.com/Yoyoyoyoyoyoyo/XlsxFormatter.
If you use sheet.cell_writer() instead of sheet.write(), then it will keep a memory of the formats you ask for on a cell-by-cell basis, so writing something new in a cell (or adding a border around it) won't delete the cell's old format, but adds to it instead.
A simple example of your code:
from format_classes import Book
book = Book(where_to_save)
sheet = book.add_book_sheet('Sheet1')
sheet.box(3, 1, 12, 5)
# add data to the box with sheet.cell_writer(...)
book.close()
Look at the code & the README to see how to do other things, like format the box's borders or backgrounds, write data, apply a format to an entire worksheet, etc.
这篇关于从 Python 2 到 Python 3 重写 xlsxwriter 框边框的一些函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
