Excel Data Manipulation Using PowerShell-CodePudding

I have a excel sheet in which column E has all decimal values like 0.5564, 0.87632 and so on.

I want to create a power shell script that selects all these values from E2 till end of the column whatever that row number is dynamically and then multiplies them by 100 and saves the file, so that there is a percentage value in column E instead of a decimal value.

This is my code but this does not work. $worksheet.Range("E2").EntireColumn is not working I believe. Can figure it out also.

PLEASE HELP.

$Excelobject=New-object -ComObject Excel.Application
$Excelobject.visible = $False
$workbook=$Excelobject.Workbooks.Open("C:\Users\Siddhartha.S.Das2\OneDrive - Shell\Desktop\Workspacesize.csv")
$worksheet=$workbook.worksheets.Item(1)
$worksheet.Activate()
$worksheet.cells.item(2,1)="Workspace Name"
$data=$worksheet.Range("E2").EntireColumn
foreach ($data in $data){$data=$data*100}
$Excelobject.DisplayAlerts= $False
$workbook.SaveAs("C:\Users\Siddhartha.S.Das2\OneDrive - Shell\Desktop\Workspacesize.csv")
$workbook.close
$Excelobject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excelobject)

CodePudding user response：

Try setting each Cell's value individually:

# Select the Cells with data
$data=$worksheet.Range("E1:E5").Cells | Where Value2
$data | Foreach {
  $_.Value2 = $_.Value2 * 100
}

CodePudding user response：

Not as concise as Cpt.Whale's answer, but instead of hardcoding the range "E1:E5", you could have the script determine how many rows it needs to update:

$rowMax = $worksheet.UsedRange.Rows.Count
# I believe you want to skip the first row, otherwise start with `for ($row = 1; ...)`
for ($row = 2; $row -le $rowMax; $row  ) {
    # casting to double here lets $val become 0 on empty cells
    $val = [double]$worksheet.Cells.Item($row, 5).Value2
    if ($val) {
        [double]$worksheet.Cells.Item($row, 5).Value2 = $val * 100
    }
}

# close and save the workbook
$workbook.Close($true)
$Excelobject.Quit()
# clean-up used Com objects
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excelobject)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()