0

I am using below code, for inserting emojis into excel using apache POI-HSSF, Please let me know how can I insert emojis into .xlsx file using POI-XSSF in Java,

Workbook workBook =new HSSFWorkbook();
Sheet createSheet = workBook.createSheet("Emoji");
String str =""+"somevalue";

Row createRow = createSheet.createRow(0);
createRow.createCell(0).setCellValue(str);
//creating a file and writing the Workbook data 
try {
    FileOutputStream fileOutputStream = new FileOutputStream("/tmp/MyFirstExcel.xls");
    workBook.write(fileOutputStream);
    fileOutputStream.close();
} catch (IOException iException) {
    System.out.println("IO exception occured while creating the file" + iException.getMessage());
}

Any help would be highly appreciated.

Thej
  • 191
  • 1
  • 6
  • 23
  • 1
    Your code works for me. What are the issues you have? Which `apache poi` version and which `Excel` version are you using? – Axel Richter Sep 15 '17 at 12:12
  • It works because, I am using 'HSSFWorkbook', but I want the same functionality using 'XSSF' – Thej Sep 15 '17 at 12:23
  • 1
    Then this should be part of the question, shouldn't it? See https://stackoverflow.com/questions/38007641/write-16-bits-character-to-xlsx-file-using-apache-poi-in-java/38039869#38039869 for the problems of `XSSF` using unicode. There is a bug in `xmlbeans-2.6.0.jar`. My patch works for me. Some times ago I had found a `xmlbeans-2.6.2.jar` which contains that patch already. But can't find it any more. – Axel Richter Sep 15 '17 at 12:40
  • I am using xmlbeans-2.3.0.jar – Thej Sep 15 '17 at 12:43
  • The `xmlbeans-2.3.0.jar` contains the same bug. – Axel Richter Sep 15 '17 at 12:46
  • Found [xmlbeans-2.6.2.jar](https://maven2repo.com/com.github.pjfanning/xmlbeans/2.6.2/jar) which works for me. – Axel Richter Sep 18 '17 at 05:47
  • Even I tried with below dependency, but no luck. com.github.pjfanning xmlbeans 2.6.2 – Thej Sep 20 '17 at 11:49
  • 1
    As said it works for me using this jar. Of course you need removing the `xmlbeans-2.3.0.jar` from the class path. Else this will be possibly used. – Axel Richter Sep 20 '17 at 12:07
  • Thanks for your time, It works for me. Thanks again! – Thej Sep 20 '17 at 12:54
  • 1
    Sorry for this I cannot help since I am not using Maven. But surely is the groupId different since both the jars are from different sources. So I would suggest you refining your question by showing what works and what not and adding the tag `maven` additionally. Then possibly the `maven` specialists will answer this. – Axel Richter Sep 20 '17 at 13:33

2 Answers2

0

You need to move on from xmlbeans-2.6.0 to higher version. I did replace it with 3.1.0 plus your UTF-8 needs to be enforced. This step is not necessary but rather ensuring

String cleanedText = StringEscapeUtils.unescapeJava(yourstringhere);
                    byte[] bytes = cleanedText.getBytes(StandardCharsets.UTF_8);
                    String text = new String(bytes, StandardCharsets.UTF_8);
                    Charset charset = Charset.forName("UTF-8");
                    CharsetDecoder decoder = charset.newDecoder();
                    decoder.onMalformedInput(CodingErrorAction.IGNORE);
                    decoder.onUnmappableCharacter(CodingErrorAction.IGNORE);
                    CharsetEncoder encoder = charset.newEncoder();
                    encoder.onMalformedInput(CodingErrorAction.IGNORE);
                    encoder.onUnmappableCharacter(CodingErrorAction.IGNORE);
                    try {
                        // The new ByteBuffer is ready to be read.
                        ByteBuffer bbuf = encoder.encode(CharBuffer.wrap(text));
                        // The new ByteBuffer is ready to be read.
                        CharBuffer cbuf = decoder.decode(bbuf);
                        String str = cbuf.toString();
                        RichTextString rx = createHelper.createRichTextString(str);
                            row.createCell((short) 1).setCellValue(rx);
                    } catch (CharacterCodingException e) {
                        logger.error("PUT SOME CODE HERE FOR DEBUGGING");
                        row.createCell((short) 1).setCellValue(new XSSFRichTextString(""));
                    }

Do not forget this it does not get lost in transformation for HttpServletResponse response :

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + filename + ";");

By the way, here is the change log for xmlbeans 2.6.0 vs 3.1.0

  1. XMLBEANS-517: use safe XML parsers

    XMLBEANS-516: remove unnecessary javax and org.w3c classes

    XMLBEANS-515: remove piccolo support

    XMLBEANS-514: make java 6 the lowest supported runtime

    XMLBEANS-489: fix for Cursor getAllNamespaces not returning default namespace

    Fix for XMLBEANS-499: xmlbeans2.6.0.jar contains duplicate class files (causes issues on Android)

    XMLBEANS-447: Drop the ConcurrentReaderHashMap source code

    Fix for XMLBEANS-404: entitizeContent CDATA loop iterating too many times (causes assertion error or ArrayIndexOutOfBoundsException in replace)

    Fix for XMLBEANS-332: XMLBeans changes surrogate pair bytes to question marks

mike oganyan
  • 137
  • 5
0

This is working since xmlbeans 3.0.0

Someone mentioned version 2.6.2, which is not indexed anymore (as of now, June 2019), so jump directly to 3.x.x

jchacana
  • 49
  • 4