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


How to Install Virtualbox 4.3.26

May 13, 2015

Getting Virtualbox installed on a Windows 7 x64 was not that easy ?

Especially this version 4.3.26

image

I saw similar issues relating to earlier versions as well on the net.

Here are the fixes :

1st issue was running the installer that ended up in an error, and next did a ‘complete rollback’ in the installed files Sad smile

To fix it follow these steps:

Uninstall Virtualbox

  1. Uninstall Any Virtual Box Network Adaptors from Device Manager

image

  1. Go into the registry at: HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Network
  2. Change “MaxFilters” from 8 to 20 (decimal)”
  3. image

  4. Reboot your PC (I don’t think this is needed but can’t hurt)
  5. Install VirtualBox 4.3.X (Run as Administrator)

After this change the installation ran just fine.

2nd issue

Once installed I got an other error when launching the VM.

Error STATUS_OBJECT_NAME_NOT_FOUND 0xc0000034

image

First run this command : sc.exe query vboxdrv

If you see that it can’t find the vboxdrv driver, you need to check this registry key :

HKLM\SYSTEM\CurrentControlSet\Services\VBoxDrv

Check the ImagePath key, and make sure it holds this value

\??\C:\Program Files\Oracle\VirtualBox\drivers\vboxdrv\VBoxDrv.sys 

image

If not change it and run this command as Administrator :

sc start vboxdrv

image

Once the service is RUNNING all is working fine Smile

image

Enjoy!