I'm writing a small program in Python 3.6 that reads an excel file and then modifies the format of a cell. I want multiple formats within the same cell so I'm using xlwt with write_rich_text:
import xlrd
import xlwt
from xlutils.copy import copy
col = 0
row = 0
rb = xlrd.open_workbook('test.xls', formatting_info=True)
r_sheet = rb.sheet_by_index(0)
text_cell = r_sheet.cell_value(row, col)
book = copy(rb)
first_sheet = book.get_sheet(0)
font1 = xlwt.easyfont('struck_out true, color_index red')
font2 = xlwt.easyfont('color_index green')
seg1 = (text_cell[0:10], font1)
seg2 = (text_cell[10:], font2)
first_sheet.write_rich_text(row, col, [seg1, seg2])
book.save('test.xls')
Everything works fine up to the point of writing on to the excel file but then I get the error TypeError: must be str, not bytes when saving the Workbook.
Full error:
File "test.py", line 91, in <module>
book.save('test.xls')
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\Workbook.py", line 710, in save
doc.save(filename_or_stream, self.get_biff_data())
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\Workbook.py", line 674, in get_biff_data
shared_str_table = self.__sst_rec()
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\Workbook.py", line 636, in __sst_rec
return self.__sst.get_biff_record()
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\BIFFRecords.py", line 79, in get_biff_record
self._add_rt_to_sst(s)
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\BIFFRecords.py", line 108, in _add_rt_to_sst
rt_str, rt_fr = upack2rt(rt, self.encoding)
File "C:\Users\...\AppData\Local\Continuum\Anaconda2\envs\py36\lib\site-packages\xlwt\UnicodeUtils.py", line 86, in upack2rt
fr += pack('<HH', offset, fontx)
TypeError: must be str, not bytes
I've been searching for a solution to this problem but the answers there don't seem to solve my problem:
xlwt book.save TypeError: must be str, not bytes
Python code, not able to write into xls
Then I found that this issue could be related to the way strings are handled in Python 3 vs Python 2 and that I might be getting a byte string somewhere that is causing the error.
I looked at the type of text_cell and is str, I tried ommiting the font in seg1 and seg2 but it still didn't work.
Finally I ran this same script on Python 2.7 and it worked!
The bad news is that I need the code to work in Python 3 since the server where this would run only has Python 3 and cannot be changed.
Anyone has a clue of what might be causing the issue?
Copyright Notice：Content Author:「Javier Garcia de Leaniz」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/44677179/python-3-xlwt-save-workbook-error