如何只将可见的excel列导出到csv？
如何只将可见的excel列导出到csv？
提问于 2020-04-29 21:05:22
好的，有像this这样的线程只将可见的行导出到csv，但是列呢？我需要一个循环吗？或者，它可以像可见工作表一样容易完成吗？
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"
}
}
}特别是，这是我正在处理的部分，它告诉脚本在布尔参数$ExcludeHiddenColumns指定的情况下只导出可见的列，但不确定实现此目的的最佳方法。
<# WIP
if($ExcludeHiddenColumns) {
if ($worksheet.Column.Visible -eq -1) {
$worksheet.sheets.columns.entirecolumn.hidden=$true
}
} #>回答 1
Stack Overflow用户
回答已采纳
发布于 2020-04-30 05:35:19
经过大量的试验和错误，我弄明白了！
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
}
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61511673
复制相关文章
相似问题
腾讯云开发者
