Defining range for pivot table

Aspose.Cells Product Family
1

Hi there.

I create a workheet reading data from an ms-access database with the following code.

I have tried to add a pivot table once the report is created but I keep hitting a dead end. I also need to define the range for the data which must be variable dependant on the number of rows in the sheet generated by the extracted data. ie I cannot have a set number of rows, these must be variable.

Could you help me please

<script runat="server">

Dim instance As OleDbCommand

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

'Instantiate an instance of license and set the license file through its path

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Cells.lic")

'Set the data source for the designer spreadsheet

Dim con As New OleDbConnection

Dim cmd As New OleDbCommand

Dim da As New OleDbDataAdapter

Dim ds As New DataSet

Dim dt As New DataTable

Dim wd As WorkbookDesigner = New WorkbookDesigner()

con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/fpdb/passliabcapture.mdb"))

con.Open()

cmd = New OleDbCommand("SELECT * FROM [datadump] Where Comment = @comment AND [monthnumber] >= @monthnumber AND [yearno] >= @yearno AND [monthnumber] <= @monthto AND [yearno] <= @yearto", con)

Dim broker As String = CType(FindControl("brokerone"), DropDownList).Text

Dim monthnumber As String = CType(FindControl("monthnumber"), DropDownList).Text

Dim yearno As String = CType(FindControl("yearno"), DropDownList).Text

Dim monthto As String = CType(FindControl("monthto"), DropDownList).Text

Dim yearto As String = CType(FindControl("yearto"), DropDownList).Text

cmd.Parameters.Add(New OleDbParameter("@comment", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@monthnumber", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@yearno", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@monthto", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@yearto", OleDbType.VarChar, 200))

cmd.Parameters("@comment").Value = CStr(broker)

cmd.Parameters("@monthnumber").Value = CStr(monthnumber)

cmd.Parameters("@yearno").Value = CStr(yearno)

cmd.Parameters("@monthto").Value = CStr(monthto)

cmd.Parameters("@yearto").Value = CStr(yearto)

da.SelectCommand = cmd

ds = New DataSet()

da.Fill(ds, "datadump")

wd = New WorkbookDesigner()

wd.Open(Server.MapPath("~/reports/brokeronlyrecon.xls"))

wd.SetDataSource(ds)

wd.Process(False)

wd.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInBrowser, Response)

End Sub

Protected Sub EmployeeFormView_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewPageEventArgs)

End Sub

Protected Sub broker_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)

End Sub

</script>

2

Hi,

Well, I think you may use Cells.MaxDataRow and Cells.MaxDataColumn properties once you have processed the smart markers and want to define the range for pivot table report.
e.g

wd = New WorkbookDesigner()

wd.Open(Server.MapPath("~/reports/brokeronlyrecon.xls"))

wd.SetDataSource(ds)

wd.Process(False)

'Getting the sheet
Worksheet sheet = wd.Worksheets(0)

Dim targetRange As Range = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)

...............

For "I have tried to add a pivot table once the report is created but I keep hitting a dead end. "

Please give us more details, preferably a console sample application, zip it and post it here to show the issue, we will check it soon.

Thank you.


3

Hi Amjad, Thanks for the quick reply.

I now understand cells.Maxdata... - thank you.

When I use the two lines you provide I get the following errors

Worksheet(sheet = wd.Worksheets(0))

