起因
导出的excel需要在表格内换行,但搜索到的方法都实现不了我的需求,经同事搜查得知,这是POI的一个bug,已经在17年八月后被解决。
生成方式
pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.0.0</version>
</dependency>
生成excel换行代码
@Test
public void test() throws IOException {
Workbook wb = new SXSSFWorkbook(); //or new HSSFWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(2);
Cell cell = row.createCell(3);
XSSFRichTextString rich = new XSSFRichTextString("测试\r" + "换行");
cell.setCellValue(rich);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {
wb.write(fileOut);
}
}
代码解析
SXSSFWorkbook是先将数据生成xml存储在本地,转化的时候会对表格内数据进行转义,具体代码见
org.apache.poi.xssf.streaming.SheetDataWriter#outputQuotedString()
方法,内部代码为:
String codepoint = (String)var2.next();
byte var5 = -1;
switch(codepoint.hashCode()) {
case 9:
if (codepoint.equals("\t")) {
var5 = 6;
}
break;
case 10:
if (codepoint.equals("\n")) {
var5 = 4;
}
break;
case 13:
if (codepoint.equals("\r")) {
var5 = 5;
}
break;
case 34:
if (codepoint.equals("\"")) {
var5 = 3;
}
break;
case 38:
if (codepoint.equals("&")) {
var5 = 2;
}
break;
case 60:
if (codepoint.equals("<")) {
var5 = 0;
}
break;
case 62:
if (codepoint.equals(">")) {
var5 = 1;
}
break;
case 160:
if (codepoint.equals(" ")) {
var5 = 7;
}
}
switch(var5) {
case 0:
this._out.write("<");
break;
case 1:
this._out.write(">");
break;
case 2:
this._out.write("&");
break;
case 3:
this._out.write(""");
break;
case 4:
this._out.write("
");
break;
case 5:
this._out.write("
");
break;
case 6:
this._out.write("	");
break;
case 7:
this._out.write(" ");
break;
default:
if (codepoint.length() == 1) {
char c = codepoint.charAt(0);
if (replaceWithQuestionMark(c)) {
this._out.write(63);
} else {
this._out.write(c);
}
} else {
this._out.write(codepoint);
}
}
由上可见,\r
会被解析成
,这个字符会被excel解析成换行,以此来达到换行的目的。而我之前使用的是3.9的版本,那个版本里会将\r
和 \n
都解析成

,这个字符会被excel解析成空格,而不是换行。