__tests__/Copy-ExcelWorksheet.Tests.ps1
|
$path1 = "$env:TEMP\Test1.xlsx" $path2 = "$env:TEMP\Test2.xlsx" Remove-item -Path $path1, $path2 -ErrorAction SilentlyContinue $ProcRange = Get-Process | Export-Excel $path1 -DisplayPropertySet -WorkSheetname Processes -ReturnRange if ((Get-Culture).NumberFormat.CurrencySymbol -eq "�") {$OtherCurrencySymbol = "$"} else {$OtherCurrencySymbol = "�"} [PSCustOmobject][Ordered]@{ Date = Get-Date Formula1 = '=SUM(F2:G2)' String1 = 'My String' Float = [math]::pi IPAddress = '10.10.25.5' StrLeadZero = '07670' StrComma = '0,26' StrEngThousand = '1,234.56' StrEuroThousand = '1.555,83' StrDot = '1.2' StrNegInt = '-31' StrTrailingNeg = '31-' StrParens = '(123)' strLocalCurrency = ('{0}123.45' -f (Get-Culture).NumberFormat.CurrencySymbol ) strOtherCurrency = ('{0}123.45' -f $OtherCurrencySymbol ) StrE164Phone = '+32 (444) 444 4444' StrAltPhone1 = '+32 4 4444 444' StrAltPhone2 = '+3244444444' StrLeadSpace = ' 123' StrTrailSpace = '123 ' Link1 = [uri]"https://github.com/dfinke/ImportExcel" Link2 = "https://github.com/dfinke/ImportExcel" # Links are not copied correctly, hopefully this will be fixed at some future date } | Export-Excel -NoNumberConversion IPAddress, StrLeadZero, StrAltPhone2 -WorkSheetname MixedTypes -Path $path2 Describe "Copy-Worksheet" { Context "Simplest copy"{ BeforeAll { Copy-ExcelWorkSheet -SourceWorkbook $path1 -DestinationWorkbook $path2 $excel = Open-ExcelPackage -Path $path2 $ws = $excel.Workbook.Worksheets["Processes"] } it "Inserted a worksheet " { $Excel.Workbook.Worksheets.count | Should be 2 $ws | Should not benullorEmpty $ws.Dimension.Address | should be $ProcRange } } Context "Mixed types using a package object"{ BeforeAll { Copy-ExcelWorkSheet -SourceWorkbook $excel -DestinationWorkbook $excel -DestinationWorkSheet "CopyOfMixedTypes" Close-ExcelPackage -ExcelPackage $excel $excel = Open-ExcelPackage -Path $path2 $ws = $Excel.Workbook.Worksheets[3] } it "Copied a worksheet, giving the expected name, number of rows and number of columns " { $Excel.Workbook.Worksheets.count | Should be 3 $ws | Should not benullorEmpty $ws.Name | Should be "CopyOfMixedTypes" $ws.Dimension.Columns | Should be 22 $ws.Dimension.Rows | Should be 2 } it "Copied the expected data into the worksheet " { $ws.Cells[2, 1].Value.Gettype().name | Should be 'DateTime' $ws.Cells[2, 2].Formula | Should be 'SUM(F2:G2)' $ws.Cells[2, 5].Value.GetType().name | Should be 'String' $ws.Cells[2, 6].Value.GetType().name | Should be 'String' $ws.Cells[2, 18].Value.GetType().name | Should be 'String' ($ws.Cells[2, 11].Value -is [valuetype] ) | Should be $true ($ws.Cells[2, 12].Value -is [valuetype] ) | Should be $true ($ws.Cells[2, 13].Value -is [valuetype] ) | Should be $true $ws.Cells[2, 11].Value | Should beLessThan 0 $ws.Cells[2, 12].Value | Should beLessThan 0 $ws.Cells[2, 13].Value | Should beLessThan 0 if ((Get-Culture).NumberFormat.NumberGroupSeparator -EQ ",") { ($ws.Cells[2, 8].Value -is [valuetype] ) | Should be $true $ws.Cells[2, 9].Value.GetType().name | Should be 'String' } elseif ((Get-Culture).NumberFormat.NumberGroupSeparator -EQ ".") { ($ws.Cells[2, 9].Value -is [valuetype] ) | Should be $true $ws.Cells[2, 8].Value.GetType().name | Should be 'String' } ($ws.Cells[2, 14].Value -is [valuetype] ) | Should be $true $ws.Cells[2, 15].Value.GetType().name | Should be 'String' $ws.Cells[2, 16].Value.GetType().name | Should be 'String' $ws.Cells[2, 17].Value.GetType().name | Should be 'String' ($ws.Cells[2, 19].Value -is [valuetype] ) | Should be $true ($ws.Cells[2, 20].Value -is [valuetype] ) | Should be $true } } } |