I'm trying to create an "XLS" file, but it will be available as txt file. (It means when you right click and select open with in windows and choose notepad it will show as tab separated file)
This is the sample file that I'm trying to create (I have to remove some entry because it is big). When you turn it into UTF-8 in notepad++, you will see "hidden characters" https://docs.google.com/spreadsheets/d/1q_AkGaQK8Glc6OzmVl4gRmItO4Ojnq7G/edit?usp=sharing&ouid=113904619378239546124&rtpof=true&sd=true
When you download this file and open it in notepad++. Choose the encoding as UTF-8. You will able to see those hidden characters:
This is what it looks like if you open it with excel: As you can see at the beginning of the content you will see those xA0 as the hidden characters. Now I know that AS400 use EBCDIC characters code.
This is the code that they put in the cobol program:
HSPACE PIC X VALUE X'41'
What are the equivalent of that hidden characters in Java?
I have create a test program below:
List<Object[]> data = new ArrayList<>();
data.add(new Object[]{"\u0020 AS1", "185914", "\u0020 NETHERLANDS", "NL", "A0", "\u00202023714", "\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[]{"\u0020 AS1", "\u0020200893", "\u0020 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."});
FileWriter writer = new FileWriter("output.XLS", StandardCharsets.UTF_8);
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) {
for (int i = 0; i < row.length; i++) {
writer.write("\"" + row[i].toString() + "\"");
if (i < row.length - 1) {
writer.write("\t");
}
}
writer.write("\n");
}
writer.close();
System.out.println("Done");
However, the file when I open in notepad++ with encoding UTF-8, I see nothing
Although you will see there is white space like in that text file. However, if you open this file in my generated file in excel: You can see it has weird characters that I put in my code! How can I create a text file in Java that output a "XLS / TXT" file like the IBM I series(AS400) cobol program? Can someone help me with this, please?
The file produce by AS400 is encoded (probably) with windows 1252 charset, notepad++ names it ansi. When you display it as utf8 you see XA0 because the way it is encode is illegal in utf-8.
So to produce a similar file you have to write it with charset 1252 too and use \u00A0 in your java strings, so that when writen java nio translates it from \u00a0 to \xa0
FileWriter writer = new FileWriter("output.XLS", Charset.forName("windows-1252"));
writer.write("\u00a0");