pythonexcelopenpyxlarray-formulas

I have a problem with removed records in Excel using Openpyxl

Today at work, I decided to use Openpyxl for the first time so that my colleagues don't have to do manual work filling out Excel spreadsheets.

The code I wrote works fine, but when I try to insert the following formula, it can't do it.

Formula:

ws[‘D11’] = r‘=IF(AND(AND(LEFT(’drive ‘!B2, 4)=\’Gig \‘,OR(MID(’drive‘!B2, 6, 3)=\’500\‘, MID(’drive'!B2, 6, 3)=\‘800\’, MID(‘drive’!B2, 6, 4)=\‘1000\’)), MEDIAN(IF(‘drive’!$B$2:$B$136=‘drive’!B2, ‘drive’!$C$2:$C$136))),)’

What could be the error? It's supposed to be an array formula, but it didn't work with it either.....

It's possible that I'm making a mistake in the apostrophes, but I've been trying to solve this problem for the past few hours and just can't.

What the code works with:

Created an Excel document with multiple sheets. Two of them contain information on which the calculation takes place, and the third sheet is completely empty. There by means of Python are inserted formulas that, when you run the code, automatically count the result in their cells.

I expect the code to return me the median value calculated from the data in the ‘drive’ sheet.

If anyone knows a simpler solution, I'd love to have it.

Solution

  • I write the formula

    =IF(AND(AND(LEFT(drive!B2, 4)="Gig",OR(MID(drive!B2, 6, 3)="500", MID(drive!B2, 6, 3)="800", MID(drive!B2, 6, 4)="1000")), MEDIAN(IF(drive!$B$2:$B$136=drive!B2, drive!$C$2:$C$136))),)

    in Excel. save it. unzip it. Goto /Book1/xl/worksheets/sheet1.xml. I saw it is stored like this:

    enter image description here

    So below code should work:

    import openpyxl
from openpyxl.worksheet.formula import ArrayFormula
from openpyxl import utils

wb = openpyxl.load_workbook("formula.xlsx")
ws = wb['Sheet']
reference_ws = wb['drive']
quote_sheetname = utils.quote_sheetname(reference_ws.title)

ws["A1"] = ArrayFormula("A1", f'=IF(AND(AND(LEFT({quote_sheetname}!B2, 4)="Gig",OR(MID({quote_sheetname}!B2, 6, 3)="500", MID({quote_sheetname}!B2, 6, 3)="800", MID({quote_sheetname}!B2, 6, 4)="1000")), MEDIAN(IF({quote_sheetname}!$B$2:$B$136={quote_sheetname}!B2, {quote_sheetname}!$C$2:$C$136))),)')

wb.save("formula.xlsx")

    Edited, I update drive in the formula to utils.quote_sheetname(reference_ws.title)