Cannot open pivotTable source file�

Aspose.Cells Product Family
1

Hi,

I tried to save a workbook with pivot table using the following code

workbook.Save("PTable1_new.xls", SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

file download dialog box will contain OPEN and SAVE buttons.

If I click the save button, it will save the file. If I click the open button, it displays that “cannot open pivotTable source file…”.

How to fix this issue?

Thanks

Charlie

2

Hi,

Well, It might be Https problem which is related to IE 6.0 on your system. Could you try to replace your line with the following and checks if it works fine.:

//Saves file to memory
MemoryStream stream = new MemoryStream();
workbook.Save(stream, FileFormatType.Default);
response.ContentType = "application/vnd.ms-excel";
//This is same as OpenInBrowser option
//response.AddHeader( "content-disposition","inline; filename=PTable1_new.xls");
response.BinaryWrite(stream.ToArray());

And also tell us, if you save the file to the disk, does the file open fine in MS Excel or not ?

Please be sure that your are using the latest version of Aspose.Cells. If the problem still persits, could you please post your complete sample code that generates such file.

Thank you.

3

The save option worked before and still works.

Open option is still not working. I am using IE6 and Aspose.cells.dll (Net 1.0) version is 4.2.0.0.

Thanks

Charlie

try

{

ds = (DataSet)Session["dsForecast"];

int iRow = ds.Tables[0].Rows.Count; // row count is one short

int iCol = ds.Tables[0].Columns.Count;

Workbook workbook = new Workbook();

Worksheet sheet1 = workbook.Worksheets[0]; // Data

sheet1.Name = "Data";

Cells cells = sheet1.Cells;

//Import data

cells.ImportDataTable(ds.Tables[0], true, 0, 0, iRow, iCol);

//Add a new worksheet to the Workbook object

workbook.Worksheets.Add();

//Obtain the reference of the newly added worksheet by passing its sheet index

Worksheet sheet2 = workbook.Worksheets[1];

//Set the name of the newly added worksheet

sheet2.Name = "Summary";

// create Title for summary

cells = sheet2.Cells;

Cell cell = cells["A2"];

cell.PutValue("SLT Forecast Summary");

cell.Style.Font.Size = 14;

cell.Style.Font.Color = Color.Blue;

//Auto-fitting the 3rd row of the worksheet

sheet2.AutoFitRow(1);

//string strRange = "!A1:C6";

int iTotalRow = iRow + 1; // one row short

string strRange = "!A1:" + "L" + iTotalRow.ToString();

string sourceData = "=" + sheet1.Name + strRange; // "=SheetName!A1:L29"

string destCellName = "A3";

// create the pivot table

PivotTables pTables = sheet2.PivotTables;

int index = pTables.Add(sourceData,destCellName,"PivotTable2");


// access the new pivottable

PivotTable pTable = pTables[index];

// unshowing grand totals for rows

//pTable.RowGrand = false;

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Row, 1); // product

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Column, 6); // fcastdate

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Data, 4); // quantity

//Accessing the row fields.

PivotFields rowPivotFields = pTable.RowFields;

//Accessing the first row field in the row fields.

PivotField rowPivotField = rowPivotFields[0];

//Setting Subtotals.

rowPivotField.SetSubtotals(PivotFieldSubtotalType.Sum,true);

rowPivotField.SetSubtotals(PivotFieldSubtotalType.Count,true);

//Setting autosort options.

//Setting the field auto sort.

rowPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

rowPivotField.IsAscendSort = true;

//Setting the field auto sort using the field itself.

//pivotField.AutoSortField = -1;

//Accessing the column fields.

PivotFields colPivotFields = pTable.ColumnFields;

//Accessing the first col field in the col fields.

PivotField colPivotField = colPivotFields[0];

//Setting Subtotals.

colPivotField.SetSubtotals(PivotFieldSubtotalType.Sum,true);

colPivotField.SetSubtotals(PivotFieldSubtotalType.Count,true);

//Setting autosort options.

//Setting the field auto sort.

colPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

colPivotField.IsAscendSort = true;

//Setting the field auto sort using the field itself.

