Search code examples
pythonunixlibreofficexlsxwriter

How can i add an image to a header of a xlsx file using xlsxwriter and python?

I want to add an image to the header of the xlsx but it's showing nothing on the generated file (our application picks a .csv, then converts to .xlsx using .py file with xlsxwriter, and then to .pdf using a libreoffice command)

We've already tried with different image formats and sizes but it made no difference.

Also tried with the examples from the library (https://xlsxwriter.readthedocs.io/example_headers_footers.html?highlight=set_header) with no luck.

We used the worksheet.insert_image(), it adds the image but not in the header. This is our current result: https://ibb.co/QNXv8bM

We want to add the image directly on the header (maybe using the set_header() ) but so far our tries with this method hasn't produced any results. When we use the set_header() to place the image it shows nothing on the header.

Here is a piece of the python file that we are using:

def create_worksheet(workbook, data, image, p_header_text):
    '''
    Creates and formats worksheet
    :param workbook: Main workbook
    :type workbook: xlsxwriter.Workbook
    :param data: dict with data to use in the worksheet
    :type data: dict
    data example:
    data = {'headings': [head1, head2, ..., headn], 'rows': [[data1, ..., datan], ...]}
    :return: Nothing
    '''
    worksheet = workbook.add_worksheet()
    ### Page Setup
    worksheet.set_margins(top=1.4)
    worksheet.set_landscape()
    worksheet.hide_gridlines(2)
    #worksheet.set_paper(9)  # 9 = A4
    worksheet.fit_to_pages(1, 0)
    ### Header and footer
    header_text = p_header_text
    #worksheet.set_header('&C&16&"Calibri,Bold"{}'.format(header_text))
    worksheet.set_header('&L&G', {'image_left': '/home/reports/LTA-logo.jpg'})


    worksheet.set_footer('&L&D&RPage &P of &N')
    #worksheet.insert_image('A1', '/home/reports/LTA-logo.jpg', {'x_offset': 0, 'y_offset': 0})
    #worksheet.set_header('&C&G', {'image_left': '/home/reports/LTA-logo.jpg'})

    ### Create table
    create_table(worksheet, data)

Note: The worksheet.set_header('&C&16&"Calibri,Bold"{}'.format(header_text)) works fine, it shows the text on the header. The problem is when we try to put the image...

The expected result is to make the image appear in the header, left aligned with the title as shown on this picture: https://ibb.co/vQTytK2

Note 2: For business reasons (company) i cannot show the data on the print screens

Solution

  • It should work with XlsxWriter. You just need to build the format string in the right way with the &L left part and the &C centre part.

    For example:

    import xlsxwriter

workbook = xlsxwriter.Workbook('headers_footers.xlsx')
worksheet = workbook.add_worksheet('Image')

# Adjust the page top margin to allow space for the header image.
worksheet.set_margins(top=1.3)

worksheet.set_header('&L&[Picture]&C&16&"Calibri,Bold"Revenue Report',
                     {'image_left': 'python-200x80.png'})

workbook.close()

    Note, I use the more explicit &[Picture] in the example but &G works as well.

    Output:

    enter image description here