Apply format to a cell after being written in XlsxWriter

Question

I work on python using XlsxWriter and I've been trying to solve this problem with no success:

My app must create an Xlsx file in which data is shown in a table-like structure. That table has some empty cells.

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.

Sibeal

From a look at the documentation, this doesn't appear to be possible. – Shot

Just to be clear, you can still create the final result that you want; it will just require you to structure your program logic such that you already know what formatting to apply to each cell as you write it. (You may well have realized this already, but I don't want anyone reading this to see the words "that isn't possible" and mistakenly take that to mean they can't work around it.) – Delectate

Thank you for your advice, @John, I already did it today and of course that's the way: instead of writing data on the worksheet as it comes from database, I've managed to store it on variables and then check whether a given cell has to be written with data or just blank to build the layout. – Sibeal

Answer 1

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.

Update: this feature was never implemented and is no longer planned.

Byron

Thank you very much for XlsxWriter and your answer. We'll keep an eye on future updates. – Sibeal

For those who don't click through to find out why it wasn't implemented ... @Byron says: "Have a look at github.com/webermarcolivier/xlsxpandasformatter instead." – Conjunct

Answer 2

Another workaround is to use conditional_format, and use type='no_errors':

worksheet.conditional_format(your_range, {'type': 'no_errors',
                                          'format': your_format})

Selfabsorption

While this will circumvent the issue for many use cases, I'd like to draw attention to some limits addressed here: In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified. Properties that cannot be modified in a conditional format are font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties. – Printmaker

beautiful for the original poster and the reply – To

Answer 3

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()

Belly

This worked great for me. I modified it to allow adding sheets as you go rather than needing them to be set with an add_sheets method. – Lyudmila

Answer 4

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')
...

Tail

I don't recommend this workaround since it can have some unintended consequences such as affecting the sizing of images inserted into the worksheet. – Byron

love your answer~ – To