Tag Archives: Excel

Excel related content

Creating Excel files with Powershell and EPPlus

One of the great things with Powershell is that it’s a layer ontop of the .Net framework. It may not be obvious at first but it’s kind of a “hybrid” of a command line shell and for a lack of better analogy, a cousin to C#.

What it means for us that use Powershell is that we can extend the capabilities in our scripts and modules beyond the already large collection of cmdlets in Powershell. By using built-in or third party .Net libraries.

In this blog post we are going to look at creating Excel ‘.xlsx’ files without installing Excel. Which is quite handy if you need to create Excel files on a system where you don’t want to install Office and e.g. create automated reports as Excel files.

To achieve this we are going to use the downloadable and free .NET library “EPPlus” for the creation of the Excel file and content.

A couple of things worth noting before we begin.

  • This post was made using Windows Server 2012 R2, Powershell version 4 and the DotNet4 version of the EPPlus library.
  • The post is going to show you how to create the Excel file and add content, however the EPPlus library is capable of much more but would require a much longer post. So this post will concentrate on the basics to get you going.
  • If you don’t want to know how to do it from scratch there is an (as far as I can tell) excellent module in the PS Gallery by “Douglas Finke” called “ImportExcel” that let you use standard Powershell syntax. ImportExcel also utilizes EPPlus to create the files.

With that said you may ask yourself, why go through all this if there is an available module already?

Good question, from the last project I was involved in these were some of my reasons.

  • Some features were not implemented yet unless you used the Export-Excel function. E.g. the autofit column width.
  • The spread sheets I was going to create was quite specific, so I didn’t need to deal with “generic” data input.
  • From a code perspective, I wanted the code to be as condensed as possible and not mix and match parts from a seperate module  and then calling functions directly from EPPlus.

Your milage may wary of course but those were some of the reasons which made me look into EPPlus in more detail.

If you want to follow the code in a text-version then I’ve created a link to the sample code on GitHub/Gist (EPPlus_Sample.ps1), you can find the links at the bottom of this post.

Before we can start we need to download the EPPlus library, the link to the library is available at the bottom of this post.

Note: You may need to unblock the files, otherwise you’ll get an error when you try to add the .dll to the powershell session.

But lets start looking at the interesting stuff, the code. I’ll be going through this from top to bottom. Again if you want to follow this in a “text” format then you have the link to the sample code.

epplus_sample

  1. First we need to add the EPPlus library to our session. This is where you’ll get an error if the .dll is blocked.
  2. Then we create a path to the .xlsx that we want to create and initiate the “Excel Package”. The Excel Package have “overloaded” methods for the initation but for this example we reference a file path.
  3. Next we create a reference to the workbook of the package, this way it will be cleaner to reference the underlaying properties.
  4. In the next section we add an Author and Title to the Excel file, this is optional.
  5. Next we add the first worksheet, if you want more than one sheet in your excel file then you can create more by using the same syntax. The pipe to Out-Null isn’t really needed but will supress the output to console if you don’t want to see it.
  6. Next we add a reference to the specific worksheet that we just created. You reference the worksheet by it’s index number and they start from 1.
  7. Next we add a value to the first cell in the first column.
  8. The next section will add a background color with a solid red color. As you can see we reference the cells between A1->E1.
  9. Once you’ve filled the sheet(s) with information then it might be a good time to autofit the colums. It’s good to do this at the end of the file creation. Since otherwise you may’ve added more information later on that will not have been part of a previous autofit.
  10. Next you find an example of setting the column width to a specific size. The columns are referenced by a number 1=A, 2=B and so forth.
  11. Next we save the file and at the very end we clean up any references to the package.

The result should look something like this:

result

I never promised it would look pretty 🙂

But what if you need to edit an Excel file that already exist?
It’s very similar to what we did before, you can find the sample code at the bottom of the post.

epplus_sample2

The only difference from what we did before is:

  • We don’t add a new sheet
  • We get the value from cell A1 and add more information to it.
  • Then change the value of cell A1 to the new value.
  • I also replaced the ugly red color of the first row to something more pleasing to the eye.
  • And finaly added some information in the A2 cell.

It should now look like this:

result2

To conclude this post the basic structure to create an Excel file is not that complicated once you’ve seen the structure and initiation.The reference to cells are the same as if you were using Excel. E.g. you can reference multiple cells using the standard range syntax like A1:A5.

You can add dropdown lists and add validation (may become a future post) and many more features that are available from the EPPlus library.

Finaly, I hope you’ve found this information useful and I wish you good luck with the next Excel automation project.

Reference Links:

EPPlus Library
ImportExcel – Douglas Finke
EPPlus_Sample.ps1
EPPlus_Sample2.ps1