Post reply

Create/write to an Excel 2007/2010 spreadsheet from an SSIS package

  • December 15, 2016 at 2:32 pm

    #1918293

    CoolDown (12/15/2016)

    How do you get EPPlus.dll installed in GAC on windows server 2008 R2 without GACUTIL or Win SDK?

    You might try this: https://wingac.codeplex.com/[/url]

  • December 15, 2016 at 2:42 pm

    #1918296

    There's a NuGet package to install EPPlus. I think I did it that way. It'll also get installed with the ExcelPSLib.psm1 module, but in the subfolder for the module, so when I do this:

    import-Module ExcelPSLib -Force > $null

    #... then EPPlus.dll will be available to your Powershell script, too...

    #This may help as well:

    #http://www.madwithpowershell.com/2013/10/add-type-vs-reflectionassembly-in.html

    #how to directly reference EPPLus.dll if you know where it's at..

    [Reflection.Assembly]::LoadFrom("C:\Windows\System32\WindowsPowerShell\v1.0\Modules\ExcelPSLib\EPPlus.dll")

    #[Reflection.Assembly]::LoadWithPartialName("EPPlus") > $null

    Add-Type -AssemblyName EPPlus > $null

    then, this should get you started:

    [OfficeOpenXml.ExcelPackage]$excel = New-OOXMLPackage -author "Alfred E. Newman" -title "GettingSerious"

    [OfficeOpenXml.ExcelWorkbook]$book = $excel | Get-OOXMLWorkbook

    #the rest is very similar to how you manipulate Excel via COM interop/VBA.

    One thing, to get dates into a reasonable format, you'll want to set up first your own StyleSheet:

    $StyleDateTime = New-OOXMLStyleSheet -WorkBook $book -Name "DateTime1" -NFormat "m/d/yyyy hh:mm"

    $StyleDateMDYYYY = New-OOXMLStyleSheet -WorkBook $book -Name "DateTime2" -NFormat "m/d/yyyy"

    (don't worry, you actually end up doing the same thing when you set a custom date/time format manually on cells/range in Excel, too).

    I apologize for the crudeness of my code snips, and obviousness of my copy-and-pastings from Google searches, but it works good enough for me, mostly as a proof-of-concept for now.

    Of note: EPPlus.dll v4.1 has some new methods that will help load bigger sets of data into Excel. Not quite as bitchin' fast as COM Interop's Range.LoadFromArray, but much better than one cell at a time.

    I have not used NPOI to work with Excel files, but if you have to use Excel.old (.xls) files, then EPPlus can't help, as it only does Excel.new (.xlsx).

    The main weirdness I've noticed is that EPPlus puts text data values into cells basically as a nvarchar(max), rather than a nvarchar(255). So when you open your spreadsheet in Excel the first time, it'll ask if you want to save it when you close it, where I think it downcasts the values.

    other than that, EPPlus seems great. No setting up template spreadsheets. No trying to undo what Excel tries to "help" with respect to coercing numeric strings to number values, the stupid default date format for imported date strings, having to mess with the text import wizard manually, or setting up SCHEMA.INI files for ODBC.

    It should make your systems admins happy too, for not having to install Office on servers, having to write the ham-fisted code to try and kill your hanging Excel process after the fact, trying to write all the code to get all the COM references released so your Excel process can go away gracefully on its own, etc.

    The sample C# application from the EPPlus crew is pretty useful and transferable to Powershell.

    The other way I like to get database data into Excel, albeit manually, is via PowerQuery...

    happy hacking

Viewing 2 posts - 46 through 46 (of 46 total)

You must be logged in to reply to this topic. Login to reply