Flexsheet - text/string cell format problems

Flexsheet - text/string cell format problems

Posted by: talaber on 26 June 2018, 4:51 am EST

• 

  talaber

  • Post Options:
  • Link
    Copy

  Posted 26 June 2018, 4:51 am EST

  Hello.

  I noticed some issues with formats and import…

  Issue 1.

  Using sample at: http://demos.wijmo.com/5/angular/excelbook/excelbook/standalone.htm

  1. In first step i enter some string ‘AAA’ and everything is ok.
  2. In second step i enter some numeric value in cell, for example ‘01’.
     
     After edit completes in some step value is parsed and converted to number. Zero is thrown away.
  3. In third step if you try to edit same value and enter again some string, for example ‘AAAA’. It is not possible because somewhere in second step cell format was switched to number format and new value ‘AAAA’ is invalid becauase it is not number.

  You can see this behaviour in attached video.

  bandicam 2018-06-26 10-22-05-066.zip

  Same behaviour is happening when entering date in first step. Once cell saves date, it is not possible to enter string or number in that cell anymore.

  Issue 2.

  Also i noticed that after importing excel document with a text formatted cell. Text formated cell have format “n0” in style object.I’m using getCellStyle() function;

  Beside those issues how can i set cell format to be text format like in excel, not general cell format. I’m using AngularJs

  Thank you.

• 

  talaber

  • Post Options:
  • Link
    Copy

  Posted 26 June 2018, 5:55 am EST

  Here is video and excel file for import problem demonstration.

  ImportFiles.zip

  You can test it here:

  https://codepen.io/anon/pen/LrJLYv?editors=1001

  Thank you

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 27 June 2018, 9:24 am EST

  Hi,
  
  We are looking into this. We will update you soon by tomorrow.

  ~Manish

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 28 June 2018, 7:29 am EST

  Hi,
  
  We are sorry for the delayed response.
  
  We are able to replicate the issue at our end. Hence, the issue has been forwarded to the concerned team with tracking id 330005.
  
  We will let you know as we get any update on this.

  In the meantime, you may handle cellEditEnding event and update cell value from FlexSheet activeEditor using the setCellData method. Please refer to the following code snippet:

  flex.cellEditEnding.addHandler((s,e)=>{
                    if(flex.selectedSheetIndex==0&&!e.cancel){
                       flex.setCellData(e.row,e.col,s.activeEditor.value,false);
                    }
  });
  

  Please also refer to the following demo sample: https://codepen.io/anon/pen/NzEdMd?editors=1001

  Regarding importing:
  
  The behavior you are observing is by design. Here sheet converts the imported format into equivalent number/date format for the underlying grid to format number/date value and have no effect on text/strings.
  
  To check the actual imported style you can get it from the workbook instance.
  
  Pplease refer to following code snippet:

  workbook.sheets[0].rows[1].cells[1].style.format;
  /*it should return '@' */
  

  ~Manish

• 

  talaber

  • Post Options:
  • Link
    Copy

  Posted 29 June 2018, 3:07 am EST - Updated 3 October 2022, 8:28 pm EST

  Hello Manish,

  Thank you for reply.

  Regarding your sample and solution. I came to similar solution like in your sample. But actually your sample don’t work. You can test it by entering ‘01’ in first cell. Even if we set value inside setCellData with coerce = false, after cellEditEnding event ends and before cellEditEnded event fires wijmo will call setCellData where again value will be parsed and converted to number and set to 1. To make sample work we need to add e.cancel = true before flex.setCellData. Here is pen:

  https://codepen.io/talaber/pen/MXZypg?editors=1000#0

  This is not solution because we are braking editing events chain, and all values are now strings.

  /*it should return ‘@’ */
  
  So ‘@’ is correct format for string cells? Please confirm is this is true.

  If this is true than on cells that i want to behave like string/text cells i would set format to ‘@’.
  
  Then i could check inside cellEditEnding cell format , and if cell format is ‘@’ we can cancel editing and setCellValue. By that we will have behavior by design (general cell behaviour like in excel) for all other cells.

  I think in this line 4342 is the problem. If ‘@’ is valid format for text strings maybe here in this line you can check cell format and if it is ‘@’ then cancel value parsing, converting to number or date.

  

  1425×767 277 KB

  Tom

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 2 July 2018, 4:51 am EST

  Hi,
  
  Thanks for pointing out the problem in the last sample.

  This is not the solution because we are braking editing events chain, and all values are now strings.

  This is not supposed to be a permanent solution, it is just a workaround till the team fixes the bug. The issue is already with the dev team and a fix will be released soon.

  So ‘@’ is correct format for string cells? Please confirm is this is true.

  ‘@’ is text placeholder in excel cell formats. You can confirm this from this document:-
  
  https://support.microsoft.com/en-in/help/922069/information-about-the-settings-in-the-format-cells-dialog-box-in-excel

  I think in this line 4342 is the problem.

  Thanks for taking time to investigate the problem, we will certainly take it into consideration.

  ~Manish