The system keep seperating Worksheet sheet to worksheet(sheet...

If I Dim the statement

Dim Worksheet(sheet = wd.Worksheets(0))

I get wd.worksheet is not a member of aspose.cells workbookdesigner

and 'sheet' is not declared.

Could you help

Thank you

Jonathan

4

Hi,



Sorry for my mistake,



Please try:

'Getting the sheet

Dim sheet As Worksheet = wd.Workbook.Worksheets(0)





Thank you.

5

Hi,

Please check your range you are creating as it has some invalid arguments and hence invalid:
'Note: the first two argument in CreateRange method are starting row index and column index.
'So you should change it accordingly:
Dim targetRange As Range = sheet.Cells.CreateRange(A10, Y12, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1)

This line is also invalid.

Dim index As Integer = pivotTables.Add("targetRange", "A1", "StatsTable")

Please refer to my sample code and it works absolutely fine, you may create your own code for your need too taking help from my code.

Sample code: (you may simply run that code)

'Instantiating an Workbook object
Dim workbook As New Workbook()
'Obtaining the reference of the newly added worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = sheet.Cells
'Setting the value to the cells
Dim cell As Aspose.Cells.Cell = cells("A1")
cell.PutValue("Sport")
cell = cells("B1")
cell.PutValue("Quarter")
cell = cells("C1")
cell.PutValue("Sales")
cell = cells("A2")
cell.PutValue("Golf")
cell = cells("A3")
cell.PutValue("Golf")
cell = cells("A4")
cell.PutValue("Tennis")
cell = cells("A5")
cell.PutValue("Tennis")
cell = cells("A6")
cell.PutValue("Tennis")
cell = cells("A7")
cell.PutValue("Tennis")
cell = cells("A8")
cell.PutValue("Golf")
cell = cells("B2")
cell.PutValue("Qtr3")
cell = cells("B3")
cell.PutValue("Qtr4")
cell = cells("B4")
cell.PutValue("Qtr3")
cell = cells("B5")
cell.PutValue("Qtr4")
cell = cells("B6")
cell.PutValue("Qtr3")
cell = cells("B7")
cell.PutValue("Qtr4")
cell = cells("B8")
cell.PutValue("Qtr3")
cell = cells("C2")
cell.PutValue(1500)
cell = cells("C3")
cell.PutValue(2000)
cell = cells("C4")
cell.PutValue(600)
cell = cells("C5")
cell.PutValue(1500)
cell = cells("C6")
cell.PutValue(4070)
cell = cells("C7")
cell.PutValue(5000)
cell = cells("C8")
cell.PutValue(6430)

Dim pivotTables As PivotTables = sheet.PivotTables
'Adding a PivotTable to the worksheet

Dim targetRange As Range = cells.CreateRange(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1)
targetRange.Name = "MyRange"
Dim index As Integer = pivotTables.Add(targetRange.Name, "E3", "PivotTable2")
'Accessing the instance of the newly added PivotTable
Dim pivotTable As PivotTable = pivotTables(index)
'Unshowing grand totals for rows.
pivotTable.RowGrand = False
'Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0)
'Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1)
'Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2)
Dim pivotFields As PivotFields = pivotTable.BaseFields

workbook.Save("f:\test\output_file.xls")



Kindly let us know if you still find any issue.

Thank you.


6

Hi Armjad

Sorry to keep on. I have applied the changes and now get the following error

Server Error in '/AJAXEnabledWebSite1' Application.

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Source Error: 

Line 56:         
Line 57:         Dim wb As Workbook = New Workbook()
Line 58:         Dim sheet As Worksheet = wd.Workbook.Worksheets(2)
Line 59:         Dim cells As Cells = sheet.Cells
Line 60:         Dim cell As Aspose.Cells.Cell = cells("A1")

My code is

<%@ Page Language="VB" %>

<%@ Import Namespace="Microsoft.VisualBasic" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="Aspose.Cells" %>

<script runat="server">

Dim instance As OleDbCommand

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)

'Instantiate an instance of license and set the license file through its path

Dim license As Aspose.Cells.License = New Aspose.Cells.License()

license.SetLicense("Aspose.Cells.lic")

'Set the data source for the designer spreadsheet

Dim con As New OleDbConnection

Dim cmd As New OleDbCommand

Dim da As New OleDbDataAdapter

Dim ds As New DataSet

Dim dt As New DataTable

Dim wd As WorkbookDesigner = New WorkbookDesigner()

con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/fpdb/passliabcapture.mdb"))

con.Open()

cmd = New OleDbCommand("SELECT * FROM [datadump] Where Comment = @comment AND [monthnumber] >= @monthnumber AND [yearno] >= @yearno AND [monthnumber] <= @monthto AND [yearno] <= @yearto", con)

Dim broker As String = CType(FindControl("brokerone"), DropDownList).Text

Dim monthnumber As String = CType(FindControl("monthnumber"), DropDownList).Text

Dim yearno As String = CType(FindControl("yearno"), DropDownList).Text

Dim monthto As String = CType(FindControl("monthto"), DropDownList).Text

Dim yearto As String = CType(FindControl("yearto"), DropDownList).Text

