[JAVA] How to solve the problem that line breaks in cells are amplified when outputting EXCEL using SXSSF Workbook (3.16 or older)

Overview

When outputting EXCEL using SXSSF Workbook, Use ver poi-3.17 or later. that's all.

Below, I will leave it as a memo if you absolutely must use poi-3.16.

Event

When using SXSSFWorkbook, if a character string with a line feed code of CRLF is set in a cell, an EXCEL file with double the number of line breaks in the cell is generated. When using a version other than the above memory-saving version, CRLF line breaks do not double.

Conclusion

When using SXSSFWorkbook, if the line feed code of the character string to be set in the cell is CRLF, set it to CR only or LF only before passing it.

Untested example

SXSSFWorkbook wb = new SXSSFWorkbook();
 // Abbreviation
 String setString = "1st line \ r \ n2nd line";
Row row = sheet1.createRow(rowNum);
Cell cell = row.createCell(colNum);
cell.setCellValue(setString.replaceAll("\r\n", "\n"));

Cause of failure

Grep the source with'\ r'to find out. Scan character by character with the switch of org.apache.poi.xssf.streaming.SheetDataWriter, Because'\ r'and'\ n'are converted to line breaks respectively. Missing consideration of "\ r \ n".

Even poi-3.16 didn't fix it.

that's all.

2019/3/19 postscript

It seems to have been fixed in 3.17. Thank you to @ yuki-teraoka for pointing this out.

Bugzilla: Bug 61048 - Newlines in cells not rendering with SXSSF github: [Bug-61048] SXSSF module writes wrong escape sequence for carriage re…

Recommended Posts

How to solve the problem that line breaks in cells are amplified when outputting EXCEL using SXSSF Workbook (3.16 or older)
How to solve the unknown error when using slf4j in Java
How to solve the problem that it is not processed normally when nesting beans in Spring Batch
How to fix the problem that the upper half is cut off when using UITabBar
How to solve the problem when the value is not sent when the form is disabled in rails and sent
[jOOQ] How to CASE WHEN in the WHERE / AND / OR clause
[Rails] How to solve the problem that the default image is overwritten when editing without uploading the image [Active Storage]
How to solve the problem that notification cannot be requested on iOS14
How to display the text entered in text_area in Rails with line breaks
[Swift] How to display the entered characters in Widget via UserDefaults when using WidgetKit
[Rails] How to solve the error "undefined method` visit'" when using Capybara with Rspec
How to convert A to a and a to A using AND and OR in Java