You could use set_column as follows:

worksheet1.set_column(1, 1, 25)

This is defined as follows:

set_column(first_col, last_col, width, cell_format, options)

You would need to determine a suitable width, perhaps based on the longest length of text in the whole column. Care though would be needed to base this on the font and size being used. Also consider if a proportional or fixed width font is used.

If you want to autofit all of the columns automatically regardless of the font and size, then you will need to use the win32com interface as follows:

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'file.xlsx')
ws = wb.Worksheets("Sheet1")
ws.Columns.AutoFit()
wb.Save()
excel.Application.Quit()

This can easily be done after you closed the file using your current xlsxwriter code. Note, you might need to supply a full path to your file.

There is a relevant set_column() method that accept width:

set_column(first_col, last_col, width, cell_format, options)

Set properties for one or more columns of cells.

Here is how you can apply it:

worksheet.set_column(0, 2, 100)

The answer is that ... you cannot adjust the width of an individual cell without respect to the column and this isn't an xlsxwriter limitation but a limitation of Excel itself.

The way pandas does it is by merging multiple header cells for similar effect.

So the way to handle the goal of this question, namely, to size the header row cell proportional to the sum of its related subheader row cells is to size the the columns according the sub header cells and to merge the cells in the header related to the sub header cells into a single cell.

[NOTE: as of Jan 2023 xslxwriter added a new method called autofit. See jmcnamara's answer below]

As a general rule, you want the width of the columns a bit larger than the size of the longest string in the column. The with of 1 unit of the xlsxwriter columns is about equal to the width of one character. So, you can simulate autofit by setting each column to the max number of characters in that column.

Per example, I tend to use the code below when working with pandas dataframes and xlsxwriter.

It first finds the maximum width of the index, which is always the left column for a pandas to excel rendered dataframe. Then, it returns the maximum of all values and the column name for each of the remaining columns moving left to right.

It shouldn't be too difficult to adapt this code for whatever data you are using.

def get_col_widths(dataframe):
    # First we find the maximum length of the index column   
    idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
    # Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right
    return [idx_max] + [max([len(str(s)) for s in dataframe[col].values] + [len(col)]) for col in dataframe.columns]

for i, width in enumerate(get_col_widths(dataframe)):
    worksheet.set_column(i, i, width)