Search code examples
pythonpandasexport-to-excel

How to change row height for pandas export .to_excel() after wrapping texts in DataFrame?

I'm wrapping texts in a Pandas DataFrame with this code:

        for column in dataframe:
          if column != '':
            dataframe[column] = dataframe[column].str.wrap(len(column) + 20)

and export the DataFrame to an excel document with .to_excel('filename'). And the result is (LibreOffice on Linux) shown in the image below:

enter image description here

How can I change the row height to get following result:

enter image description here ?

I want to also mention that when I remove above code and wrap text manually in LibreOffice - it works. Maybe it's not possible from code side?

enter image description here

Solution

  • How can I change the row height of the row with the wrapped text in order to see the entire text in Libre Office Calc as shown how it should be in the image?

    The 'problem' you experience is a result of the wrong expectation that the from pandas dataframe with .to_excel() exported .xls file will auto-magically contain beside the content of the cells and the row/column names also the data about the appropriate formatting of the spreadsheet columns/rows (width/height/font size/etc) so that you can see all of the content in the viewed spreadsheet.

    Such expectation does not consider beside other things for example the fact that you haven't along with the export of pandas dataframe to excel file neither specified the font size for displaying the cells nor the widths/heights of the columns and rows which are given in pixels. This makes it impossible to infer the optimal row heights and column widths from the available cell data and store this formatting information along with the content.

    In other words there can't be any specific, data-depending formatting information stored in the exported file and if the file is loaded in LibreOffice Calc it is displayed using the standard formatting.

    The image how it is shows that after loading the file you see only the last line of the wrapped text because the used standard row height with the standard font size can display only one line of the string content of the cell.

    When I remove above code and wrap text manually in LibreOffice - it works. Maybe it's not possible from code side?

    Is it possible on the script side to specify what I achieved by manually change?

    If you specify in addition to the spreadsheet cell values also the information about formatting of the rows, columns and cells it is possible to achieve any result you want using Python script code.

    Look here (Improving Pandas Excel Output) for more explanations as these ones provided in the comments in the following code which will accomplish what you want to achieve:

    row_number =   5 # row number of the cell as shown in the Calc spreadsheet
row_height = 100 # choose appropriate one to show all wrapped text

# get the writer object in order to be able to specify formatting:
writer = pd.ExcelWriter("wrapping_column.xlsx", engine='xlsxwriter')
dataframe.to_excel(writer, index=False, sheet_name='Cell With Wrapped Text')

# get the sheet of the spreadsheet to work on: 
workbook  = writer.book
worksheet = writer.sheets['Cell With Wrapped Text']

# adjust the height of the row: 
worksheet.set_row(row_number-1, row_height)

# save the data along with the formatting changes to file: 
writer.save()