//pivotField.AutoSortField = -1;

//workbook.Save("PTable10_new.xls"); // save to c:\windows\system32\

//workbook.Save("C:\\PTable10_new.xls"); // to c:\

//workbook.Save("PTable1_new.xls", FileFormatType.Default, SaveType.OpenInExcel, Response);

//Save file and send to client browser

//workbook.Save("PTable1_new.xls", SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

//Saves file to memory

MemoryStream stream = new MemoryStream();

workbook.Save(stream, FileFormatType.Default);

Response.ContentType = "application/vnd.ms-excel";

//This is same as OpenInBrowser option

//response.AddHeader( "content-disposition","inline; filename=PTable1_new.xls");

Response.BinaryWrite(stream.ToArray());

labelError.Text = " Export Successfully";

}

catch(Exception err)

{

labelError.Text = err.Message;

labelError.Visible = true;

}
4

Hi,

Could you post your created file?

5

How do I attach the file?

charlie

6

Hi Charlie,

If the file is a big one, kindly zip it. After clicking the "Reply" button, click:

[Options]tab -> [Add/Update]button -> [Browse]button to select the file -> finally [Save]button to save the file.

Thank you.

7

There are two files. One with pivot table and one without.

thanks

Charlie

8

Hi,

Thanks for the files,

We will check and get back to you soon.

Thank you.

9

Hi,

I tried your code.It works fine.The following is my code :

//Instantiate a new Workbook.
Workbook workbook = new Workbook();
workbook.Open(@"Pivot.xls");//source workbook
workbook.Worksheets.Add();
Worksheet sheet2 = workbook.Worksheets[1];
sheet2.Name = "Summary";
PivotTables pTables = sheet2.PivotTables;
string sourceData = "=Data!A1:L29";
string destCellName = "A3";
int index = pTables.Add(sourceData, destCellName, "PivotTable2");
// access the new pivottable
PivotTable pTable = pTables[index];
// drag 1st field to the row data
pTable.AddFieldToArea(PivotFieldType.Row, 1); // product
// drag 1st field to the row data
pTable.AddFieldToArea(PivotFieldType.Column, 6); // fcastdate
// drag 1st field to the row data
pTable.AddFieldToArea(PivotFieldType.Data, 4); // quantity
//Accessing the row fields.
PivotFields rowPivotFields = pTable.RowFields;
//Accessing the first row field in the row fields.
PivotField rowPivotField = rowPivotFields[0];
//Setting Subtotals.
rowPivotField.SetSubtotals(PivotFieldSubtotalType.Sum, true);
rowPivotField.SetSubtotals(PivotFieldSubtotalType.Count, true);
//Setting autosort options.
//Setting the field auto sort.
rowPivotField.IsAutoSort = true;
//Setting the field auto sort ascend.
rowPivotField.IsAscendSort = true;
//Accessing the column fields.
PivotFields colPivotFields = pTable.ColumnFields;
//Accessing the first col field in the col fields.
PivotField colPivotField = colPivotFields[0];
//Setting Subtotals.
colPivotField.SetSubtotals(PivotFieldSubtotalType.Sum, true);
colPivotField.SetSubtotals(PivotFieldSubtotalType.Count, true);
//Setting autosort options.
//Setting the field auto sort.
colPivotField.IsAutoSort = true;
//Setting the field auto sort ascend.
colPivotField.IsAscendSort = true;
MemoryStream stream = new MemoryStream();
workbook.Save(stream, FileFormatType.Default);
Response.ContentType = "application/vnd.ms-excel";
//This is same as OpenInBrowser option
//Response.AddHeader("content-disposition", "inline; filename=PTable1_new.xls");
Response.BinaryWrite(stream.ToArray());

Could you remove the pivot table in your attached file and try the list code ?

10

Using you code and file without pivot table, I still got the same error message for Open option.

machine: windows xp professional service pack 2
Pentium M 1600 MHz
1 GB Ram

Browser: IE6

ASP.Net : 1.1

thanks

Charlie

11

Hi Charlie,

Which Excel version do you use?

Please try the fix in Aspose.Cells Downloads

I attached my created file.

