Python Forum
XlsxWriter and Python inheritance
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
XlsxWriter and Python inheritance
#1
Hi guys,

I have some issues with my code that I can't solve, and an improvment that I can't to implement. my head is splitting.. I'm really stuck with them. I really hope that someone can help me. let's start with the issues.

today I created two subclasses of "Workbook" and "Worksheet" classes taken from XlsxWriter library. below my code:
from xlsxwriter.workbook import Workbook
from xlsxwriter.worksheet import Worksheet
from xlsxwriter.worksheet import convert_cell_args
from xlsxwriter.compatibility import str_types


class WorksheetPlus(Worksheet):
    @convert_cell_args
    def write(self, row, col, *args):
        data = args[0]
        
        # Reverse strings to demonstrate the overridden method.
        if isinstance(data, str_types):
            data = data[::-1]
            return self.write_string(row, col, data)
        else:
            # Call the parent version of write() as usual for other data.
            return super(WorksheetPlus, self).write(row, col, *args)
    
    # custom function
    def write_table(self, info, row, column, header, normal):
        y = column
        for key in info[0]:
            self.write(row, y, info[0][key], header)
            y += 1
        row += 1
        for dic in info[1:]:
            y = column
            for key in dic:
                self.write(row, y, dic[key], normal)
                y += 1
            row += 1

class WorkbookPlus(Workbook):
    # Overwrite add_worksheet() to create a WorksheetPlus object.
    def add_worksheet(self, name=None):
        worksheet = super(WorkbookPlus, self).add_worksheet(name, WorksheetPlus)
        return worksheet

                  
# test:
if __name__ == "__main__":
    info = [{1:"ENGLISH", 2:"ITALIAN", 3:"SPANISH", 4:"RUSSIAN", 5:"JAPANESE", 6:12345, 7:"12345"}, {1:"thanks", 2:"grazie", 3:"gracias", 4:"спасибо", 5:"ありがとう", 6:12345, 7:"12345"}]
    obj_wb = WorkbookPlus("languages.xlsx")
    obj_ws = obj_wb.add_worksheet("EXAMPLE")
    
    header = obj_wb.add_format({'bold': True, 'valign': 'left', 'valign': 'top'})
    normal = obj_wb.add_format({'valign': 'left', 'valign': 'top', 'text_wrap': True})
    obj_ws.write_table(info, 0, 0, header, normal)
    
    obj_wb.close()
my goal is using XlsxWriter module with my custom functions. for example, the "write_table" function, written under the "WorksheetPlus" subclass, help me to create a table in a very simple way (you just have to give him a list of dictionaries).

now, if I try my test code (see at the bottom of my code) it seems works just with the numbers, the strings instead are "mirrored" and with them the cell formats "header" and "normal" are not applied (see the screenshot in attached)

how can I solve these issues with my subclasses?

Attached Files

Thumbnail(s)
   
Reply
#2
Let me start by saying that what you do is wrong.
Also, before one start to tinker with someone's code, especially complex package like XlsxWriter, they should have at least decent understanding of the internals - e.g. logic and implementation.
I will go step by step, making small changes, so that you understand.

  1. The particular problem.
    Compare line#15 and line#18 in your code. On line#15 you pass just data (i.e. args[0], after you reversed the string). You fail to pass on the cell format.
        @convert_cell_args
        def write(self, row, col, *args):
            data = args[0]
            args = list(args)
            # Reverse strings to demonstrate the overridden method.
            if isinstance(data, str_types):
                args[0] = data[::-1]
                return self.write_string(row, col, *args)
            else:
                # Call the parent version of write() as usual for other data.
                return super(WorksheetPlus, self).write(row, col, *args)
    Is this good? No.

  2. Overload the right method
    You already demonstrate you know XlsxWriter.Worksheet has write_string() method. There is also internal _write_string() method. In this case we can overload the internal _write_string(). So instead of write() we overload the _write_string().No decorator.
        def _write_string(self, row, col, string, cell_format=None):
            string = string[::-1]
            return super(WorksheetPlus, self)._write_string(row, col, string, cell_format=cell_format)
    As I understand this is just a "test" to demonstrate overloaded method. You don't really want to overload string write method, right? You don't want all strings to be written reversed.

  3. No need to define your own Workbook class.
    It just complicates things. Not to mention, that the way you do it, you will not be able to create any other sheet class (e.g. chartsheet). The best practice dictate that signature of the parent and the child class method should be the same. So just remove the WorkbookPlus class and do:
    obj_wb = Workbook("languages.xlsx")
    obj_ws = obj_wb.add_worksheet("EXAMPLE", worksheet_class=WorksheetPlus)
  4. Do you need to create WorksheetPlus class at all?
    well, NO.
    You can create your own table class, e.g. MyTable and then define custom handler for that type. This way you don't mess with the XlsxWriter at all. You do customization via API provided to you.

    from xlsxwriter.workbook import Workbook
    from xlsxwriter.worksheet import Worksheet
    from xlsxwriter.worksheet import convert_cell_args
    from xlsxwriter.compatibility import str_types
     
    
    class MyTable:
        def __init__(self, info, header_style=None, body_style=None):
            self.info = info
            self.header_style = header_style
            self.body_style = body_style
    
    
    def write_table(worksheet, row, col, my_table, cell_format=None):
        formats = {row:my_table.header_style}
        for row_idx, row_data in enumerate(my_table.info, start=row):
            for col_idx, data in enumerate(row_data.values(), start=col):
                cell_format = formats.get(row_idx, my_table.body_style)
                result = worksheet.write(row_idx, col_idx, data, cell_format)
                if result: # result is either 0 (success) or -1 (error)
                    break
        return result
    
    
    # test:
    if __name__ == "__main__":
        info = [{1:"ENGLISH", 2:"ITALIAN", 3:"SPANISH", 4:"RUSSIAN", 5:"JAPANESE", 6:12345, 7:"12345"}, {1:"thanks", 2:"grazie", 3:"gracias", 4:"спасибо", 5:"ありがとう", 6:12345, 7:"12345"}]
        obj_wb = Workbook("languages.xlsx")
        obj_ws = obj_wb.add_worksheet("EXAMPLE")
        obj_ws.add_write_handler(MyTable, write_table) # here we add custom handler for MyTable class
        header = obj_wb.add_format({'bold': True, 'valign': 'left', 'valign': 'top'})
        normal = obj_wb.add_format({'valign': 'left', 'valign': 'top', 'text_wrap': True})
        my_table = MyTable(info=info, header_style=header, body_style=normal)
        obj_ws.write(0, 0, my_table)
        obj_wb.close()
    Note that actually there is no need that info to be dict. There is really no use of the keys whatsoever. Note that dict is ordered only on python 3.7+, so before that there is no guarantee of the order.
    The above custom class and handler can be written in multiple different ways, so the above is just an example.

  5. And FINALLY - Do you really need to do any of the above?
    Right - NO, you don't need it. Just use the available add_table() method (the docs). Or simply write using 2 nested for loops (maybe in a separate function) and there will be no need of own class and custom write handler - the function will be what you otherwise pass as handler. Of course, you need to read the docs first and familiarize withe the API. Hey, but I already mentioned that, right?
    Quote:before one start to tinker with someone's code, especially complex package like XlsxWriter, they should have at least decent understanding of the internals - e.g. logic and implementation.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Inheritance vs Instantiation for Python classes mr_byte31 7 2,789 Oct-14-2021, 12:58 PM
Last Post: mr_byte31
  xlsxwriter + mplfinance: Plot Stock Chart in Excel Worksheet KMV 1 2,005 Mar-09-2021, 09:44 PM
Last Post: KMV
  Xlsxwriter: Create Multiple Sheets Based on Dataframe's Sorted Values KMV 2 3,441 Mar-09-2021, 12:24 PM
Last Post: KMV
  xlsxwriter in windows dfstrottersfan 2 2,285 Sep-23-2020, 11:41 AM
Last Post: dfstrottersfan
  Write tables from Word (.docx) to Excel (.xlsx) using xlsxwriter jackie 1 3,138 May-27-2020, 11:47 PM
Last Post: mcmxl22
  How can I create a subclass of XlsxWriter? aquerci 2 2,026 May-04-2020, 07:41 PM
Last Post: aquerci
  How inheritance works in Python ARV 1 1,800 Oct-03-2019, 03:06 PM
Last Post: Larz60+
  XlsxWriter: How can I append new data into not blank cells? aquerci 1 9,123 Jun-01-2019, 04:37 AM
Last Post: heiner55
  Downloading a module Xlsxwriter dan789 6 11,096 Jan-26-2019, 02:13 PM
Last Post: dan789
  I am having a big issue in XlsxWriter pratheep 3 2,876 Jan-19-2018, 05:24 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020