Can�t read cell data from Aspose created Pivot Table

Aspose.Cells Product Family
1

I can’t read the cell data from Aspose created Pivot Table, but can read data from Excel created Pivot. These two files are attached.

What are the differences between these two pivot tables?

Thanks

Charlie

private void TestReadPivotTable()
{

Workbook src_workbook;

Worksheet src_sheet;

Cells src_cells;

string strDate;

string [] strFcastDate;

// 1. read manual create pivot table

// open source file

src_workbook = new Workbook();

src_workbook.Open("C:\\FCast\\SLT_Forecast_Excel.xls"); // manually created using Excel

src_sheet = src_workbook.Worksheets["SLT"];

src_cells = src_sheet.Cells;

// locate the month cells on destination

for (int j=0; j < 6; j++)
{

// get month info from source

strDate = src_cells[3,j+1].StringValue; // ****** get right data *******

strFcastDate = strDate.Split('-');

}

// 2. read aspose created pivot table

// open source file

src_workbook = new Workbook();

src_workbook.Open("C:\\FCast\\SLT_Forecast_Aspose.xls"); // Using Aspose API created

src_sheet = src_workbook.Worksheets["SLT"];

src_cells = src_sheet.Cells;

// locate the month cells on destination

for (int j=0; j < 6; j++)
{

// get month info from source

strDate = src_cells[3,j+1].StringValue; // **** didn't get data ****

strFcastDate = strDate.Split('-');

}

}
2

this file is created by usig Aspose API.

---

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

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

int iCol = dsForecast.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(dsForecast.Tables[0], true, 0, 0, iRow, iCol);

//Add a new worksheet for Pivot Table

workbook.Worksheets.Add();

Worksheet sheet2 = workbook.Worksheets[1];

sheet2.Name = "SLT";

/*

// create Title for Pivot Table

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,"Pivot_Table_2");

// access the new pivot table

PivotTable pTable = pTables[index];

//pTable.RowGrand = false; // unshowing grand totals for rows

// drag product field to the row data

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

// drag fcastdate field to the column data

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

// drag quantity field to the data

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

// drag department_name field to the page

pTable.AddFieldToArea(PivotFieldType.Page, 3); // department_name

// 1. 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 the field auto sort.

rowPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

//rowPivotField.IsAscendSort = true;

//2. 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 the field auto sort.

colPivotField.IsAutoSort = true;

//Setting the field auto sort ascend.

//colPivotField.IsAscendSort = true;

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

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

3

Hi ,

Now we do not support to set the PivotTable data to the dest cell range.So the PivotTable is forced to refresh when the file is opened by Excel.

4

Would you please explain it in deatil? I don't quite understand.

thanks

charlie

5

Hi Charlie,

We do not calculate the data of the pivot table to the dest cell range.

In the Excel, there are two steps to generate a PivotTable: setting info of the PiovtTable(such as :which items are dragged to Row/Column/Data/Page area?) and generate the data to the cell range.

Now we only set info of the PivotTable and force Excel to generate the data to the cell range.Because generating the data of PivotTable is very complex.

6

As you mentioned, Aspose force Excel to generate the data to the cell range. If the file is saved, the data should be there.

I am attaching a file which contains two sheets. Aspose_PTable sheet was created by the Aspose demo code and Manual_PTable sheet was manually created using Excel. Cell 7E on Aspose_PTable sheet and 7A on Manual_PTable sheet should contain the same string “Grand Total”.

Why can’t I read the cell 7E on Aspose_PTable sheet? The code is below.

Thanks

Charlie

private void ReadPivotTable()
{

Workbook workbook;

Worksheet sheet1, sheet2;

Cells cells;

string strTotal;

// open source file

workbook = new Workbook();

workbook.Open("C:\\PivotTableFromSample.xls");

sheet1 = workbook.Worksheets["Aspose_PTable"];

cells = sheet1.Cells;

strTotal = cells[6,4].StringValue; // cell 7E= "" ??????? why?


sheet2 = workbook.Worksheets["Manual_PTable"];

cells = sheet2.Cells;

strTotal = cells[6,0].StringValue; // cell 7A = "Grand Total"

}
7

Hi,

If you want to get the cell data of the PivotTable created by Aspose.Cells, there are 3 steps:

1, Creats a PivotTable and saves the file with Aspose.Cells;

2,Opens the file and saves the file with Excel;

3,Opens the file and get the cell data with Aspose.Cells.

8

It is working now by following your 3 steps.

Step 2 needs me to explicitly open/save the file using Excel.

Are there any other ways to “force” Excel to do the Open/Save in Aspose.Cells or ASP.NET? this will be the perfect solution.

Thanks

Charlie

9

It is not practical to do that in the ASP.net level because it needs to modify the IIS services to allow interact with desktop. IIS admin won’t allow that.

Does Aspose.Cells support it? If not, will it be supported in the near future?

Thanks

Charlie

10

Currently your requested feature is not supported. We will check to see if we can make this feature in the future version. However, I don’t think it will be available in a short time.