Delete row that includes locked cell

Posted by: ngocnguyen09910060 on 7 August 2022, 4:34 am EST

Please login to follow topic

ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 7 August 2022, 4:34 am EST
Hi teams,

On my application, i want to have a label cell (user can’t change content of this cell). So, i using this code:

sheet.options.isProtected = true; sheet.getCell(0, 4).locked(true);

Now, user can’t change content or press Delete/Backspace to clear content of cell E1.

But it causes another issue: user want to delete all content of row but when they select row header and press Delete/Backspace, nothing changes.

Please check my sample source code.

DeleteRowExcludeLabelCell.zip
ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 7 August 2022, 9:00 pm EST
Hi teams,

On my application, i want to have a label cell (user can’t change content of this cell). So, i 'm using this code:

sheet.options.isProtected = true; sheet.getCell(0, 4).locked(true);

Now, user can’t change content or press Delete/Backspace to clear content of cell E1.

But it causes another issue: user want to delete all content of row but when they select row header and press Delete/Backspace, nothing changes.

To handle this requirement, i’m using this code:

sheet.bind(GC.Spread.Sheets.Events.CellClick, function (sender, args) { if (args.sheetArea == GC.Spread.Sheets.SheetArea.rowHeader && args.col == 0) { sheet.options.isProtected = false; } else { sheet.options.isProtected = true; } });

Now, user can select row header and press Delete/Backspace to clear content of row include content of cell E1.

But, in my application, cell E1 is primary key and i don’t want to clear content of this cell.

How can i delete all content of row exclude cell E1 ?

Please check my sample source code.
avinash.pathak
- Post Options:
- Link
  Copy
Posted 7 August 2022, 11:44 pm EST
HI,

For this, you may use Row3Changing Event. Please refer to the following code snippet and let me know if you face any issues.

ss.bind(GC.Spread.Sheets.Events.RowChanging, function (e, info) {console.log(info); if(info.row==0) info.cancel = true; });

RowChanging: https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.Sheets.Events~RowChanging_EV.html?highlight=rowchanging%2C

Regards,

Avinash
ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 8 August 2022, 12:08 am EST

Hi avinash.pathak,

RowChanging isn’t fired when i select row header and press Delete/Backspace.

When they select row header and press Delete/Backspace, how can i delete all content of row exclude a cell (such as E1) ?

avinash.pathak

Post Options:
Link
Copy

Posted 8 August 2022, 4:55 pm EST

Hi,

For handling the delete key you need to override the clear command and for handling back escape key you need to add checks in EditStarting event, Please refer to the following code snippet and let me know if you face any issues.


spread.bind(GC.Spread.Sheets.Events.EditStarting, (e, args) => {
  if (!hasValidRange(spread, args.sheet.name())) {
    args.cancel = true;
  }
});
let old = GC.Spread.Sheets.Commands.clear.execute;
GC.Spread.Sheets.Commands.clear.execute = function (spread, options, isUndo) {
  if (hasValidRange(spread, options.sheetName))
    return old.apply(this, arguments);
  return false;
};

function hasValidRange(spread, sheetName) {
  let sheet = spread.getSheetFromName(sheetName);
  let selections = sheet.getSelections();
  let hasRowCOlHeader = selections.filter((x) => x.col === -1 || x.row === -1);
  return hasRowCOlHeader.length ? false : true;
}

Regards,

Avinash

ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 8 August 2022, 8:23 pm EST

Hi avinash.pathak,

Your solution is using to cancel event delete content of row.

My expectation is: delete content of all normal cells (A1, B1, C1, D1, F1, G1,…) but keep content of locked cell (E1).

How can i do that ?

avinash.pathak

Post Options:
Link
Copy

Posted 9 August 2022, 4:57 pm EST

HI,

For this, You may need to create your own custom command and register it with delete key.

Please refer to the following links and code snippet that explains the same.

var sheet = spread.getActiveSheet();
		var command = {
			canUndo: true,
			execute: function(spread, options, isUndo) {
				var Commands = GC.Spread.Sheets.Commands;
				if (isUndo) {
					Commands.undoTransaction(spread, options);
					return true;
				} else {
					Commands.startTransaction(spread, options);
					spread.suspendPaint();
					var selections = options.selections || spread.getActiveSheet().getSelections();
					var value = options.backColor;
					selections.forEach(function(sel) {
						sheet.getRange(sel.row, sel.col, sel.rowCount, sel.colCount).backColor("red");
					});
					spread.resumePaint();
					Commands.endTransaction(spread, options);
					return true;
				}
			}
		};
		var selections = sheet.getSelections();
		var commandManager = spread.commandManager();
		commandManager.register('changeBackColor', command);
		commandManager.execute({
			cmd: 'changeBackColor',
			sheetName: spread.getSheet(0).name(),
			selections: selections,
			backColor: 'rgb(130, 188, 0)'
		});

        commandManager.setShortcutKey("",GC.Spread.Commands.Key.del);
        commandManager.setShortcutKey('changeBackColor', GC.Spread.Commands.Key.del, false, false, false, false);

