Apache POI jar is not able to write a special character in excel file

Shubham Jain

I have a special character in my string which I want to write in my excel file but poi jar replacing it with ?

Special character as below string:

enter image description here

Some site throw below unicode

1)

Unicode character          Oct  Dec Hex HTML
\x{F}   si shift in ctrl-o  017 15  0xF 

2)

U+000F : <control-000F> (SHIFT IN [SI])

Example:

My string is as below

enter image description here

Excel showing output as :

PrincipalOffice~?DIRECTION

Code I am using is as something below:

String filename= "D:\\DataFiles\\"+File+"";
XSSFWorkbook hwb =new XSSFWorkbook();
XSSFSheet sheet =  hwb.createSheet("Data");
XSSFRow rowhead=   sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("my");
XSSFRow row=   sheet.createRow((short)i);
String value = "PrincipalOffice~DIRECTION";
row.createCell((short) 0).setCellValue(value);
FileOutputStream fileOut =  new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
hwb.close();

I have tried with 3.15 and 3.17 jars of apache poi Do I need to use some other libary?, if yes please suggest

Axel Richter

The character 0x0F or &#15;, you are mentioned, is not able to store directly in XML since it is a control character. So since *.xlsx files stores content using XML, that character cannot be stored directly.

Nevertheless does Microsoft defining: ECMA-376 Part 1 22.4 Variant Types 22.4.2.4 bstr (Basic String):

22.4.2.4 bstr (Basic String)

This element defines a binary basic string variant type, which can store any valid Unicode character. Unicode characters that cannot be directly represented in XML as defined by the XML 1.0 specification, shall be escaped using the Unicode numerical character representation escape character format _xHHHH_, where H represents a hexadecimal character in the character's value. [Example: The Unicode character 8 is not permitted in an XML 1.0 document, so it shall be escaped as _x0008_. end example] To store the literal form of an escape sequence, the initial underscore shall itself be escaped (i.e. stored as _x005F_). [Example: The string literal _x0008_ would be stored as _x005F_x0008_. end example]

The possible values for this element are defined by the W3C XML Schema string datatype.

This extends the W3C XML Schema string datatype. So that the character sequence _xHHHH_ does have a special meaning as a kind of entity like &#xHHHH;.

So the following will work and Excel will have the character 0x0F as cell content.

import java.io.FileOutputStream;

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

public class CreateExcelControlCharacter {

 public static void main(String[] args) throws Exception {

  Workbook wb = new XSSFWorkbook();

  Sheet sheet = wb.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);

  String value = "PrincipalOffice\u000FDIRECTION";

  value = value.replace("\u000F", "_x000F_");

  cell.setCellValue(value);

  wb.write(new FileOutputStream("CreateExcelControlCharacter.xlsx"));
  wb.close();
 }

}

But may I ask why you are needing this control character to be a Excel cell content? That could be the answer to my question Useful use cases for escape character format _xHHHH_ in Office Open XML?.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Write to excel using Apache POI. FileNotFoundException:(The requested operation cannot be performed on a file with a user-mapped section open)

分類Dev

Writing to an excel using Apache POI corrupts the excel file

分類Dev

Cannot read from Excel file using apache poi

分類Dev

How to make my path relative in selenium apache poi while I input my Excel file? and How to write the test result in excel after performing testing

分類Dev

Apache POI Write to ExcelはEclipseで機能しますが、OpenJDK11でJarにコンパイルした後は機能しません

分類Dev

Apache poi XSSFSheet.write() not finishing

分類Dev

Read from excel file .xlsx using java Apache POI 3.9 Eclipse

分類Dev

Can't read Excel 2010 file with Apache POI. First Row number is -1

分類Dev

How to auto adjust the column in excel in apache POI

分類Dev

Crontabbed shell script not able to create/write to a file

分類Dev

apache poi error zip file is closed

分類Dev

Excel Apache Poiのトルコ通貨

分類Dev

Accessing Excel Sheets in Java using Apache POI with Eclipse IDE

分類Dev

Extract Text after special character in a multi line Excel Cell

分類Dev

Write a character array with null values into a file stream

分類Dev

Write a unicode character to a file in a binary way

分類Dev

How to create a text file and write text character by character using MATLAB?

分類Dev

Apache csv file download not in excel

分類Dev

msbuild not able to write to files

分類Dev

Apache Poi Date値

分類Dev

Apache POI例外

分類Dev

Set margins with Apache poi

分類Dev

Java Apache POI

分類Dev

Apache POI ClassNotFoundException

分類Dev

Unable to write extracted items properly in an excel file?

分類Dev

How to write a Matlab-struct to an Excel file?

分類Dev

Special character in LIKE in PostgreSQL

分類Dev

Python: encode special character

分類Dev

Replace special character in htaccess

Related 関連記事

  1. 1

    Write to excel using Apache POI. FileNotFoundException:(The requested operation cannot be performed on a file with a user-mapped section open)

  2. 2

    Writing to an excel using Apache POI corrupts the excel file

  3. 3

    Cannot read from Excel file using apache poi

  4. 4

    How to make my path relative in selenium apache poi while I input my Excel file? and How to write the test result in excel after performing testing

  5. 5

    Apache POI Write to ExcelはEclipseで機能しますが、OpenJDK11でJarにコンパイルした後は機能しません

  6. 6

    Apache poi XSSFSheet.write() not finishing

  7. 7

    Read from excel file .xlsx using java Apache POI 3.9 Eclipse

  8. 8

    Can't read Excel 2010 file with Apache POI. First Row number is -1

  9. 9

    How to auto adjust the column in excel in apache POI

  10. 10

    Crontabbed shell script not able to create/write to a file

  11. 11

    apache poi error zip file is closed

  12. 12

    Excel Apache Poiのトルコ通貨

  13. 13

    Accessing Excel Sheets in Java using Apache POI with Eclipse IDE

  14. 14

    Extract Text after special character in a multi line Excel Cell

  15. 15

    Write a character array with null values into a file stream

  16. 16

    Write a unicode character to a file in a binary way

  17. 17

    How to create a text file and write text character by character using MATLAB?

  18. 18

    Apache csv file download not in excel

  19. 19

    msbuild not able to write to files

  20. 20

    Apache Poi Date値

  21. 21

    Apache POI例外

  22. 22

    Set margins with Apache poi

  23. 23

    Java Apache POI

  24. 24

    Apache POI ClassNotFoundException

  25. 25

    Unable to write extracted items properly in an excel file?

  26. 26

    How to write a Matlab-struct to an Excel file?

  27. 27

    Special character in LIKE in PostgreSQL

  28. 28

    Python: encode special character

  29. 29

    Replace special character in htaccess

ホットタグ

アーカイブ