Rewriting some functions for xlsxwriter box borders from Python 2 to Python 3

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://mcmap.net/q/617469/-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.

Recommended topics

Hot tags