demo: https://www.grapecity.com/spreadjs/demos/features/worksheet/actions/custom-action#demo_source_name

setShortcutKeyMethod: https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.Commands.CommandManager~setShortcutKey.html?highlight=setshortcutkey%2C

Regards,

Avinash

ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 18 September 2022, 3:08 pm EST

Hi avinash.pathak,

I has implemented your solution and it’s working well. Thank you very much.

But i has another problem:

1. On the above row (row 0 with cell E1 is locked), i click row header and Ctrl + C.

2. I click row header of row 1 and click Ctrl + V.

Actual result: InvalidOperation is fired with message “The cell you are trying to change is protected and therefore read-only.”

My expectation:

1. Content of all normal cells (A1, B1, C1, D1, F1, G1,…) is pasted sucessfully.

2. Content of locked cell (E1) isn’t changed.

How can i do it ?
avinash.pathak
- Post Options:
- Link
  Copy
Posted 19 September 2022, 8:54 pm EST

Hi,

This is expected behavior. Pasting can not happen if the cell range contains the locked cell. If you want to change this behavior you may need to change it inside the ClipboardPasted Event.

Please refer to the following sample and let me know if you face any issues.

sample: https://codesandbox.io/s/jovial-chebyshev-2zpejx?file=/src/index.js

Regards,

Avinash
ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 30 September 2022, 4:01 am EST

Hi avinash.pathak,

I has applied your solution and it’s working well. Thank you very much.

But i’m facing another problem: in my web application, my customer want to display 2 SpreadJS to show 2 type of data.

After i applied your solution, everytime i press Ctrl + V to paste to any SpreadJS, paste is executed 2 times.

Please check my sample source code and give me solution.

SpreadJS_PasteWithTwoSpreadJS.zip

avinash.pathak

Post Options:
Link
Copy

Posted 2 October 2022, 4:15 pm EST

Hi,

The issue is you have called initSpread method 2 times. Please refer to the following code snippet and let me know if you face any problems.


 $(document).ready(function () {
            let old = GC.Spread.Sheets.Commands.paste.execute;
            GC.Spread.Sheets.Commands.paste.execute = function (e, context) {
                alert("Paste is executing...");
                old.apply(this, arguments);
            };
            var spread1 = new GC.Spread.Sheets.Workbook($("#ss1")[0]);
            initSpread(spread1);
            var spread2 = new GC.Spread.Sheets.Workbook($("#ss2")[0]);
            initSpread(spread2);
        });

        function initSpread(spread) {
            let sheet = spread.getActiveSheet();
        }

Regards,

Avinash

ngocnguyen09910060

Post Options:
Link
Copy

Posted 12 October 2022, 6:23 pm EST - Updated 12 October 2022, 6:24 pm EST

Hi avinash.pathak,

I has implemented this solution and it’s working well.

var sheet = spread.getActiveSheet();
		var command = {
			canUndo: true,
			execute: function(spread, options, isUndo) {
				var Commands = GC.Spread.Sheets.Commands;
				if (isUndo) {
					Commands.undoTransaction(spread, options);
					return true;
				} else {
					Commands.startTransaction(spread, options);
					spread.suspendPaint();
					var selections = options.selections || spread.getActiveSheet().getSelections();
					var value = options.backColor;
					selections.forEach(function(sel) {
						sheet.getRange(sel.row, sel.col, sel.rowCount, sel.colCount).backColor("red");
					});
					spread.resumePaint();
					Commands.endTransaction(spread, options);
					return true;
				}
			}
		};
		var selections = sheet.getSelections();
		var commandManager = spread.commandManager();
		commandManager.register('changeBackColor', command);
		commandManager.execute({
			cmd: 'changeBackColor',
			sheetName: spread.getSheet(0).name(),
			selections: selections,
			backColor: 'rgb(130, 188, 0)'
		});

        commandManager.setShortcutKey("",GC.Spread.Commands.Key.del);
        commandManager.setShortcutKey('changeBackColor', GC.Spread.Commands.Key.del, false, false, false, false);

But i has another problem:

Content of cell A1: “Hello SpreadJS”
User double click cell A1.
User select text “Hello”.
User press “Delete” key.

In default SpreadJS, “Hello” is deleted. But after apply your solution, nothing happend.

