Excel Powershell

The Powershell Excel module is a way you can interact with Excel files. There is more than one Excel module you can choose from. The great thing about this approach is that you don’t need to install Excel on your machine in order to generate and manipulate Excel files.

Alternative methods to Excel PowerShell modules

Before PowerShell, there have been a few ways to manipulate Excel files.

Excel installation on the local machine

The simplest and most straightforward way to work with Excel files. There are at least two disadvantages of using this method. The first one is that you need to have an additional license. The second one it’s not the fastest method to work with a huge number of files.

Create a CSV file

Another way to work with files is to create a Comma Separated File. The problem is that you don’t have control when it comes to formatting or can create special objects like charts or pivot tables. As the name suggests the element of the files are just separated by comma (or different type of delimiter).

There is also another problem with this approach. Sometimes, you just have to work with Excel files, because this is the requirement of your company.

Com objects

Another way to operate on Excel files is to remotely control. You can do it with the help of the COM interface. You can create workbook and worksheets and insert data into appropriate cells.

Here’s a code that creates Excel and then adds a workbook.

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
$xl.Workbooks.Add()

ODBC connection

In this case, you use a .NET Open Database Connectivity. After setting up you can work in a similar way as you work with the COM interfaces.

Finding modules

In order to find PowerShell modules, you can visit the PowerShell repository.

After you click a link to a module, there, you will see an installation command.

On the left, you can see how many times the module was downloaded, and at the bottom, you can see how often the module is updated.

Installing a module

Installing a module is pretty straightforward. First, you have to open the PowerShell window.

In order to do it, navigate to search icon on a windows toolbar.

Type “PowerShell”. Right-click the icon and select Run as administrator. You can install modules only if you are an admin.

Inside the window, paste the command from installation options by clicking the right mouse button. Press Enter to start the installation.

When you installing a module, you probably will get a message about the untrusted repository. Press Y. The PowerShell Gallery is uncurated, so people can upload harmful scripts. Be sure that you download save scripts.  After a few seconds of installation, you will get back to the console.