搜索和在Excel中替换而不循环? [英] Search & Replace in Excel without looping?

问题描述

这似乎是

This seems to be the common answer on the internet for how to do a bulk search and replace on an excel file column.

我的问题是，在只有8K行的文件上， 需要5分钟才能在单列中替换一个简单的两个字符的字符串 ，而该文件不是甚至只有1MB的大小.

My problem is that on a file of only 8K rows, it takes 5 minutes to replace a simple two-character string in a single column and the file isn't even 1MB in size.

有没有更快/更好的方法，或者甚至有优化它的方法?

Is there a faster/better way, or even a way to optimize this?

当前代码:(通过将搜索/替换逻辑放在单独的函数中，使模块更具模块化和可重复使用性

Current code: (made a little more modular and re-usable by putting the search/replace logic in a separate function)

function excel_search_replace ( $worksheet, $column_name, $search_str, $replace_str ) {
    echo "Replacing all '$search_str' with '$replace_str' in column '$column_name'"
    $range = $worksheet.Range( "$($column_name)1" ).EntireColumn
    $search = $range.find( $search_str )
    $i = 0

    if ( $search -ne $null ) {
        $i += 1
        $first_addr = $search.Address
        do {
            $i += 1
            $search.value() = $replace_str
            $search = $range.FindNext( $search )
        } while ( $search -ne $null -and $search.Address -ne $first_addr )
    }
    echo "...Found and replaced $i instances of '$search_str'"
    return $void
}

$source_file = 'C:\some\excel\file.xlsx'
$excel_obj = New-Object -ComObject 'Excel.Application'
$excel_obj.DisplayAlerts = $false
$excel_obj.Visible = $false
$workbook = $excel_obj.Workbooks.Open( $source_file ) # Open the file
$sheet = $workbook.Sheets.Item( 1 ) # select target worksheet by index

excel_search_replace $sheet 'A' 'find this' 'and replace with this'
[void]$workbook.save() # Save file
[void]$workbook.close() # Close file
[void]$excel_obj.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject( $excel_obj ) >$null # Release COM

推荐答案

$Excel = New-Object -ComObject Excel.Application
$Workbook=$Excel.Workbooks.Open("Files\MyFile.xlsx")
$WorkSheet = $Workbook.Sheets.Item(1)
$WorkSheet.Columns.Replace("ThisNeedsTobeReplaced","ImReplaced")

这篇关于搜索和在Excel中替换而不循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！