41

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.

vbarb
  • 413
  • 1
  • 4
  • 4
  • From a look at the [documentation](http://xlsxwriter.readthedocs.org/working_with_formats.html), this doesn't appear to be possible. – Emmett Butler Mar 12 '14 at 13:39
  • 2
    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.) – John Y Mar 13 '14 at 13:17
  • 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. – vbarb Mar 13 '14 at 17:17

4 Answers4

64

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • 1
    Thank you very much for XlsxWriter and your answer. We'll keep an eye on future updates. – vbarb Mar 13 '14 at 07:24
  • 3
    For those who don't click through to find out why it wasn't implemented ... @jmcnamara says: "Have a look at https://github.com/webermarcolivier/xlsxpandasformatter instead." – TryTryAgain Apr 11 '21 at 18:40
42

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

worksheet.conditional_format(your_range, {'type': 'no_errors',
                                          'format': your_format})
Robin Trietsch
  • 1,662
  • 2
  • 19
  • 31
  • 13
    While this will circumvent the issue for many use cases, I'd like to draw attention to some limits addressed [here](http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html#working-with-conditional-formats): *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.*** – modulus0 Jun 07 '17 at 14:13
  • 1
    beautiful for the original poster and the reply – Toma Oct 05 '21 at 06:03
10

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

enter image description here

pymen
  • 5,737
  • 44
  • 35
  • 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. – Nesha25 Nov 17 '22 at 00:10
6

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')
...
xingpei Pang
  • 1,185
  • 11
  • 15