How can I modify the existing sheetobj.data_validations in openpyxl?

68 views Asked by At

I am writing an application to create a new blank day's worth of cells in a financial transaction spreadsheet. The spreadsheet was generated by WPS. Everything is working as I'd like, except that data validation for one column only does not copy successfully from the old cells to the new cells in the same column. When I examine the sheetobj.data_validations, I find that the working columns end like this:

M3217:M65536, E3217:E65536

The column that doesn't work correctly ends like this:

N3196:N3215

Here is the code that actually copies the cells:

for r in range(frod, eod):
        for c in range(1, maxc + 1):
                nrow = r + rinc
                cell_tmp = sheet.cell(row = r, column = c)
                val_tmp = cell_tmp.value
                new_cell = sheet.cell(row = nrow, column = c)
                new_cell.font = copy(cell_tmp.font)
                new_cell.fill = copy(cell_tmp.fill)
                new_cell.number_format = copy(cell_tmp.number_format)
                new_cell.alignment = copy(cell_tmp.alignment)
                new_cell.border = copy(cell_tmp.border)
                headstr = col_string(sheet.title, c)
                method = get_method(headstr)
                #print('header string for col', c, 'is', headstr, 'form method is', method)
                if cell_tmp.data_type is not 'f':
                    if method == 'cp_eod' and r == (eod - 2):
                        new_cell.value = val_tmp
                    else:
                        new_cell.value = None
                        new_cell.data_type = cell_tmp.data_type
                elif method == 'tc_eod':
                    new_cell.value = form_self(val_tmp, r, nrow)
                elif method == 'prev_inc':
                    if r == frod:
                        new_cell.value = form_inc(val_tmp, val_tmp[val_tmp.rindex('+')+1:])
                    else:
                        new_cell.value = date_prev(val_tmp, r, nrow)
                elif method == 'self':
                    if r == eod:
                        #print('*** EOD ***')
                        new_cell.value = form_self_dec(val_tmp, r, nrow)
                    else:
                        new_cell.value = form_self(val_tmp, r, nrow)
                elif method == 'self_fod':
                    if r == frod:
                        new_cell.value = form_self(val_tmp, r, nrow)
                    elif r == eod:
                        new_cell.value = form_self_dec(val_tmp, r, nrow)
                    else:
                        new_cell.value = form_frod(val_tmp, frod, frod + rinc, r, nrow)
                elif method == 'self_eod':
                    new_cell.value = form_eod(val_tmp, r, nrow, eod, eod + rinc)
                elif method == 'tcsbs':
                    new_cell.value = form_tcsbs(val_tmp, val_tmp[val_tmp.rindex('$')+1:])
                elif method == 'self_prev':
                    if r == eod:
                        new_cell.value = form_prev(val_tmp, nrow)
                    else:
                        new_cell.value = form_self_dec(val_tmp, r, nrow)

The column in question falls into this block of code:

                    else:
                        new_cell.value = None
                        new_cell.data_type = cell_tmp.data_type

Is it possible to edit the sheetobj.data_validations for column N to end the same way the working columns do?

Other things I have tried:

Directly setting a new data validation for the cells in the column. Python showed the cells having data validation but when I saved the workbook and opened in google sheets, it was not there.

Copying a cell from a good column into the bad column on the original sheet.

This also did not solve my problem, when I copied a new block of cells, the data validation did not copy.

Copying a cell from a good column in another sheet into the bad column on the original sheet.

This resulted in losing all data validation when I copied a new block of cells.

0

There are 0 answers