Unable to display Chinese characters in excel using CSV Bean Writer

Question

I am using ICsvBeanWriter to export data as CSV and i am trying to open it in Excel.But I am unable to view Chinese characters in Excel by default.But if i load the csv file and change encoding from Western European to 65001 Unicode(UTF-8) then i am able to see Chinese characters displayed in excel.So my question is "Is it possible to open excel with default UTF-8 encoding? Here is my code

Controller.java

    response.setHeader("Content-Disposition",
            "attachment; filename=Report " + downloadDateFormatted + ".csv");
    response.setHeader(HttpHeaders.ACCESS_CONTROL_EXPOSE_HEADERS, HttpHeaders.CONTENT_DISPOSITION);
    **response.setContentType("text/csv;charset=UTF-8");**
    CsvExporter csvExporter = new CsvExporter(transactions);
    csvExporter.export(response);

CsvExporter.java

    public void export(HttpServletResponse response) throws IOException {
       csvWriter = new CsvBeanWriter(response.getWriter(), CsvPreference.STANDARD_PREFERENCE);
       writeHeaderRow();
    }

    private void writeHeaderRow() throws IOException {
    String[] csvHeader = {"Chinese address" };
            csvWriter.writeHeader(csvHeader);
    String[] nameMapping = { "chineseAddress" };
    csvWriter.write(transaction, nameMapping);
    }
    csvWriter.close();
    }

I tried setting charset to utf-8 but still it is opening in Western European encoding in Excel by default.I tried with UTF-16.But still same result.When i open in other editors like notepad chinese characters are displayed perfectly but not working in Excel

Tempt

Perhaps you should consider saving the original output in the correct coding. I do this with BBEdit... – Rip

@SolarMike Can you please elaborate? – Tempt

BBEdit has an encoding choice box when doing save as. Choose the encoding necessary. Not sure what “elaboration” you need. – Rip

@SolarMike I am looking for something code level where i can set encoding and open excel in UTF-8 – Tempt

I think BBEdit can be driven by code, tell it to open a file then save as needed... – Rip

Do not use Excel to read CSV files. Excel is a very powerfull tool, but as CSV is not a Microsoft specific format, it only has an awful support for it. You'd better use LibreOffice calc because it allows to specify the encoding at read time. I know this is not the expected answer (hence a comment) but I am afraid it is the best I can do here... – Transducer

Answer 1

Try using UTF-8 and adding \uFEFF at the very start of the file .

This is the Byte Order Mark used by Excel to also identify UTF-8 encoded files (not only the byte order).

Code used to do a quick test:

var writer = new FileWriter("/tmp/a.csv", StandardCharsets.UTF_8);
writer.write("\uFEFF");  // BOM
writer.write("test:,\u4E2D,ok");  // or writer.write("test:,中,ok");
writer.close();

In Excel:

Ecklund

Thanks for the reply.But in the line var writer = new FileWriter("/tmp/a.csv", StandardCharsets.UTF_8); here we need to specify file path right? I dont have any specific file path.I am just passing it as httpservlet response as you can see in my code. – Tempt

Can you tell me what is this line of code is meant for writer.write("test:,\u4E2D,ok"); ? – Tempt

I modified code like this Writer writer = response.getWriter(); writer.write("\uFEFF"); csvWriter = new CsvBeanWriter(writer, CsvPreference.STANDARD_PREFERENCE); .But still i am not able to see Chinese characters displayed.It is displaying something like this 怀柔 – Tempt

Let us continue this discussion in chat. – Tempt