12

I am using Excel 2003 sp2.

Is the zip file for Net 1.0?

What is this file? will this file official release later?

-Charlie

13

Hi Chalie,

Yes, the zip file is for .Net 1.0

Yes, we will release our next official version soon (might be at the end of this week) which will include all the previous enhancement, fixes and advanced features etc.

Thank you.

14

it didn't work on version 4.2.0.11.

Can you send your test code to me? Could my machine and IIS configurations are different?

thanks

Charlie

15

Hi Charlie,

Here is my testing code using your file (I eliminated the pivot table in the second sheet. Attached is the source file and output file.):

//Instantiate a new Workbook.

Workbook workbook = new Workbook();

//workbook.Open(@"Pivot_No1.xls");//source workbook

workbook.Open(Server.MapPath(@"\CellsApp\Designer\Pivot_No1.xls"));

workbook.Worksheets.Add();

Worksheet sheet2 = workbook.Worksheets[1];

sheet2.Name = "Summary";

PivotTables pTables = sheet2.PivotTables;

string sourceData = "=Data!A1:L29";

string destCellName = "A3";

int index = pTables.Add(sourceData, destCellName, "PivotTable2");

// access the new pivottable

PivotTable pTable = pTables[index];

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Row, 1); // product

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Column, 6); // fcastdate

// drag 1st field to the row data

pTable.AddFieldToArea(PivotFieldType.Data, 4); // quantity

//Accessing the row fields.

PivotFields rowPivotFields = pTable.RowFields;

//Accessing the first row field in the row fields.

PivotField rowPivotField = rowPivotFields[0];

//Setting Subtotals.

rowPivotField.SetSubtotals(PivotFieldSubtotalType.Sum, true);

rowPivotField.SetSubtotals(PivotFieldSubtotalType.Count, true);

//Setting autosort options.

//Setting the field auto sort.

rowPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

rowPivotField.IsAscendSort = true;

//Accessing the column fields.

PivotFields colPivotFields = pTable.ColumnFields;

//Accessing the first col field in the col fields.

PivotField colPivotField = colPivotFields[0];

//Setting Subtotals.

colPivotField.SetSubtotals(PivotFieldSubtotalType.Sum, true);

colPivotField.SetSubtotals(PivotFieldSubtotalType.Count, true);

//Setting autosort options.

//Setting the field auto sort.

colPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

colPivotField.IsAscendSort = true;

MemoryStream stream = new MemoryStream();

workbook.Save(stream, FileFormatType.Default);

Response.ContentType = "application/vnd.ms-excel";

//This is same as OpenInBrowser option

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

Response.BinaryWrite(stream.ToArray());

Thank you.

16

It could be a bug.

I am using the example code from your wiki. The dll version is 4.2.0.11.

I only replace this line

workbook.Save("BOOKS.xls", SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

It didn’t work for IE 6 and Firefox for OPEN, but it worked for SAVE. The sample code is only for SAVE.

Thanks

Charlie

private void PTableFromExample()
{

//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","E3","PivotTable2");

//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.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:\\book1.xls");

workbook.Save("BOOKS.xls", SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

}
17

I forgot to include the screenshot of error.

18

This is a problem of Excel. If you open an Excel file with pivot table in browser, you will get this error. You can verify it with following sample code:

FileStream fs1 = new FileStream("d:\\book1.xls", FileMode.Open, FileAccess.Read); // read a file with pivot table
byte[] data1 = new byte[fs1.Length];
fs1.Read(data1, 0, data1.Length);


this.Response.ContentType = "application/xls";
Response.AddHeader( "content-disposition","inline; filename=book1.xls");
Response.BinaryWrite(data1);
Response.End();

19

Hi,

Could you use the post method to another page which generate the file with the PivotTable.Please try to open the file with the URL should like "http://localhost/spg/eng/Forcast/frmFrocastSum.aspx" without any param(as "?dept=all").

If the url has params, I get the same error as yours. It seems a bug of the IE. We will continue to look into it .

20

I will try it when I am in the office.

Did you use IE7? I used IE6 now. The result from firefox seems different.

-Charlie