Bug 59707

Summary: Create new line in cell doesn't work with BufferedReader
Product: POI Reporter: rbidet
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal    
Priority: P2    
Version: 3.15-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: test file

Description rbidet 2016-06-15 13:48:30 UTC 
Hi all,

First of all, many thanks for this amazing library.

I'm trying to create a new line in a cell by reading a file.
I've implemented the sample https://poi.apache.org/spreadsheet/quick-guide.html#NewLinesInCells that works fine.

I've modified the code in order to read an external file with the same content, and the \n is written in the file instead of having a new line.

{code}
    public static void readTextFile() {
        BufferedReader br = null;
        ;
        try {
            br = new BufferedReader(new FileReader("C:/tmp/toto.txt"));
            String fileLine = "";
            String excelFileName = "C:/tmp/Test.xlsx";// name of excel file

            String sheetName = "Sheet1";// name of sheet

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(sheetName);
            while ((fileLine = br.readLine()) != null) {
                System.out.println(fileLine);
                Row row = sheet.createRow(2);
                Cell cell = row.createCell(2);
                cell.setCellValue(fileLine);
                // to enable newlines you need set a cell styles with wrap=true
                CellStyle cs = wb.createCellStyle();
                cs.setWrapText(true);
                cell.setCellStyle(cs);

            }
            FileOutputStream fileOut = new FileOutputStream(excelFileName);

            // write this workbook to an Outputstream.
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            br.close();
        } catch (IOException ioe) {
            ioe.getMessage();
        }
    }
{code}

My file contains only :
Use \n with word wrap on to create a new line

I've tried with new XSSFRichTextString(fileLine), but the \n is still present in the xlsx file.

Do i miss something ? Is it a bug ?

Thank you in advance for your replies and/or correction.

Rich.
Comment 1 Dominik Stadler 2016-07-26 05:40:27 UTC 
Can you attach the toto.txt-file?
Comment 2 Dominik Stadler 2016-08-13 21:03:31 UTC 
No update on the question and I don't think we would be handling a literal \n as newline anywhere in POI.

If this is still a problem for you then please reopen this bug with some more information, ideally a self-contained unit-test that shows the problem that you are facing.
Comment 3 rbidet 2016-08-18 13:42:04 UTC 
Created attachment 34159 [details]
test file

The test file i've used.
Comment 4 rbidet 2016-08-18 13:46:38 UTC 
Hi Dominik, 

Sorry for the delay, i was on holidays.

I've attached my test file (toto.txt).
I'm a bit surprised about your last response "I don't think we would be handling a literal \n as newline anywhere in POI."

It's part of the sample https://poi.apache.org/spreadsheet/quick-guide.html#NewLinesInCells

Let me know if i can help or need further details.
Rich.
Comment 5 Mark B 2016-08-18 15:43:35 UTC 
The \n sequence is an escape sequence that is interpreted to mean a new line when it - the escape sequence - a[pears in a Java string. The example in the Quick Guide uses Java to create the sheet/row/cell and populate it. I suspect this is not what you are doing, rather you are reading from an existing file and attempting to modify it.

Am I correct in assuming that you have a test file that you have created using Excel? Further, that this file contains a cell into which you have written the string? If that is correct, then you never will see a new line. To create a new line in a cell using Excel itself then you need to press and hold down the Alt key and then press the enter key.
Comment 6 rbidet 2016-08-18 15:47:59 UTC 
Hi Mark,

My testFile is a just a plain text file containing the same string that the sample.

If i use :
Cell cell = row.createCell(2);
cell.setCellValue("Use \n with word wrap on to create a new line");

=> This create
Comment 7 rbidet 2016-08-18 15:50:39 UTC 
[...] This create a new line.

If i'm using the same thing but via a BufferedReader, it doesn't create the new line, that correspond to the ALT+Enter combination key.

Sorry for the 2 comments, i submitted the first too quickly.

Thank for helping.
Comment 8 rbidet 2016-08-18 15:57:28 UTC 
(In reply to Mark B from comment #5)
> The \n sequence is an escape sequence that is interpreted to mean a new line
> when it - the escape sequence - a[pears in a Java string. The example in the
> Quick Guide uses Java to create the sheet/row/cell and populate it. I
> suspect this is not what you are doing, rather you are reading from an
> existing file and attempting to modify it.
> 
> Am I correct in assuming that you have a test file that you have created
> using Excel? Further, that this file contains a cell into which you have
> written the string? If that is correct, then you never will see a new line.
> To create a new line in a cell using Excel itself then you need to press and
> hold down the Alt key and then press the enter key.

In addition, my test file wasn't created with Excel but via the POI library :
 XSSFWorkbook wb = new XSSFWorkbook();
 XSSFSheet sheet = wb.createSheet(sheetName);

So it's a brand new one, and i'm trying to create a cell (via the POI library), and to populate this cell with the content of a plain text file.
It works great, except for the lines that contains \n
The cell.setCellValue(fileLine); write the \n instead of creating a new line.
Comment 9 Mark B 2016-08-18 16:25:46 UTC 
I suspect that you will need to escape the backslash character in that case. Rather than "Use \n with word wrap on to create a new line" your text file will need to contain "Use \\n with word wrap on to create a new line". Not certain on this one but I suspect that is the solution to the problem.
Comment 10 Dominik Stadler 2016-08-18 18:30:21 UTC 
The \n is a Java thing which only works in Strings that are embedded in code, not if you put it into a text-file which you read in via some sort of Reader/InputStream. The only way to have a newline inserted here is to actually put a newline into the textfile, so try to make the file look as follows and it should work:

---- cut here -------
Use 
 with word wrap on to create a new line
---- cut here -------
Comment 11 Dominik Stadler 2018-10-21 17:07:46 UTC 
No update on this discussion for some time, as far as I see this is not a problem in Apache POI.