与Script Lab共享

merge two table.yaml

name: merge two table
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) {
            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++
                        // 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]), '', '', tableIndex]
                    newRow[1 + tableIndex] = row[1]
                    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:D1', true /* true means has headers */)
            table.name = 'table' + uniqueName(true)
            table.getHeaderRowRange().values = [['key', ...sheetNames, 'check']]

            let keyMap = {}
            let tableValues = []
            for (let index = 0; index < ranges.length; ++index) {
                await mergeSheet(ctx, keyMap, tableValues, index, ranges[index])
            }

            // 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