Change a pivot tables datasource and refresh pivot - using webservice

Question

I'm using the ExcelService web service API to open an existing Excel Sheet (template) saved on sharepoint. This workbook has 2 sheets - a data sheet and a sheet with a pivot table based on that data.

What I'd like to do is modify the numbers in the datasheet, have the pivot table refresh, then direct the new file back to the user (The new modified file doesn't actually get saved, except maybe on the users computer). The code seems pretty straightforward, and looks something like this:

// initialize
ExcelEngine.ExcelService es = new ExcelEngine.ExcelService();
es.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

ExcelEngine.Status[] stati;

string sessionId = es.OpenWorkbook("http://invisodev2:34567/personal/v-bridah/Shared%20Documents/Book1.xlsx",
	String.Empty, String.Empty, out stati);

// Write some data
es.SetCellA1(sessionId, "Sheet1", "B2", "123");
// write other data as needed

// refresh data connection (specifically, the pivot table)
es.Refresh(sessionId, null);



// get the bits, and save, and redirect to user.
byte[] workbook = es.GetWorkbook(sessionId, ExcelEngine.WorkbookType.PublishedItemsSnapshot, out stati);
string path = Server.MapPath("Book1.xlsx");

System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create);
System.IO.BinaryWriter binaryWriter = new System.IO.BinaryWriter(fs);

binaryWriter.Write(bytes);
binaryWriter.Close();

Response.Redirect("Book1.xlsx");

// close workbook.
es.CloseWorkbook(sessionId);

Seems to work great, except for one problem: When the excel file actually gets sent to the user, the pivot table is refreshed, however it is no longer a pivot table. It is as if someone just copied the values/formatting of each cell. So, the user can't do anything more with the data - no filtering, no sorting, etc.

Wanted to know how to avoid this, if possible. The goal is really just to deliver an excel pivot table with data selected/inserted from a website, but it doesn't seem to work.

Answer 1

Ok, I think I found the problem. Change the args to es.GetWorkbook to show as the following:

byte[] workbook = es.GetWorkbook(sessionId, ExcelEngine.WorkbookType.FullWorkbook, out stati);

I think the "PublishedItemSnapshot" is a format that doesn't support stuff like pivots.