Simulate autofit column in xslxwriter

by

[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)

More Related Contents:

  1. How to save a new sheet in an existing excel file, using Pandas?
  2. xlsxwriter: is there a way to open an existing worksheet in my workbook?
  3. append dataframe to excel with pandas
  4. pandas xlsxwriter, format table header – not sheet header
  5. How to write/update data into cells of existing XLSX workbook using xlsxwriter in python
  6. Merge rows based on value (pandas to excel – xlsxwriter)
  7. Write to StringIO object using Pandas Excelwriter?
  8. Putting many python pandas dataframes to one excel worksheet
  9. Adjust cell width in Excel
  10. Python How to use ExcelWriter to write into an existing worksheet
  11. Appending Pandas DataFrame to existing Excel document
  12. XlsxWriter object save as http response to create download in Django
  13. how to calculation cost time [closed]
  14. How to use a decimal range() step value?
  15. Your CPU supports instructions that this TensorFlow binary was not compiled to use: AVX AVX2
  16. Post values from an HTML form and access them in a Flask view
  17. ‘module’ object has no attribute ‘loads’ while parsing JSON using python
  18. How do I create animated sprites using Sprite Sheets in Pygame?
  19. How do I bind the enter key to a function in tkinter?
  20. Compile main Python program using Cython
  21. django abstract models versus regular inheritance
  22. PyLint message: logging-format-interpolation
  23. How do I read a random line from one file?
  24. Python getting common name from URL using ssl.getpeercert()
  25. How can I wrap a synchronous function in an async coroutine?
  26. Plot a bar using matplotlib using a dictionary
  27. Change values on matplotlib imshow() graph axis
  28. How to Print “Pretty” String Output in Python
  29. Using %matplotlib notebook after %matplotlib inline in Jupyter Notebook doesn’t work
  30. How can I specify a database for Django Tests to use instead of having it build it every time?

Leave a Comment