在进行数据处理的过程中,我们经常会遇到制作数据透视表的需求,那么今天就来谈谈,如何在uipath中制作数据透视表。
首先呢,我们这边主要讲的是Uipath2019,在新版的Uipath中,其实数据透视表的功能已经比较全面了,但是Uipath2019中,数据透视表的功能还比较不尽人意,他只能创建数据透视表,接下来的操作,比如说把某个数据字段放在行或者列里面,或者设置值为求和项计数项什么的,都只能进行人工操作了。我来做个示例吧。
首先,我们先利用uipath的自带控件Create Pivot Table,来制作数据透视表。
我们可以看到,他这个属性都是提前定死的,我们没办法在流程中对其进行配置,比如说我的序号原来想放在行那列,但是控件直接把我的属性定死了,我根本没办法修改,所以说这个控件使用起来并不方便,很多人都采用uipath中的筛选分类来绕过这个步骤,直接获得结果表。但是这种操作再怎么样也都是掩耳盗铃,如果客户确实需求要一张数据透视表或者说,用筛选很难绕过亦或是想节约开发步骤,那就得实实在在的把数据透视表做出来。
接下来,我将说明,如何在uipath中做出数据透视表。
由于uipath中并没有其他的相关控件,因此,我们就需要自己写代码来完成这个功能。这边,我们需要使用invoke code,并在里面嵌入VB代码。
Dim xlapp As Microsoft.Office.Interop.Excel.Application
Dim xlbook As Microsoft.Office.Interop.Excel.Workbook
Dim xlsheet As Microsoft.Office.Interop.Excel.Worksheet
Dim pivottablerange As Microsoft.Office.Interop.Excel.Range
Dim pivottable As Microsoft.Office.Interop.Excel.PivotTable
Dim sheetexists As Boolean = False
Dim pivotField As Microsoft.Office.Interop.Excel.PivotField
Dim cellrange As Object
'参数:
'1.path:需要进行数据透视的excel文件路径。 <一个>
'2.sheets:需要进行数据透视的表。 <一个>
'3.sheet_range:需要进行数据透视的范围。 <一个>
'4.Pivot_table_sheet_name:数据透视表所存放的sheet名称 <一个>
'5.Pivot_table_range:数据透视表存放的起始位置 <一个>
'6.Pivot_table_name:数据透视表的名称 <一个>
xlapp = New Microsoft.Office.Interop.Excel.Application()
'创建excel会话
xlbook = xlapp.Workbooks.Open(path)
'1.path 确定需要进行数据透视的excel的路径
xlsheet = CType(xlbook.Worksheets(sheets), Worksheet)
'2.sheets 确定需要进行数据透视的表
cellrange = xlsheet.Range(sheet_range)
'3.sheet_range 确定需要进行数据透视的范围
For Each sheet As Microsoft.Office.Interop.Excel.Worksheet In xlbook.Worksheets
'遍历excel中的每个sheet,查找是否有名字为《这里是你想将数据透视表存放的表的名字》的sheet,如果没有就创建
If sheet.Name = Pivot_table_sheet_name Then
sheetexists = True
Exit For
End If
Next
If Not sheetexists Then
Dim sheet4 As Microsoft.Office.Interop.Excel.Worksheet = CType(xlbook.Worksheets.Add(), Worksheet)
sheet4.Name = Pivot_table_sheet_name
'4.Pivot_table_sheet_name 这里可以设置你需要将数据透视表存放的sheet名
End If
Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlbook.Worksheets(Pivot_table_sheet_name), Microsoft.Office.Interop.Excel.Worksheet)
'确定数据透视表存放的sheet表
pivottablerange = CType(xlWorksheet.Range(Pivot_table_range), Microsoft.Office.Interop.Excel.Range)
'5.Pivot_table_range 确定数据透视表的起始范围
xlbook.PivotTableWizard(SourceType:=Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, SourceData:=cellrange, TableDestination:=pivottablerange, TableName:=Pivot_table_name)
'6.Pivot_table_name:数据透视表的名称 这里创建了数据透视表,并且规定了一些透视表的参数
xlWorksheet = CType(xlbook.Worksheets(Pivot_table_sheet_name), Microsoft.Office.Interop.Excel.Worksheet)
pivottable = Nothing
If xlWorksheet IsNot Nothing Then
pivottable = TryCast(xlWorksheet.PivotTables(Pivot_table_name), Microsoft.Office.Interop.Excel.PivotTable)
End If
If pivottable IsNot Nothing Then
End If
If pivottable IsNot Nothing Then
'设置数据透视表字段所在的区域。
'筛选:pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlPageField
'列:pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField
'行:pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
'值:datafield1.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
pivotField = CType(pivottable.PivotFields("姓名"), PivotField)
pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
pivotField = CType(pivottable.PivotFields("线别"), PivotField)
pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
pivotField = CType(pivottable.PivotFields("日期"), PivotField)
pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
Dim datafield1 As Microsoft.Office.Interop.Excel.PivotField = CType(pivottable.PivotFields("计件工资"), PivotField)
datafield1.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
datafield1.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
'设置为求和项。注意,在数据透视表中,值可以是计数项,求和项等等,你按照需求即可,这边代码会自动提醒。
Dim datafield2 As Microsoft.Office.Interop.Excel.PivotField = CType(pivottable.PivotFields("异常工时"), PivotField)
datafield2.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
datafield2.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
Dim datafield3 As Microsoft.Office.Interop.Excel.PivotField = CType(pivottable.PivotFields("出勤工时"), PivotField)
datafield3.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
datafield3.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
'=================================Σ数值区域顶部======================================================================================
Dim rowFields As Microsoft.Office.Interop.Excel.PivotFields = DirectCast(pivottable.RowFields, Microsoft.Office.Interop.Excel.PivotFields)
Dim lastrowfieldindex As Integer = rowFields.Count
Dim rowfield As PivotField = Nothing
If lastrowfieldindex >= 1 Then
rowfield = CType(pivottable.RowFields(lastrowfieldindex), PivotField)
End If
If rowfield IsNot Nothing Then
rowfield.Orientation = XlPivotFieldOrientation.xlColumnField
'以上几行,将行里面最后一行自动生成的Σ数值给移动到列模块,各位可以按照各自需求来选取,区域已经被划分出,不需要的可以直接注释掉。
Else
Console.WriteLine("数据透视表中没有行字段。")
End If
'================================Σ数值区域底部=======================================================================================
pivotField = DirectCast(pivottable.PivotFields("姓名"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
pivotField = DirectCast(pivottable.PivotFields("线别"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
pivotField = DirectCast(pivottable.PivotFields("日期"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
pivotField = DirectCast(pivottable.PivotFields("计件工资"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
pivotField = DirectCast(pivottable.PivotFields("异常工时"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
pivotField = DirectCast(pivottable.PivotFields("出勤工时"), Microsoft.Office.Interop.Excel.PivotField)
If pivotField IsNot Nothing Then
pivotField.Subtotals = {False, False, False, False, False, False, False, False, False, False, False, False}
End If
'上面这些设置主要是把什么求和啊汇总啥的功能给他关掉,各位可以按照自己的需求来操作
pivottable.TableStyle2 = "pivotstylemedium9"
Console.Write("succeed to create pivottable.")
Else
Console.Write("failed to create pivottable.")
End If
xlbook.Save()
xlbook.Close()
xlapp.Quit()
For Each proc As Process In Process.GetProcessesByName("excel")
proc.Kill()
Next
这边我还要补充一点,有些人Imports Microsoft.Office.Interop.Excel并没有提示,那是因为还没添加引用,添加引用步骤如下。
有关的参数以及代码说明已经写的非常详细了,我这边就不进行赘述了,这边我就展示一个成果。
如图,我们可以按照各自的需求在代码中进行设置。至于参数传入,我也在这边给各位展示一下。
然后就是我们这边要导入一个依赖Microsoft.Office.Interop.Excel,如图。
接下来就自己去试试吧。