Export data to excel by creating instance of the Excel.Application object in PowerShell

Question

Currently i'm using ImportExcel module to fetch data from multiple csv file and export in single excel file. But due to network restriction from this month I am not able to install importexcel module. Is there is any way i can use excel without importing/installing any module.

Reference Site: - Visit https://learn-powershell.net/2012/12/20/powershell-and-excel-adding-some-formatting-to-your-report/

After mutliple search on internet i found something like below but showing error at line 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)

Answer 1

That line 36 error is because of this...

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

It should be this:

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

All the below samples are just items discoverable on the web, on SU, or on Youtube.

You can practice getting the idea of Excel COM with something as simple as this:

$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"

Messing with color:

#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)

See more here:

Converting CSV file or files into an Excel workbook