Odoo - Tips to Create and Write to Excel file (Python)

This Odoo technical development blog is my way to contribute back to the Odoo Community, for all the selfless and great sharing by the community members.

Pre-requisite of creating a Excel file is to download and install the following OCA module:

Base Report xlsx

It is important to at least study a little bit on the Xlsxwriter.

a) Create a class and inherit the report.report_xlsx.abstract class. Implement the generate_xlsx_report.

class ShippingInstructionXlsx(models.AbstractModel):
    _name = 'report.sci_goexcel_freight_2.report_si_xlsx'
    _inherit = 'report.report_xlsx.abstract'
    
    def generate_xlsx_report(self, workbook, data, lines):

b) The following are useful methods in the xlsxwriter:

1. Add the name to the excel sheet

workbook.add_worksheet('worksheet name')

2. Create the format. Eg, set the font size, border line on left and right, align top and font color red, non-bold and wrap the text.

format_label = workbook.add_format({'font_size': 12, 'valign': 'top', 'bold': False, 'left': True, 'right': True, 'text_wrap':True})
format_label.set_font_color('red')

3. Set the column width to 15 for column D to I .

sheet.set_column(3, 8, 15)

4. Merge the row 1, column D and E into 1 column, and write string 'Booking No.' to row 1, column D, with the format_label.

sheet.merge_range(1, 3, 1, 4, '', merge_format)
sheet.write(1, 3, 'Booking No.', format_label)

5. Merge the Cell row 2 to 7 and write 'Shipper' to it.

merge_format = workbook.add_format({'align': 'center'})
sheet.merge_range(1, 0, 6, 0, '', merge_format)
sheet.write(1, 0, 'Shipper', format_label)

c) When there is an programming error, unfortunately, the error message will not be displayed. A good old method to debug is to use the print statement to print on different breakpoint (or using the debug breakpoint),