Adjust cell width in Excel

Question

I am using xlsxwriter to write into Excel sheet. I am facing issue: when text is more then cell size it's getting hidden.

import xlsxwriter

workbook = xlsxwriter.Workbook("file.xlsx")
worksheet1 = workbook.add_worksheet()

worksheet1.write(1, 1,"long text hidden test-1" )
worksheet1.write(2, 1,"long text hidden test-2")
worksheet1.write(3, 1,"short-1")
worksheet1.write(4, 1,"short-2")
worksheet1.write(1, 2,"Hello world" )
worksheet1.write(2, 2,"Hello world")
worksheet1.write(3, 2,"Hello world")
worksheet1.write(4, 2,"Hello world")

workbook.close()

What I am getting

What I am expecting with adjusted widths

Martin Evans · Answer 1 · 2020-11-15T12:53:29.293

59

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.

edited Nov 15 '20 at 12:53

answered Nov 12 '15 at 07:06

Martin Evans

43,220
16
78
90

2

Nice, the `win32com` solution is perfect. Thanks! – Walt Reed Mar 05 '18 at 16:05
How can I auto fit without open that excel in front? open it in background would be better . – Mithril Nov 15 '18 at 02:31
For me it is normally hidden, but you could try adding `excel.Visible = False` – Martin Evans Nov 15 '18 at 12:06

Fahad Sarfraz · Answer 2 · 2015-11-12T07:19:16.153

17

Unfortunately xlsxwriter doesnt provide autofit option.

You can however track the largest entry for each column and then set the column width in the end with set column command.

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

In your case for instance, you should set the width of B column to the length of the largest string.

width= len("long text hidden test-1")
worksheet1.set_column(1, 1, width)

edited Nov 12 '15 at 07:19

answered Nov 12 '15 at 07:07

Fahad Sarfraz

1,170
8
19

Any clue how to get largest entity in column !! – Ravichandra Nov 12 '15 at 07:33
1

Assuming your bringing your data from some data set and writing it to an excel sheet. You can parse the data set for the max string lenth and set that the width. A more specific answer requires more information. – Fahad Sarfraz Nov 12 '15 at 08:03
If you are using pandas dataframes you can get the length of the longest text using: width = pd.Series(df.columns).str.len().max() – Abimael Domínguez Jul 28 '21 at 20:15

score 2 · Answer 3 · answered Jan 20 '21 at 13:22

The below worked for me via a df - it finds max width of each column and adjusts accordingly, as suggested here: Simulate autofit column in xslxwriter

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)

Adjust cell width in Excel

3 Answers3

Linked