时间戳

首页 > 解决方案 > CSV 到 Excel 与数据透视表

excel - CSV 到 Excel 与数据透视表

问题描述

所以我正在尝试编写一个简单的代码来将 csv 转换为 excel，插入一个数据透视表，然后保存它。下面的代码运行良好，但 excel 文件以 $name 的名称而不是 $name 的内容保存...而且我可以在 powershell 脚本中使用 VBA 代码吗？我想插入一个数据透视表，我只能找到它的 vba 代码....

    Add-Type -AssemblyName System.Windows.Forms

    $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{

    InitialDirectory = [Environment]::GetFolderPath('Desktop')

    Filter = 'SpreadSheet (*.csv)|*.csv'

    }

    $FileBrowser.ShowDialog()

    $file = $FileBrowser.FileName

    $name= $FileBrowser.SafeFileName

    Write-Host $name

    $excel = New-Object -ComObject Excel.Application

    $excel.Visible = $true

    $excel.DisplayAlerts = $false

    $excel.Workbooks.Open($file)

    $excel.Workbooks.item(1).SaveAs('C:\Users\User\Desktop\$name.xlsx',51)

    $excel.Quit()

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

所以在这里修复代码之后就是最终产品....

    Add-Type -AssemblyName System.Windows.Forms
    $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{ 
    InitialDirectory = [Environment]::GetFolderPath('Desktop')
    Filter = 'SpreadSheet (*.csv)|*.csv'
     }
    $FileBrowser.ShowDialog()
    $file = $FileBrowser.FileName
    $name = $FileBrowser.SafeFileName -replace ".{4}$"
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $excel.DisplayAlerts = $false
    $excel.Workbooks.Open($file)
    #$excel.Workbooks.Item(1).activate()
    #$excel.ActiveSheet.Cells.Select()  
$excel.Workbooks.item(1).SaveAs("C:\Users\User\Desktop\$name.xlsx",51)
    $excel.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

现在我想在其中添加一个数据透视表。我添加了激活工作簿并选择所有单元格的行......现在我有了 VBA 代码，但有人可以帮我将它翻译成 powershell 吗？

Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "2!R1C1:R1048576C27", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Domain")
        .Orientation = xlRowField
        .Position = 1

上面的 VBA 是我记录的一个宏，它为我们提供了步骤……现在我们只需要将它转换为 power shell……我迷失了，因为直到现在为止，代码本身就是几个小时的修补……

标签： excelvbapowershellcsvscripting

解决方案

这是因为这正是你告诉它要做的事情。

$excel.Workbooks.item(1).SaveAs('C:\Users\User\Desktop\$name.xlsx',51)

报价很重要。单引号意味着完全按原样传递字符串。

如帮助文件中所定义。

变量必须展开，并且在字符串中，必须使用双引号。

$excel.Workbooks.item(1).SaveAs("C:\Users\User\Desktop\$name.xlsx",51)

这里有几篇很好的文章，可以让你对这个话题有所启发：

注意点：如果您所做的只是将文本输出到屏幕，则不需要 Write-Host。在 Powe3rShell 中输出到屏幕是默认的。此外，根据您使用的 PowerShell 版本，Write-Host 会清空缓冲区，之后的任何内容都无法在其他地方使用。更高版本的 PowerShell 没有此问题，但最好还是避免 Write-Host，除非您将彩色文本发送到屏幕或其他自定义格式方案。

补充阅读：

... Jeffrey Snover 在 2016 年 5 月改变了他的立场。

使用 PowerShell v5 Write-Host 不再“杀死小狗”。数据被捕获到信息流中......

https://twitter.com/jsnover/status/727902887183966208 ...

https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/Write-Information?view=powershell-5.1

最后，你并没有完全清理自己的身后。虽然您正在退出 Excel，但您并没有退出 Excel 进程，因此释放了这些资源和 COM 内容。因此，有必要停止该进程并进行垃圾收集。

例如：

Get-Process -Name 'EXCEL' | Stop-Process

Function Clear-ResourceEnvironment
{
    # Clear any PowerShell sessions created
    Get-PSSession | Remove-PSSession

    # Release any COM object created
    $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$Shell)

    # Perform garbage collection on session resources 
    [System.GC]::Collect()         
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()

    <# 
    Remove any custom variables created
    some list of your variable, or a wildcard when prefix was used.
    #>

    Get-Variable -Name MyShell -ErrorAction SilentlyContinue | Remove-Variable
}

推荐阅读