☰

通过在 PowerShell 中创建 Excel.Application 对象的实例将数据导出到 Excel

tag-icon • tag-icon powershell powershell-5.0 powershell-2.0 powershell-3.0 powershell-4.0
通过在 PowerShell 中创建 Excel.Application 对象的实例将数据导出到 Excel

目前我正在使用 ImportExcel 模块从多个 csv 文件中提取数据并导出到单个 excel 文件中。但由于本月的网络限制，我无法安装 importexcel 模块。有没有什么方法可以在不导入/安装任何模块的情况下使用 excel？

参考网站：- 访问https://learn-powershell.net/2012/12/20/powershell-and-excel-adding-some-formatting-to-your-report/

在网上多次搜索后，我发现了类似下面的内容，但在第 26 行显示错误。

$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.visible=$False
$Myworkbook = $Myexcel.workbooks.add()
$Sheet1 = $Myworkbook.worksheets.item(1)
$Sheet1.name = "OverAll"
$Sheet1.Range("A1:F1").font.size = 18
$Sheet1.Range("A1:F1").font.bold = $true
$Sheet1.Range("A1:F1").font.ColorIndex = 2
$Sheet1.Range("A1:F1").interior.colorindex = 1

$destPath = "\\192.168.90.12\Data\"

$Results = Get-ChildItem $destPath -Recurse -Include '*.csv' | ForEach-Object {
    $Object = [PSCustomObject]@{

        SerialNumber = $_.BaseName
        
    }
    Import-Csv -Path $_.FullName | ForEach-Object {
        $Object | Add-Member -MemberType NoteProperty -Name $_.Parameter -Value $_.Status -Force
    }
    $Object
}

 $Results | Select-Object SerialNumber,ComputerName, Antivirus, Firewall,GoogleChrome, MicrosoftEdge |
$Myfile = "C:\Temp\OverAll.xlsx"
$Myexcel.displayalerts = $false
$Myworkbook.Saveas($Myfile)
$Myexcel.displayalerts = $true
$Myworkbook.Close()
 $Excelobj.Quit()  
 [System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj)

答案1

第 36 行的错误是由于这个造成的......

$Results | Select-Object SerialNumber,ComputerName, Antivirus, Firewall,GoogleChrome, MicrosoftEdge |
$Myfile = "C:\Temp\OverAll.xlsx"

应该是这样的：

$Results | Select-Object SerialNumber,ComputerName, Antivirus, Firewall,GoogleChrome, MicrosoftEdge
$Myfile = "C:\Temp\OverAll.xlsx"

以下所有示例都只是在网络、SU 或 Youtube 上可发现的项目。

您可以通过如下简单的方法练习了解 Excel COM：

$FileName = "$env:temp\Report"

# create some CSV data
Get-Process | 
Export-Csv -Path "$FileName.csv" -NoTypeInformation -Encoding UTF8


# load into Excel
$excel         = New-Object -ComObject Excel.Application 
$excel.Visible = $true

# change thread culture
[System.Threading.Thread]::CurrentThread.CurrentCulture = 'en-US'

$excel.Workbooks.Open("$FileName.csv").SaveAs("$FileName.xlsx",51)
$excel.Quit()

explorer.exe "/Select,$FileName.xlsx"

弄乱颜色：

#requires -Version 2.0
Add-Type -AssemblyName System.Drawing

# accessing excel via COM
$excel = New-Object -ComObject Excel.Application
# # make it visible (for debugging only, can be set to $false later in production)
$excel.Visible = $true

# add workbook
$workbook = $excel.Workbooks.Add()

# access workbook cells
$workbook.ActiveSheet.Cells.Item(1,1) = 'Hey!'

# formatting cell
$workbook.ActiveSheet.Cells.Item(1,1).Font.Size = 20

$r = 200
$g = 100
$b = 255

[System.Drawing.ColorTranslator]::ToOle([System.Drawing.Color]::FromArgb(255,$r,$g,$b))
$workbook.ActiveSheet.Cells.Item(1,1).Font.Color = $r + ($g * 256) + ($b * 256 * 256)

# saving workbook to file
$Path = "$env:temp\excel.xlsx"
$workbook.SaveAs($Path)

更多详情请见：

将 CSV 文件转换为 Excel 工作簿

相关内容