Working with EPPlus and PowerShell


I recently worked on a requirement, where the end user wanted to convert Excel files into tab-delimited text files. In my head I was like, this is easy: “PowerShell FTW! Yay!”. The reason for the excitement was because we already have two repositories that I know work well with Excel and fit my requirement.

When I started writing the solution, I used Doug’s module because it is as easy as it can get, I was able to write and “test” the code in under 30 minutes. Basically, all I had to do was:

  • Import the module
  • Call ‘ConvertFrom-ExcelSheet’ with the proper parameters

Viola! you can now convert Excel Workbooks with multiple sheets in them.

As much as I hate reinventing the wheel, customers sometimes want you to do stuff that simply does not make any sense. So, I had to write a script that did not use the modules – sigh!.

However, while working on the script I encountered two small issues and thought, it might be useful for others to know about this as well.

  • Figuring out the bottom most populated cell in the sheet.
    • Because the excel sheet that I had did not have any data in the first three rows, Dimension.Rows or Dimension.Columns was not working me. This I believe is because, If a range of cells is available Dimension returns the properties for the range. However, the End property does get the row and column of the bottom right cell. The original code with the cells to be changed highlighted and then follows the changed code.
$xl = New-Object OfficeOpenXml.ExcelPackage $FullName
$workbook = $xl.Workbook
$worksheet=$workbook.Worksheets[$Sheet]
$dimension=$worksheet.Dimension
$Rows=$dimension.Rows
$Columns=$dimension.Columns
Difference when there are empty cells in worksheet

Difference when there are empty cells in worksheet

$Rows=$worksheet.Dimension.End.Row
$Columns=$worksheet.Dimension.End.Column
  • Figuring out the active worksheet
    • In order to export the data, the end user wanted to use the last active sheet and not the first sheet. Based on what I have read the below code, may not work if the excel workbooks were created programatically. It worked for me because the Excel workbooks were created manually so, there was always one and only one active sheet.
$activeTabIndex = [Int]$($xl.Workbook.WorkSheets | Where-Object {$_.View.TabSelected -eq $true} | Select Index).Index
$workSheet = $workBook.Worksheets[$activeTabIndex]

References:

Advertisements
About

By profession, I’m a SQL Server Database Administrator. I love to poke my nose into different corners and see how stuff looks in there. I keep looking for new things to do as my mind refuses to settle on one topic.

Tagged with: , , ,
Posted in PowerShell
One comment on “Working with EPPlus and PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: