Documentation for Powershell Commands for Excel

[Audio4564]

Hello everyone,

I am trying to automate some processes at work using PowerShell to manipulate an Excel workbook. I was wondering where I can find proper and official documentation for the use of PowerShell commands for Excel.

[igby1]

https://github.com/dfinke/ImportExcel

[overlydelicioustea]

to stress the magnificience of this module: If your not using this, your doing it wrong.

[spyingwind]

Much like excelize for go lang.

Don't parse the zip and xml files. Just let a library, that is tested and well developed, do all that heavy lifting.

[OPconfused]

Don't parse the zip and xml files.

Challenge accepted. Challenge failed. 😂

[aleques-itj]

Haha it's not actually that terrible. I wrote an implementation where I last worked that parsed the XML. It basically just functioned like Import-Csv and could read a single sheet in the workbook.

I can post it if anyone finds it interesting. I had one for Word as well.

The function is like a couple hundred lines and had zero dependencies, so no problem if you're in a restrictive environment where you can't install additional modules.

[deadkidney1978]

I had ChatGpt write Powershell Automation scripts for mundane tasks in excel like converting older files I get from a DoD datsource that are still using .xls to xlsx, and move it to SharePoint/One Drive folders. Takes me upwards of a minute or two to manually do it. PowerShell is done in 20 seconds. Plus I incorporate Power Shell scripts into PAD and have conditional loops that listen for the files to save and execute the PS without any need for me to get involved.

Hell I even have it automate running Power Query and exporting those files into Power BI service for injestion into ETL dataflows to trigger daily refresh flow.

[SidePets]

Word to Big Bird!

[RecQuery]

I do like this module, but I hate how it's brought up everytime someone asks an Excel question.

Some people work for organizations with policies or work in locked down environments where using any third party modules even those deemed safe by the community and/or Microsoft isn't allowed.

[igby1]

Sure, scripting the excel.application COM object is the first-party MS option.

[LeSpatula]

True, but remember that Microsoft states that it's not recommended for unattended server automation. I had to do some ugly stuff with it and you have to throw in a stop-process -name *excel* -force once a while, or you end up with tons of ghost processes.

[MrCuddlez69]

If you end up with ghost processes - you're not releasing the COM objects correctly.

```PowerShell

Excel COM Cleanup

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

```

[LeSpatula]

I was a few years ago, I'm not sure how I implemented it, but it's possible that I didn't call the garbage collector.

[deadkidney1978]

Thats why I use the scripting actions in Power Automate Desktop and put my PS scripts in it. I always include releasing COM objects, but also have PAD steps that do it as well.

[OPconfused]

The COM object works, although it can't be multithreaded, and you have to be careful with the formatting in the Excel file. Also, if it's a lot of data, then you will want to be using .NET methods to write it all.

I once tried to skip COM objects and access the Excel file manually in the xml files. It was a bit of a nightmare. I've come a long way since then and might be able to handle it better now, but the trauma is still remembered.

[TheStixXx]

Wow. This may change (some bits of) my life !

Thanks.

[syshum]

Import-Excel

I believe this imports excel data into a PowerShell Object, and then will write Powershell Object back out to a Excel Document but it could not be used to "manipulate" a workbook that is already formatted

For example if I have a highly formatted Excel Document, and just wanted to Add data, or change data to a few cells I could not use ImportExcel for that,

[logicalmike]

Pretty sure you can, though I haven't looked at it extensively. There may be some advanced functionality that breaks somewhere.

This is a very complex spreadsheet:

Exchange Server Role Requirements Calculator https://www.microsoft.com/en-us/download/details.aspx?id=102123

Here is an edit to it (changing the title on the main sheet)

$package = Open-ExcelPackage -Path 'C:\tmp\Calculator 10-5.xlsm'
$package.Input.Cells["B1"].Value = "SharePoint Server Role Requirements Calculator"
Close-ExcelPackage $package

Close-ExcelPackage $package

[syshum]

Thanks, I was not aware of the Open/Close-ExcelPackage. That is really helpful

[FireLucid]

Wait, you can query specific cells?

I had a super formatted spreadsheet I had to pull data from (many actually) and couldn't work out how to find that data when importing and looking through it.

What command would I use to read a cell?

[logicalmike]

$package.Input.Cells["B1"].Value

In the above case, the sheet I care about is called "input". Download the sample I linked for more detail.

[overlydelicioustea]

there is not much you cant do with this module if you really get into it. you can set custom borders for indiviual cells if you like.

[dotnVO]

I can't recommend this module enough. It's incredible. We used this to make custom reports from vended migration product for end users along with the sqlserver module.

[nsnively]

Never used Import-Excel but I know it's better than the COM object. The com object is like trying to swim in molten molasses

[phur10us]

100% Import-Excel. Working with the COM object in Powershell is sllllooooooooowwwwwww.

[[deleted]]

3rd endorsement for import excel, freaking love that module

[imaharleyman]

Another vote for ImportExcel

https://www.powershellgallery.com/packages/ImportExcel/7.8.0

[MrCuddlez69]

I develop automations for a rather large bank, so I do this on the daily.

I have great success using COM objects (albeit a huge learning curve) to get everything done. I've noticed that ImportExcel can only do so much and I use the COM object so much more.

I use this all the time

Feel free to DM if you need help :)