Как правильно написать функцию фильтра Python в Excel 365?
Работая над проектом, я хотел использовать функцию фильтра в Excel, чтобы отображать вещи аккуратно и профессионально. К сожалению, openpyxl (3.1.4) не поддерживает функцию фильтра, поэтому ее необходимо добавить в код Python следующим образом:
example = "=_xlfn.FILTER(Index!$D$4:$D$100,Index!$F$4:$F$100=\"JA\",\"\")"
ws['C4']= example
Когда программа запускается, она вводит данные в книгу... с @ в начале формулы, например:
=@FILTER(Index!$D$4:$D$100;Index!$F$4:$F$100 = "JA";"")
Этот символ заставляет функцию фильтра отображать только первый экземпляр элемента вместо отображения всего списка. Конечно, когда удаляешь символ, все работает нормально.
Конечно, я могу каждый раз удалять ее вручную, но через некоторое время это будет раздражать, поскольку эта программа будет использоваться довольно часто. Как я могу использовать эту мощную функцию Python? Или мне нужно просто усмехнуться и стерпеть это?
@python_noob Попробуйте это: ws['C4'].value = re.sub(r'@', '', ws['C4'].value)
Excel автоматически добавляет символ «@» в графический интерфейс точно так же, как он переводит и бесполезную транскрипцию «,» в «;» в разделителях и "." до "," в цифрах.
Ответы 1
Короткий ответ
Боюсь, вы до сих пор не можете использовать Формулы динамических массивов и поведение разделенных массивов с OpenPyXL. Поведение распределенного массива было представлено в Excel версии 365. Его нельзя использовать в предыдущих версиях. OpenPyXL был представлен и основан на спецификациях версии Excel 2007 года (первая версия, использующая Office Open XML и формат файлов *.xlsx).
Но почему символ @?
Это описано в Оператор неявного пересечения: @:
Символ @... используется... для обозначения неявного пересечения. ... @ указывает, что формула должна использовать неявное пересечение для получить значение
Если OpenPyXL хранит формулу FILTER, то он делает это так же, как версии Excel до 365, которые сохраняли формулы. Он не учитывает поведение распределенного массива, для которого потребуется хранить специальные метаданные ячеек для описания поведения распределенного массива - распределения значений по такому количеству ячеек, сколько необходимо. В OpenPyXL до сих пор даже нет классов для этих метаданных. Таким образом, когда Excel открывает файл, он находит формулу FILTER без поведения массива, которая не может работать правильно и возвращает только одно значение. Это отмечается знаком @.
Можешь убрать @?
Боюсь, не совсем так. Как уже говорилось, до сих пор вы не можете использовать формулы динамических массивов и поведение распределенного массива с помощью OpenPyXL. Можно было использовать FILTER в контексте старой формулы массива. Но для этого необходимо знать размер результирующего массива, поскольку старые формулы массива не являются динамическими и явно не распределяют свои значения по необходимому количеству ячеек.
Пример:
from openpyxl import Workbook
from openpyxl.worksheet.formula import ArrayFormula
wb = Workbook()
ws = wb.create_sheet("Index", 1)
ws['D3'] = ""
data = [{'D':"D4"}, {'D':"D5", 'F':"JA"}, {'D':"D6"}, {'D':"D7", 'F':"JA"}, {'D':"D8"}]
for row in data:
ws.append(row)
ws = wb.active;
formula = "=_xlfn.FILTER(Index!$D$4:$D$100,Index!$F$4:$F$100=\"JA\",\"\")"
ws['C4'] = ArrayFormula('C4:C5', formula)
wb.save('excelResult.xlsx')
Спасибо за объяснение. Это имеет смысл.
Поддержка метаданных FWIW будет в версии 3.2, но только согласно спецификации.
К вашему сведению, запросы на рекомендации ресурсов (в том числе «есть ли пакет Python для X») здесь обычно не по теме. Лучше вместо этого просто спросить, как чего-то добиться на Python, и если решение будет «использовать пакет X», то это нормально.