Friday, April 29, 2016

Use SpreadsheetGear with PowerShell to Export an Excel File.

I’ve been starting a new job and haven’t really had time to keep this blog updated.  Recently, I figured out how to export an Excel file using PowerShell and Spreadsheet Gear.  I worked at a shop that utilized the Spreadsheet Gear assembly pretty extensively.  So naturally, when we’re out Excel files we wanted a way to export from PowerShell and rather than learn a whole new framework we wanted to use Spreadsheet Gear.  I worked on it for a week or so and couldn’t get it.  There are barely any examples of Spreadsheet Gear and PowerShell that are searchable.  All that has changed.

Why not just use InterOp?

First problem with that, is well… I don’t want to.  I’m used to using Spreadsheet Gear.  Secondly, it creates the object as a ComObject.  The .net framework has a terrible time keeping track of ComObjects.  This is why they have lost favor (a long time ago)…

Let’s Go -

Ok.  So now we know what we are doing, how do we do it?  First, you need the assembly.  You can download a 30 day evaluation from Spreadsheet Gear.  The thing you will notice is that it is Spreadsheet Gear 2012.  Yup, they have not updated it since 2012.  Quite frankly, what it does is pretty simplistic.  Meaning, there isn’t much more they can do with it other than to make sure it is compatible with new versions of Excel.

First things first.  We need to make sure we can develop in PowerShell in VS20xx.  You do this by going into Tools | Extensions and Updates… In the modal window that opens up, make sureto highlight Online in the left pane and type powershell in the search.  You will be given a list of powershell extensions you can install.  We want PowerShell Tools for Visual Studio (insert version here) as in this example -

image

Once that is installed and ready to go we can create a PowerShell project and it will automagically create us a script -

image

Now that we have the project and script created we can start with the real work.

First thing we need to do is create the table of data that we will cycle through and populate the Excel spreadsheet with.  You can see that done in the following example (creating a datatable in PowerShell is beyond the scope of this article) -

$table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Year,([int])
$col2 = New-Object system.Data.DataColumn Album_Nm,([string])
$col3 = New-Object system.Data.DataColumn US_Chart,([int])
 
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
 
$row = $table.NewRow()
$row.Year = 1978
$row.Album_Nm = "Van Halen"
$row.US_Chart = 19
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Year = 1979
$row.Album_Nm = "Van Halen II"
$row.US_Chart = 6
$table.Rows.Add($row)

For brevity I only added the first two albums, but you get the idea.  So here we are with our data so now we need to load it into Spreadsheet Gear.  First thing we need to do is load the assembly into memory.  We do that using Reflection.

What is Reflection

There are four principles of OOP (Object-Orientated Programming).  They are Reflection, Encapsulation, Abstraction and Polymorphism.  We’ll discard Abstraction and Polymorphism as they are beyond the scope of this article.

Encapsulation

Encapsulation is the wizard behind the curtain.  I am here because I know what you want and I know what I want but most importantly, I don’t care what you have to do to accomplish the goal.  I don’t care what gears need to turn or buttons need to be pushed. 

Reflection

It is the exact opposite of Encapsulation.  I need to know everything about you so I can use you to give me what I want.  I have to know how your methods work to accomplish the task.

