Multi-line strings can come in two forms in Excel files (shown by B and C in the Figure below). In B, the sentence is broken word by word so that each word appears in its line, whereas, in C, text wrapping is implemented.
Neither text breaking nor line breaks are implemented in A; therefore, the sentence overflows the cell.
Text wrapping in Excel is a feature in which a word at the end of a line is automatically moved to the next line to keep the text within the cell width.
This article will discuss different ways of writing multi-line strings into excels through either text wrapping or line breaks.
Writing Multi-line Strings into Cells Using openpyxl
This package works for both line breaks and text wrapping. Additionally, it is suitable for editing already existing Excel files.
Implementing line breaks with openpyxl package
Line breaks are implemented using the “\n” – the new line character in Python. A string breaks at the position of the character.
The following code example shows how to implement line breaks on Excel cells with openpyxl.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import openpyxl # Load XLSX file - the file should exist on the path. # See the next code example for the creation of a new file. workbook = openpyxl.load_workbook("employees.xlsx") # Select a specific worksheet. Use workbook["sheet_name"] or # workbook.get_sheet_by_name("sheet_name") sheet = workbook.active # selects the first worksheet. # Insert value to a cell or update the existing 1. sheet["B2"].value = "Allan Smith \nOlola" sheet.cell(row=3, column=2, value="Caroline Hillary Joseph") # Save the workbook to the workbook.save("employees.xlsx") |
Output (updated Excel file):
If you need to create a new file instead of updating an existing one, use the following code.
Be careful: If you use a path to a file that already exists, it will be overwritten without warning.
|
1 2 3 4 5 6 7 8 9 10 |
import openpyxl # Create a new XLSX file workbook = openpyxl.Workbook() # Use the first worksheet created by the line above. sheet = workbook.active # Insert or update the value at cell "B3". Implementing line break. sheet["B3"].value = "Beatrice \nBrown" # Save the data into a new "employees2.xlsx" workbook.save("employees2.xlsx") |
Writing multi-line string into Excel through text wrapping with openpyxl
The openpyxl supports formatting style for text wrapping. Here is an example,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
import openpyxl # Define text wrapping formatting style. alignment = openpyxl.styles.Alignment(wrap_text=True) # Load a workbook. workbook = openpyxl.load_workbook("employees.xlsx") # Select a worksheet to write into. sheet = workbook["names"] # Insert or update cell "B2" sheet["B2"] = "Allan Smith Olola" # Implement text wrapping formatting on the cell. sheet["B2"].alignment = alignment # Insert or update cell "B3" and use line breaks. sheet["B3"] = "Allan \nSmith \nOlola Olo Arap 2" # Implement text wrapping on top of the line breaks. sheet["B3"].alignment = alignment # Insert or update the cell on row 4, column 2. # No line breaks or text wrapping implemented on the cell. sheet.cell(row=4, column=2, value="This is a very loooooooooong sentence.") # Save the workbook to a file. workbook.save("employees.xlsx") |
Output (updated employees.xlsx file):
How to Create Multi-line Cells in Excel Using xlsxwriter Package
The xlsxwriter is another great package for writing data into Excel files. It is important, however, to note that xlsxwriter cannot be used to update existing files. Instead, this package is best for writing new Excel files into storage.
“It cannot read or modify existing Excel XLSX files” – xlsxwriter documentation.
Be cautious: If you provide a path to a file that already exists, it will be overwritten with no warning.
Line breaks are implemented with the “\n” character like we did with openpyxl. The only difference is that, unlike openpyxl, xlsxwriter uses zero-based indexing for cell location. That is, the first cell is (0, 0) in xlsxwriter, but in openpyxl, it is (1,1)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import xlsxwriter # install xlsxwriter # file1.xlsx file will be created. workbook = xlsxwriter.Workbook("file1.xlsx") # Create a new sheet in the workbook worksheet = workbook.add_worksheet("sheet1") ## Enter multi-line strings into the worksheet # Line breaks are indicated by "\n" - the new line character in Python. worksheet.write("A1", "A \nB") # equivalent to row=0, column=0 worksheet.write(4, 3, "Alice \nKamare") # equivalent to "D5" worksheet.write(5, 1, "Line1\nLine2\nLine3") # equivalent to "B6" # Close the workbook workbook.close() |
Text-wrapping in xlsxwriter
Text-wrapping in xlsxwriter can be implemented, as shown in the example below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
import xlsxwriter workbook = xlsxwriter.Workbook("file2.xlsx") worksheet = workbook.add_worksheet("sheetA") # Defining text wrapping format. format = workbook.add_format({"text_wrap": True}) # Implementing text wrapping for each data entered. worksheet.write("A2", "AA BB", format) worksheet.write("B1", "AA\nBB", format) worksheet.write(2, 3, "Bob Smith The Second", format) # Close the workbook workbook.close() |
Output (a view of file2.xlsx after writing to it):
Conclusion
This article discussed using openpyxl and xlsxwriter packages to write multi-line strings into Excel, which can be achieved using line breaking or text wrapping.
We also noted that openpyxl is the best choice for updating existing Excel files, while xlsxwriter is best used when writing data to a new Excel file.