read write active excel window.ps1

$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')

$rng = $xl.Range("a1")
$lastRowNumber = $rng.End(($xlDown = -4121)).Row
$rngEntireColumn = $rng.Resize($lastRowNumber)

$newData = $rngEntireColumn.value2 -replace '.{15}\B', "`$0`n" -split "`n"

$safeArr = New-Object 'object[,]' $newData.Count, 1
$i = 0
foreach ($v in $newData) {
    $safeArr[$i++, 0] = $v
}

$rng = $xl.Range("d1").Resize($safeArr.Count)
$rng.ClearFormats()
$rng.NumberFormatLocal = "@"
$rng.value2 = $safeArr

test2.ps1

$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')

$rng = $xl.Range("a1").CurrentRegion
$arr = $rng.Value2
$rowCount = $arr.GetLength(0)
$dataRowCount = $rowCount - 2

$xl.Range("b3").Resize($dataRowCount).NumberFormatLocal = ""  # 设置第 2 列单元格格式为常规
$xl.Range("c3").Resize($dataRowCount).NumberFormatLocal = "@" # 设置第 3 列单元格格式为文本

# 去除第三列首尾空格
$startLine = 3
for ($i = $startLine; $i -le $rowCount; $i++) {
    $arr[$i, 3] = $arr[$i, 3].Trim()
}

# 修改区域的全部数据
$rng.Value2 = $arr

# 设置第 2 列的公式
$xl.Range("b3").Resize($dataRowCount).Formula = '=CHOOSE(MOD(LEFT(RIGHT(C3,2)),2)+1, "女", "男")'

test3.ps1

$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
$arrA = $xl.ActiveSheet.UsedRange.Columns("A").Value2
$arrB = $xl.ActiveSheet.UsedRange.Columns("B").Value2
$n = 1

for ($i = 2; $i -le $arrB.GetLength(0); $i++) {
    if ($arrB[$i, 1] -eq $null) {
        $arrA[$i, 1] = ''
        $n += 1
    } else {
        $arrA[$i, 1] = $n
    }
}

$xl.Range("A1").Resize($arrA.Length).Value2 = $arrA