使用openpyxl创建评估为错误的DataValidation [英] Creating DataValidation that evaluates to an error with openpyxl

问题描述

使用python和openpyxl我正在尝试使用 from openpyxl import Workbook from openpyxl.worksheet.datavalidation import DataValidation wb = Workbook() sheet1 = wb.active data_sheet = wb.create_sheet("DATA") data_sheet['A1'] = "data1" data_sheet['A2'] = "value1" data_sheet['A3'] = "value2" data_sheet['B1'] = "data2" data_sheet['B2'] = "other value1" data_sheet['B3'] = "other value2" data_sheet['B4'] = "other value3" formula = "DATA!1:1" data_validation = DataValidation(type='list', formula1=formula, allow_blank=True) sheet1.add_data_validation(data_validation) data_validation.add("A1") # when I comment this section out # and create data validation with following formula by hand # it can be saved in excel and it works # but as I mentioned, error popup appears in excel formula2 = "OFFSET(DATA!A2;0;MATCH(A1;DATA!1:1;0)-1;COUNTA(OFFSET(DATA!A:A;0;MATCH(A1;DATA!1:1;0)-1))-1)" data_validation2 = DataValidation(type='list', formula1=formula2, allow_blank=True) sheet1.add_data_validation(data_validation2) data_validation2.add("B1") wb.save("example.xlsx")

注意:我现在正在尝试采用不同的方法来处理使用Excel公式处理错误，以先到者为准，即可解决我的问题.

解决方案

所以我的朋友excel-specialist向我指出 #working formula, replaced semicolons with commas formula2 = "OFFSET(DATA!A2,0,MATCH(A1,DATA!1:1,0)-1,COUNTA(OFFSET(DATA!A:A,0,MATCH(A1,DATA!1:1,0)-1))-1)"

Using python and openpyxl I'm trying to create dependent list of data with DataValidation like in example from this article.

DataValidation works fine when I'm pointing directly to some range of cells. But when I'm creating cascade then resulting excel file is corrupted and can be opened only in recovery mode.

My only guess right now is that it might be related to error when first column is empty then after saving cascaded DataValidation excel shows error popup

The Source currently evaluates to an error. Do you want to continue?

Still we can save such potentially erroneous DataValidation when doing it manually in excel. It just leaves us with empty data list.

So my question is does openpyxl not support such errors? Or is there some DataValidation argument which I missed to silently skip such errors? Any other idea to create DataValidation that evaluates to an error with python?

Example code to reproduce the error:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
sheet1 = wb.active
data_sheet = wb.create_sheet("DATA")
data_sheet['A1'] = "data1"
data_sheet['A2'] = "value1"
data_sheet['A3'] = "value2"
data_sheet['B1'] = "data2"
data_sheet['B2'] = "other value1"
data_sheet['B3'] = "other value2"
data_sheet['B4'] = "other value3"

formula = "DATA!1:1"
data_validation = DataValidation(type='list', formula1=formula, allow_blank=True)
sheet1.add_data_validation(data_validation)
data_validation.add("A1")

# when I comment this section out 
# and create data validation with following formula by hand 
# it can be saved in excel and it works
# but as I mentioned, error popup appears in excel
formula2 = "OFFSET(DATA!A2;0;MATCH(A1;DATA!1:1;0)-1;COUNTA(OFFSET(DATA!A:A;0;MATCH(A1;DATA!1:1;0)-1))-1)"
data_validation2 = DataValidation(type='list', formula1=formula2, allow_blank=True)
sheet1.add_data_validation(data_validation2)
data_validation2.add("B1")

wb.save("example.xlsx")

NOTE: I'm trying now different approach to handle error with excel formula, whichever comes first could solve my problem.

解决方案

So my friend excel-specialist pointed me to , vs ;

My bad was that I tried to put into openpyxl formula separated by semicolon as in my excel but library uses only commas for this purpose regardless of windows regionale settings.

Again fooled by windows... Hope this will save others the many hours I wasted on finding out the error.

#working formula, replaced semicolons with commas
formula2 = "OFFSET(DATA!A2,0,MATCH(A1,DATA!1:1,0)-1,COUNTA(OFFSET(DATA!A:A,0,MATCH(A1,DATA!1:1,0)-1))-1)"

这篇关于使用openpyxl创建评估为错误的DataValidation的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！