A few days ago at work I needed to create a quick report on some info from one of our VMware clusters. I proceeded to dump some data to a CSV file and then converted it to an XLS file.
I then modified/cleaned up the data and my report was done.
I started to clean up the code for reuse and decided to skip the CSV file altogether and just save the data directly to an Excel file.
Listed here are some samples to do just that:
NOTE: This assumes that you have powershell and Excel installed on your local machine.
Create the Spreadsheet
For starters, we use the Excel COM object to create a workbook with a worksheet.
$excel = new-object -comobject Excel.Application $workbooks = $excel.Workbooks.Add() $worksheets = $workbooks.worksheets $worksheet = $worksheets.Item(1) $worksheet.Name = “Name of Worksheet”
If you have data in a spreadsheet that you just want to query, its usually best to just keep the Excel window hidden by setting the following value to $False. However if you want to see the Excel window you just set the value to $True:
$excel.Visible = $True
If you were run the script at this point, and you enabled the $Excel.Visible value, you would have a blank Excel spreadsheet open and ready to receive data, but not very useful at this point.
Now we can start doing some useful things. We can actually modify specific cells within the spreadsheet. The following examples will add some text to a few cells.
$worksheet.Cells.Item(1,1) = “This is CELL A1” $worksheet.Cells.Item(3,4) = “This is CELL D3”
This puts “This is CELL A1” into A,1 and “This is CELL D3” into D,3. The first number is the row identifier and the second number is the column identifier.
You can even customize the cells by changing the fonts used, the font size and even BOLD the text if needed. The following sample shows how to accomplish this:
$worksheet.Cells.Item(1,1).Font.Bold = $True $worksheet.Cells.Item(1,1).Font.Size = 22 $worksheet.Cells.Item(4,3).Font.Italic = $True $worksheet.Cells.Item(4,3).Font.Size = 14
What if you have over 100 cells that need format changes? If would be very time consuming to add more than 200 lines of code needed to accomplish that by using the methods above. But there are alternatives available. We can format a range of cells by using the following:
$range = $excel.Range(“C3″,”C4″) $range.ColumnWidth = 50 $range.Borders.Color = 0 $range.Borders.Weight = 2 $range.Interior.ColorIndex = 37 $range.Font.Bold = $True $range.HorizontalAlignment = 3
If you are curious about all of the values that can be modified, type the following command:
$range | get-member
Saving the Spreadsheet
Once your done modifying the spreadsheet, you should be sure to save your data.
This can be done by using:
Once the spreadsheet is saved, you could even use Powershell to send it via email or copy it to a network share. You can even schedule the powershell script to run as a scheduled task and automate the whole process.