Python 在 XlsxWriter 中写入后将格式应用于单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22352907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Apply format to a cell after being written in XlsxWriter
提问by vbarb
I work on python using XlsxWriter and I've been trying to solve this problem with no success:
我使用 XlsxWriter 在 python 上工作，我一直在尝试解决这个问题，但没有成功：
My app must create an Xlsx file in which data is shown in a table-like structure. That table has some empty cells.
我的应用程序必须创建一个 Xlsx 文件，其中数据以类似表格的结构显示。那个表有一些空单元格。
I'd like to set borders to some cells to make a grid for the table so I use:
我想为某些单元格设置边框以制作表格的网格，因此我使用：
format6 = excelbook.add_format()
format6.set_left(1)
for y in range(24):
excel.write(y+5, 1, None, format6)
in order to have border applied to those cells. Then, I write data on the table.
以便将边框应用于这些单元格。然后，我在表上写数据。
Since the table layout is quite complex, it would be easy to write data and, once everything is written, apply format to cells to have borders, but I can't find the way.
由于表格布局非常复杂，因此写入数据很容易，并且一旦写入所有内容，将格式应用于单元格以具有边框，但我找不到方法。
Is there any way to apply format to a cell once it's been written previously without losing its content?
有没有办法将格式应用于先前写入的单元格而不会丢失其内容？
Thank you in advance.
先感谢您。
采纳答案by jmcnamara
I'm the author of that module and unfortunately that isn't possible.
我是该模块的作者，不幸的是这是不可能的。
It is a planned feature, and (a small) part of the internal infrastructure is there to support it, but it isn't currently available and I can't say when it will be.
这是一个计划中的功能，内部基础设施的（一小部分）可以支持它，但它目前不可用，我不能说什么时候可用。
回答by pymen
One way of doing that - using one wrapper method to write cell, and helper method to overwrite cell's value and style
这样做的一种方法 - 使用一种包装方法来编写单元格，并使用辅助方法来覆盖单元格的值和样式
import xlsxwriter
class XLSGenerator:
def __init__(self):
self.workbook = xlsxwriter.Workbook('file.xls')
sheet1 = self.workbook.add_worksheet('sheet1')
sheet2 = self.workbook.add_worksheet('sheet2')
self.sheets = {'sheet1': sheet1, 'sheet2': sheet2}
# dictionary with all written cells
self.written_cells = {sheet: {} for sheet in self.sheets}
def write_cell(self, sheet_name, cell, value, cell_format_dict=None):
"""Writes value and style, and saves it in self.written_cells"""
sheet = self.sheets[sheet_name]
if cell_format_dict:
cell_format = self.workbook.add_format(cell_format_dict)
sheet.write(cell, value, cell_format)
else:
cell_format_dict = None
sheet.write(cell, value)
# save sheet_name, cell and cell_value, and cell_format (dict)
# example ['sheet1']['C12'] = ('some_text', {'font_size': 14, 'bold': True}
self.written_cells[sheet_name][cell] = (value, cell_format_dict)
def apply_style(self, sheet_name, cell, cell_format_dict):
"""Apply style for any cell, with value or not. Overwrites cell with joined
cell_format_dict and existing format and with existing or blank value"""
written_cell_data = self.written_cells[sheet_name].get(cell)
if written_cell_data:
existing_value, existing_cell_format_dict = self.written_cells[sheet_name][cell]
updated_format = dict(existing_cell_format_dict or {}, **cell_format_dict)
else:
existing_value = None
updated_format = cell_format_dict
self.write_cell(sheet_name, cell, existing_value, updated_format)
Usage like this
像这样的用法
generator = XLSGenerator()
generator.write_cell('sheet1', 'A1', '10')
generator.write_cell('sheet1', 'B2', '20')
generator.write_cell('sheet1', 'C3', '30')
table_borders = {"left": 1, 'right': 1, 'top': 1, 'bottom': 1}
for cell in ('A1', 'A2', 'A3', 'B1', 'B2', 'B3', 'C1', 'C2', 'C3'):
generator.apply_style('sheet1', cell, table_borders)
generator.workbook.close()
回答by Robin Trietsch
Another workaround is to use conditional_format, and use type='no_errors':
另一种解决方法是使用conditional_format, 并使用type='no_errors'：
worksheet.conditional_format(your_range, {'type': 'no_errors',
'format': your_format})
回答by xingpei Pang
you could set the default format of the workbook:
您可以设置工作簿的默认格式：
import xlsxwriter
workbook = xlsxwriter.Workbook('example.xlsx')
# default cell format to size 10
workbook.formats[0].set_font_size(10)
# default cell format to center
workbook.formats[0].set_align('center')
...
