javascript Office脚本花费的时间较长

f45qwnt8  于 2023-01-04  发布在  Java
关注(0)|答案(1)|浏览(146)

当使用Office脚本在电力自动化它需要很长的时间,并在花了很长时间后,它得到失败的运行脚本步骤。并给我的问题,因为[68,62]调用读取方法内的循环可能会导致缓慢的脚本性能。我必须更新范围。它的工作,但它只更新最后一行的工作表,我必须更新最后一行的每个项目

function main(workbook: ExcelScript.Workbook, nonGDOWorksheetName: string, gdoWorksheetName: string) {

let projectIDLength = 12

nonGDOSheet.getRange('A:A').unmerge();
let nonGDORange = nonGDOSheet.getUsedRange();
let nonGDOValues = nonGDORange.getValues();

let gdoSheet = workbook.getWorksheet('GDO Dollarized');

gdoSheet.getRange("GG:IN")
    .delete(ExcelScript.DeleteShiftDirection.left);

const gdoRange = gdoSheet.getUsedRange();
const gdoValues = gdoRange.getValues();

const blankRowsBeforeUsedRangeGDO = 2
const blankRowsBeforeUsedRangeNonGDO = 2
const startingRow = 5
const summationCellIndex = 7
const projectIndex = 0

let currentproject: string;
let previousProject: string;
   
for (let row = startingRow; row < nonGDOValues.length; row++) {
    
    currentproject = String(nonGDOValues[row][projectIndex]);
    
    if (currentproject != 'Σ' && currentproject.length == projectIDLength) {
       
        if (nonGDORange.getCell(row, summationCellIndex).getValue() == '') {
            
            previousProject = String(nonGDOValues[row - 1][projectIndex]);
           
            row =
                addGDOData(previousProject, gdoRange, gdoValues, blankRowsBeforeUsedRangeGDO, blankRowsBeforeUsedRangeNonGDO, nonGDOSheet, gdoSheet, row, false)

        }
    }
    
    nonGDORange = nonGDOSheet.getUsedRange();
    nonGDOValues = nonGDORange.getValues();
}

nonGDORange = nonGDOSheet.getUsedRange();
nonGDOValues = nonGDORange.getValues();

let lastRow = nonGDOValues.length - blankRowsBeforeUsedRangeNonGDO

let lastProject: String = String(nonGDOValues[lastRow][projectIndex]);

addGDOData(lastProject, gdoRange, gdoValues, blankRowsBeforeUsedRangeGDO, blankRowsBeforeUsedRangeNonGDO, nonGDOSheet, gdoSheet, lastRow, true)

return;}

function getGDORange(projectName: String, gdoRange: ExcelScript.Range, gdoValues: (string | number | boolean)[][]) {

let st = -1, en = 0;

for (let row = 4; row < gdoValues.length; row++) {

    let project: String = String(gdoValues[row][0]);

    if (project != 'Σ') {

        if (project == projectName) {

            if (gdoRange.getCell(row, 7).getValue() !== '') {

                if (st == -1) {
                    st = row;
                    en = st
                }
                en = row;
            }
        }
    }
}

return {
    start: st,
    end: en
} }

function addGDOData(project: String, gdoRange: ExcelScript.Range, gdoValues: (string | number | boolean)[][], blankRowsBeforeUsedRangeGDO: number, blankRowsBeforeUsedRangeNonGDO: number, nonGDOSheet: ExcelScript.Worksheet, gdoSheet: ExcelScript.Worksheet, row: number, isLastRow: boolean) {
let GDOSampleRangeObj = {
    start: 0,
    end: 0
}

GDOSampleRangeObj = getGDORange(project, gdoRange, gdoValues)

if (GDOSampleRangeObj.start == -1) {
    return row;
}

let GDOSampleRange = GDOSampleRangeObj.start + (blankRowsBeforeUsedRangeGDO) + ':' + (GDOSampleRangeObj.end + blankRowsBeforeUsedRangeGDO)

let GDORangeDifference = GDOSampleRangeObj.end - GDOSampleRangeObj.start

let nonGDOSampleRange: string;

if (!isLastRow) {
    nonGDOSampleRange = (row + blankRowsBeforeUsedRangeNonGDO) + ':' + (row + GDORangeDifference + blankRowsBeforeUsedRangeNonGDO)
} else {
    nonGDOSampleRange = (row + blankRowsBeforeUsedRangeNonGDO + 2) + ':' + (row + GDORangeDifference + blankRowsBeforeUsedRangeNonGDO + 1)
}

nonGDOSheet.getRange(nonGDOSampleRange).insert(ExcelScript.InsertShiftDirection.down);

nonGDOSheet.getRange(nonGDOSampleRange).copyFrom(gdoSheet.getRange(GDOSampleRange));

row = row + GDORangeDifference + blankRowsBeforeUsedRangeNonGDO - 1;

return row }

我已经尝试作为保持对循环内更新范围的注解

laik7k3q

laik7k3q1#

问题出在这一行:

if (nonGDORange.getCell(row, summationCellIndex).getValue() == '')

getValue()调用将返回并重新读取Excel工作簿,这需要较慢的工作簿会话调用。您无需执行此操作;在nonGDOValues中已经有了相同的值(除非我误解了什么,并且这些值在您开始阅读之后发生了变化)。

nonGDOValues[row][summationCellIndex]

相关问题