Copy Paste Excel Style

SpreadJS supports copying styles and spans from Excel then pasting to sheets in SpreadJS. You can also copy styles and spans from sheets in SpreadJS and paste to Excel. This makes it easy for users to copy and paste data and styles to/from Excel and Spread without having to import/export entire sheets.

<p>If you want use this feature, just set the workbook <strong>allowCopyPasteExcelStyle</strong> option to true. For example:</p> <pre><code class="hljs js language-js"> workbook.options.allowCopyPasteExcelStyle = <span class="hljs-literal">true</span>; </code></pre> <p>Then you can copy the style to or from Excel. <strong>The default value is true</strong></p> <p>The <strong>copyData</strong> event argument has been changed from string to <strong>Object{text,html}</strong> in <strong>ClipboardChanging and ClipboardChanged</strong>.</p> <p>The <strong>pasteData{text,html}</strong> event argument has been added to <strong>ClipboardPasting and ClipboardPasted</strong>.</p>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); var sheet = spread.getActiveSheet(); sheet.options.allowCellOverflow = true; sheet.getCell(0, 0).value("Copy the following data then paste to Excel."); loadSaleDataAnalysisTable(sheet, 1, 0, true); spread.resumePaint(); var allowCopyPasteExcelStyle = document.getElementById('allowCopyPasteExcelStyle'); allowCopyPasteExcelStyle.checked=spread.options.allowCopyPasteExcelStyle; allowCopyPasteExcelStyle.addEventListener('change',function () { spread.options.allowCopyPasteExcelStyle = allowCopyPasteExcelStyle.checked; }); } function loadSaleDataAnalysisTable(sheet, startRow, startCol, haveTitle) { var spread = sheet.parent; if (!spread) { return; } spread.suspendPaint(); if (startRow === undefined) { startRow = 0; } if (startCol === undefined) { startCol = 0; } if (sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport) - startRow < 19 || sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport) - startCol < 10) { return; } spread.options.referenceStyle = GC.Spread.Sheets.ReferenceStyle.r1c1; if (haveTitle) { sheet.addSpan(startRow + 0, startCol + 1, 1, 10); sheet.setRowHeight(startRow + 0, 40); sheet.setValue(startRow + 0, startCol + 1, "Sale Data Analysis"); sheet.getCell(startRow + 0, startCol + 1).font("bold 30px arial"); sheet.getCell(startRow + 0, startCol + 1).vAlign(GC.Spread.Sheets.VerticalAlign.center); } sheet.addSpan(startRow + 1, startCol + 1, 1, 3); sheet.setValue(startRow + 1, startCol + 1, "Store"); sheet.addSpan(startRow + 1, startCol + 4, 1, 7); sheet.setValue(startRow + 1, startCol + 4, "Goods"); sheet.addSpan(startRow + 2, startCol + 1, 1, 2); sheet.setValue(startRow + 2, startCol + 1, "Area"); sheet.addSpan(startRow + 2, startCol + 3, 2, 1); sheet.setValue(startRow + 2, startCol + 3, "ID"); sheet.addSpan(startRow + 2, startCol + 4, 1, 2); sheet.setValue(startRow + 2, startCol + 4, "Fruits"); sheet.addSpan(startRow + 2, startCol + 6, 1, 2); sheet.setValue(startRow + 2, startCol + 6, "Vegetables"); sheet.addSpan(startRow + 2, startCol + 8, 1, 2); sheet.setValue(startRow + 2, startCol + 8, "Foods"); sheet.addSpan(startRow + 2, startCol + 10, 2, 1); sheet.setValue(startRow + 2, startCol + 10, "Total"); sheet.setValue(startRow + 3, startCol + 1, "State"); sheet.setValue(startRow + 3, startCol + 2, "City"); sheet.setValue(startRow + 3, startCol + 4, "Grape"); sheet.setValue(startRow + 3, startCol + 5, "Apple"); sheet.setValue(startRow + 3, startCol + 6, "Potato"); sheet.setValue(startRow + 3, startCol + 7, "Tomato"); sheet.setValue(startRow + 3, startCol + 8, "Sandwich"); sheet.setValue(startRow + 3, startCol + 9, "Hamburger"); sheet.addSpan(startRow + 4, startCol + 1, 7, 1); sheet.addSpan(startRow + 4, startCol + 2, 3, 1); sheet.addSpan(startRow + 7, startCol + 2, 3, 1); sheet.addSpan(startRow + 10, startCol + 2, 1, 2); sheet.setValue(startRow + 10, startCol + 2, "Sub Total:"); sheet.addSpan(startRow + 11, startCol + 1, 7, 1); sheet.addSpan(startRow + 11, startCol + 2, 3, 1); sheet.addSpan(startRow + 14, startCol + 2, 3, 1); sheet.addSpan(startRow + 17, startCol + 2, 1, 2); sheet.setValue(startRow + 17, startCol + 2, "Sub Total:"); sheet.addSpan(startRow + 18, startCol + 1, 1, 3); sheet.setValue(startRow + 18, startCol + 1, "Total:"); sheet.setValue(startRow + 4, startCol + 1, "NC"); sheet.setValue(startRow + 4, startCol + 2, "Raleigh"); sheet.setValue(startRow + 7, startCol + 2, "Charlotte"); sheet.setValue(startRow + 4, startCol + 3, "001"); sheet.setValue(startRow + 5, startCol + 3, "002"); sheet.setValue(startRow + 6, startCol + 3, "003"); sheet.setValue(startRow + 7, startCol + 3, "004"); sheet.setValue(startRow + 8, startCol + 3, "005"); sheet.setValue(startRow + 9, startCol + 3, "006"); sheet.setValue(startRow + 11, startCol + 1, "PA"); sheet.setValue(startRow + 11, startCol + 2, "Philadelphia"); sheet.setValue(startRow + 14, startCol + 2, "Pittsburgh"); sheet.setValue(startRow + 11, startCol + 3, "007"); sheet.setValue(startRow + 12, startCol + 3, "008"); sheet.setValue(startRow + 13, startCol + 3, "009"); sheet.setValue(startRow + 14, startCol + 3, "010"); sheet.setValue(startRow + 15, startCol + 3, "011"); sheet.setValue(startRow + 16, startCol + 3, "012"); for (var i = 4; i < 10; i++) { sheet.setFormula(startRow + 10, startCol + i, "=SUM(R[-6]C:R[-1]C"); sheet.setFormula(startRow + 17, startCol + i, "=SUM(R[-6]C:R[-1]C"); sheet.setFormula(startRow + 18, startCol + i, "=R[-8]C + R[-1]C"); } sheet.setFormula(startRow + 18, startCol + 10, "=R[-8]C + R[-1]C"); for (var i = startRow; i < 14 + startRow; i++) { sheet.setFormula(4 + i, startCol + 10, "=SUM(RC[-6]:RC[-1])"); } sheet.getRange(startRow + 1, startCol + 1, 3, 10).backColor("#D9D9FF"); sheet.getRange(startRow + 4, startCol + 1, 15, 3).backColor("#D9FFD9"); sheet.getRange(startRow + 1, startCol + 1, 3, 10).hAlign(GC.Spread.Sheets.HorizontalAlign.center); sheet.getRange(startRow + 1, startCol + 1, 18, 10).setBorder(new GC.Spread.Sheets.LineBorder("Black", GC.Spread.Sheets.LineStyle.thin), {all: true}); sheet.getRange(startRow + 4, startCol + 4, 3, 6).setBorder(new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.dotted), {innerHorizontal: true}); sheet.getRange(startRow + 7, startCol + 4, 3, 6).setBorder(new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.dotted), {innerHorizontal: true}); sheet.getRange(startRow + 11, startCol + 4, 3, 6).setBorder(new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.dotted), {innerHorizontal: true}); sheet.getRange(startRow + 14, startCol + 4, 3, 6).setBorder(new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.dotted), {innerHorizontal: true}); fillSampleData(sheet, new GC.Spread.Sheets.Range(startRow + 4, startCol + 4, 6, 6)); fillSampleData(sheet, new GC.Spread.Sheets.Range(startRow + 11, startCol + 4, 6, 6)); function fillSampleData(sheet, range) { for (var i = 0; i < range.rowCount; i++) { for (var j = 0; j < range.colCount; j++) { sheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300)); } } }; spread.options.referenceStyle = GC.Spread.Sheets.ReferenceStyle.a1; spread.resumePaint(); }
<!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="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"> <div class="option-row"> <label> <input type="checkbox" id="allowCopyPasteExcelStyle">allowCopyPasteExcelStyle </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; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }