Defining Pivot table range after data imported from datareader

Aspose.Cells Product Family
1

Hi...I've been working on this for a couple of hours and have read everything I could find on your site for an answer before I sent this post for help...

I'm using "Cells.ImportFromDataReader" to add data to a worksheet.

Now I want to create a PivotTable from that newly entered data but can't figure out how to define the range based on different number of records, fields, etc. of the original datasource...is there a way to name a range of the newly imported datareader and then call to build the pivot table in that fashion?

Here's the meat of what I have so far and can't go any further...

With pWS
.Cells.ImportFromDataReader(pdr, True, pnListStartRow, pnListStartCol, True)
.TabColor = Color.Blue

Dim lsStartCellCol As String = a.XL_GetColumnLetter(pnListStartCol + 1) & (pnListStartRow + 1).ToString

Dim lsEndCellCol As String = a.XL_GetColumnLetter(pnListStartCol + lnNumFields + 1) & (pnListStartRow + 1).ToString

.AutoFilter.Range = lsStartCellCol & ":" & lsEndCellCol

For i As Integer = pnListStartCol + 1 To lnNumFields + pnListStartCol + 1

.AutoFitColumn(i, pnListStartRow + 2, pnListStartRow + 10)

.Cells(pnListStartRow, i).Style.BackgroundColor = Color.Cornsilk

Next

'-- HERE'S WHERE I NEED TO CREATE THE PIVOT TABLE BASED ON THE import above...

End With

THANKS! I'm STUCK...I'm sure it's simple...

2

Hi,

Please check the doc in http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/how-to-create-a-pivottable.html

3

simon…I know how to create a pivot table if I know how many rows exist and can specifically define the range of cells that contain the data, e.g., the “=A1:C8” param in the online help that you linked me to as in:

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

what I don't know is how to establish the pivottable range when the data has just been imported via

ImportFromDataReader

and I don't know how many records are in the DataReader, so I can't create the EndCell.

Does that make sense? I don't know the range of data because it's manipulated via "ImportFromDataReader"

4

Hi,

Cells.ImportFromDataReader method will return Total number of rows imported.Such as

int rowCount = cells.ImportFromDataReader();

5

Ok...that's resolved (thanks) and now 2 others that I "think" there's an issue with...

1.**CRITICAL** When I try to change how to summarize the value field, e.g., count versus automatic versus sum, it ALWAYS dies using this code: (I've attached a file with a Pivot wksht to show what I'm looking for)

lnPageFieldNum = lpt.AddFieldToArea(PivotFieldType.Data, Me.getPivotTableDataColumnIndex("NumBeds"))

With .DataFields(lnPageFieldNum)

.DisplayName = "Num Beds"
.SetSubtotals(PivotFieldSubtotalType.Count, True)
'.GetSubtotals(PivotFieldSubtotalType.Count)
'.DataDisplayFormat = PivotFieldDataDisplayFormat.Index

End With

If I comment out the ".SetSubtotals" it works like a champ, although without the correct aggregation, e.g., Count, Min, Max, etc.

How do I get the DataFields value to COUNT or MIN or MAX?

2. Pivot table location Row location doesn't seem to change.

When I add a PT using:

Dim lnPTIndex As Integer = lPTs.Add(lsPTRange, 3, 0, "PivotAnalysisTable")
** OR ** Try to move its location using

.Move(3, 3)

It always seems to ignore the row-level movement (although I can get it located in different columns).

THANKS IN ADVANCE...MAN THIS IS A GREAT PRODUCT! Love it! P.

6

Hi,

Could you try the attached version as I tested it works fine with no issues.

Thank you.

7

Same thing...Here's the exception I receive:

Aspose.Cells.CellsException: Subtotals are only valid for nondata fields

at Aspose.Cells.PivotField.SetSubtotals(PivotFieldSubtotalType subtotalType, Boolean shown)

Is the subtotals be called from either column or row? I've tried to do it that way as well just about every way I could think of, mostly using the DataFields.Position value to call it from either Column or Row, and I can't seem to make it work...

Lastly, the "Location" issue I had kept messing me up, becasue the cellName where it would be placed referred to the actual pivot data table, which is below the Page Filters...I thought it would have referred to the entire pivot "thing".

Speaking of PageField filters, is there a way to programmatically sort the display order of these things, e.g., alphabetically?

Thanks and that'd be super helpful if you could give me some direction on the SetSubtotals thing...I'm stuck and have exhausted all my best guesses at this point...

Again, this product is GREAT! P.

8

Hi,

You could not set the subtotals for PivotTable.DataField. You can check it in MS Excel.

You can use PivotField.AutoSortField, PivotField.IsAscendSort, PivotFiled.IsAutoSort to set the display order. We do not support munual set the display order now.

Please post a sample file which contains the pivot table which you want to create. We will check how to create it with Aspose.Cells API.

9

Here's the XL chart (same one uploaded earlier) that shows a pretty simple highlight to count or min, max, etc.

I'm stuck on this one thing for this spreadsheet. Thanks. P.

10

Hi,

Please use "pivotTable.DataFields[0].Function = ConsolidationFunction.Count;" to set the the function used to summarize the PivotTable data field.

11

That was it! Fantastic…guys, this is a great product and it’s made far superior based on the quick help/response that support provides. Good job and keep up the great work!