Hi. I have a problem with Aspose .net for pivot which is I try to get the data from existing worksheet. The examples in google mostly create the new data into worksheet and pivot. Different to my case which is I want to get the data from existing sheet and pivot it. In my sheet I have range from A1:AJ25.
But I only want 5 column to pivot, for example, column M(project), AJ(Aging date), G(Pending Number), S(Pending Quantity), and X(pending amount). So far my code is like this,
Blockquote
Public Sub testtest()
'open the existing file
Dim stream As FileStream = New FileStream(“C:\Users\SitiZalekoh\Desktop\PIVOT\4.xlsx”, FileMode.Open)
Dim loadOptions As Aspose.Cells.LoadOptions = New LoadOptions(LoadFormat.Xlsx)
Dim wb As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(stream, loadOptions)
Dim worksheet As Aspose.Cells.Worksheet = wb.Worksheets(0)
'create a new sheet for Pivot Table
Dim sheet2 As Aspose.Cells.Worksheet = wb.Worksheets(wb.Worksheets.Add)
' Naming the sheet
sheet2.Name = "PivotTable"
Dim pivotTables As Aspose.Cells.Pivot.PivotTableCollection = sheet2.PivotTables
Dim index As Integer = pivotTables.Add("A1:AJ25", "A3", "PivotTable1")
Dim pivotTable As Aspose.Cells.Pivot.PivotTable = pivotTables(index)
' Add row field
pivotTable.AddFieldToArea(PivotFieldType.Row, "Project")
pivotTable.AddFieldToArea(PivotFieldType.Row, "Aging Date")
pivotTable.AddFieldToArea(PivotFieldType.Row, "Del No")
pivotTable.AddFieldToArea(PivotFieldType.Row, "Ship Qty")
pivotTable.AddFieldToArea(PivotFieldType.Row, "Ext Cost")
' Please call the PivotTable.RefreshData() and PivotTable.CalculateData(). Before using PivotItem.Position,
' PivotItem.PositionInSameParentNode and PivotItem.Move(int count, bool isSameParent).
pivotTable.RefreshData()
pivotTable.CalculateData()
End Sub
Blockquote
And the output that I want almost like this,
image.png (91.8 KB)