In our instance, we are using reflection.  This isn’t necessarily true but think of it this way-
When you import (VB) or using (C#) you are using encapsulation.  You don’t care about anything but the signature of the object (what it requires and what you are getting back) but when inherit an object, you need to know everything about it (like in reflection).

So the first thing to accomplish is to get the assembly loaded.  We’ll use the Reflection.Assembly namespace to accomplish this task-

[Reflection.Assembly]::LoadFile("C:\Personal Projects\Project Reporter\Powershell 
Project Reporter\SpreadsheetGear.dll")

Obviously you will need to store your assembly where the LoadFile path points to.  This loads the assembly into memory and prepares it for us to use.  Next we need to create a workbook.  We do this by using the shared (VB) or static (C#) method GetWorkbook() in the assembly-

$wb = [SpreadsheetGear.Factory]::GetWorkbook()

If you wanted to load a workbook that had been previously created then you would use GetWorkbook(“C:\Path\To\File.xlsx”)

Now we need to create a worksheet in our new Workbook.  We do this using the Add() method for the workbook-

[SpreadsheetGear.IWorksheet]$ws = $wb.Worksheets.Add()

Here you can see we are creating the $ws variable and type-casting it to an IWorksheet.  Now we need a way to keep track of what row we are on.  We want to be able to dynamically move between rows.  So we create a “pointer” which really isn’t a pointer but simply an integer indicating the number row we are on -

[int]$pointer = 1

Now that we have our pointer, let’s go ahead and add a header to the report.  We’ll name this report Van Halen Albums -

$ws.Cells["B1:E1"].Merge()
$ws.Cells["B1"].Value = "Van Halen Albums"
$ws.Cells["B1"].Font.Size = 20
$ws.Cells["B1"].Font.Bold = $true

So here we Merged cells B1 – E1 and then wrote “Van Halen Albums” in the cell.  We changed the font size and then made it bold.  We also could have changed the font color by using .Font.Color or changed the background color of the cell by using .Interior.Color…

Now it is time to put our Row headers in.  So we can identify what data is what -

$pointer = 3
$ws.Cells["A$pointer"].Value = "Year"
$ws.Cells["B$pointer"].Value = "Album Name"
$ws.Cells["C$pointer"].Value = "Chart Placement"

So here you can see we incremented the pointer to row #3.  We then put our text into the appropriate cell.  From here, we are going to go through a loop so that we can style the header correctly.  We need to know what cells to apply this to so we have to define that and then go through the loop, the rest is pretty much self-explanatory -

$cells = ("A", "B", "C")
foreach ($cell in $cells) {
 $c = $cell + $pointer.ToString()
 #$ws.Cells[$c].HorizontalAlignment = [SpreadsheetGear.HAlign]::Center
 $ws.Cells[$c].Font.Size = 11
 $ws.Cells[$c].Font.Bold = $true
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeTop].Weight = 
[SpreadsheetGear.BorderWeight]::Thin $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeRight].Weight = 
[SpreadsheetGear.BorderWeight]::Thin $ws.Cells[$c].ColumnWidth = 15      }

Now you see how the pointer is an added value.  This only gives us part of the example, because next we will want to add the data (the actual album stuff from the table we created…) -

foreach ($row in $table.Rows) {
 $pointer +=1
 $ws.Cells["A$pointer"].Value = $row.Item("Year")
 $ws.Cells["B$pointer"].Value = $row.Item("Album_Nm")
 $ws.Cells["C$pointer"].Value = $row.Item("US_Chart")
}

Last thing we need to do is name the file and save it.  There are specific file types for SpreadsheetGear and you will need to google to find out which one is the one that best suits your needs but the OpenXMLWorkbook is backward compatible to Excel 2007 -

$wb.SaveAs("C:\temp\VanHalenAlbums.xlsx", [SpreadsheetGear.FileFormat]::OpenXMLWorkbook)

 

So here we created an Excel file in PowerShell without using ComObjects and then populated it with data.  Finally we saved the file for archiving.  Now we can automate creating Excel in PowerShell -

image

Unformatted completed example code follows -

$table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Year,([int])
$col2 = New-Object system.Data.DataColumn Album_Nm,([string])
$col3 = New-Object system.Data.DataColumn US_Chart,([int])
 
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
 
$row = $table.NewRow()
$row.Year = 1978
$row.Album_Nm = "Van Halen"
$row.US_Chart = 19
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Year = 1979
$row.Album_Nm = "Van Halen II"
$row.US_Chart = 6
$table.Rows.Add($row)
 
 
## SPREADSHEETGEAR Example starts here
[Reflection.Assembly]::LoadFile("C:\Personal Projects\Project Reporter\Powershell Project Reporter\SpreadsheetGear.dll")
 
[int]$pointer = 1
$wb = [SpreadsheetGear.Factory]::GetWorkbook()
[SpreadsheetGear.IWorksheet]$ws = $wb.Worksheets.Add()
$ws.Cells["B1:E1"].Merge()
$ws.Cells["B1"].Value = "Van Halen Albums"
$ws.Cells["B1"].Font.Size = 20
$ws.Cells["B1"].Font.Bold = $true
$pointer = 3
$ws.Cells["A$pointer"].Value = "Year"
$ws.Cells["B$pointer"].Value = "Album Name"
$ws.Cells["C$pointer"].Value = "Chart Placement"
 
$cells = ("A", "B", "C")
foreach ($cell in $cells) {
 $c = $cell + $pointer.ToString()
 #$ws.Cells[$c].HorizontalAlignment = [SpreadsheetGear.HAlign]::Center
 $ws.Cells[$c].Font.Size = 11
 $ws.Cells[$c].Font.Bold = $true
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeTop].Weight = [SpreadsheetGear.BorderWeight]::Thin
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeRight].Weight = [SpreadsheetGear.BorderWeight]::Thin
 $ws.Cells[$c].ColumnWidth = 15
    
}
#start data cells
foreach ($row in $table.Rows) {
 $pointer +=1
 $ws.Cells["A$pointer"].Value = $row.Item("Year")
 $ws.Cells["B$pointer"].Value = $row.Item("Album_Nm")
 $ws.Cells["C$pointer"].Value = $row.Item("US_Chart")
}
$wb.SaveAs("C:\temp\chadisawesome.xlsx", [SpreadsheetGear.FileFormat]::OpenXMLWorkbook)
#You get the point....

 

Håþþ¥ .ñꆆïñg…

No comments:

Post a Comment