Python 3 xlutils+xlrd+xlwt

Question

I'm struggling with editing existing xls workbook with xlrd/xlwt/xlutils I can change values in xlrd workbook, but I need to write them in existing workbook. My code doesn't work, I know that alghorytm is right, but code isn't.

  rb = open_workbook(filename)
#set active sheet
rs = rb.sheet_by_index(0)
#make instance for xlsutils
wb = copy(rb)
ws = wb.get_sheet(0)

rows = rs.nrows
cols = rs.ncols
#iterate and prepare format for SQL db tables
for row_idx in range (0,rows):
    for col_idx in range (0,cols):
        cell=rs.cell(row_idx,col_idx)
        clval = cell.value
        cltp = cell.ctype
        if cltp == xlrd.XL_CELL_BLANK:
           clval ="xy"
           ws.write(row_idx,col_idx,"xy")
        elif cltp == xlrd.XLDateError:
            date_format = XFStyle()
            date_format.num_format_str = 'dd/MM/yyyy'
            clval = '01/01/2018'
            ws.write(row_idx,col_idx, '01/01/2018', date_format)
        elif cltp == xlrd.XL_CELL_EMPTY:
            clval="XYU"
            ws.write(row_idx,col_idx,"xy")
        elif cltp == xlrd.XL_CELL_NUMBER:
            if clval < 0:
             clval=0
             ws.write(row_idx, col_idx,0)
        elif cltp == xlrd.XL_CELL_TEXT:
             clval = ftfy.fix_text(clval)
             ws.write(row_idx,col_idx, ftfy.fix_text(clval))

        elif clval == -693594:
             date_format = XFStyle()
             date_format.num_format_str = 'dd/MM/yyyy'
             clval='01/01/2018'
             ws.write(row_idx, col_idx,'01/01/2018',date_format)
             #print (row_idx,col_idx)
        print(clval)
        save(wb,'abbcards_2.xls')

Output:
 File "D:/rs_al/IdeaProjects/ExcelToSQL/PyXLSSQL/XLStoSQL.py", line 69, in 
 xls_wrk
save(wb,'abbcards_2.xls')
File "C:\Python\lib\site-packages\xlutils\save.py", line 24, in save
StreamWriter(stream)
File "C:\Python\lib\site-packages\xlutils\filter.py", line 941, in process
reader(chain[0])
File "C:\Python\lib\site-packages\xlutils\filter.py", line 65, in __call__
filter.workbook(workbook,filename)
File "C:\Python\lib\site-packages\xlutils\filter.py", line 291, in workbook
self.wtbook.dates_1904 = rdbook.datemode
AttributeError: 'Workbook' object has no attribute 'datemode'

I suppose that I print existing in memory instance of xlrd workbook, values are right. But how write them?

I can't understand what copy from xlutils really does. Does it make another object? If so, how to use xlrd+xlwt for writing? I found out that pandas can't be used, because I need to know exactly cell type, to change value there.

In java I did same thing with one package where were methods to read/write/save within same object.

[Edit] your Question and show your `import ...` to verify which modules you are using. Read about [xlutils.copy](https://xlutils.readthedocs.io/en/latest/copy.html) — stovfl, Oct 19 '18 at 10:39
@stovfl full code on pastebin https://pastebin.com/FeGJpLBg. Yep, these are 2 objects and 2 different workbooks. So It's a problem, because I use xrld to find specific cells and then I use xlwt to write, but xlrd works with xlrd.book object and xlwrt works with xlwt.book. — Rostislav Aleev, Oct 19 '18 at 10:54

score 0 · Answer 1 · answered Oct 23 '18 at 14:59

0

The only working way:

Open workbook with xlrd
Iterate and get cell values
Create new Workbook with xlwt

Through iteration copy cell values

For dates create a proper style for Excel dd/mm/yyyy.

answered Oct 23 '18 at 14:59

Rostislav Aleev

351
5
19

Python 3 xlutils+xlrd+xlwt

1 Answers1