Calculated Field in Excel 2007 not showing up

Aspose.Cells Product Family
1

Hi,

I'm using Aspose.Cells 4.4.0.5 and when we generate a pivot table that has a calculated field it works fine when opened (via the Response object) in Excel 2003 but the field is skipped in Excel 2007, if a field is supposed to come after the calculated field it shows up where the calculated field should be.

Any ideas?
Thanks,
Justin

2

Hi Justin,

Could you post your sample code with template file here.

And always use SaveType.OpenInBrowser if you are using Workbook.Save() method

//Or

//Update to make "inline" if you are using "attachment" option in your code. e.g.,

Response.AddHeader( "content-disposition","inline; filename=MyBook.xls");

Thank you.

3

Hi Justin,

I tried the following codes. It works fine.


//Instantiating an Workbook object
Workbook workbook = new Workbook();


//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];


Cells cells = sheet.Cells;


//Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter test");
cell = cells["C1"];
cell.PutValue("Sales");
cell = cells["D1"];
cell.PutValue("Sales1");

cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");


cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");


cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);

cell = cells["D2"];
cell.PutValue(0);
cell = cells["D3"];
cell.PutValue(0);
cell = cells["D4"];
cell.PutValue(0);
cell = cells["D5"];
cell.PutValue(0);
cell = cells["D6"];
cell.PutValue(0);
cell = cells["D7"];
cell.PutValue(0);
cell = cells["D8"];
cell.PutValue(0);

PivotTables pivotTables = sheet.PivotTables;


//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Sheet1!A1:D8", "E20", "PivotTable1");


//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];


//Unshowing grand totals for rows.
// pivotTable.RowGrand = false;


//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 0);


//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);

pivotTable.AddCalculatedField("test", "=IF(ISERROR(Sales/Sales1),0,5)");
// pivotTable.AutoFormatType = PivotTableAutoFormatType.Table1;
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.DataFields[0].NumberFormat = "yyyy-mm-dd";


workbook.Save(@"F:\FileTemp\dest.xls");

Could you post your template file and codes?