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 XLS Converter 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]$xlsConverterFolderPath, # 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 XLS Converter Functions from the Spire DLL ImportXLSConverterFunctions -XlsConverterFolderPath $xlsConverterFolderPath # 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 } } |