Copy/paste merged cell

Copy/paste merged cell

Posted by: ngocnguyen09910060 on 25 July 2022, 4:23 pm EST

• 

  ngocnguyen09910060

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 25 July 2022, 4:23 pm EST - Updated 3 October 2022, 12:09 am EST

  Hi teams,

  On my application, i use the addSpan method to merge a cell across multi rows.

  
  sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;
  sheet.addSpan(1, 0, 3, 1);
  
  

  

  I select a range: A1 to A5 and Ctrl+C. After that, i select cell C1 and Ctrl+V.
  
  This is result:
  
  
  
  This isn’t my expectation.

  I want to change result become 1 in 2 options:

  • "

  • “Option 1: “3” is pasted into C3.”

  • “Option 2: Remove value of cells below merged cell. It’s means: values of C3, C4, C5 is blank.”

  Please guide me.
  
  "

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 26 July 2022, 8:03 pm EST

  Hi,

  For this, you may need to handle the ClipboardPasted event and change cell value by yourself. Please refer to the following attached sample that demonstrates the basic implementation.

  
    spread.bind(GC.Spread.Sheets.Events.ClipboardPasted, (e, args) => {
      if (
        rangeContainSpan(
          args.sheet,
          args.cellRange
        ) &&
        args.fromRange?.colCount === 1 &&
        args.cellRange.colCount === 1
      ) {
        putValues(args.sheet, args.cellRange, args.pasteData.text);
      }
      else if (rangeContainSpan(args.sheet, pasteInfo.fromRange)) {
        /*handle the case when copy happen on spanned range
        //idea is to traverse the cell from up to down and if any null value get found then 
        //replace the value from next value inside the paste cell range*/
        let cellRange = args.cellRange;
        for (let row = cellRange.row; row < cellRange.row + cellRange.rowCount; row++) {
          for (let col = cellRange.col; col < cellRange.col + cellRange.colCount; col++) {
            if (sheet.getValue(row, col) === null) {
              let nextValue = getNextValue(sheet, row, col, (cellRange.rowCount + cellRange.row));
              sheet.setValue(row, col, nextValue);
            }
          }
  
        }
      }
    });
  
  

  Regards,
  
  Avinash

  PasteOnSpan.zip

• 

  ngocnguyen09910060

  • Post Options:
  • Link
    Copy

  Posted 2 August 2022, 3:25 pm EST - Updated 3 October 2022, 12:09 am EST

  Hi avinash.pathak,
  
  In my above sample, i use the addSpan method to merge another cell across multi rows.

  sheet.addSpan(1, 5, 8, 1);
  

  I select a range: A1 to A5 and Ctrl+C. After that, i select cell F1 and Ctrl+V.
  
  In this case, i don’t see any changes on cell F.
  
  
  
  I has debugged but i don’t see any events (ClipboardPasting, CellChanged, RangeChanged, ClipboardPasted) are executed in this case.
  
  I understood that SpreadJS don’t have any changes because size of 2 merged cells is different.
  
  So, please tell me event is executed in this case and how to show alert to inform user about reason of this case.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 2 August 2022, 9:52 pm EST

  Hi,

  for Pasting the ClipboardPasted, RangeChnaged should get fire. I tested the above-attached sample in that the event gets called properly. Could you check that sample and edit the sample to replicate the issue so that we could investigate further and help you accordingly?

  regards,
  
  Avinash

• 

  ngocnguyen09910060

  • Post Options:
  • Link
    Copy

  Posted 3 August 2022, 4:19 am EST

  Hi avinash.pathak,
  
  In your sample code, i has changed index from 0 become 1 as below:

  
  function addSpan(sheet) {
    sheet.suspendPaint();
    for (let i = 1; i < 11; i += 2) {
      sheet.addSpan(i, 0, 2, 1);
    }
    addData(sheet);
    sheet.resumePaint();
  }
  
  

  After that, try to copy range B1:B10 and paste it on A1
  
  In this case, i don’t see any changes on cells A.
  
  Please refer to the following attached sample and tell me event is executed in this case and how to show alert to inform user about reason of this case.
  
  PasteOnSpan.zip

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 4 August 2022, 12:13 am EST - Updated 3 October 2022, 12:09 am EST

  Hi,

  This expected behavior actually what are you is Invalid Operation. you may handle all such operations using the InvaildOperation. Please refer to the following code snippet and let me know if you face any issues.

  Regards,
  
  Avinash

  

  821×403 38.6 KB

• 

  jamesrobertscs

  • Post Options:
  • Link
    Copy

  Posted 6 March 2023, 7:29 pm EST - Updated 7 March 2023, 9:54 am EST

  When you copy/paste merged cells in a spreadsheet program like Microsoft Excel or Google Sheets, the contents of the merged cells are pasted into a single cell, rather than being distributed across the cells that were merged in the original. This can result in unexpected formatting and layout issues.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 9 March 2023, 1:25 am EST

  Hello,

  As I can understand, you are merging some cells which have data. Then you are copying and pasting the merged cell and when you un-merge the pasted merged cells, the original merged values are spread across multiple cells.

  For this use case, you can create a custom command and register it with command manager using commandManager.register() method. This command merges the selected cell range and clears styles and data from cells which are now hidden after merging. You can use sheet.addSpan() method to merge cells. Now, when you un-merge the merged cells, the value will only be set to one cell instead of multiple cell.

  Please refer to the attached sample for further understanding

  Sample: https://jscodemine.grapecity.com/share/GV6E6zMdD0WBmkNxYsknBw/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2FmergeCellCommand.js"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2FmergeCellCommand.js"}

  Please let us know if you still face any issues.

  Doc reference

  commandManager.register():https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Commands.CommandManager#register

  sheet.addSpan():https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Worksheet#addspan

  commandManager.execute(): https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Commands.CommandManager#execute

  Regards,

  Ankit