将代码从Excel宏转换为PowerShell [英] Convert code from Excel Macro to PowerShell

问题描述

我在Excel中打开CSV文件并运行此宏以更改背景颜色.我正在尝试将这部分代码转换为PowerShell.

I open a CSV file in Excel and run this macro to change the background color. I am trying to convert this part of code to PowerShell.

   lrow = Range("G" & Rows.Count).End(xlUp).Row
    Set MR = Range("G2:G" & lrow)
    For Each cell In MR
        If UCase(Trim(cell.Value)) = "FALSE" Then
            cell.Interior.ColorIndex = 3
        End If
    Next

将此代码转换为PowerShell的任何帮助.

Any help converting this code to PowerShell.

谢谢SR

推荐答案

您可以这样写:

$objExcel = New-Object -ComObject Excel.Application

$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false

$filePath = "c:\logs\2015-04-23.csv"

$xlsFilePath = Get-Item -Path $filePath | % { Join-Path (Split-Path $_ -Parent) "$($_.BaseName).xls" } 

$workBook = $objExcel.Workbooks.Open($filePath)
$workSheet = $WorkBook.sheets | select -First 1

$xlup = -4162

$lrow = $workSheet.cells.Range("G" + $workSheet.Rows.Count).End($xlup).Row

$workSheet.cells.Range("G2:G" + $lrow) | % { 
    $value = $_.Text
    if($value.ToUpper() -eq "TRUE"){
       $_.Interior.ColorIndex = 3 
    }
}

$WorkBook.SaveAs($xlsFilePath, 18) 
$objExcel.Quit()

如果文件很大，则使用powershell搜索值然后更新Excel工作表会更快.以下示例看起来有些有趣，但是执行速度更快.

If you have a very large file, it is faster to search values using powershell then updating the Excel sheet. The following example looks a bit funny but executes much faster.

$filePath = "c:\logs\2015-04-23.csv"

$rowAliases = 97..122 | foreach { ([char]$_).ToString().ToUpper() }
$selectedRow = "G"
$selectedName = (Get-Content $filePath -ReadCount 1 -TotalCount 1).Split(",")[$rowAliases.IndexOf($selectedRow)]
$startRow = 2
$rowCount = 1;

$objExcel = New-Object -ComObject Excel.Application

$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false

$xlsFilePath = Get-Item -Path $filePath | % { Join-Path (Split-Path $_ -Parent) "$($_.BaseName).xls" } 

$workBook = $objExcel.Workbooks.Open($filePath)
$workSheet = $WorkBook.sheets | select -First 1

Import-Csv -Path $filePath | % {

    if($rowCount -ge $startRow){
        [string]$value = $_ | select -ExpandProperty $selectedName

        if($value.ToUpper() -eq "TRUE"){
            $workSheet.cells.Item($rowCount + 1, $selectedIndex + 1).Interior.ColorIndex = 3 
        }
    }

    $rowCount ++
}

$WorkBook.SaveAs($xlsFilePath, 18) 
$objExcel.Quit()

这篇关于将代码从Excel宏转换为PowerShell的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！