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

Free Support Forum - aspose.com

Create a pivot table from multiple data ranges

Hello,

I try to create a pivot table from multiple sheets in Excel (as I have more than 100 000 rows, I have to split them in several sheets).

I tried your recommandation in the following post, but I cannot get it to work:

<A href="</A></P> <P>1) I tried </P><FONT color=#0000ff size=2> <P>string</FONT><FONT size=2>[] sourceDataRanges ={ </FONT><FONT color=#800000 size=2>"DataCreder!A1:BS100", "DataCreder_1!A1:BS100" };

index = pivotTables.Add(sourceDataRanges,false,null, pivotLocation, pivotTableName);

-> it gives the error "Object reference not set to an instance of an object."

I don't need to have a page field corresponding to the sheet so it is ok for me to set 'isAutoPage' to false. But them, how can I set the parameter 'pageFields'?

2) I tried :

string[] sourceDataRanges ={ "DataCreder!A1:BS100", "DataCreder_1!A1:BS100" };

index = pivotTables.Add(sourceDataRanges,true,null, pivotLocation, pivotTableName);

-> the pivot table is created but then I get an error (Index was outside the bounds of the array) in the instruction:

pivotTable.AddFieldToArea(PivotFieldType.Page, dtDataCreder.Columns[field].Ordinal);

I dit not have this error when creating the pivot table with only one source sheet.

How can I solve this ?

Below is my complete code.

Thanks for your help,

Benjamin

Worksheet sheet = workbook.Worksheets[config.Sheet_PivotCreder];

//Adding a PivotTable to the worksheet

PivotTables pivotTables = sheet.PivotTables;

string pivotLocation = sheet.Cells[4, 0].Name;

string pivotTableName = "PivotTableCreder";

int firstRow = 0;

bool withHeaders = true;

int totalRows = dtDataCreder.Rows.Count;

int limit = config.MaxRowsPerSheet;

int nbRowsPerSheet = (withHeaders) ? limit - firstRow - 1 : limit - firstRow;

int remainingRows = totalRows % nbRowsPerSheet;

int nbSheets = (remainingRows == 0) ? totalRows / nbRowsPerSheet : totalRows / nbRowsPerSheet + 1;

int index = 0;

if (nbSheets==1)

{

//One data sheet

Worksheet dataSheet = workbook.Worksheets[config.Sheet_DataCreder];

Range dataCrederRange = dataSheet.Cells.CreateRange(0, 0, dtDataCreder.Rows.Count, dtDataCreder.Columns.Count);

string sourceDataRange = "=" + config.Sheet_DataCreder + "!A1:" + dataSheet.Cells[dataCrederRange.RowCount, dataCrederRange.ColumnCount - 1].Name;

index = pivotTables.Add(sourceDataRange, pivotLocation, pivotTableName);

}

else

{

//Several data sheets

string[] sourceDataRanges ={ "DataCreder!A1:BS100", "DataCreder_1!A1:BS100" };

//string[] sourceDataRanges ={ "=DataCreder!1:65536", "DataCreder_1!1:65536" };

//string sourceDataRanges ="{ DataCreder!!1:65536, DataCreder_1!!1:65536 }";

index = pivotTables.Add(sourceDataRanges,true,null, pivotLocation, pivotTableName);

}

//Accessing the instance of the newly added PivotTable

PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = false;

pivotTable.ColumnGrand = false;

string[] pageFieldList = new string[] { "ShiftedType", "Restruct", "Instrument" };

foreach (string field in pageFieldList)

{

pivotTable.AddFieldToArea(PivotFieldType.Page, dtDataCreder.Columns[field].Ordinal);

}

string[] rowFieldList = new string[] { "ShiftedCurve" };

foreach (string field in rowFieldList)

{

int i = pivotTable.AddFieldToArea(PivotFieldType.Row, dtDataCreder.Columns[field].Ordinal);

}

//Style styleNum = workbook.Styles(workbook.Styles.Add());

//styleNum.Custom = "### ### ##0";

//StyleFlag styleFlag = new StyleFlag();

//styleFlag.NumberFormat = true;

foreach (string maturity in maturityTab)

{

int i = pivotTable.AddFieldToArea(PivotFieldType.Data, dtDataCreder.Columns[maturity].Ordinal);

pivotTable.DataFields[i].DisplayName = dtDataCreder.Columns[maturity].ColumnName;

pivotTable.DataFields[i].Number = 3;

}

string[] dataFieldList = new string[] { "Hedge", "Hedge5Yequiv", "CDSSpread_5Y", "Spread*2", "Spread/2", "StressTestDefault", "SensiRecovPostDefault", "Recovery", "CDSRecovery", "VolSpread+10%", "Convexity" };

int indexHedge5Yequiv = 0;

foreach (string field in dataFieldList)

{

int i = pivotTable.AddFieldToArea(PivotFieldType.Data, dtDataCreder.Columns[field].Ordinal);

pivotTable.DataFields[i].DisplayName = dtDataCreder.Columns[field].ColumnName;

if (field != "CDSSpread_5Y" )

{

pivotTable.DataFields[i].Number = 3;

}

if ((field == "CDSSpread_5Y" || field == "CDSRecovery"))

{

pivotTable.DataFields[i].Function = ConsolidationFunction.Min;

}

if (field == "Hedge5Yequiv" )

{

indexHedge5Yequiv = i;

}

}

pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

pivotTable.ColumnGrand = true;

PivotField rowPivotField = pivotTable.RowFields[0];

rowPivotField.IsAutoSort = true;

rowPivotField.IsAscendSort = true;

rowPivotField.AutoSortField = indexHedge5Yequiv;

pivotTable.AutoFormatType = PivotTableAutoFormatType.Classic;

Hi Benjamin,

Please try this with Excel, you will find Excel only create 4 base pivot fields(1 page pivot field, 1 row pivot field, 1 column pivot field, 1 data pivot field. We work as Excel. If you call

index = pivotTables.Add(sourceDataRanges,true,null, pivotLocation, pivotTableName); you only can get the list fields above.

Thank you Warren. Then I don't know how to create a pivot table on more than 65536 rows. I made a previous post for creating a pivot table with an external csv source and I understood it is not supported currently in Aspose. I cannot either create a pivot table with many columns on several ranges. Is there a workaround ?

Regards,

Benjamin

Hi Warren,

I really need to find a solution to create a pivot table on more than 65536 rows. My client is waiting for this. Is there a way to do it?

Regards,

Benjamin

Hi Benjamin,

We will get back to you soon.

Thank you.

Hi Benjamin,

Sorry,we do not support this feature. We will look into this feature soon. Thanks for your patience.

Hello,

Do we have any solution for this option?

Thanks
Kiran

@kiran_pasham_splashbi_com,

You may try using latest version of Aspose.Cells and create your desired pivot table in XLSX file format, it should work for your needs. If you still find any issue, kindly do provide your sample code (runnable), sample files (input file (if any) and output file, etc.) and your expected output file, we will check it soon. You may create your expected output file in MS Excel manually.

PS. please zip the files prior attaching.