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

Free Support Forum - aspose.com

Calculated Field in Excel 2007 not showing up

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

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.

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?