We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pivot - Get data from existing sheet

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)

@sitizalekoh,

Thanks for the sample code segment and screenshot.

Please provide us your template file and output file via Aspose.Cells APIs, please zip the files prior attaching. Do you need to extract/copy your desired range of data from PivotTable report, please elaborate? Also, provide your expected data sheet (you may accomplish the task manually in MS Excel and provide the template Excel file). We will check on how to do it via Aspose.Cells APIs.