0

I am trying to autofill the excel formula =IFS(COUNTIF('PO'!$A$2:$A$11173,Q2)=0,"NO PO",COUNTIF('PO'!$A$2:$A$11173,Q2)>0,"HAVE PO") in a column in an excel file with the following code in python.

#Pick the excel file I want to edit.
file_source =XXX.xlsx'
#The number of items in column "ELEMENT"
row_len = len(pd.read_excel(file_source,sheet_name='XCX', usecols=['ELEMENT']))

#load excel file
workbook = load_workbook(filename=file_source)

ws4 = workbook["XCX"]    

#Autofill the formula
for g in range(2,row_len+2):
    k="=IFS(COUNTIF('PO'!$A$2:$A$"+str(row_len+1)+",Q"+str(g)+')=0,"NO PO",'+"COUNTIF('PO'!$A$2:$A$"+str(row_len+1)+",Q"+str(g)+')>0,"HAVE PO")'
    print(k)
    ws4.cell(row=g,column=71).value=k
workbook.save(filename=file_source)
print(pd.read_excel(file_source,sheet_name='XCX', usecols=['Check']))

However, the output formulas shown in the output file are =@IFS(COUNTIF('PO'!$A$2:$A$11173,Q2)=0,"NO PO",COUNTIF('PO'!$A$2:$A$11173,Q2)>0,"HAVE PO") which was added an @ between = and IFS(COUNTIF('PO'!$A$2:$A$11173,Q2)=0,"NO PO",COUNTIF('PO'!$A$2:$A$11173,Q2)>0,"HAVE PO").

I had checked that the loop that I used to create formula wasn't wrong. Please help me finding out why the output foumula in excel is different from the one in python.

CanaryTheBird
  • 239
  • 1
  • 11
  • https://stackoverflow.com/questions/66008301/symbol-appearing-after-inserting-if-formula-into-excel-using-openpyxl – BigBen Jul 15 '22 at 13:54
  • Though see [this comment](https://stackoverflow.com/questions/66008301/symbol-appearing-after-inserting-if-formula-into-excel-using-openpyxl#comment116712216_66010728) as well. – BigBen Jul 15 '22 at 13:55
  • @BigBen mark this post as duplicate if it is – Nicholas Hansen-Feruch Jul 15 '22 at 13:56
  • @NicholasFeruch - I will if OP confirms that the proposed solution works. – BigBen Jul 15 '22 at 13:58
  • I added ```workbook.formula_attributes['BS2'] = {'t': 'array', 'ref': "BS2:BS9"}``` before ```workbook.save(filename=file_source)```. It showed the error ```'Workbook' object has no attribute 'formula_attributes'```. – CanaryTheBird Jul 15 '22 at 14:27
  • Right, because that is the attribute of a sheet, not a workbook. – BigBen Jul 15 '22 at 14:31
  • I added formulas ```ii = 'BS'+str(g)``` and ```ws4.formula_attributes[ii] = {'@': ''}``` after ```ws4.cell(row=g,column=71).value=k```. However, it keeps showing the error ```ParseError: not well-formed (invalid token): line 1, column 7872```. – CanaryTheBird Jul 15 '22 at 17:05

0 Answers0