Created
May 30, 2018 12:26
-
-
Save GZShi/f1fb45376216ec987c8736e575289a29 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 3 | |
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() | |
} | |
async function mergedTableValues( | |
ctx, range1, range2, | |
newTable, | |
table1KeyIndexs, table2KeyIndexs, | |
table2ValueIndexs) | |
{ | |
let keyMap = {} | |
range1.values.forEach((row, rowIndex) => { | |
if (rowIndex == 0) return | |
let key = table1KeyIndexs.map(index => row[index]).join('&&') | |
if (key in keyMap) { | |
keyMap[key].push(rowIndex) | |
} else { | |
keyMap[key] = [rowIndex] | |
} | |
}) | |
let mappedValues = range1.values.slice(1).map(d => table2ValueIndexs.map(index => '')) | |
let unmappedValues = [] | |
console.log('range2 data count: ', range2.values.length, range2.values[0]) | |
console.log('table2ValueIndexs', table2ValueIndexs) | |
range2.values.forEach((row, rowIndex) => { | |
if (rowIndex == 0) return | |
let key = table2KeyIndexs.map(index => row[index]).join('&&') | |
let value = table2ValueIndexs.map(index => row[index]) | |
if (key in keyMap) { | |
keyMap[key].forEach(mappedIndex => { | |
mappedValues[mappedIndex] = value | |
}) | |
} else { | |
unmappedValues.push(value) | |
} | |
}) | |
mappedValues.shift() // delete first header row | |
let tableValues = [...mappedValues, ...unmappedValues] | |
console.log('tableValue[0]', tableValues[0]) | |
let packData = [] | |
for (let i = 0; i < tableValues.length; ++i) { | |
packData.push(tableValues[i]) | |
if (i % 1000 == 0) { | |
console.log(`write data: ${i}/${tableValues.length} (${(100*i/tableValues.length).toFixed(2)}%)`) | |
newTable.rows.add(null, packData) | |
await ctx.sync() | |
packData = [] | |
} | |
} | |
if (packData.length > 0) { | |
console.log(`write data: ${tableValues.length}/${tableValues.length} (${(100).toFixed(2)}%)`) | |
newTable.rows.add(null, packData) | |
await ctx.sync() | |
} | |
console.log(`write finished`) | |
} | |
function charAfterN(char, n) { | |
let alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('') | |
let charIndex = alpha.indexOf(char) | |
return alpha[charIndex + n] | |
} | |
async function createNewTableInSheet(ctx, sheet, leftTopName, headers) { | |
let rightTopName = leftTopName.replace(/[A-Z]/, char => charAfterN(char, headers.length - 1)) | |
let table = sheet.tables.add(`${leftTopName}:${rightTopName}`, true) | |
//table.name = 'table-' + uniqueName(true) | |
table.getHeaderRowRange().values = [headers] | |
await ctx.sync() | |
return table | |
} | |
// 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 sheet1 = ctx.workbook.worksheets.getItem(sheetNames[0]) | |
let table1 = await createNewTableInSheet(ctx, sheet1, 'M1', | |
['Sheet2-A', 'Sheet2-B', 'Sheet2-C', 'Sheet2-D']) | |
let table2 = await createNewTableInSheet(ctx, sheet1, 'Q1', | |
['Sheet2-A', 'Sheet2-B', 'Sheet2-C', 'Sheet2-D'] | |
) | |
await mergedTableValues(ctx, ranges[0], ranges[1], table1, | |
// keys | |
[1, 2], [0, 1], | |
// table2 values | |
[0, 1, 2, 3] | |
) | |
console.log('task(1/2) done!') | |
await mergedTableValues(ctx, ranges[0], ranges[1], table2, | |
// keys | |
[3, 4], [0, 1], | |
// table2 values | |
[0, 1, 2, 3] | |
) | |
console.log('task(2/2) done!') | |
// auto fit column | |
// if (Office.context.requirements.isSetSupported('ExcelApi', 1.2)) { | |
// newSheet.getUsedRange().format.autofitColumns() | |
// newSheet.getUsedRange().format.autofitRows() | |
// } | |
sheet1.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"> | |
<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