search

ucsd-ccbb / qiimp

Web application to collect metadata specifications from an experimenter and produce metadata input files with appropriate constraints
3 stars 7 forks source link

After copying and pasting into Qiimp template cells become protected from editing when they should not be. #159

Open adswafford opened 5 years ago

adswafford commented 5 years ago

Files to reproduce attached.

  1. Create Qiimp template
  2. Copy over the sample names from the environmental samples sheet to the sample_name column
  3. Copy and paste the study title from Qiita to the title column; cells become protected from editing after pasting
  4. Similar issue observed when copying and pasting (from Chrome) from the OLS viewer for env_feature, though only the cell in row 2 became locked. 2018-08-27_sdzooenvirofixed_4169.xlsx 2018-08-27_Gauglitz_metadata_environmental (1).xlsx
AmandaBirmingham commented 5 years ago

Verified: when copying cells in from another excel spreadsheet WITH their formatting, the receiving cells in qiimp template become write-protected, with following message:

screen shot 2018-11-01 at 2 25 26 pm
AmandaBirmingham commented 5 years ago

I am 98% sure that the explanation in the accepted answer at https://superuser.com/questions/392528/why-does-pasting-into-an-unprotected-cell-in-excel-protect-it is the correct DIAGNOSIS for why we are seeing this behavior:

It is actually expected behavior (well expected by Microsoft, not necessarily by users) which is the good news. .... When you paste in a piece of formatted text the cell gets given the "Normal" style and then has the required formatting layered on top. The "Normal" style by default has protection set to locked. So this is why it is being applied.

The overall formatting on the qiimp metadata sheet is protected, so apparently when you paste in formatted text (even to a cell that was specifically set as unprotected) it gets set back to protected, hence the issue.

Will take a bit more thought to figure out how to correct the issue, as the work-around in the referenced post is not available through the xlsxwriter api as far as I can tell.