Last active
May 29, 2018 15:15
-
-
Save GZShi/b0ac5fbeb9c317548c6913fbfd65e5cb to your computer and use it in GitHub Desktop.
与Script Lab共享
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: merge two table 2 | |
description: '' | |
author: GZShi | |
host: EXCEL | |
api_set: {} | |
script: | |
content: |- | |
/** Default helper for invoking an action and handling errors. */ | |
async function tryCatchExcelRun(callback) { | |
try { | |
await Excel.run(callback) | |
} | |
catch (error) { | |
OfficeHelpers.UI.notify(error) | |
OfficeHelpers.Utilities.log(error) | |
} | |
} | |
$("#run").click(() => tryCatchExcelRun(main)) | |
async function loadSheetRange(ctx, name) { | |
let sheet = ctx.workbook.worksheets.getItem(name) | |
let range = sheet.getUsedRange() | |
range.load('values') | |
await ctx.sync() | |
return range | |
} | |
async function mergeSheet(ctx, keyMap, tableValues, tableIndex, range, sheetNames) { | |
let dumpCount = 0 | |
let values = range.values // slice 1: ignore the header row | |
values.forEach((row, i) => { | |
if (i == 0) return | |
if (row[0] == '' && row[1] == '') return | |
let id = row[0] | |
if (id in keyMap) { | |
let existIndex = keyMap[id] | |
let existRow = tableValues[existIndex] | |
if (existRow[1 + tableIndex]) { | |
dumpCount++ | |
if (existRow[1 + tableIndex] != row[1]) { | |
existRow[4 + tableIndex] = 'conflict' | |
} else { | |
// ignore this case | |
// existRow[4 + tableIndex] = 'same' | |
} | |
// todo | |
} else { | |
existRow[1 + tableIndex] = row[1] | |
existRow[3] = existRow[1] == existRow[2] ? 'same' : 'conflict' | |
} | |
} else { | |
keyMap[id] = tableValues.length | |
let newRow = [String(row[0])/*0*/, '', '', ''/*3*/, ''/*4*/, ''/*5*/] | |
newRow[1 + tableIndex] = row[1] | |
newRow[3] = sheetNames[tableIndex] | |
tableValues.push(newRow) | |
} | |
// record the progress per 1000 processed | |
if (i % 1000 === 0) { | |
console.log(`task running: ${i}/${values.length} ${(100 * i / values.length).toFixed(2)}%`) | |
} | |
}) | |
console.log(`dump keys count: ${dumpCount}`) | |
await ctx.sync() | |
} | |
// code here | |
async function main(ctx) { | |
// input from web ui | |
let sheetNames = [ | |
$('input#sheet1-name').val(), | |
$('input#sheet2-name').val() | |
] | |
console.log('sheet names', sheetNames) | |
let ranges = [] | |
for (let i = 0; i < sheetNames.length; ++i) { | |
ranges.push(await loadSheetRange(ctx, String(sheetNames[i]).trim())) | |
} | |
// create a table on the new sheet | |
let newSheetName = String($('input#new-sheet-name').val()).trim() | |
let newSheet = await addSheet(ctx, newSheetName) | |
let table = newSheet.tables.add('A1:F1', true /* true means has headers */) | |
table.name = 'table' + uniqueName(true) | |
table.getHeaderRowRange().values = [['key', ...sheetNames, 'check-BC', 'check-BB', 'check-CC']] | |
let keyMap = {} | |
let tableValues = [] | |
for (let index = 0; index < ranges.length; ++index) { | |
await mergeSheet(ctx, keyMap, tableValues, index, ranges[index], sheetNames) | |
} | |
// table.rows.add(null, [tableValues]) | |
table.rows.add(null, tableValues) | |
await ctx.sync() | |
// auto fit column | |
if (Office.context.requirements.isSetSupported('ExcelApi', 1.2)) { | |
newSheet.getUsedRange().format.autofitColumns() | |
newSheet.getUsedRange().format.autofitRows() | |
} | |
newSheet.activate() | |
await ctx.sync() | |
console.log('task done!') | |
} | |
// add sheet | |
async function addSheet(ctx, name) { | |
const sheets = ctx.workbook.worksheets | |
const sheet = sheets.add(name || uniqueName()) | |
sheet.load('name, position') | |
await ctx.sync() | |
console.log(`added sheet named ${sheet.name}`) | |
return sheet | |
} | |
let _uniqueNameIndex = 1 | |
function uniqueName(rand=false) { return `new${_uniqueNameIndex++}${rand?Math.random():''}` } | |
language: typescript | |
template: | |
content: | | |
<div class="section"> | |
<h3>Name of sheet1</h3> | |
<input placeholder="sheet1 name" id="sheet1-name" type="text"> | |
</div> | |
<div class="section"> | |
<h3>Name of sheet2</h3> | |
<input placeholder="sheet2 name" id="sheet2-name" type="text"> | |
</div> | |
<div class="section"> | |
<h3>Name of new sheet</h3> | |
<input placeholder="new sheet name" id="new-sheet-name" type="text"> | |
</div> | |
<div class="section"> | |
<button id="run" class="ms-Button"> | |
<span class="ms-Button-label">Run</span> | |
</button> | |
</div> | |
language: html | |
style: | |
content: |- | |
/* Your style goes here */ | |
h3 { | |
font-size: 14px; | |
padding: 0; | |
margin: 0; | |
} | |
div.section { | |
margin-top: 1em; | |
} | |
language: css | |
libraries: | | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.d.ts | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
jquery@3.1.1 | |
@types/jquery |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment