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.