与Script Lab共享

merge two table 3.yaml

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