エクセルスタイルのコピーと貼り付け

SpreadJSでは、Excelのスタイルや連結セルをコピーしてSpread.Sheetsシートに貼り付けたり、Spread.Sheetsシートのスタイルや連結セルをコピーしてExcelに貼り付けたりできます。したがって、シート全体をインポートまたはエクスポートすることなく、ExcelとSpreadの間で、データとスタイルを簡単にコピーおよび貼り付けることができます。

<p>この機能を使用するには、workbookの<strong>allowCopyPasteExcelStyle</strong>オプションをtrueに設定するだけです。次に、例を示します。</p> <pre><code class="hljs js language-js"> workbook.options.allowCopyPasteExcelStyle = <span class="hljs-literal">true</span>; </code></pre> <p>これで、スタイルをSpreadとExcel間でコピーできます。<strong>デフォルトでは、このオプションはtrueに設定されています。</strong></p> <p><strong>ClipboardChanging</strong>および<strong>ClipboardChanged</strong>イベントにおいて、イベント引数<strong>copyData</strong>が文字列から<strong>Object{text,html}</strong>に変更されました。</p> <p>また、<strong>ClipboardPasting</strong>および<strong>ClipboardPasted</strong>イベントでは、イベント引数として<strong>pasteData{text,html}</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 name="spreadjs culture" content="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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; }