SetRangeA1 Issues

Question

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?

 

Thanks.

 

Here is the code:

 

Code Snippet

namespace SNDEMO.Forecast.Promotion
{
    public class ForecastPromotion : SPItemEventReceiver
    {
        public override void ItemUpdated(SPItemEventProperties properties)
        {
            try
            {
                SPListItem forecast = properties.ListItem;
                string status = forecast["Forecast Approval"].ToString();
                if (status == "16")
                {
                    Debug.Write("Forecast Approved.");
                    ExcelService xlSvc = new ExcelService();
                    xlSvc.Credentials = System.Net.CredentialCache.DefaultCredentials;
                    Status[] outStatus;
                    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);
                    Debug.Write("Success.");
                    Debug.Write("Recalculating...");
                    xlSvc.CalculateWorkbook(sessionID2, CalculateType.CalculateFull);
                    Debug.Write("Success.");
                    Debug.Write("Closing workbooks...");
                    xlSvc.CloseWorkbook(sessionID);
                    xlSvc.CloseWorkbook(sessionID2);
                    Debug.Write("Success.");
                }
            }
            catch (Exception ex)
            {
                Debug.Write("Error: " + ex.Message.ToString());
            }
   
        }

    }

}

 

Answer 1

Joe,

 

Have you looked  http://msdn2.microsoft.com/en-us/library/ms500767.aspx

 

Code Snippet

object[] rangeResults = xlservice.GetRangeA1(sessionId, "Sheet1", "B2:D3", true, out outStatus);

 

Andrew

 

Answer 2

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.

Danny