36

I'm trying to write multiline text to excel cells.

cell.setCellValue("line1 \n line2");

But when I open the document, I see only one line until I double-click it for editing, then it becomes two-lined. Why is it so? Thanks

JimmyPena
  • 8,694
  • 6
  • 43
  • 64
Timofei Davydik
  • 7,244
  • 7
  • 33
  • 59

3 Answers3

58

You need to set the row height to accomodate two lines of text.

row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));

You need to set the wrap text = true to get the new line. Try this : Here wb is the Workbook.

 CellStyle cs = wb.createCellStyle();
 cs.setWrapText(true);
 cell.setCellStyle(cs);
vikiiii
  • 9,246
  • 9
  • 49
  • 68
  • 6
    This doesn't help. My problem is not about row height. The text is written in one line until I double-click the cell. – Timofei Davydik Jul 11 '12 at 08:19
  • 4
    @vikiiii I guess wraptext would work according to column width. What if I forcefully need text in the next line?? – jaychapani Jul 11 '12 at 08:24
  • @vikiiii I have the same issue but the solution doesn't work for me. If I press "Format" in excel before double-clicking the cell, "word wrap" property is not checked. After double-click text is split in 2 line and "word wrap" is check. Where is the magic? – Dennis Dec 17 '17 at 12:56
  • @Dennis - you have to set the row height and wrap text style as above but also adjust the width of that column after you've added the data, e.g., "sheet.autoSizeColumn(2)". This works for me with the 3.15 JAR – Ed Norris Mar 17 '18 at 05:33
  • You must set cell value before you set cell style.
    Not work:
    cell.setCellStyle(cellStyle);
    cell.setCellValue(new HSSFRichTextString("abc\r\ndef"));
    Work: cell.setCellValue(new HSSFRichTextString("abc\r\ndef"));
    cell.setCellStyle(cellStyle);     – Gabriel.ge Jul 17 '18 at 01:02
2

I found that you have to now include a third step after you follow the accepted answer to this question. You have to auto-size the column after you've added all of your data.

Assuming you're concerned about column 2,

sheet.autoSizeColumn(2);

See this example from Apache for context. It works for me with Java 8, poi-3.15.jar, and Excel for Mac.

Ed Norris
  • 4,233
  • 5
  • 27
  • 29
  • 1
    I would not recommend using autoSizeColumn it's a slow operation , instead a function can be created that sets size to a particular column. I would highly recommend creating a wrapper class called for example ExcelBuilder and create function that you would often use to modify your excels – Artjom Prozorov Feb 20 '20 at 21:34
  • @ArtjomProzorov can you write up something as another answer? I can't test this right now – Ed Norris Feb 23 '20 at 02:55
  • This indeed slow like hell. – wonsuc Sep 16 '21 at 06:50
0

This worked for me

cell.setCellValue("line1 \n\r line2");

user1619768
  • 53
  • 1
  • 1
  • 7