Pandas_VBA_数据筛选比较
Pandas与VBA筛选数据的比较
Author:Collin_PXY
需求:
将B列里值为Completed 和 Pending的A,B,D三列数据筛选出来,新建一个名为 Filited_data的worksheet来存放筛选数据。
Python解决:
from pandas import DataFrame
import pandas as pd
import openpyxl
# 处理数据
file_path = r"C:\Users\12078\Desktop\UIPATH_test\filter_data\data_need_to_be_filtered_python.xlsx"
df=pd.read_excel(file_path)
df=df.loc[df['status']!="Cancelled",['CNUM','status',"Tax"]]
df=df.reset_index(drop=True)
df['Tax']=df['Tax'].astype("float")
# 保存数据
writer = pd.ExcelWriter(file_path,engine='openpyxl')
writer.book = openpyxl.load_workbook(writer.path) # 此句如果缺少,后面语句执行时会覆盖文件里原有内容。
df.to_excel(excel_writer=writer,sheet_name="Filited_data",index=False) # 会新建一个新建 Filited_data,如果已经存在,则加数字后缀。
writer.save()
writer.close()
VBA解决
Sub FilterData()
On Error GoTo errorhandling
Dim wb_data As Workbook
Dim ws_data As Worksheet
Dim ws_filted As Worksheet
Dim file_path As String
Dim usedrows As Integer
Dim arr_filter As Variant
arr_filter = Array("status", "Completed", "Pending")
file_path = "C:\Users\12078\Desktop\UIPATH_test\filter_data\data_need_to_be_filtered.xlsx"
Set wb_data = checkAndAttachWorkbook(file_path)
Set ws_data = wb_data.Worksheets("data")
wb_data.Worksheets.Add(After:=Worksheets("data")).Name = "Filted_data"
Set ws_filted = wb_data.Worksheets("Filted_data")
usedrows = getLastValidRow(ws_data, "B")
ws_data.Range("$A$1:$D$" & usedrows).AutoFilter Field:=2, Criteria1:=arr_filter, Operator:=xlFilterValues
'Copy column A,B,D to new sheet:
'ws_data.Range("A1:D" & usedrows).SpecialCells(xlCellTypeVisible).Copy ws_filted.Range("A1")
'Copy column A,B,D to new sheet:
Union(ws_data.Range("A1:B" & usedrows), ws_data.Range("D1:D" & usedrows)).Copy ws_filted.Range("A1")
ws_filted.Cells.WrapText = False
ws_filted.Columns("A:AF").AutoFit
checkAndCloseWorkbook file_path, True
Exit Sub
errorhandling:
checkAndCloseWorkbook file_path, False
End Sub
'辅助函数:
'Get last row of Column N in a Worksheet
Function getLastValidRow(in_ws As Worksheet, in_col As String)
getLastValidRow = in_ws.Cells(in_ws.Rows.count, in_col).End(xlUp).Row
End Function
Function checkAndAttachWorkbook(in_wb_path As String) As Workbook
Dim wb As Workbook
Dim mywb As String
mywb = in_wb_path
For Each wb In Workbooks
If LCase(wb.FullName) = LCase(mywb) Then
Set checkAndAttachWorkbook = wb
Exit Function
End If
Next
Set wb = Workbooks.Open(in_wb_path, UpdateLinks:=0)
Set checkAndAttachWorkbook = wb
End Function
Function checkAndCloseWorkbook(in_wb_path As String, in_saved As Boolean)
Dim wb As Workbook
Dim mywb As String
mywb = in_wb_path
For Each wb In Workbooks
If LCase(wb.FullName) = LCase(mywb) Then
wb.Close Savechanges:=in_saved
Exit Function
End If
Next
End Function