Is there a way to use sheet.add_table() with some columns using rich_text/url formatting?

Question

I have some code with a big table I am preparing ahead of time (often very large so adding lines in the table one by one would kill the performances). Some of the columns are made of URLs which I would like to hide with some shorter text like 'click here' . I just cannot find a way to do this today.

    xls_headers = ['col1', 'col2', 'col3', 'url']
    big_data = ['one', 'two', 'three',  'https://wwwwww/']
    xls_worksheet.add_table(0, 0, 1, 3,
      {'header_row': True, 'data': big_data, 'columns': xls_headers}
      )

I was expecting that for example instead of giving an array of strings, some of the values could be a dict like:

{ 'format: 'url', 'text': 'click here', 'url'='https://xxxx...' }

then the add_table() would detect such an object and use make_url() or make_rich_text() sub routines ...

thank you!

EDIT: added some code examples

Answer 1

I am not aware of any 'bulk formatting' methods in XlsxWriter.

But if it is just URL you want to format, you can write '=HYPERLINK("https://wwwwww/", "click here")' in your data instead:

workbook = xlsxwriter.Workbook('test.xlsx')
xls_worksheet = workbook.add_worksheet('Main')

xls_headers = ['col1', 'col2', 'col3', 'url']
big_data = [['one', 'two', 'three', '=HYPERLINK("https://wwwwww/", "click here")']]
xls_worksheet.add_table(0, 0, 1, 3,
                        {'header_row': True, 'data': big_data,  'columns': [{'header': x} for x in xls_headers]}
                        )

workbook.close()

Answer 2

The data option in add_table() is a syntactic helper for adding data to the table but you don't have to use it. Instead you could loop over the data and write it cell by cell into the table and then apply any rules you want.

For example like this where the code intercepts "http" strings and writes them as urls:

import xlsxwriter

workbook = xlsxwriter.Workbook("test.xlsx")
xls_worksheet = workbook.add_worksheet()

xls_headers = ["col1", "col2", "col3", "url"]
big_data = [["one", "two", "three", "https://example.com"]]

xls_worksheet.add_table(0, 0, 1, 3,
    {
        "header_row": True,
        "columns": [{'header': x} for x in xls_headers]
    }
)

for (row_num, row_data) in enumerate(big_data):
    for (col_num, data) in enumerate(row_data):
        if data.startswith("http"):
            xls_worksheet.write_url(row_num + 1, col_num, data, None, "Click")
        else:
            xls_worksheet.write(row_num + 1, col_num, data)
            

workbook.close()

Output:

You can tighten up the matching logic to suit your data.