Excel.psm1
|
<#
.SYNOPSIS This function downloads the BitTitan XlsToPdf program zip file into a folder and unzips it. #> function Get-BitTitanXlsToPdfProgram { [CmdletBinding(PositionalBinding=$true)] [OutputType([Bool])] param ( # The path to the folder where the program will be installed. [Parameter(Mandatory=$true)] [String]$installFolderPath ) # Generate the program zip file path $zipFilePath = "$($installFolderPath)\XlsToPdf.zip" # Verify the filepath if (!(Test-Path -Path $installFolderPath -ErrorAction SilentlyContinue)) { Write-Error "The path '$($installFolderPath)' is not available." return $false } # Download the program zip file if (!(Get-FileFromUrl -FileUrl "https://bittitangdpreport.blob.core.windows.net/template/XlsToPDF.zip" -DestinationPath $zipFilePath)) { Write-Error "Failed to download the BitTitan XlsToPdf program." return $false } # Unzip the program zip file try { Expand-Archive -Path $zipFilePath -DestinationPath $installFolderPath -Force -Confirm:$false } catch { Write-Error "Error while unzipping the BitTitan XlsToPdf program.`r`n$($_.Exception.Message)" return $false } # Test for all the files in the zip # TODO: Implement checksum to ensure that files are not corrupted $expectedFiles = @( "Microsoft.mshtml.dll", "Spire.License.dll", "Spire.Pdf.dll", "Spire.Xls.dll", "license.elic.xml" ) foreach ($expectedFile in $expectedFiles) { if (!(Test-Path -Path "$($installFolderPath)\$($expectedFile)" -ErrorAction SilentlyContinue)) { Write-Error "Expected file '$($expectedFile)' to be present after unzipping the BitTitan XlsToPdf program." return $false } } # Installation was successful return $true } <# .SYNOPSIS This function converts a CSV string into an Excel workbook. #> function Convert-CsvStringToExcelWorkbook { [CmdletBinding(PositionalBinding=$false)] [OutputType([OfficeOpenXml.ExcelPackage])] param ( # The CSV string containing the data which will be stored in the Excel workbook [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [String]$csvString, # The path where the Excel workbook will be stored. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [String]$excelWorkbookFilePath, # The name of the worksheet that will hold the CSV data within the workbook. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [String]$excelWorksheetName ) # Test the workbook directory path $workbookDirectoryPath = Split-Path $excelWorkbookFilePath -Parent if (!(Test-Path -Path $workbookDirectoryPath -ErrorAction SilentlyContinue)) { Write-Error "Destination directory for Excel workbook '$($workbookDirectoryPath)' is invalid." return } # Create the Excel workbook try { $excelWorkbook = $csvString | ConvertFrom-Csv | Export-Excel -Path $excelWorkbookFilePath -WorksheetName $excelWorksheetName -PassThru } catch { Write-Error "Error while converting CSV string to Excel workbook.`r`n$($_.Exception.Message)" return } if (!$excelWorkbook) { Write-Error "Failed to convert CSV string to Excel workbook." return } # Return the Excel workbook return $excelWorkbook } <# .SYNOPSIS This function performs the conversion of an Excel workbook to PDF. .SYNOPSIS This function performs the conversion of an Excel workbook to PDF. It currently uses Spire.XLS for .NET, which is packaged as .dll files. #> function Convert-ExcelWorkbookToPDF { [CmdletBinding(PositionalBinding=$false)] [OutputType([Bool])] param ( # The path for the Excel workbook [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$excelWorkbookFilePath, # The path for the PDF. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path (Split-Path $_ -Parent) -ErrorAction SilentlyContinue) })] [String]$pdfFilePath, # The path for the folder containing the XlsToPdf converter. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$xlsToPdfFolderPath ) 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." $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.License.dll")) $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.Pdf.dll")) $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.XLS.dll")) if (!$loadedAssembly) { 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) # Save the workbook as a pdf $workbook.ConverterSetting.SheetFitToPage = $true $workbook.SaveToFile($pdfFilePath, [Spire.Xls.FileFormat]::PDF) # Success return $true } catch { Write-Error "Error on line $($_.InvocationInfo.ScriptLineNumber) while converting Excel workbook to PDF. `r`n$($_.Exception.Message)" return $false } } <# .SYNOPSIS This function performs the conversion of an Excel worksheet to PDF. .SYNOPSIS This function performs the conversion of an Excel worksheet to PDF. It currently uses Spire.XLS for .NET, which is packaged as .dll files. #> function Convert-ExcelWorksheetToPDF { [CmdletBinding(PositionalBinding=$false)] [OutputType([Bool])] param ( # The path for the Excel workbook [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$excelWorkbookFilePath, # The name of the Excel worksheet [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [String]$excelWorksheetName, # The path for the PDF. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path (Split-Path $_ -Parent) -ErrorAction SilentlyContinue) })] [String]$pdfFilePath, # The path for the folder containing the XlsToPdf converter. [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateScript({ (Test-Path -Path $_ -ErrorAction SilentlyContinue) })] [String]$xlsToPdfFolderPath ) 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." $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.License.dll")) $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.Pdf.dll")) $loadedAssembly = [System.Reflection.Assembly]::Load([IO.File]::ReadAllBytes("$($xlsToPdfFolderPath)\Spire.XLS.dll")) if (!$loadedAssembly) { 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() # Setup fit to page $workbook.ConverterSetting.SheetFitToPage = $true # Load workbook from file and worksheet $workbook.LoadFromFile($excelWorkbookFilePath, [Spire.Xls.ExcelVersion]::Version2016) $worksheet = $workbook.Worksheets[$excelWorksheetName] # Save the workbook as a pdf $worksheet.SaveToPdf($pdfFilePath) # Success return $true } catch { Write-Error "Error on line $($_.InvocationInfo.ScriptLineNumber) while converting Excel worksheet to PDF. `r`n$($_.Exception.Message)" return $false } } |