如何用Java生成含IBM i系列(AS400)特殊隐藏字符的类XLS文本文件？

你需要生成制表符分隔的TXT文件（伪装为XLS格式），其中需包含IBM i（AS400）COBOL定义的HSPACE PIC X VALUE X'41'字符。首先明确核心对应关系：IBM i使用EBCDIC编码，X'41'在EBCDIC中对应Unicode的U+00A0（非断空格），也就是你在Notepad++中看到的xA0字符。你的测试代码存在两个关键问题：

• 字符串中的\\u00A0是错误写法，Java中\\会被解析为普通反斜杠，导致写入的是\u00A0字符序列而非实际的非断空格。
• 文件编码处理不符合Excel识别要求，导致打开显示异常。

修正步骤 #

• 正确表示非断空格：在Java字符串中直接使用\u00A0表示Unicode非断空格，无需额外转义反斜杠。
• 保证编码兼容性：使用UTF-8编码写入，添加UTF-8 BOM（解决Excel识别UTF-8文件的异常问题）。
• 替换错误字面量：将数组中所有\\u00A0替换为\u00A0，\\u0020替换为普通空格 。

修正后的代码 #

import java.io.BufferedWriter;
import java.io.OutputStreamWriter;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;

public class TxtXlsGenerator {
    public static void main(String[] args) {
        List<Object[]> data = new ArrayList<>();
        // 替换错误的转义字符，使用实际的非断空格和普通空格
        data.add(new Object[]{" AS1", "185914", " NETHERLANDS", "NL", "A0", "2023714", "\u00A02023714", "27-AUG-2022", "03-FEB-2023", "\u00A0", "\u00A04", "00000000", "\u00A0IF-ADAMAS", "\u00A0", "\u00A0PTF166091NL00", "\u00A0P166091NL00", "\u00A0", "\u00A0", "\u00A0", "\u00A0", "\u00A0IF ADAMAS B V"});
        data.add(new Object[]{" AS1", "200893", " GERMANY", "DE", "\u00A0", "\u00A013801864.3", "\u00A02915188", "05-NOV-2022", "22-FEB-2023", "\u00A0R80049", "\u00A010", "00000434", "\u00A0MICRONIT M", "\u00A0", "\u00A0PTF124241DEEP", "\u00A0P118354DEEP", "\u00A0", "\u00A0", "\u00A0", "\u00A0", "\u00A0MICRONIT MICROFLUIDICS B.V."});

        try (BufferedWriter writer = new BufferedWriter(
                new OutputStreamWriter(Files.newOutputStream(Paths.get("output.XLS")), "UTF-8"))) {
            // 写入UTF-8 BOM，确保Excel正确识别编码
            writer.write('\uFEFF');
            
            // 写入表头
            writer.write("\"Client\"\t\"Case Number\"\t\"Country\"\t\"WIPO\"\t\"Subcase\"\t\"Application Number\"\t\"Patent Number\"\t\"Due Date\"\t\"Paid Date\"\t\"Invoice Number\"\t\"Annuity Number\"\t\"Invoice Amount\"\t\"Client/Division\"\t\"Client Ref(Inv)\"\t\"Client Ref#1(Ctry)\"\t\"Client Ref#2(Ctry)\"\t\"Attorney(Inv)\"\t\"Attorney(Ctry)\"\t\"Remarks\"\t\"Local Title\"\t\"Title Holder\"\n");

            // 写入数据行
            for (Object[] row : data) {
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < row.length; i++) {
                    sb.append("\"").append(row[i].toString()).append("\"");
                    if (i < row.length - 1) {
                        sb.append("\t");
                    }
                }
                sb.append("\n");
                writer.write(sb.toString());
            }
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

关键说明 #

• 字符对应：\u00A0直接对应EBCDIC X'41'转换后的Unicode非断空格，写入后在Notepad++（UTF-8编码）中会显示为xA0，Excel打开时会正常识别为空格。
• UTF-8 BOM：写入\uFEFF作为BOM头，避免Excel因编码识别错误显示乱码。
• 写入方式：使用OutputStreamWriter明确指定UTF-8编码，替代依赖系统默认编码的FileWriter，保证跨环境一致性。

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