与Script Lab共享

Merge table by key.yaml

name: Merge table by key
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))
        $('#add-sheet').click(() => tryCatchExcelRun(ctx => addSheet(ctx, null)))

        // code here
        async function main(ctx) {
            // load the data of the first sheet
            let dataSheet = ctx.workbook.worksheets.getFirst()
            let range = dataSheet.getUsedRange()
            range.load('values')
            await ctx.sync()
            console.log(`used range row count is ${range.values.length}`)

            // create a table on the new sheet
            let newSheet = await addSheet(ctx, null)
            let table = newSheet.tables.add('A1:B1',
                true /* true means has headers */)
            table.name = 'keyTable'
            table.getHeaderRowRange().values = [['项目编码', '项目名称']]

            let keyMap = {}
            let tableValues = []
            let dumpCount = 0
            let values = range.values // slice 1: ignore the header row
            values.forEach((row, i) => {
                if (i == 0) return
                let id = row[0] + '@' + row[1]
                if (keyMap[id]) {
                    dumpCount++
                    //dataSheet.getRange(`A${i + 2}`, `B${i + 2}`).format.fill.color = "yellow";
                    return
                } else {
                    keyMap[id] = true
                }

                tableValues.push(row)
                // 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()

            // put the unique datas into the table
            table.rows.add(null, tableValues)

            // 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() { return `new${_uniqueNameIndex++}` }
    language: typescript
template:
    content: |
        <button id="run" class="ms-Button">
            <span class="ms-Button-label">Run main</span>
        </button>

        <button id="add-sheet" class="ms-Button">
            <span class="ms-Button-label">Add sheet</span>
        </button>
    language: html
style:
    content: |
        /* Your style goes here */
    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