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;