0

I am using xlsx-populate to write to excel files. Whenever the text contains special characters such as smart quotes or bullets points, it gets printed as weird character like ’. However if replace them with their Unicode value (Right Single Quotation mark with \u2019), it renders correctly in excel sheet.

I tried js libraries like slugify to convert the special characters into ascii version but all of them would individually convert every constituent byte of special char and not as whole.

Currently, I replace the special character with their Unicode value using regex but there will be always some character that I will miss. Is there a better way to handle this problem?

LoneWolf
  • 86
  • 8
  • 1
    You are writing using one encoding and Excel is read using different encoding (it's not UTF-8 by default!) – Justinas Aug 04 '21 at 05:58
  • @Justinas Is it possible to handle this before I write to the excel sheet? Like converting all special characters to ASCII or replacing them with Unicode values. – LoneWolf Aug 04 '21 at 06:04
  • 1
    Yes, it's possible to convert from one encoding to another (unless there is no representation of that char in other encoding). But even if you convert it, it still depends on user specified Excel in-app encoding – Justinas Aug 04 '21 at 16:40
  • 1
    This is a [mojibake](https://en.wikipedia.org/wiki/Mojibake) mechanism (example in Python): `'\u2019'.encode('utf-8') .decode('cp1252')` returns `'’'` – JosefZ Aug 04 '21 at 18:44
  • 1
    Thanks a lot @JosefZ . I first encoded the input string into "cp1252" and then decoded it using "utf-8", giving the required result. – LoneWolf Aug 05 '21 at 15:51
  • 1
    Please consider [self-answering](https://stackoverflow.com/help/self-answer)… – JosefZ Aug 05 '21 at 16:29

1 Answers1

0

I used iconv-lite library to first encode the string into 'win1252' and then decoded it with 'utf-8'. The resulting string renders correctly when written to excel.

iconv.decode(iconv.encode(string, 'win1252'), 'utf-8')

Thanks @JosefZ for suggesting this path.

LoneWolf
  • 86
  • 8