cmd.Parameters.Add(New OleDbParameter("@comment", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@monthnumber", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@yearno", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@monthto", OleDbType.VarChar, 200))

cmd.Parameters.Add(New OleDbParameter("@yearto", OleDbType.VarChar, 200))

cmd.Parameters("@comment").Value = CStr(broker)

cmd.Parameters("@monthnumber").Value = CStr(monthnumber)

cmd.Parameters("@yearno").Value = CStr(yearno)

cmd.Parameters("@monthto").Value = CStr(monthto)

cmd.Parameters("@yearto").Value = CStr(yearto)

da.SelectCommand = cmd

ds = New DataSet()

da.Fill(ds, "datadump")

wd = New WorkbookDesigner()

wd.Open(Server.MapPath("~/reports/brokeronlyrecon.xls"))

wd.SetDataSource(ds)

wd.Process(False)

Dim wb As Workbook = New Workbook()

Dim sheet As Worksheet = wd.Workbook.Worksheets(1)

Dim cells As Cells = sheet.Cells

Dim cell As Aspose.Cells.Cell = cells("A1")

'Getting the sheet

Dim pivotTables As PivotTables = sheet.PivotTables

'Adding a PivotTable to the worksheet

Dim targetRange As Range = cells.CreateRange(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1)

targetRange.Name = "MyRange"

Dim index As Integer = pivotTables.Add(targetRange.Name, "E3", "PivotTable2")

'Accessing the instance of the newly added PivotTable

Dim pivotTable As PivotTable = pivotTables(index)

'Unshowing grand totals for rows.

pivotTable.RowGrand = False

'Draging the first field to the row area.

pivotTable.AddFieldToArea(PivotFieldType.Row, 0)

'Draging the second field to the column area.

pivotTable.AddFieldToArea(PivotFieldType.Column, 1)

'Draging the third field to the data area.

pivotTable.AddFieldToArea(PivotFieldType.Data, 2)

Dim pivotFields As PivotFields = pivotTable.BaseFields

wd.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInBrowser, Response)

End Sub

7

Sorry that was the wrong error - this is the correct one

Server Error in '/AJAXEnabledWebSite1' Application.

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error: 

Line 66:         Dim targetRange As Range = cells.CreateRange(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1)
Line 67:         targetRange.Name = "MyRange"
Line 68:         Dim index As Integer = pivotTables.Add(targetRange.Name, "E3", "PivotTable2")
Line 69: 
Line 70:         'Accessing the instance of the newly added PivotTable
8

Hi,

I think you might be using some older version in which set named ranges as pivot table source was not supported. Please try the attached version, it works fine.

If you want to stick with the older version you may change your code accordingly:

 Dim index As Integer = pivotTables.Add("=A1:C8", “E3”, “PivotTable2”)


Thank you.
9

Using the old version I get the following error

Invalid cell name

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Invalid cell name

Source Error: 

Line 67:         'targetRange.Name = "MyRange1"
Line 68:         'Dim index As Integer = pivotTables.Add(targetRange.Name, "A2", "PivotTable2")
Line 69:         Dim index As Integer = pivotTables.Add("=A10:U12", "Statistics!B3", "PivotTable2")
Line 70:         'Accessing the instance of the newly added PivotTable
Line 71:         Dim pivotTable As PivotTable = pivotTables(index)

10

Hi now I get the following error

Invalid cell name

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Invalid cell name

Source Error: 

Line 67:         'targetRange.Name = "MyRange1"
Line 68:         'Dim index As Integer = pivotTables.Add(targetRange.Name, "A2", "PivotTable2")
Line 69:         Dim index As Integer = pivotTables.Add("=A10:U12", "Statistics!B3", "PivotTable2")
Line 70:         'Accessing the instance of the newly added PivotTable
Line 71:         Dim pivotTable As PivotTable = pivotTables(index)
11

Hi,

There is a problem in your code:

Dim index As Integer = pivotTables.Add("=A10:U12", “Statistics!B3”, “PivotTable2”)

Please change it to:
Dim index As Integer = pivotTables.Add("=A10:U12", “B3”, “PivotTable2”)


Thank you.
12

Hi I still get the same error

The PivotTable field name is invalid.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Aspose.Cells.CellsException: The PivotTable field name is invalid.

Source Error: 

Line 67:         'targetRange.Name = "MyRange1"
Line 68:         'Dim index As Integer = pivotTables.Add(targetRange.Name, "A2", "PivotTable2")
Line 69:         Dim index As Integer = pivotTables.Add("=A10:U12", "B3", "PivotTable2")
Line 70:         'Accessing the instance of the newly added PivotTable
Line 71:         Dim pivotTable As PivotTable = pivotTables(index)

Thanks

Jonathan

13

Hi,

Well, there might be some issue with your source pivot table’s range (based on which you are trying to add pivot table) as I am not sure about it.

We appreciate if you could create a simple console application and provide us the code (as I have written in one of my previous posts) and template excel file or other files etc, zip the project and post it to reproduce the issue, we will check it soon.

Thank you.