Filtrowanie danych w Excel z użyciem Power Automate Desktop
W tym poście pokażę, jak filtrować dane w tabeli w programie Microsoft Excel.
Proces jest naprawdę prosty i łatwy. Wszystko kręci się wokół akcji o nazwie „Send keys” z ustawieniami mówiącymi akcji, aby wysłać je do określonego okna. Po pierwsze, kiedy otwierasz plik Excel, musisz włączyć filtrowanie. Można to zrobić, wysyłając skrót klawiaturowy Ctrl+Shift+L. To w PAD powinno być wyrażone jako {Control}({Shift}(L)).
Następnie musisz ustawić focus na komórce, która zawiera nagłówek kolumny, którą chcesz filtrować (lub innymi słowy – gdzie widoczny jest mały przycisk do otwierania okna filtrowania).
Po ustawieniu fokusu musisz wysłać kolejny skrót klawiaturowy, tym razem: Alt+strzałka w dół. W PAD byłoby to: {Alt}({Down}).
Następnie użyj „UI selector”, aby uzyskać elementy interfejsu użytkownika określonych części okna dialogowego filtra, np. pole wyszukiwania i przycisk OK. Gdy już je masz, po prostu zbuduj wokół nich logikę, na przykład wypełnij pole wyszukiwania terminem, którego musisz użyć do filtrowania, a następnie naciśnij przycisk OK.
I to wszystko!
Poniżej znajdziesz kod, który po skopiowaniu i wklejeniu do PAD-a zamieni się w 7 akcji wraz z selektorami, dzięki czemu możesz spróbować sam. Powodzenia!
Excel.LaunchExcel.LaunchAndOpen Path: $'''C:\\Users\\USER\\Downloads\\book.xlsx''' Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''A''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Control}({Shift}(L))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 1 EndColumn: $'''B''' EndRow: 1
MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Alt}({Down})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: True
UIAutomation.PopulateTextField TextField: appmask['Window \'book.xlsx - Excel\'']['Edit \'Type field name to search for\''] Text: 2 Mode: UIAutomation.PopulateTextMode.Replace ClickType: UIAutomation.PopulateMouseClickType.SingleClick
UIAutomation.PressButton Button: appmask['Window \'book.xlsx - Excel\'']['Button \'OK\'']
# [ControlRepository][PowerAutomateDesktop]
{
"ApplicationInfo": {
"Name": "ClipboardControlRepository",
"Version": "1.0"
},
"Screens": [
{
"Controls": [
{
"AutomationProtocol": "uia3",
"ElementTypeName": "Edit",
"InstanceId": "85bbf960-d360-4df5-97e7-49f73a6e04fe",
"Name": "Edit 'Type field name to search for'",
"SelectorCount": 1,
"Selectors": [
{
"CustomSelector": null,
"Elements": [
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUIToolWindow"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "Menu 'NetUIToolWindow'",
"Tag": "menu"
},
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUIDismissBehavior"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "UI Custom 'NetUIDismissBehavior'",
"Tag": "custom"
},
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUITextbox"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": "Type field name to search for"
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "Edit 'Type field name to search for'",
"Tag": "edit"
}
],
"Ignore": false,
"IsCustom": false,
"IsWindowsInstance": false,
"Order": 0
}
],
"Tag": "edit"
},
{
"AutomationProtocol": "uia3",
"ElementTypeName": "Button",
"InstanceId": "509b6bbb-8b91-4426-81a1-459b9be42b3e",
"Name": "Button 'OK'",
"SelectorCount": 1,
"Selectors": [
{
"CustomSelector": null,
"Elements": [
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUIToolWindow"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "Menu 'NetUIToolWindow'",
"Tag": "menu"
},
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUIDismissBehavior"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "UI Custom 'NetUIDismissBehavior'",
"Tag": "custom"
},
{
"Attributes": [
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "NetUIButton"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": "OK"
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "Button 'OK'",
"Tag": "button"
}
],
"Ignore": false,
"IsCustom": false,
"IsWindowsInstance": false,
"Order": 0
}
],
"Tag": "button"
}
],
"ElementTypeName": "Window",
"InstanceId": "2497b5ce-8aac-4541-b166-85d9d2dfed34",
"Name": "Window 'book.xlsx - Excel'",
"SelectorCount": 1,
"Selectors": [
{
"CustomSelector": null,
"Elements": [
{
"Attributes": [
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Class",
"Operation": "EqualTo",
"Value": "XLMAIN"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Enabled",
"Operation": "EqualTo",
"Value": true
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Id",
"Operation": "EqualTo",
"Value": ""
},
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Name",
"Operation": "EqualTo",
"Value": "book.xlsx - Excel"
},
{
"Ignore": true,
"IsOrdinal": true,
"Name": "Ordinal",
"Operation": "EqualTo",
"Value": -1
},
{
"Ignore": false,
"IsOrdinal": false,
"Name": "Process",
"Operation": "EqualTo",
"Value": "EXCEL"
},
{
"Ignore": true,
"IsOrdinal": false,
"Name": "Visible",
"Operation": "EqualTo",
"Value": true
}
],
"CustomValue": null,
"Ignore": false,
"Name": "Window 'book.xlsx - Excel'",
"Tag": "window"
}
],
"Ignore": false,
"IsCustom": false,
"IsWindowsInstance": false,
"Order": 0
}
],
"Tag": "window"
}
],
"Version": 1
}
