Open excel sheet with pivot table without saving it

Aspose.Cells Product Family
1

Hi,

My requirment is to open the excel sheet with pivot table even before saving it.

Other words I should be able to open the file when file download prompts to open or save the file.

When I open the file directly without saving it, throws an error saying cannot open pivotTable source file C:\.....test[1].xls

One more thing. The below line gives the sum but not the count

pivotTable.DataFields[0].AddCalculatedItem("CountSurveyID", "=COUNT('SurveyID')");

Please see the code below

public void CreateStaticReport()

{

Workbook wBook;

Worksheet wSheet;

string path = System.Web.HttpContext.Current.Server.MapPath("~");

path += @"\ExcelTemplates\Test.xls";

System.IO.StreamReader s = new System.IO.StreamReader(path);

wBook = new Workbook(s.BaseStream);

wSheet = wBook.Worksheets[4];

LocalBuilder.Reset();

LocalBuilder.StoredProcedureName = "abc";

DataTable dt = LocalBuilder.GetDataTable();

int rowCount = 1;

int colCount = 0;

foreach (DataRow dr in dt.Rows)

{

colCount = 0;

wSheet.Cells[rowCount, colCount++].PutValue(dr["SurveyID"]);

wSheet.Cells[rowCount, colCount++].PutValue(dr["Stage"]);

wSheet.Cells[rowCount, colCount++].PutValue(dr["ARD"].ToString());

wSheet.Cells[rowCount, colCount++].PutValue(dr["CNPartner"].ToString());

wSheet.Cells[rowCount, colCount++].PutValue(dr["PML"]);

rowCount++;

}

Worksheet wSheet1 = wBook.Worksheets[1];

Aspose.Cells.Pivot.PivotTableCollection pivotTables = wSheet1.PivotTables;

int index = pivotTables.Add("=Detail!A1:Q"+rowCount, "B3", "PivotTable1");

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

pivotTable.RowGrand = true;

pivotTable.ColumnGrand = true;

pivotTable.IsAutoFormat = true;

pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "PML");

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "ARD");

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, "Stage");

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data,"SurveyID");

pivotTable.DataFields[0].AddCalculatedItem("CountSurveyID", "=COUNT('SurveyID')");

this.Response.Clear();

this.Response.AddHeader("content-disposition", "attachment;filename=Test.xls");

this.Response.ContentType = "application/excel";

wBook.Save(this.Response.OutputStream, SaveFormat.Excel97To2003);

}

protected void lbl_Click(object sender, EventArgs e)

{

CreateStaticReport();

}

2

Hi,


Please create a simple sample web application (you may create a dummy datatable or use MS Access database table), zip it and post it here to show the issue, we will check it soon.

Also, which version of the product you are using? Please try our latest version/fix v6.0.0.4(attached).

Also, you may try the line of code:
this.Response.AddHeader(“content-disposition”, “attachment;filename=Test.xls”);
to:
this.Response.AddHeader(“content-disposition”, “inline;filename=Test.xls”);

Thank you.
3

Thanks for the quick response.

Sure will the try the inline thing.

One more thing. The below line gives the sum but not the count

pivotTable.DataFields[0].AddCalculatedItem("CountSurveyID", "=COUNT('SurveyID')");

Please suggest..

4

Amjad, Thanks alot. The inline thing worked.

Coming to calculated field, the data needs to be string format. Once I converted it to the string format, count worked.

I have another question.

How do I add Report Filter to the pivot table

5

Hi,


Good to know that your issue is resolved.

For report filtering, I am not completely sure about it. I think you may check the document for data filtering feature:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/data-filtering-and-validation.html


Thank you.