Can we create a single pivot table from mutiple ranges?

Aspose.Cells Product Family
1

Excel lets you specify multiple ranges (Sheet1!A1:C8, Sheet2!A1:C8, Sheet3!A1:C8) to have included in the same pivot table.

Is this possible using Cells? I see that pivotTables.add accepts a sourceData string, but would it accept more than one range as shown above?

2

Hi drider,

Please try this fix.

3

Hi ,

The attached zip is Java-Doc for Aspose.Cells for Java.

Please use PivotTables.add(String[] sourceData, boolean isAutoPage, PivotPageFields pageFields,
String destCellName, String tableName) method.

The method works as you do in the PivotTable and PivotChart Wizard in Ms Excel.

In the step 1, you select "Mulitple consolidation ranges" as the data that you want to analyze.

In the step 2, if you select "Create a single page field for me", you should set the param isAutoPage is true.We will auto create a page field and the page pageFields will be ignored.

If you select another option, you should set the param isAutoPage is false and init the param pageFields.

The class PageFields works as the step3 , you will be asked to add the page fields and choose the item of the page field to identify the data rage. So you should call pageFields.addPageField(String[]) to add the page field. Then you should call addIdentify() method to add relation with the item in the page field and the data range.

4

Hi,

I have quite a huge amount of records which could reach to 100,000 and I am splitting these records to multiple sheets having each sheet to contains 10,000 records. May I know how can I read the records in these sheets to create a single pivot tables in C#?

Thanks.

5

Hi,

Well, although you may try to use the code, e.g
int index = pivotTables.Add(new string[] {"=Sheet1!A1:C10000", “=Sheet2!A1:C10000…”}, true, null, “A3”, “PivotTable1”);
But this will not suit your requirements. I think MS Excel 2003 cannot support your need if your data source exceeds 65536 rows/records. Moreover, if you use consolidation ranges option, the pivot fields would only be with Page 1, Pag2… and with Row,Column,Data fields or other attributes and we think it will not be the way you want to create your desired pivot table report.

So, we suggest you to use and create xlsx file format (MS Excel 2007) that does not have 65536 rows/records restriction. You should export/fill all records (e.g 100,000 records) in a single worksheet and create the pivot table based on the single range.


Thank you.

6

Hi,

Thanks. We have manage to stream down the number of records.

But I have another issue, that is the data column suppose to show a character but instead it is showing number. May I know how can I make it to show character?

Thanks.

7

Hi,

Well, you may use PivotField.Number or PivotField.NumberFormat attributes to show the data in string/text format, e.g

//…
int dataFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
PivotField df = pivotTable.DataFields[dataFieldIndex];
df.Number = 49;
//Or
[//df.NumberFormat ](//df.NumberFormat) = “@”;

Thank you.

8

Hi,

I have tried but it does not seems to work.

Pls advice. Thanks.

9

Hi,

Please create a simple console application, zip it and post it here to show the issue, we will check it soon.

Also, please use the latest version v4.8.0.x.

Thank you.

10

Perhaps I shall provide an extract of my code.

My data sheet has this column

cell = cells["N1"];
cell.PutValue("NT");

cell = cells["N2"];
cell.PutValue("1");

cell = cells["N3"];
cell.PutValue("2");

// Pivot table i add these value in column 13 as Data
int dataFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Data, 13);
PivotField df = pivotTable.DataFields[dataFieldIndex];
df.Number = 49;

My output in excel is showing all 1 instead.

11

Hi,

Please use "\"@\"" as the custom number format of the pivot data field.See following codes:

int dataFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Data, 13);
PivotField df = pivotTable.DataFields[dataFieldIndex];
df.NumberFormat= "\"@\"";

12

This code is showing the “@” is the cell instead of value

13

Hi ,

My understanding for the Number and NumberFormat property is to formt the date and number, it does not applies to string value right?

Please correct me if my understanding is wrong.

Thanks.

14

Hi,

Thank you for considering Aspose.

Your understanding is right. Number and NumberFormat properties are used to format the date and numbers and are not applied to the strings. If you still face any problem, please manually create an excel file showing your requirement and post it here. We will check it soon.

Thank You & Best Regards,

15

Hi,

Attached is a sample excel. Refering to the Data worksheet column 13, highlighted in yellow, you can see it is appearing as "1" in the PivotTable worksheet (portion highlighted in yellow).

For your advise.

Thanks.

16

Hi,

Thank you for considering Aspose.

If you use cell.PutValue(“1”), we will set a string value to the cell, so you will see a triangle in the left corner of the cell in MS Excel. If you want to set a numeric value to the cell, please simply use Cell.PutValue(1).

Also, please create your mentioned pivot table in MS Excel and post the template file here. We will check it soon.
BTW, we tried the following codes and it works fine.

internal void TestUserPivot()
{

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\pivotTable_test.xls");//source sheet
Worksheet failuresheet = workbook.Worksheets[1];
PivotTables pivotTables = failuresheet.PivotTables;
int index = pivotTables.Add("=Data!A1:N26", "E3", "PivotTable2");
PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(PivotFieldType.Row, 6);
pivotTable.AddFieldToArea(PivotFieldType.Column, 7);
pivotTable.AddFieldToArea(PivotFieldType.Data, 13);
pivotTable.DataFields[0].NumberFormat = "\"@\"";

workbook.Save(@"F:\FileTemp\dest.xls", FileFormatType.Excel2003);
}

Thank You & Best Regards,

17

Hi,

Thanks. I have attached the sample pivot table in excel in my previous post.

Please check it out.

Regards.

18

Hi,

The following sample code creates the pivot table similar to the pivot table in your your attached file, kindly refer to it.

Sample code:

internal void TestUserPivot()
{

Workbook workbook = new Workbook();
workbook.Open(@“F:\FileTemp\pivotTable_test.xls”);//source sheet

Worksheet failuresheet = workbook.Worksheets[1];

PivotTables pivotTables = failuresheet.PivotTables;

int index = pivotTables.Add("=Data!A1:N26", “E3”, “PivotTable2”);

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
pivotTable.AddFieldToArea(PivotFieldType.Row, 2);
pivotTable.AddFieldToArea(PivotFieldType.Row, 3);
pivotTable.AddFieldToArea(PivotFieldType.Row, 4);


pivotTable.AddFieldToArea(PivotFieldType.Column, 6);
pivotTable.AddFieldToArea(PivotFieldType.Column, 7);
pivotTable.AddFieldToArea(PivotFieldType.Column, 8);
pivotTable.AddFieldToArea(PivotFieldType.Column, 9);

pivotTable.AddFieldToArea(PivotFieldType.Data, 13);



workbook.Save(@“F:\FileTemp\dest.xls”, FileFormatType.Excel2003);

}

Thank you.