How can i fix it ?

ngocnguyen09910060

Post Options:
Link
Copy

Posted 12 October 2022, 6:31 pm EST

Hi avinash.pathak,

I has implemented this solution and it’s working well.

var command = {
        canUndo: true,
        execute: function(spread: any, options: any, isUndo: any) {
          var Commands = GC.Spread.Sheets.Commands;
          if (isUndo) {
            Commands.undoTransaction(spread, options);
            return true;
          } else {
            options.cmd = 'pressDel';
            Commands.startTransaction(spread, options);
            spread.suspendPaint();

            var selections = options.selections || spread.getActiveSheet().getSelections();
            selections.forEach((selection: any) => {
              let { row, col, rowCount, colCount } = selection;
              row = row < 0 ? 0 : row;
              col = col < 0 ? 0 : col;
              rowCount = rowCount < 0 ? sheet.getRowCount() : rowCount;
              colCount = colCount < 0 ? sheet.getColumnCount() : colCount;
              // Set value of normal cells to blank.
              for (let i = row; i < row + rowCount; i++) {
                for (let j = col; j < col + colCount; j++) {
                  if (!sheet.getCell(i, j).locked()) {
                    sheet.setValue(i, j, '');
                  }
                }
              }
            });

            spread.resumePaint();
            Commands.endTransaction(spread, options);
            return true;
          }
        }
      }
      let commandManager = spread.commandManager();
      commandManager.register('pressDel', command);
      commandManager.setShortcutKey("",GC.Spread.Commands.Key.del);
      commandManager.setShortcutKey('pressDel', GC.Spread.Commands.Key.del, false, false, false, false);

But i has another problem:

Content of cell A1: “Hello SpreadJS”
User double click cell A1.
User select text “Hello”.
User press Delete key.

=> In default SpreadJS, “Hello” is deleted. But after i custom Delete command, nothing happen.

How can i fix it ?

avinash.pathak

Post Options:
Link
Copy

Posted 13 October 2022, 4:48 pm EST

HI,

This is expected if you do not want to override the delete key in edit mode you could use the following code snippet.


  var command = {
    canUndo: true,
    execute: function (spread, options, isUndo) {
      var Commands = GC.Spread.Sheets.Commands;
      if (isUndo) {
        Commands.undoTransaction(spread, options);
        return true;
      } else {
        options.cmd = "pressDel";
        Commands.startTransaction(spread, options);
        if (!sheet.isEditing()) {
          spread.suspendPaint();
          let sheet = spread.getActiveSheet();
          var selections =
            options.selections || spread.getActiveSheet().getSelections();
          selections.forEach((selection) => {
            let { row, col, rowCount, colCount } = selection;
            row = row < 0 ? 0 : row;
            col = col < 0 ? 0 : col;
            rowCount = rowCount < 0 ? sheet.getRowCount() : rowCount;
            colCount = colCount < 0 ? sheet.getColumnCount() : colCount;
            // Set value of normal cells to blank.
            for (let i = row; i < row + rowCount; i++) {
              for (let j = col; j < col + colCount; j++) {
                if (!sheet.getCell(i, j).locked()) {
                  sheet.setValue(i, j, "");
                }
              }
            }
          });

          spread.resumePaint();
        } else {
          return false;
        }
        Commands.endTransaction(spread, options);
        return true;
      }
    }
  };
  let commandManager = spread.commandManager();
  commandManager.register("pressDel", command);
  commandManager.setShortcutKey("", GC.Spread.Commands.Key.del);
  commandManager.setShortcutKey(
    "pressDel",
    GC.Spread.Commands.Key.del,
    false,
    false,
    false,
    false
  );

sample: https://codesandbox.io/s/crazy-cray-4jr6o1?file=/src/index.js

regards,

Avinash

ngocnguyen09910060
- Post Options:
- Link
  Copy
Posted 18 October 2022, 2:25 pm EST

Hi avinash.pathak,

I can use Delete key to delete content of cell after apply your suggestion.

But i 'm facing another problem:

*Content of cell A1: “Hello SpreadJS”

*User double click cell A1.

*User select text “Hello”.

*User press Delete key.

*User focus out cell A1.

*User press Ctrl + Z.

In default SpreadJS, “Hello SpreadJS” is undo successfully. But on your solution, nothing happen. How can i fix it ?
avinash.pathak
- Post Options:
- Link
  Copy
Posted 18 October 2022, 8:41 pm EST

Hi,

Please refer to the following updated sample and let me know if you still face any issues.

sample: https://codesandbox.io/s/crazy-cray-4jr6o1?file=/src/index.js

regards,

Avinash

Please login to reply to thread

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.