Simulate autofit column in xslxwriter
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)
How to set automatically the width of a column in xlsxwriter
Is there any possibility of setting the width of all columns automatically?
Unfortunately, not.
From the XlsxWriter FAQ:
Q. Is there an "AutoFit" option for columns?
Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate "AutoFit" in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end.
python xlsxwriter change all cell widths when using write_row
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)
Related Topics
Python - Use Previous Row'S Value to Update the New Rows Values
How to Check If Numbers Are in a List in Python
Best Way to Get the Max Value in a Spark Dataframe Column
How to Remove Square Brackets from List in Python
How to Make a Tkinter Label Background Transparent
How to Clear Only Last One Line in Python Output Console
How to Clear/Delete the Contents of a Tkinter Text Widget
How to Limit the User Input to Only Integers in Python
Python Selenium - Element Is Not Currently Interactable and May Not Be Manipulated
How to Determine If My Python Shell Is Executing in 32Bit or 64Bit
Why I Get Key Error Even Though Column Present in Pandas
Replace Single Quote With Double Quote in a String Python
How to Do a Conditional Count After Groupby on a Pandas Dataframe
Python Format Size Application (Converting B to Kb, Mb, Gb, Tb)