All formulas are automatically removed on opening SheetView

All formulas are automatically removed on opening SheetView

Posted by: thaufe on 5 December 2023, 12:32 am EST

• 

  thaufe

  • Post Options:
  • Link
    Copy

  Posted 5 December 2023, 12:32 am EST

  We are currently upgrading an old spread to the current version.

  With the old version our customers were able to create their own sheets, store them in a database and edit them later.
  
  For smaller and mid-sized sheets (around A4/letter) this still works fine after the update, but larger sheets now lose their formulas.

  byte[] sheetdata = Load_Old_Sheet_From_Database();
  FarPoint.Win.Spread.SheetView view = new FarPoint.Win.Spread.SheetView();
  using (System.IO.MemoryStream ms = new System.IO.MemoryStream(sheet))
  {
  	view.Open(ms);
  }

  Right with the Open() all formulas are gone.
  
  view.Cells[x,y].Formula is empty for each cell and view.Cells[x,y].Text contains the last calculated text.

  I am not sure at what exact conditions the formulas are getting removed.
  
  It is probably not only the size of the sheet. If I add new formulars to one of the large corrupted sheets, then these will be stored again.
  
  So I think it might be a high number of formulas, that trigger this behavior.

  Is there any way to prevent this?
  
  Right now, this obliterates most of the sheets, the next time they are opened.

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 6 December 2023, 11:31 am EST

  Hi Thomas,

  As per our understanding, you are saving your FpSpread data in a database and then loading it again in the spread control from the database.

  We request you provide us with further information in order to replicate the issue on our end:

  1. What type of data is stored in your FpSpread control and how much it is?
  2. The code snippet that you are using to save your data into the database.
  3. The implementation of Load_Old_Sheet_From_Database() method.
  4. How you have used formulas in your sheet?
  5. Specify the old version from which you are upgrading your project.

  Kindly provide us with a stripped-down version of the sample application with some dummy data, so that we could replicate the issue on our end for further analysis.

  Thanks & Regards,
  
  Aastha

• 

  thaufe

  • Post Options:
  • Link
    Copy

  Posted 12 December 2023, 3:31 am EST

  Hello Aastha,

  When trying to create a minimal test application, I found out that the problem has nothing to do with the size or the way the sheet was stored or opened.

  The problem is, that most formulas are just invalid now, because now the spread expects a semicolon as separator rather than a comma.

  =If(1=2,“true”,“false”)
  
  Is not working anymore and is removed whenever inserted manually and in existing sheets.

  The spread now expects formulas like this:
  
  =If(1=2;“true”;“false”)

  I haven’t found any information why this happened or how to disable this.

  Maybe it has something to do, that the application runs on a German Windows. The formulas are also getting translated automatically even without setting up a LanguagePackage. [ =IF(…) becomes to =WENN(…) on leaving a cell ]. But this is only a guess.

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 13 December 2023, 1:13 pm EST

  Hi Thomas,

  You have guessed it right that the value separator in an Excel formula depends on the regional settings of your system. You can refer to the following link for the same: https://www.excel-exercise.com/comma-or-semicolon-in-excel-formula/

  We checked on our end and found that the excel formula value list is separated via semi-colon in German regional settings. As per our understanding of your use-case, we have created a sample application for reference. See LoadFormulaData.zip

  We request you please update the attached sample as per your use-case and let us know your exact system regional settings used for saving and loading the spread content. Please provide us a step-by-step guide that we can follow to replicate the exact issue on your end.

  Your cooperation is highly appreciated.

  Thanks & Regards,
  
  Aastha

• 

  thaufe

  • Post Options:
  • Link
    Copy

  Posted 14 December 2023, 9:16 pm EST

  Hello Aastha,

  The change of the LegacyBehaviors parameter in the Spread constructor had some minor effect.

  In combination with

  System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-us");

  at the program start, old sheets work again.

  But that’s not really an option. The output numbers must have German format.

  I hope you can reproduce my problem if you add

  System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("de-de");

  at the start of your demo program and load the attached sheet. (simple 3x3 with only one formula)
  
  sheet1.zip

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 18 December 2023, 12:59 pm EST

  Hi Thomas,

  Thanks for the information.

  We could replicate the issue on our end. We are in discussion with our developers regarding this. [Internal Tracking ID: SPNET-35935]

  We will update you once we hear back from them.

  Thanks & Regards,
  
  Aastha