Aspose Cells dll in Powershell

I’m new to powershell, I want to perform some operations on excel. I’m able to do that by using Com Object. But i dont want to use Com Object , instead of that im trying to use aspose cells dll.

Could you please help how can i import or add this dll in my powershell script.

Below is code by using Com Object , i want to re-write it using aspose dll.

$Source = ‘D:\Test’ # the path to where the Excel files are

create an Excel COM object

$excel = New-Object -ComObject Excel.Application

find Excel files in the Source path and loop through.

you may want to add the -Recurse switch here if the code should also look inside subfolders

$result = Get-ChildItem -Path $Source -Filter ‘*.xlsx’ -File | ForEach-Object {
$workBook = $excel.Workbooks.Open($.FullName)
$workSheet = $Workbook.Sheets.Item(1)
$count = 0
$label = $workSheet.Range(’$A:$B’).Find(‘Animal count:’)
if ($label) {
# get the numeric value for the cell next to the label
# empty cells will translate to 0
$count = [int]$workSheet.Cells.Item($label.Row, $label.Column +2 ).Value()
}
# output a PSObject with the full filename and the animal count value
[PsCustomObject] @{
‘File’ = $
.FullName
‘AnimalCount’ = $count
}
$workBook.Close()
}

quit Excel and clean up the used COM objects

$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workBook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

output on screen

$result | Format-Table -AutoSize

#output to CSV file
$result | Export-Csv -Path ‘D:\Test\AnimalCount.csv’ -UseCulture -NoTypeInformation
e

@Kukkudan,

Generally, you can use .NET libraries in PowerShell. Aspose.Cells for .NET is written in managed C#.NET, so it is a .NET assembly that can also be used in PowerShell environment (just similar way as you use a custom .NET library) using standard approach. For example, please visit this link on how to use a .NET assembly in PowerShell. Please note, we offer documentation in standard C# only and you have to accommodate/convert it to PowerShell scripts by yourself. Regarding PowerShell and other relevant configurations we might not help you much as it is beyond the scope of our APIs.

Regarding your COM interop. sample code segments, please visit the following documents’ links using Aspose.Cells APIs for your reference for different tasks you are performing:
(How to open different MS Excel file formats.)

(How to find or search data in the worksheet)

(How to convert to CSV file format)

Moreover, please browse Aspose.Cells for .NET documentation, download/test featured Examples @ Github repos. here (you may download complete VS.NET Examples (C#) project) and check the Aspose.Cells [API Reference] (Aspose.Cells for .NET | Aspose API References) pages for your complete reference.

Hope, this helps a bit.