I continually get the the "Cannot open PivotTable source file" error message when I try to create a spreadsheet from scratch, even when I use the savetype OpenInBrowser. All of your examples use workbook.Open(filename), rather than workbook = new Workbook();
The error shows up in the statement PivotTables.Add(sourcename). I place this statement before the workbook.Save() because I am not finished constructing the spreadsheet. If I save before the PivotTables.Add() then the Pivot Table is not included in the spreadsheet.
Can you provide a simple example that uses workbook = new Workbook() that will allow me to create valid pivot tables?
Ron
Hi,
Thank you for considering Aspose.
I have implemented you scenario with the latest version of Aspose.Cells and it works fine. Please see the following sample code which creates a workbook and then adds a pivot table to it.
Sample Code:
//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");
cell = cells["C1"];
cell.PutValue("Sales");
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);
PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E20", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
//Saving the Excel file
workbook.Save("C:\\Pivot.xls");
Please try the attached latest version of Aspose.Cells. If you still face any problem, please share you code and we will figure it out soon.
Thank You & Best Regards,
Hi,
And if I replace the last line of code of Nausherwan for an Asp.NET application i.e…,
//Saving the Excel file
workbook.Save("C:\\Pivot.xls");
with
//Saving the Excel file
workbook.Save("Pivot.xls", FileFormatType.Default, SaveType.OpenInBrowser, Response);
it works abs fine using the fix attached by Nausherwan, the pivot table is displayed fine into MS Excel(97-2007). I have saved as the file and attached it here for your reference.
Thank you.
Thank you for your quick response. You have helped me locate the problem, it was not a problem with Aspose.Cells nor in how I was using it.
I was passing a parameter to my aspx page, "pagename.aspx?param=value", and apparently Excel doesn't like that. I changed it to "pagename.aspx" and it worked ok. Of course, I still want to pass a parameter to the page but I will have to find another way to do that. Do you have a "best practices" suggestion?
Hi,
Well, generally, I think you have to eliminate the parameter from the part of the url: "pagename.aspx?param=value" if the workbook contains the pivot table in it and you are using OpenInBrowser save type.
Anyways, we will try to check if there is any other way around on our side, you should also try to sort it out for your need on your end too.
Thank you.
Hi,
Well, if you want to generate a file on the fly which contains a pivot table, please make sure that the URL should be in the format as "pagename.aspx" without any param attached. However if you want to pass parameter, please set the method attribute to "post" and the param attribute as the member of the form tag, such as,
Hope, it will help your need.
Thank you.