Functions/Convert-ExcelToPdf.ps1
|
<#
.SYNOPSIS This function converts an Excel workbook, worksheet or range of cells to PDF. #> function Convert-ExcelToPdf { [CmdletBinding(PositionalBinding=$true, DefaultParameterSetName="workbook")] [OutputType([Bool])] param ( # The path for the Excel workbook file. [Parameter(Mandatory=$true, ParameterSetName="workbook", Position=0)] [Parameter(Mandatory=$true, ParameterSetName="worksheet", Position=0)] [Parameter(Mandatory=$true, ParameterSetName="cellRange", Position=0)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$excelWorkbookFilePath, # The path for the PDF file. [Parameter(Mandatory=$true, ParameterSetName="workbook", Position=1)] [Parameter(Mandatory=$true, ParameterSetName="worksheet", Position=1)] [Parameter(Mandatory=$true, ParameterSetName="cellRange", Position=1)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path (Split-Path $_ -Parent) -ErrorAction SilentlyContinue) })] [String]$pdfFilePath, # The path for the folder containing the XlsToPdf program installation. [Parameter(Mandatory=$true, ParameterSetName="workbook", Position=2)] [Parameter(Mandatory=$true, ParameterSetName="worksheet", Position=2)] [Parameter(Mandatory=$true, ParameterSetName="cellRange", Position=2)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$xlsToPdfFolderPath, # The name of worksheet to convert. [Parameter(Mandatory=$true, ParameterSetName="worksheet")] [Parameter(Mandatory=$true, ParameterSetName="cellRange")] [ValidateNotNullOrEmpty()] [String]$worksheetName, # The cell range to convert. [Parameter(Mandatory=$true, ParameterSetName="cellRange")] [ValidateNotNullOrEmpty()] [String]$cellRange ) try { # Import the Spire.XLS dlls # Use ReadAllBytes to load instead so that there is no lock on the dll files when we're done # This ensures that the dll files can be deleted afterwards Write-Information "Loading the Spire.XLS dll." if (![System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.License.dll"))) { throw "Failed to load the Spire.License dll." } if (![System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.pdf.dll"))) { throw "Failed to load the Spire.Pdf dll." } if (![System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.XLS.dll"))) { throw "Failed to load the Spire.XLS dll." } # Apply License file [Spire.License.LicenseProvider]::SetLicenseFileFullPath("$($xlsToPdfFolderPath)\license.elic.xml") # Create Spire.XLS workbook and load it with the template contents Write-Information "Creating a Spire.XLS workbook with the Excel file contents." $workbook = [Spire.Xls.Workbook]::new() $workbook.LoadFromFile($excelWorkbookFilePath, [Spire.Xls.ExcelVersion]::Version2016) # Perform the conversion switch ($PSCmdlet.ParameterSetName) { # Convert the entire workbook workbook { $workbook.ConverterSetting.SheetFitToPage = $true $workbook.SaveToFile($pdfFilePath, [Spire.Xls.FileFormat]::PDF) } # Convert a worksheet worksheet { $workbook.ConverterSetting.SheetFitToPage = $true $workbook.Worksheets[$worksheetName].SaveToPdf($pdfFilePath) } # Convert a cell range cellRange { $workbook.ConverterSetting.SheetFitToPage = $true # Create a new worksheet with the cell range contents $tempSheetName = [Guid]::new().Guid $workbook.Worksheets.Add($tempSheetName) $workbook.Worksheets[$excelWorksheetName].Range[$cellRange].Copy($workbook.Worksheets[$tempSheetName].Range[$cellRange]) # Save the worksheet as a pdf $workbook.Worksheets[$tempSheetName].SaveToPdf($pdfFilePath) } } # Success return $true } catch { Write-Error "Error on line $($_.InvocationInfo.ScriptLineNumber) while converting Excel workbook to PDF. `r`n$($_.Exception.Message)" return $false } } |