Copy Paste Enhancement

SpreadJS provides an option to specify which headers are included when data is copied or pasted.

Just set the workbook copyPasteHeaderOptions option to specify which headers are included. For example: GC.Spread.Sheets.CopyPasteHeaderOptions: noHeaders: 0 rowHeaders: 1 columnHeaders: 2, allHeaders: 3 SpreadJS supports copying multiple non-contiguous cells then pasting to a single range. The following cases are supported: same row and row count same column and column count SpreadJS ignores filtered rows when copying.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { var sd = data; if (!spread) { return; } spread.suspendPaint(); var sheet = spread.getActiveSheet(); setDataWithHeader(sheet); sheet.setValue(5, 0, "Copy non-contiguous cells of all names(A8:A14) and all prices(C8:C14) then paste to somewhere(such as F5)."); loadGoodListTable(sheet, 6, 0); var rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(8, 0, 6, 4)); sheet.rowFilter(rowFilter); rowFilter.addFilterItem(1, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.notEqualsTo, expected: "Vegetable" })); rowFilter.filter(1); sheet.defaults.colWidth = 100; spread.resumePaint(); var copyPasteHeaderOptions = document.getElementById('copyPasteHeaderOptions'); copyPasteHeaderOptions.value=spread.options.copyPasteHeaderOptions; copyPasteHeaderOptions.addEventListener('change',function() { spread.options.copyPasteHeaderOptions = parseInt(copyPasteHeaderOptions.value); }); } function setDataWithHeader(sheet) { sheet.getRange(0, 0, 1, 5, 1).backColor("rgb(0, 176, 240)"); sheet.getRange(0, 0, 1, 5, 1).foreColor("white"); sheet.setValue(0, 0, 'MON', 1); sheet.setValue(0, 1, 'TUE', 1); sheet.setValue(0, 2, 'WED', 1); sheet.setValue(0, 3, 'THU', 1); sheet.setValue(0, 4, 'FRI', 1); sheet.setValue(0, 0, '8:00', 2); sheet.setValue(1, 0, '9:00', 2); sheet.setValue(2, 0, '10:00', 2); sheet.setValue(3, 0, '11:00', 2); sheet.setRowHeight(0, 45); sheet.setRowHeight(1, 45); sheet.setRowHeight(2, 45); sheet.setRowHeight(3, 45); sheet.getRange(0, 0, 4, 1).backColor("rgb(255, 192, 0)"); sheet.getRange(0, 1, 4, 1).backColor("rgb(255, 255, 0)"); sheet.getRange(0, 2, 4, 1).backColor("rgb(146, 208, 80)"); sheet.getRange(0, 3, 4, 1).backColor("rgb(0, 176, 80)"); sheet.getRange(0, 4, 4, 1).backColor("rgb(0, 176, 240)"); sheet.setValue(0, 0, 'French'); sheet.setValue(0, 2, 'French'); sheet.setValue(0, 4, 'French'); sheet.setValue(1, 1, 'Art History'); sheet.setValue(1, 3, 'Art History'); sheet.setValue(2, 0, 'Math'); sheet.setValue(2, 2, 'Math'); sheet.setValue(2, 4, 'Math'); sheet.setValue(3, 1, 'Programming'); sheet.setValue(3, 3, 'Programming'); } function loadGoodListTable(sheet, startRow, startCol) { if (startRow === undefined) { startRow = 0; } if (startCol === undefined) { startCol = 0; } if (sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport) - startRow < 8 || sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport) - startCol < 3) { return; } // sheet.addSpan(startRow + 0, startCol + 0, 1, 4); sheet.setRowHeight(startRow + 0, 40); sheet.setValue(startRow + 0, startCol + 0, "Goods List"); var title = sheet.getCell(startRow + 0, startCol + 0); title.font("bold 30px arial"); title.vAlign(GC.Spread.Sheets.VerticalAlign.center); sheet.getRange(startRow + 0, startCol + 0, 1, 4).backColor("#D1CBC5"); sheet.setColumnWidth(startCol + 0, 100); sheet.setColumnWidth(startCol + 1, 100); sheet.setColumnWidth(startCol + 2, 100); sheet.setColumnWidth(startCol + 3, 120); sheet.getRange(startRow + 0, startCol + 0, 8, 4).setBorder(new GC.Spread.Sheets.LineBorder("Black", GC.Spread.Sheets.LineStyle.thin), {all: true}); sheet.setValue(startRow + 1, startCol + 0, "Name"); sheet.setValue(startRow + 1, startCol + 1, "Category"); sheet.setValue(startRow + 1, startCol + 2, "Price"); sheet.setValue(startRow + 1, startCol + 3, "Shopping Place"); for (var i = 0; i < 4; i++) { sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial"); } sheet.setValue(startRow + 2, startCol + 0, "Apple"); sheet.setValue(startRow + 3, startCol + 0, "Potato"); sheet.setValue(startRow + 4, startCol + 0, "Tomato"); sheet.setValue(startRow + 5, startCol + 0, "Sandwich"); sheet.setValue(startRow + 6, startCol + 0, "Hamburger"); sheet.setValue(startRow + 7, startCol + 0, "Grape"); sheet.setValue(startRow + 2, startCol + 1, "Fruit"); sheet.setValue(startRow + 3, startCol + 1, "Vegetable"); sheet.setValue(startRow + 4, startCol + 1, "Vegetable"); sheet.setValue(startRow + 5, startCol + 1, "Food"); sheet.setValue(startRow + 6, startCol + 1, "Food"); sheet.setValue(startRow + 7, startCol + 1, "Fruit"); sheet.setValue(startRow + 2, startCol + 2, 1.00); sheet.setValue(startRow + 3, startCol + 2, 2.01); sheet.setValue(startRow + 4, startCol + 2, 3.21); sheet.setValue(startRow + 5, startCol + 2, 2); sheet.setValue(startRow + 6, startCol + 2, 2); sheet.setValue(startRow + 7, startCol + 2, 4); var myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00"); for (var i = 2; i < 8; i++) { sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter); } sheet.setValue(startRow + 2, startCol + 3, "Wal-Mart"); sheet.setValue(startRow + 3, startCol + 3, "Other"); sheet.setValue(startRow + 4, startCol + 3, "Other"); sheet.setValue(startRow + 5, startCol + 3, "Wal-Mart"); sheet.setValue(startRow + 6, startCol + 3, "Wal-Mart"); sheet.setValue(startRow + 7, startCol + 3, "Other"); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/copyPasteEnhancement.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> Use this drop-down to select which headers are copied/pasted when data is copied/pasted in the Spread component. <div class="option-row"> <p>Select an option from the drop-down, then select full rows or columns on the worksheet, such as 'FRI' column or '8:00' row, then copy and paste.</p> <label> copyPasteHeaderOptions <select id="copyPasteHeaderOptions"> <option value="0">noHeaders</option> <option value="1">rowHeaders</option> <option value="2">columnHeaders</option> <option value="3">allHeaders</option> </select> </label> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } select { padding: 4px 6px; width: 100%; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }