How read Chinese Characters from XLSX File? (Java)

Question

I can already read in texts from xlsx cells and have:

String s = cell.getStringCellValue();

However when printing out this String, I get rubbish results. To solve this problem I used the Internet.

I tried about 8 different approaches and thus found that there is not yet a working answer on SO. I set the default encoding of my IDE and my XLSX Files to UTF-8. Pinyin can be correctly displayed.

Does anyone have an idea what could be wrong and how to solve this issue?

Answer 1

Not clear wherever your problem using chinese characters comes from, but I cannot reproduce it.

I have the following workbook in Excel:

The following simple code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;

class ReadXSSFUnicodeTest {

 public static void main(String[] args) {
  try {

   Workbook wb = WorkbookFactory.create(new FileInputStream("ReadXSSFUnicodeTest.xlsx"));

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {
    for (Cell cell : row) {
     String string = cell.getStringCellValue();
     System.out.println(string);
    }
   }

   wb.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}

produces:

If the problem is that Windows is not able displaying Unicode characters properly in CMD console because it has not a font with glyphs for it, then write the content to a text file:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.Writer;
import java.io.BufferedWriter;
import java.io.OutputStreamWriter;
import java.io.FileOutputStream;

class ReadXSSFUnicodeTest {

 public static void main(String[] args) {
  try {

   Writer out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("ReadXSSFUnicodeTest.txt"), "UTF-8"));

   Workbook wb = WorkbookFactory.create(new FileInputStream("ReadXSSFUnicodeTest.xlsx"));

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {
    for (Cell cell : row) {
     String string = cell.getStringCellValue();
     out.write(string + "\r\n");
     System.out.println(string);
    }
   }
   out.close();   

   wb.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}

This file then should have proper content even in Windows Notepad:

You could also using Swing (JTextArea) to provide your own output area for test outputs:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.Writer;
import java.io.BufferedWriter;
import java.io.OutputStreamWriter;
import java.io.FileOutputStream;

import javax.swing.*;
import java.awt.*;


class ReadXSSFUnicodeTest {

 public ReadXSSFUnicodeTest() {
  try {

   MySystemOut mySystemOut = new MySystemOut();

   Workbook wb = WorkbookFactory.create(new FileInputStream("ReadXSSFUnicodeTest.xlsx"));

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {
    for (Cell cell : row) {
     String string = cell.getStringCellValue();
     //System.out.println(string);
     mySystemOut.println(string);
    }
   }

   wb.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }

 public static void main(String[] args) {
  ReadXSSFUnicodeTest readXSSFUnicodeTest= new ReadXSSFUnicodeTest();
 }

 private class MySystemOut extends JTextArea {

  private String output = "";

  private MySystemOut() {
   super();  
   this.setLineWrap(true);
   JFrame frame = new JFrame("My System Outputs");
   frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
   JScrollPane areaScrollPane = new JScrollPane(this);
   areaScrollPane.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
   areaScrollPane.setPreferredSize(new Dimension(350, 150));
   frame.getContentPane().add(areaScrollPane, BorderLayout.CENTER);
   frame.pack();
   frame.setVisible(true);  
  }

  private void println(String output) {
   this.output += output + "\r\n";
   this.setText(this.output);
   this.revalidate();
  }
 }
}

This is only the simplest way and only to get test outputs since it uses Swing not the right way in terms of AWT threading issues.

Answer 2

I had the same problem while extracting Persian text from an Excel file. I was using ECLIPSE and change settings like:

1. Window -> Preferences -> Expand General and
2. Click Workspace, text file encoding (near bottom) has an encoding chooser.
3. Select "Other" radio button -> Select UTF-8 from the drop down. Click Apply and OK button OR click simply OK button

Answer 3

use this Code:

String new_Str = new String(excelfield.getBytes(1), "Cp1256"); //....to Persian text

String new_Str = new String(excelfield.getBytes(1), "UTF-8"); //....to Chinese text

OR

String new_Str = new String(your_str.getBytes(), "Cp1256");

String new_Str = new String(your_str.getBytes(), "UTF-8");