Apache POI生成希伯来语与英语混合内容的Excel排版问题

问题描述 #

我有一组以希伯来语开头、混合英语的字符串列表，期望生成的Excel内容格式如下：

אדר: Mon, 40 minutes and 11 חלקים after 12:00 PM
ניסן: Wed, 24 minutes and 12 חלקים after 1:00 AM
אייר: Thu, 8 minutes and 13 חלקים after 2:00 PM
סיון: ‎שבת‎, 52 minutes and 14 חלקים after 2:00 AM

但使用Apache POI生成Excel时，出现排版错乱、希伯来语显示位置异常的问题。尝试插入左右方向控制字符（‮、‭）无法统一生效，添加开头英文字符也存在部分排版问题。复现代码如下：

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.IntStream;

public class TestHebrew {

    int rowNumber = 1;
    XSSFSheet sheet;
    XSSFRow row;

    @Test
    public void TestHebrewEnglishInterlace() throws IOException {
        List<MyObject> objects = new ArrayList<>();
        objects.add(new MyObject("אדר", "Mon, 40 minutes and 11 חלקים after 12:00 PM"));
        objects.add(new MyObject("ניסן", "Wed, 24 minutes and 12 חלקים after 1:00 AM"));
        objects.add(new MyObject("אייר", "Thu, 8 minutes and 13 חלקים after 2:00 PM"));
        objects.add(new MyObject("סיון", "Sat, 52 minutes and 14 חלקים after 2:00 AM"));

        objects.get(3).description = objects.get(3).description.replace("Sat", "שבת");


        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            sheet = workbook.createSheet("TestSheet");
            row = sheet.createRow(rowNumber);

            XSSFCellStyle style = workbook.createCellStyle();
            style.setWrapText(true);
            style.setAlignment(HorizontalAlignment.LEFT);
            for (MyObject o : objects) {
                addCellAndNextRow(style, o.month + ":" + "" + o.description);
            }

            addCellAndNextRow(style, "");
            addCellAndNextRow(style, "Same values but appending English first");
            addCellAndNextRow(style, "");

            for (MyObject o : objects) {
                addCellAndNextRow(style, "a " + o.month + ": " + "" + o.description);
            }

            IntStream.range(0, 1).forEach(i -> sheet.autoSizeColumn(i));

            try (FileOutputStream out = new FileOutputStream(FileSystemView.getFileSystemView().getHomeDirectory().toPath().resolve("output.xlsx").toFile())) {
                workbook.write(out);
            }
            Desktop.getDesktop().open(FileSystemView.getFileSystemView().getHomeDirectory().toPath().resolve("output.xlsx").toFile());

        }
    }

    private void addCellAndNextRow(XSSFCellStyle style, String value) {
        XSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(value);
        row = sheet.createRow(++rowNumber);
    }

    static class MyObject {
        String month;
        String description;

        public MyObject(String month, String description) {
            this.month = month;
            this.description = description;
        }
    }
}

解决方案 #

核心思路 #

混合希伯来语（RTL，从右到左）和英语（LTR，从左到右）的文本，Excel的双向文本算法会自动调整排版，但需明确控制文本方向规则：

1. 将单元格设置为RTL对齐，适配希伯来语开头的结构
2. 在希伯来语与英语分界处插入LRM（Left-to-Right Mark，U+200E）控制字符，强制英文部分保持从左到右的顺序

修改后的代码 #

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;

import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.IntStream;

public class TestHebrew {

    int rowNumber = 1;
    XSSFSheet sheet;
    XSSFRow row;

    // 定义Left-to-Right Mark字符，分隔希伯来语与英语内容
    private static final char LRM = '\u200E';

    @Test
    public void TestHebrewEnglishInterlace() throws IOException {
        List<MyObject> objects = new ArrayList<>();
        objects.add(new MyObject("אדר", "Mon, 40 minutes and 11 חלקים after 12:00 PM"));
        objects.add(new MyObject("ניסן", "Wed, 24 minutes and 12 חלקים after 1:00 AM"));
        objects.add(new MyObject("אייר", "Thu, 8 minutes and 13 חלקים after 2:00 PM"));
        objects.add(new MyObject("סיון", "Sat, 52 minutes and 14 חלקים after 2:00 AM"));

        objects.get(3).description = objects.get(3).description.replace("Sat", "שבת");


        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            sheet = workbook.createSheet("TestSheet");
            row = sheet.createRow(rowNumber);

            XSSFCellStyle rtlStyle = workbook.createCellStyle();
            rtlStyle.setWrapText(true);
            // 设置单元格为RTL对齐（右对齐），适配希伯来语排版
            rtlStyle.setAlignment(HorizontalAlignment.RIGHT);
            rtlStyle.setVerticalAlignment(VerticalAlignment.TOP);

            for (MyObject o : objects) {
                // 在希伯来语月份与英文描述间插入LRM，保证英文正常渲染
                String formattedText = o.month + ":" + LRM + " " + o.description;
                addCellAndNextRow(rtlStyle, formattedText);
            }

            addCellAndNextRow(rtlStyle, "");
            addCellAndNextRow(rtlStyle, "修复后的排版效果");
            addCellAndNextRow(rtlStyle, "");

            IntStream.range(0, 1).forEach(i -> sheet.autoSizeColumn(i));

            try (FileOutputStream out = new FileOutputStream(FileSystemView.getFileSystemView().getHomeDirectory().toPath().resolve("output_fixed.xlsx").toFile())) {
                workbook.write(out);
            }
            Desktop.getDesktop().open(FileSystemView.getFileSystemView().getHomeDirectory().toPath().resolve("output_fixed.xlsx").toFile());

        }
    }

    private void addCellAndNextRow(XSSFCellStyle style, String value) {
        XSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(value);
        row = sheet.createRow(++rowNumber);
    }

    static class MyObject {
        String month;
        String description;

        public MyObject(String month, String description) {
            this.month = month;
            this.description = description;
        }
    }
}

代码说明 #

• HorizontalAlignment.RIGHT：将单元格对齐方式设置为右对齐，符合RTL文本的阅读习惯，确保希伯来语开头内容显示在单元格右侧
• LRM（\u200E）：插入在希伯来语与英语的分界位置，强制后续英文内容按照从左到右的顺序渲染，避免被RTL逻辑反转
• 该方法无需额外添加开头英文字符，即可稳定控制混合文本的排版顺序

内容的提问来源于stack exchange，提问作者CeePlusPlus