Ok, so there are threads like this to export only visible rows to csv, but how about columns? do i need a loop? or can it be done as easily as visible worksheets?
Begin {
$excel = New-Object -ComObject Excel.Application -Property @{
Visible = $false
DisplayAlerts = $false
}
}
Process {
#$root = Split-Path -Path $Path
$filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
$workbook = $excel.Workbooks.Open($Path)
foreach ($worksheet in ($workbook.Worksheets | Where { <# $_.Visible -eq -1 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {
<# WIP
if($ExcludeHiddenColumns) {
if ($worksheet.Column.Visible -eq -1) {
$worksheet.sheets.columns.entirecolumn.hidden=$true
}
} #>
if ($ExcludeHiddenSheets) {
if ($worksheet.Visible -eq -1) {
$ws = $worksheet
}
}
else {
$ws = $worksheet
}
if ($AppendFileName) {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "${filename}_$($ws.Name).csv"
}
else {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($ws.Name).csv"
}
try {
$ws.SaveAs($name, 6) #6 to ignore formatting and convert to pure text, otherwise, file could end up containing rubbish
}
catch {
Write-Error -Message "Failed to save csv! Path: '$name'. $PSItem"
}
}
}
Particularly, this is the part im working on to tell the script to export only the visible columns if specified by the boolean param $ExcludeHiddenColumns
, but not sure the best way to approach this..
<# WIP
if($ExcludeHiddenColumns) {
if ($worksheet.Column.Visible -eq -1) {
$worksheet.sheets.columns.entirecolumn.hidden=$true
}
} #>
After a lot of trial and error, i figured it out!
foreach ($worksheet in ($workbook.Worksheets | Where { <# $_.Visible -eq -1 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {
if($ExcludeHiddenColumns) {
$ColumnsCount = $worksheet.UsedRange.Columns.Count
for ($i=1; $i -le $ColumnsCount; $i++)
{
$column = $worksheet.Columns.Item($i).EntireColumn #$worksheet.sheets.columns.entirecolumn.hidden=$true
if ($column.hidden -eq $true)
{
$columnname = $column.cells.item(1,$i).value2
"Column {0} was deleted!" -f $columnname
$column.Delete()
#$i = $i - 1
}
}
}
}