could some one comment the ExcelService.CalculationA1

could some one comment the ExcelService.CalculationA1

• barchard

  By default Trusted File Locations are set to honor the workbook's calculation method, which by default is Automatic Calculation.

  To change the calculation method for the workbook (in case you want to recalc manually) open the workbook in Excel, go to the Formulas Tab, Calculation Options drop down, and Select Manual then save it back to the document library.

  Your workbook will now only recalc when you tell it to through the Calculate methods (or through the UI).

  You can also change the settings at the Trusted File location to ignore the workbook's setting and always do manual, or automatic calc regardless.

  Another thing to keep in mind is that there are cache settings that come into play here as well, these are also applied to the Trusted Location, and you can set things like whether to cache calculation results for automatic or manual calculations, as well as how to cache volatile functions (things like RAND())

  
  
  

• cedubose

  The CalculateA1 method is analogous do doing a recalc on the client.

  So in scenarios where you have to manually recalc your formulas (say if automatic calc is off, or you have volatile functions that you want to recalc at certain times) you could use this method.

  The one plus here is that you can also send CalculateA1 the range you want to be recalc'ed in case you don't need to recalc the whole workbook, it'll just calc the range you specify.

  <Edited for accuracy>

  
  
  

• JIM.H.

  Hi Luis Bitencourt-Emilio
  thanks for your response.
  I am in puzzled now.
  
  I create some formulas "=B1+B2" in cell B3 .
  And i use ExcelService.SetCellA1 to set the values of them.
  SetCellA1(sessionID, "sheet1", "B1", 3);
  SetCellA1(sessionID, "sheet1", "B2", 4);
  
  Then i can get the value of B3 Cell using GetCellA1(sessionID, "sheet1", "B3", true, out status)
  Is is the "automatic calc"
  How to turn it off And How to use "CalculateA1"
  
  I may misunderstand the meaning of "CalculateA1".....
  
  
  
  
  

• stonejc

  Thanks Shahar.
  
  I notice the remarks on the post ExcelService.CalculateA1 Method (Microsoft.Office.Excel.Server.WebServices)
  
  
  Remarks:
  
  If the sheetName parameter is empty (that is, an Empty string or Nullis passed in) and the rangeCoordinates parameter is empty, the entire workbook will be calculated.
  
  Achieve  same functionality
  
  
  
  
  
  

• Yassi

  1. You can use CalculateA1 with an empty range as you said to calculate the whole workbook.

  2. You get the same rand value 10 times because of a different reason: Excel Services caches the results of volatile functions for a configurable period of time, and will not call them again until that time elapses, to save resources. Rand is a volatile function, and it respects this rule. An admin can change the setting to anything that suits specific needs, even to call volatile functions each and every time. Notice that if you called Rand 100 times in different locations on the sheet, you'd still get 100 potentially different random numbers, but if you then recalculated that sheet, you'd see the same 100 numbers until the volatile function cache lifetime elapses.

  
  
  

• Rossgov

  Thank you Luis Bitencourt-Emilio
  
  I  have master the usage of CalculationA1 by following your guidence.
  
  And I have another issue now.
  
   I set "=Rand()" to cell A1 and publish it to server.
   Then i set "0"(means always calculated) to   "Volatile Function Cache Lifetime" and "Automatic" to "Workbook Calculation Mode"  in page "Excel Services Edit Trusted File Location"
  
   And i write code to fetch the value of A1:
  for(int i=0;i<10;i++)
  {
      MessageBox.Show(es.GetCellA1(sessionID,"sheet1","A1",true,out status).ToString();
  }
  
  I think i can get 10 different value.However I get 10 same value...
  Why
  
  
  

• srem

  The entire content about Excel Services extensibility is here: http://msdn2.microsoft.com/en-us/library/ms517343.aspx

  You may want to take a look for some good context.

  Thanks,

  Danny

  
  
  

• netweavernet

  I think what Shahar was trying to correct my earlier statement that:

  "CalculateA1 calculates tha range specified (and it's dependents/dependecies)"

  is incorrect and should not include the parenthesis since it *only* calcs the range and not dependencies.

  Thanks for the correction Shahar! Perhaps we want this pointed out in our MSDN entry for the method as well, I'll look into that.

  
  
  

• BeguiledFoil

  RE: The =Rand() question

  The issue with your original question is that you never call the CalculateWorkbook() function - until you ask Excel Services to calculate the workbook, it will not re-run the rand function (or any function). So, if you added a call to CalculateWorkbook() in your loop before getting the value, you should be seeing different values each time.

  RE: A small correction to Danny's answer about volatile cache life time. (This is a very common misconception)

  Volatile Cache Life Time only governs if a volatile workbook gets calculated when it's opened. Calling any of the API Calculate methods will cause the recalculation of volatile functiosn regardless to what the cache setting is. Changing that setting to zero will not make the code you showed work. Adding the call to the CalculateWorkbook method will (even if you do not change the volatile cache lifetime setting).

  
  
  

• ChoKamir

  Note that when you call CalculateA1 (as opposed to CalculateWorkbook), the server will only calculate the range you tell it to calculate - it wont calculate anything else - such as cells that range depend on.

  That is why CaculateA1 is only useful in a handful of cases where you know exactly what you are doing and want a very specific calculation to occur.

  
  
  
• could some one comment the ExcelService.CalculationA1