Empty cells in a column

[mezamoe]

Hello, I'm trying to empty a column cells while keeping the header and then write new data into the column. What is the strategy? Normally I would check the length of something and iterate over each occurrence to delete data. But after I grab the column I don't see the length method:

$worksheet.Column(4)

Shall I delete the entire column and recreate it when I enter the fresh data?

[dfinke]

please add a short repro of what you are attempting, including an xlsx

[mezamoe]

apologies.. definitely wrote in a rush..

test.xlsx

basically the script should:

1. empty column D
2. fill column D with fresh data

Here what I got so far but the data is not being written. The header yes.

$excelPack = Open-ExcelPackage -Path 'e:\rundeck\user-assets\data\mail.xlsx'
$worksheet = $excelPack.Workbook.Worksheets['sapsid']
$worksheet.DeleteColumn(4)
Set-ExcelColumn -Worksheet $worksheet -Heading "Servers Name" -Width 40 -Column 4
Set-ExcelRange -Address $worksheet.Cells["D:D"] 
Set-Format -Address $worksheet.Cells["D1"] -Bold -BackgroundColor Black -FontColor White -HorizontalAlignment Center
$count = 1
foreach($item in $servers){
    $count +=1
    $worksheet.Cells["D$count"] = $item
}
Close-ExcelPackage $excelPack

and the error is:

Unable to index into an object of type OfficeOpenXml.ExcelRange.
At line:11 char:5
+     $worksheet.Cells["D$count"] = $item
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : CannotIndex

[mezamoe]

apologies again, I've just realized I missed the method Value

$worksheet.Cells["D$count"].Value = $item

[mezamoe]

actually, as column C contains formulas in each cell the $worksheet.DeleteColumn(4) is not an option, as that breaks the formula application to column D. So, I'm back to the need of emptying each cell ..

[mezamoe]

found that too, sorry for the waste of time.. hopefully it might help other importExcel newbies:

$excelPack = Open-ExcelPackage -Path 'pathToFile'
$worksheet = $excelPack.Workbook.Worksheets['sapsid']
foreach ($cell in ($worksheet.SelectedRange["D2:D"])){
$cell.Clear()
}
$count = 1
foreach($item in $servers){
$count +=1
$worksheet.Cells["D$count"].Value = $item
}
Close-ExcelPackage $excelPack