Using Aspose.Cells for .NET v8.7.1, we are constructing a large PivotTable backed by a large worksheet. We then use PivotTable.GetCellByDisplayName to get references to cells in the PivotTable so we can format them.
When there are more than 256 fields in a given area of the Pivot Table (specifically, we tested the Data area), GetCellByDisplayName returns null for any fields beyond the 256th.
I’ve attached a C# function that demonstrates and reproduces the issue. When executed, the function outputs:
There are 300 fields in the Data area of the pivot field
By index, the field DisplayName at index 0 is ‘Sum of Bids0’. Using this in GetCellByDisplayName returns null? False
By index, the field DisplayName at index 255 is ‘Sum of Bids255’. Using this in GetCellByDisplayName returns null? False
By index, the field DisplayName at index 256 is ‘Sum of Bids256’. Using this in GetCellByDisplayName returns null? True
By index, the field DisplayName at index 299 is ‘Sum of Bids299’. Using this in GetCellByDisplayName returns null? True
If you have any suggested way to work around this issue while you’re investigating, I would really appreciate it. I’ve spent a fair amount of time looking into a workaround myself but am not familiar enough yet with Aspose.Cells to see an obvious solution. This will quickly become a business blocker for us.
Such an issue occurs because of XLS format. When you create Workbook object with its default constructor, then it is created in XLS format. You should create Workbook object in XLSX format and then it will solve your issue.
Thanks for the suggestions but that not does change the behavior. I tried updating my repro code and got the same problem. And, looking into the code itself, I can see that XLSX is the file format used by the default Workbook constructor, so I believe the two ctor calls in your post are functionally identical.
We have tested this issue with the following sample code using the latest version:Aspose.Cells for .NET (Latest Version) attached with this post. This sample excel file was generated by using your sample code. PivotTable.GetCellByDisplayName returns null for anything over the 256th field.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
CELLSNET-44304 - PivotTable.GetCellByDisplayName returns null for anything over the 256th field
I have also shown the console output of this sample code for a reference.
C#
Workbook wb = new Workbook(“sample.xlsx”);
Worksheet ws = wb.Worksheets[“Pivot Sheet”];
PivotTable pivotTable = ws.PivotTables[0];
pivotTable.RefreshData();
pivotTable.CalculateData();
var dataFields = pivotTable.Fields(PivotFieldType.Data);
for (int i = 0; i < dataFields.Count; i++)
{
var displayName = dataFields[i].DisplayName;
Cell cell = pivotTable.GetCellByDisplayName(displayName);
if (cell == null)
Debug.WriteLine(displayName + “----Null”);
else
Debug.WriteLine(displayName + “----” + cell.Name);
}
Console Output:
Sum of Bids0----B2
Sum of Bids1----B3
Sum of Bids2----B4
Sum of Bids3----B5
Sum of Bids4----B6
Sum of Bids5----B7
Sum of Bids6----B8
Sum of Bids7----B9
Sum of Bids8----B10
Sum of Bids9----B11
Sum of Bids10----B12
Sum of Bids11----B13
Sum of Bids12----B14
Sum of Bids13----B15
Sum of Bids14----B16
Sum of Bids15----B17
Sum of Bids16----B18
Sum of Bids17----B19
Sum of Bids18----B20
Sum of Bids19----B21
Sum of Bids20----B22
Sum of Bids21----B23
Sum of Bids22----B24
Sum of Bids23----B25
Sum of Bids24----B26
Sum of Bids25----B27
Sum of Bids26----B28
Sum of Bids27----B29
Sum of Bids28----B30
Sum of Bids29----B31
Sum of Bids30----B32
Sum of Bids31----B33
Sum of Bids32----B34
Sum of Bids33----B35
Sum of Bids34----B36
Sum of Bids35----B37
Sum of Bids36----B38
Sum of Bids37----B39
Sum of Bids38----B40
Sum of Bids39----B41
Sum of Bids40----B42
Sum of Bids41----B43
Sum of Bids42----B44
Sum of Bids43----B45
Sum of Bids44----B46
Sum of Bids45----B47
Sum of Bids46----B48
Sum of Bids47----B49
Sum of Bids48----B50
Sum of Bids49----B51
Sum of Bids50----B52
Sum of Bids51----B53
Sum of Bids52----B54
Sum of Bids53----B55
Sum of Bids54----B56
Sum of Bids55----B57
Sum of Bids56----B58
Sum of Bids57----B59
Sum of Bids58----B60
Sum of Bids59----B61
Sum of Bids60----B62
Sum of Bids61----B63
Sum of Bids62----B64
Sum of Bids63----B65
Sum of Bids64----B66
Sum of Bids65----B67
Sum of Bids66----B68
Sum of Bids67----B69
Sum of Bids68----B70
Sum of Bids69----B71
Sum of Bids70----B72
Sum of Bids71----B73
Sum of Bids72----B74
Sum of Bids73----B75
Sum of Bids74----B76
Sum of Bids75----B77
Sum of Bids76----B78
Sum of Bids77----B79
Sum of Bids78----B80
Sum of Bids79----B81
Sum of Bids80----B82
Sum of Bids81----B83
Sum of Bids82----B84
Sum of Bids83----B85
Sum of Bids84----B86
Sum of Bids85----B87
Sum of Bids86----B88
Sum of Bids87----B89
Sum of Bids88----B90
Sum of Bids89----B91
Sum of Bids90----B92
Sum of Bids91----B93
Sum of Bids92----B94
Sum of Bids93----B95
Sum of Bids94----B96
Sum of Bids95----B97
Sum of Bids96----B98
Sum of Bids97----B99
Sum of Bids98----B100
Sum of Bids99----B101
Sum of Bids100----B102
Sum of Bids101----B103
Sum of Bids102----B104
Sum of Bids103----B105
Sum of Bids104----B106
Sum of Bids105----B107
Sum of Bids106----B108
Sum of Bids107----B109
Sum of Bids108----B110
Sum of Bids109----B111
Sum of Bids110----B112
Sum of Bids111----B113
Sum of Bids112----B114
Sum of Bids113----B115
Sum of Bids114----B116
Sum of Bids115----B117
Sum of Bids116----B118
Sum of Bids117----B119
Sum of Bids118----B120
Sum of Bids119----B121
Sum of Bids120----B122
Sum of Bids121----B123
Sum of Bids122----B124
Sum of Bids123----B125
Sum of Bids124----B126
Sum of Bids125----B127
Sum of Bids126----B128
Sum of Bids127----B129
Sum of Bids128----B130
Sum of Bids129----B131
Sum of Bids130----B132
Sum of Bids131----B133
Sum of Bids132----B134
Sum of Bids133----B135
Sum of Bids134----B136
Sum of Bids135----B137
Sum of Bids136----B138
Sum of Bids137----B139
Sum of Bids138----B140
Sum of Bids139----B141
Sum of Bids140----B142
Sum of Bids141----B143
Sum of Bids142----B144
Sum of Bids143----B145
Sum of Bids144----B146
Sum of Bids145----B147
Sum of Bids146----B148
Sum of Bids147----B149
Sum of Bids148----B150
Sum of Bids149----B151
Sum of Bids150----B152
Sum of Bids151----B153
Sum of Bids152----B154
Sum of Bids153----B155
Sum of Bids154----B156
Sum of Bids155----B157
Sum of Bids156----B158
Sum of Bids157----B159
Sum of Bids158----B160
Sum of Bids159----B161
Sum of Bids160----B162
Sum of Bids161----B163
Sum of Bids162----B164
Sum of Bids163----B165
Sum of Bids164----B166
Sum of Bids165----B167
Sum of Bids166----B168
Sum of Bids167----B169
Sum of Bids168----B170
Sum of Bids169----B171
Sum of Bids170----B172
Sum of Bids171----B173
Sum of Bids172----B174
Sum of Bids173----B175
Sum of Bids174----B176
Sum of Bids175----B177
Sum of Bids176----B178
Sum of Bids177----B179
Sum of Bids178----B180
Sum of Bids179----B181
Sum of Bids180----B182
Sum of Bids181----B183
Sum of Bids182----B184
Sum of Bids183----B185
Sum of Bids184----B186
Sum of Bids185----B187
Sum of Bids186----B188
Sum of Bids187----B189
Sum of Bids188----B190
Sum of Bids189----B191
Sum of Bids190----B192
Sum of Bids191----B193
Sum of Bids192----B194
Sum of Bids193----B195
Sum of Bids194----B196
Sum of Bids195----B197
Sum of Bids196----B198
Sum of Bids197----B199
Sum of Bids198----B200
Sum of Bids199----B201
Sum of Bids200----B202
Sum of Bids201----B203
Sum of Bids202----B204
Sum of Bids203----B205
Sum of Bids204----B206
Sum of Bids205----B207
Sum of Bids206----B208
Sum of Bids207----B209
Sum of Bids208----B210
Sum of Bids209----B211
Sum of Bids210----B212
Sum of Bids211----B213
Sum of Bids212----B214
Sum of Bids213----B215
Sum of Bids214----B216
Sum of Bids215----B217
Sum of Bids216----B218
Sum of Bids217----B219
Sum of Bids218----B220
Sum of Bids219----B221
Sum of Bids220----B222
Sum of Bids221----B223
Sum of Bids222----B224
Sum of Bids223----B225
Sum of Bids224----B226
Sum of Bids225----B227
Sum of Bids226----B228
Sum of Bids227----B229
Sum of Bids228----B230
Sum of Bids229----B231
Sum of Bids230----B232
Sum of Bids231----B233
Sum of Bids232----B234
Sum of Bids233----B235
Sum of Bids234----B236
Sum of Bids235----B237
Sum of Bids236----B238
Sum of Bids237----B239
Sum of Bids238----B240
Sum of Bids239----B241
Sum of Bids240----B242
Sum of Bids241----B243
Sum of Bids242----B244
Sum of Bids243----B245
Sum of Bids244----B246
Sum of Bids245----B247
Sum of Bids246----B248
Sum of Bids247----B249
Sum of Bids248----B250
Sum of Bids249----B251
Sum of Bids250----B252
Sum of Bids251----B253
Sum of Bids252----B254
Sum of Bids253----B255
Sum of Bids254----B256
Sum of Bids255----B257
Sum of Bids256----Null
Sum of Bids257----Null
Sum of Bids258----Null
Sum of Bids259----Null
Sum of Bids260----Null
Sum of Bids261----Null
Sum of Bids262----Null
Sum of Bids263----Null
Sum of Bids264----Null
Sum of Bids265----Null
Sum of Bids266----Null
Sum of Bids267----Null
Sum of Bids268----Null
Sum of Bids269----Null
Sum of Bids270----Null
Sum of Bids271----Null
Sum of Bids272----Null
Sum of Bids273----Null
Sum of Bids274----Null
Sum of Bids275----Null
Sum of Bids276----Null
Sum of Bids277----Null
Sum of Bids278----Null
Sum of Bids279----Null
Sum of Bids280----Null
Sum of Bids281----Null
Sum of Bids282----Null
Sum of Bids283----Null
Sum of Bids284----Null
Sum of Bids285----Null
Sum of Bids286----Null
Sum of Bids287----Null
Sum of Bids288----Null
Sum of Bids289----Null
Sum of Bids290----Null
Sum of Bids291----Null
Sum of Bids292----Null
Sum of Bids293----Null
Sum of Bids294----Null
Sum of Bids295----Null
Sum of Bids296----Null
Sum of Bids297----Null
Sum of Bids298----Null
Sum of Bids299----Null
We are afraid, this issue is still unresolved and there is no update for you at this moment regarding this issue. However, we have logged your comment in our database against this issue and requested the product team to provide some fix or ETA for this issue. Once there is some news for you, we will update you asap by posting in this thread.
This is to update you that the ticket logged earlier as CELLSNET-44304 has been marked resolved. We will shortly share the fix here after ensuring the quality and incorporating other enhancements.
Please try the attached latest version/fix: Aspose.Cells for .NET v9.0.10 (.NET framework 4.0 compiled version).
Please add a line (in bold) to your code segment, it would work fine as I tested.
e.g. Sample code:
…
static void Main(string[] args)
{
var workbook = new Workbook();
// Create the base worksheet
var sheet = workbook.Worksheets[0];
sheet.Name = "Source Sheet";
sheet.Cells[0, 0].Value = "Date";
sheet.Cells[1, 0].Value = "2015/11/02";
sheet.Cells[2, 0].Value = "2015/11/02";
sheet.Cells[3, 0].Value = "2015/11/02";
sheet.Cells[4, 0].Value = "2015/11/02";
sheet.Cells[0, 1].Value = "Advertiser";
sheet.Cells[1, 1].Value = "Joe";
sheet.Cells[2, 1].Value = "Bob";
sheet.Cells[3, 1].Value = "Bob";
sheet.Cells[4, 1].Value = "Joe";
foreach (var i in Enumerable.Range(0, 300))
{
sheet.Cells[0, i + 2].Value = "Bids" + i;
sheet.Cells[1, i + 2].Value = 1000 + i;
sheet.Cells[2, i + 2].Value = 200 + i;
sheet.Cells[3, i + 2].Value = 400 + i;
sheet.Cells[4, i + 2].Value = 750 + i;
}
// Create range representing this data
var sourceData = string.Format(
"='{0}'!A1:{1}",
sheet.Name,
CellsHelper.CellIndexToName(row: sheet.Cells.MaxDataRow, column: sheet.Cells.MaxDataColumn));
// Create the pivot sheet
var pivotSheet = workbook.Worksheets.Add("Pivot Sheet");
// Add a pivot table to the pivot sheet
var pivotTableIndex = pivotSheet.PivotTables.Add(
sourceData,
"A1",
"PivotTable1");
var pivotTable = pivotSheet.PivotTables[pivotTableIndex];
// Set up the pivot table
pivotTable.AddFieldToArea(PivotFieldType.Row, "Advertiser");
pivotTable.AddFieldToArea(PivotFieldType.Row, "Campaign");
foreach (var i in Enumerable.Range(0, 300))
{
pivotTable.AddFieldToArea(PivotFieldType.Data, "Bids" + i);
}
pivotTable.IsExcel2003Compatible = false;
// Refresh and calculate the data
pivotTable.RefreshData();
pivotTable.CalculateData();
// Try to inspect what we produced
var dataFields = pivotTable.Fields(PivotFieldType.Data);
Console.WriteLine("There are {0} fields in the Data area of the pivot field", dataFields.Count);
foreach (var i in new[] { 0, 255, 256, 299 })
{
var displayName = dataFields[i].DisplayName;
Console.WriteLine(
"By index, the field DisplayName at index {0} is '{1}'. Using this in GetCellByDisplayName returns null? {2}",
i,
displayName,
pivotTable.GetCellByDisplayName(displayName) == null);
}
Console.ReadLine();
}
............
Kindly provide a sample console application project so that we could replicate this issue at our end and in case of bug, reopen it. Thanks for your cooperation in this regard and have a good day.