Thursday, June 30, 2016

Creating conditional hyperlinks in MS Excel




In cell 'B2'...


=IF(A2="goodbye",HYPERLINK("https://www.google.com/search?q="&A1,"Search Google for: "&A1),"")


Note: that last ~,""~ {comma double quote double quote}, provides the 'IF' statement with instructions on what to do if the argument returns a 'FALSE' - In the above case, just a blank cell.


If that part is left off, and the argument is 'FALSE', then the cell will contain 'FALSE', but you can put anything you want in there...

Some other text (between those quote-marks), or even a different HYPERLINK:


=IF(A2="goodbye",HYPERLINK("https://www.google.com/search?q="&A1,"Search Google for: "&A1),HYPERLINK("https://www.google.com/search?q="&A2,"Search Google for: "&A2))

Tuesday, June 28, 2016

iText / iTextSharp / Powershell / checkboxes

Some cryptic notes related to how 'Checkbox' values are addressed when using iText / iTextSharp, .NET PDF library - To automate filling in PDF forms.

I wasn't able to find any information on how to fill in a checkbox on a PDF form, when using iText in Powershell.
So, hopefully this blog entry will help others.
HINT: the value is 'On'

Anyway - This is a decent example (I hope) -

The 'break' just stops the script.
After that, is the mechanics of how I figured out what value needed to be used for the 'check-boxes' on the form.




$TimeStamp = Get-Date -UFormat %b_%d_%H_%M


# Sauce:
#        http://itextpdf.com/
#        http://www.tomsitpro.com/articles/powershell-fill-pdf-forms,2-38.html#xtor=RSS-100
#        https://sourceforge.net/projects/itextsharp/
#        https://github.com/adbertram/Random-PowerShell-Work/blob/master/Random%20Stuff/PdfForm.psm1
#        https://gallery.technet.microsoft.com/scriptcenter/Use-Powershell-to-Fill-3f28ba21


## NOTE - The DLL files need to be 'UNBLOCKED' before they will load / run. This has to be done manually or by using the Unblock-File command
## Run as admin...
#Get-ChildItem -recurse "C:\Powershell Stuff\itextsharp-all" | Unblock-File #-WhatIf

## And this module is needed, in order to use the actual .NET library - So Import it.
Import-Module –Name "C:\Powershell Stuff\PdfForm.psm1" -Verbose

#Get-PdfFieldNames -FilePath "C:\Powershell Stuff\test_Jun_28_11_44.pdf" -ITextLibraryPath 'C:\Powershell Stuff\itextsharp-all\itextsharp-dll-core\itextsharp.dll'

#################################################
$ITextSharpLibrary = "C:\Powershell Stuff\itextsharp-all\itextsharp-dll-core\itextsharp.dll"

$InputPdfFilePath = "C:\Users\\Documents\HR_IT_Request01.pdf"
$OutputPdfFilePath = "C:\Powershell Stuff\test_$TimeStamp.pdf"


#<#
$First_Name = "FirstName"
$MI = "E"
$Last_Name = "LastName"
$EmployeeID_Number = "OU812-ACK"
$Date_of_Request = Get-Date -format d
$Job_Title = "PEON"
$Manager = "Control Freak"
$Email_Check = "On" # The two values for a checkbox is 'On', or leaving it untouched / blank / null - Really just one value - 'On'

#Save-PdfField -Fields @{'Name' = 'test123';'Bank Name' = 'some bank'} -InputPdfFilePath "C:\Vendor-Setup-Form.pdf" -ITextSharpLibrary 'C:\users\adam\dir\itextsharp.dll' -OutputPdfFilePath 'C:\Users\Adam\test4.pdf'

Save-PdfField -Fields @{`
'First_Name' = $First_Name;`
'MI' = $MI; 
'Last_Name' = $Last_Name;`
'EmployeeID_Number' = $EmployeeID_Number;
'Email_Check' = $Email_Check;`
'Job_Title' = $Job_Title;`
'Manager' = $Manager;`

'Date_of_Request' = $Date_of_Request`
} `
-InputPdfFilePath $InputPdfFilePath -ITextSharpLibrary $ITextSharpLibrary -OutputPdfFilePath $OutputPdfFilePath

break
##

[System.Reflection.Assembly]::LoadFrom($ITextSharpLibrary)
#$OutputPdfFilePath = "C:\Powershell Stuff\test_Jun_28_12_20_01.pdf"
$reader = New-Object iTextSharp.text.pdf.PdfReader -ArgumentList $OutputPdfFilePath
# List available fields
# $reader.AcroFields.Fields
# Get the value of the the named field. Field name is case sensitive.
# The two values for a checkbox is 'On', or leaving it untouched / blank / null - Really just one value - 'On'
$FieldNames = Get-PdfFieldNames -FilePath $OutputPdfFilePath -ITextLibraryPath 'C:\Powershell Stuff\itextsharp-all\itextsharp-dll-core\itextsharp.dll'
ForEach ($FieldName in $FieldNames) {
$Name = $reader.AcroFields.GetField($FieldName)
Write-Host "$FieldName`: " -NoNewline
Write-Host "$Name" -ForegroundColor Cyan

}

$reader.Close()