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

Machiavelli

Answer 1

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.

Stutzman

Nice, the win32com solution is perfect. Thanks! – Cataplexy

How can I auto fit without open that excel in front? open it in background would be better . – Synergy

For me it is normally hidden, but you could try adding excel.Visible = False – Stutzman

Answer 2

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)

Biramous

Any clue how to get largest entity in column !! – Machiavelli

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. – Biramous

If you are using pandas dataframes you can get the length of the longest text using: width = pd.Series(df.columns).str.len().max() – Postaxial

xlxwriter now has autofit feature. You can just use sheet.autofit() – Emirate

Answer 3

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)

Wickham

Answer 4

Adding @Mehdi Zare's comment as an answer as i nearly implemented by hand. xlsxwriter now contains autofit method for sheets.

sheet.autofit() may be enough for you. There are some limitations btw. Method docs: https://xlsxwriter.readthedocs.io/worksheet.html#worksheet-autofit

For the limitations, read the docs' description:

There is no option in the xlsx file format that can be used to say “autofit columns on loading”. Auto-fitting of columns is something that Excel does at runtime when it has access to all of the worksheet information as well as the Windows functions for calculating display areas based on fonts and formatting.

It is a simulated method and may not be accurate in all cases. It is based on the default font and font size of Calibri 11. It will not give accurate results for other fonts or font sizes.

The autofit() method won’t override a user defined column width set with set_column() or set_column_pixels() if it is greater than the autofit value. This allows the user to set a minimum width value for a column.

You can also call set_column() and set_column_pixels() after autofit() to override any of the calculated values.

There's also an example in the docs: https://xlsxwriter.readthedocs.io/example_autofit.html

The example's code:

#######################################################################
# copied from the docs
#######################################################################

from xlsxwriter.workbook import Workbook

workbook = Workbook("autofit.xlsx")
worksheet = workbook.add_worksheet()

# Write some worksheet data to demonstrate autofitting.
worksheet.write(0, 0, "Foo")
worksheet.write(1, 0, "Food")
worksheet.write(2, 0, "Foody")
worksheet.write(3, 0, "Froody")

worksheet.write(0, 1, 12345)
worksheet.write(1, 1, 12345678)
worksheet.write(2, 1, 12345)

worksheet.write(0, 2, "Some longer text")

worksheet.write(0, 3, "http://ww.google.com")
worksheet.write(1, 3, "https://github.com")

# Autofit the worksheet.
worksheet.autofit()

workbook.close()

Deltoid

Answer 5

If the data is dynamic, where the length of your columns is not fixed, you can use this

   last_column = 100 // you might increase it
   for column in dataframe:
       column_width = 40 // width
       col_idx = dataframe.columns.get_loc(column)
       writer.sheets['yourheets'].set_column(col_idx, last_column, column_width)
   writer.save()

Nigeria