Monday, May 07, 2007 3:06 PM
Greetings...I am using the Excel Services methods of "GetRangeA1" and "SetRangeA1" to copy a range of cells from one spreadsheet to another. I am using this functionality in an event handler in MOSS 2007.
I have tried it using cell notation "A2:N49" and named ranges.
Everything looks perfect in my log files, as well as all of the information I am writing out to my debug viewer, but the changes are not copied over to the "master" workbook.
Can anyone provide some guidance?
Here is the code:Code Snippet
public class ForecastPromotion : SPItemEventReceiver
public override void ItemUpdated(SPItemEventProperties properties)
SPListItem forecast = properties.ListItem;
string status = forecast["Forecast Approval"].ToString();
if (status == "16")
ExcelService xlSvc = new ExcelService();
xlSvc.Credentials = System.Net.CredentialCache.DefaultCredentials;
string sessionID = xlSvc.OpenWorkbook("http://sales.newco.demo/Docs/Forecasts/smitty_2007_q1.xlsx", "en-US", "en-US", out outStatus);
object rangeResult = xlSvc.GetRangeA1(sessionID, "RAW_DATA", "Q1_Rev", false, out outStatus);
Debug.Write("Got forecast information.");
string sessionID2 = xlSvc.OpenWorkbook("http://sales.newco.demo/Reports/ReportsLibrary/Q1_2007_Master.xlsx", "en-US", "en-US", out outStatus);
Debug.Write("Writing to master...");
xlSvc.SetRangeA1(sessionID2, "FC-2007-Q1-smitty", "Covins_Q1_Rev", rangeResult);
catch (Exception ex)
Debug.Write("Error: " + ex.Message.ToString());
Thursday, May 17, 2007 9:12 PM
Have you looked http://msdn2.microsoft.com/en-us/library/ms500767.aspxCode Snippetobject rangeResults = xlservice.GetRangeA1(sessionId, "Sheet1", "B2:D3", true, out outStatus);
Monday, June 04, 2007 5:06 AM
The GetRangeA1 call is not the problem in this case. The one in Joe's code snippet should work too. Specifically, using "false" for the "formatted" argument is more correct, since this result is being used to set a range in another workbook.
I believe the issue is that Joe is expecting the session's result (state) to be written back into the workbook file once he closes the session, but this is not how Excel Services works. Sessions are private memory state; the files are never written.
You can call GetWorkbook on the master workbook session, and then use SharePoint OM or web service to create another file and write to it, or possibly to overwrite the original file.