Generating XLSX files without Excel

May 31, 2015

Ever got the need to create excel files without the need of having Excel installed.

Well start reading here http://ramblingcookiemonster.github.io/PSExcel-Intro/

The Powershell module is based on EPPLus Library.

Using this nice PSExcel Module you can do a lot.

First thing is to download the Module and install it in this location.

C:\Users\YourUser\Documents\WindowsPowerShell\Modules\PSExcel

image

Open Powershell ISE and run the Demo Script.

CLS

# Download PSExcel from https://github.com/RamblingCookieMonster/PSExcel
# Unblock the zip
# Extract the PSExcel folder to a module path (e.g. $env:USERPROFILE\Documents\WindowsPowerShell\Modules\)

# Import the module.
    Import-Module PSExcel    #Alternatively, Import-Module \\Path\To\PSExcel

# Get commands in the module
    Get-Command -Module PSExcel

# Get help for a command
    Get-Help Import-XLSX -Full

#Create some demo data
    $DemoData = 1..10 | Foreach-Object{
    
        $EID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$EID)

        New-Object -TypeName PSObject -Property @{
            Name = "jsmith$_"
            EmployeeID = $EID
            Date = $Date
        } | Select Name, EmployeeID, Date
    }

# Export it
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx

# Import it back
    $Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date

# Open that Excel file...
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx

# Get a workbook
    $Workbook = $Excel | Get-Workbook

# Get a worksheet - can pipe ExcelPackage or Workbook.
# Filtering on Name is optional
    $Excel | Get-Worksheet
    $WorkSheet = $Workbook | Get-Worksheet -Name Worksheet1

# Freeze the top row
    $WorkSheet | Set-FreezePane -Row 2

# Save and close!
    $Excel | Close-Excel -Save

# Re-open the file
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx
    
# Add bold, size 15 formatting to the header
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Bold $True -Size 14

# Save and re-open the saved changes
    $Excel = $Excel | Save-Excel -Passthru
    
#  Text was too large!  Set it to 11
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Size 11

    $Excel |
        Get-WorkSheet |
        Format-Cell -StartColumn 1 -EndColumn 1 -Autofit -AutofitMaxWidth 7 -Color DarkRed

# Save and close
    $Excel | Save-Excel -Close

# Search a spreadsheet
    Search-CellValue -Path C:\Temp\Demo.xlsx { $_ -like 'jsmith10' -or $_ -eq 280 }

# Add a table, autofit the data.  We use force to overwrite our previous demo.
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx -Table -Autofit -Force

# Fun with pivot tables and charts! Props to Doug Finke
# Get files in your profile, create a chart breaking down size by file extension
    Get-ChildItem $env:USERPROFILE -Recurse -File |
        Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie

Result is this nice PowerPivot Excel Sheet

image

Great addition for PS Smile


Excel Read – Fast way

August 24, 2014

In Powershell there are 2 ways of reading excel.

Fast and slow.

The Slow way is using the Excel COM interface.

CLS
$ExcelWB = new-object -comobject excel.application

$ExcelWB.Visible = $True
$Workbook = $ExcelWB.Workbooks.Open("C:\_\Apps\_PowerShell\test.xls") 
$Worksheet = $Workbook.Worksheets.Item(1)
...

The Fast way is using the OLEDB approach

CLS
$strFileName = "C:\_\Apps\_PowerShell\test.xls"
$strSheetName = 'Sheet1$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn
$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

$DBCounter=$DataReader.FieldCount

"Querying File $Excel ..."

While($DataReader.read())
{
 $Excel = $DataReader[0].Tostring() 
  
 for ($i = 0; $i -lt $DBCounter; $i++) {
  ($DataReader.GetName($i) + " " + $DataReader.GetValue($i))
  }
  echo ' '   
}  

$dataReader.close()
$objConn.close()

So if you have a need for speed, you know which one to choose. Smile