How to use Aspose Cells dll in Powershell Script

Question

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

score 0 · Answer 1 · answered Jul 02 '20 at 19:15

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 to 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, you have to accommodate/convert to PowerShell scripts by yourself.

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:

e.g. (How to open different MS Excel file formats.) https://docs.aspose.com/display/cellsnet/Opening+Different+Microsoft+Excel+Versions+Files

(How to find or search data in the worksheet) https://docs.aspose.com/display/cellsnet/Find+or+Search+Data

(How to convert to CSV file format) https://docs.aspose.com/display/cellsnet/Different+Ways+to+Save+Files#DifferentWaystoSaveFiles-SavingTextFileswithCustomSeparator

Hope, this helps a bit.

PS. I am working as Support developer/ Evangelist at Aspose.

How to use Aspose Cells dll in Powershell Script

1 Answers1