String formatting

[DarkLite1]

When exporting phone numbers from active directory user objects into an Excel sheet, the formatting completely disappears. In AD we have phone numbers like '+32 4 4444 444'. But when pass them on to Export-Excel it shows us '3244444444' and the plus sign disappears.

Example code:

$Users = Get-ADUser -SearchBase 'OU=Users,DC=domain,DC=net' -Filter * -Properties MobilePhone, Fax, OfficePhone, HomePhone

$Users | Export-Excel -Path S:\Test\Out_Test\ADList.xlsx -WorkSheetname 'AD' -TableName 'AD' -FreezeTopRow

Is there a way to have Excel show the number as a string, like the original? I tried the following without success:

$Users | select *, @{label='Tel';Expression={' ' + $_.'Telephone number'}} -ExcludeProperty 'Telephone number' |

 Export-Excel -Path S:\Test\Out_Test\ADList.xlsx -WorkSheetname 'AD' -TableName 'AD' -FreezeTopRow

Thank you for your help.

[dfinke]

Thanks for reporting this. I've been thinking about adding a switch [Switch]$AsIs that will not inspect data types.

I'd have thought your workaround would have solved the problem.

You might want to try to prepend a single quote to the data '

I'm not sure why this worked for me.

$file = 'c:\temp\myexcelreport.xlsx'
rm $file -ErrorAction Ignore

$(
    New-PSItem '+32 4 4444 444'
    New-PSItem '+32 4 4444 445'
) | export-excel $file -Show

[DarkLite1]

Thank you for the reply Doug. I tried your code but PowerShell complains:

New-PSItem : The term 'New-PSItem' is not recognized as the name of a cmdlet, function, script file, or operable program.

[dfinke]

Do you have the latest version of the module?

[DarkLite1]

You are correct, I wasn't using the latest version. Just updated and tried this:

$File = 'S:\Test\Out_Test\List.xlsx'
rm $File -ErrorAction Ignore

$Users = Get-ADUser -SearchBase 'OU=Users,DC=domain,DC=net' -Filter * -Properties MobilePhone
$Users | Select SamAccountName ,MobilePhone, @{l='tel';E={New-PSItem $_.MobilePhone}} | Export-Excel $File 

But then I get this error:

Cannot find an overload for "tryparse" and the argument count: "2".
At S:\Test\Input_Test\ImportExcel\Export-Excel.ps1:167 char:24

I'm probably missing something obvious here, but the help of New-PSItem is non existing. So I don't really know what it's meant to do.

Also, when I try this:

$File = 'S:\Test\Out_Test\List.xlsx'
rm $File -ErrorAction Ignore

$(
    New-PSItem '+32 4 4444 444'
    New-PSItem '+32 4 4444 445'
) | Export-Excel $File

I get this:

This is only on the server, when testing the last example on my Win 7 workstation I does keep the '+' signs. So it might be region related.

[dfinke]

Sorry, I wasn't clear. The New-PSItemis meant to work only the second way you used it.

What OS is the server? Can you find out what version of .NET is installed there too?

In your original example, can you add a ' to the from of the Phone # and see if that works?

[DarkLite1]

Hi Doug, I'm trying to answer your questions below. The following code is used for all examples with your latest version:

$File = 'S:\Test\Out_Test\List.xlsx'
rm $File -ErrorAction Ignore

$(
    New-PSItem '+32 4 4444 444'
    New-PSItem '+32 4 4444 445'
) | Export-Excel $file

Windows 7 Enterprise 64-bit

• PowerShell 4.0
• Microsoft .NET Framework 4.5 Multi-Targeting Pack - Version 4.5.50710
• Microsoft .NET Framework 4.5 SDK - Version 4.5.50710
• Microsoft .NET Framework 4.5.1 Multi-Targeting Pack - Version 4.5.50932
• Microsoft .NET Framework 4.5.1 Multi-Targeting Pack (ENU) - Version 4.5.50932
• Microsoft .NET Framework 4.5.1 SDK - Version 4.5.51641
• Microsoft .NET Framework 4.5.2 - Version 4.5.51209

Windows Server 2012 64-bit

• PowerShell 4.0
• Microsoft .NET Framework 4 Multi-Targeting Pack - Version 4.0.30319

Modifying with Select-Object expression

I tried this:

$AD | Select SamAccountName ,MobilePhone, @{l='tel';E={"'" + $_.MobilePhone}} | Export-Excel $File 

Result:

The moment I try to modify the phone number in 'tel' it adjusts correctly:

Is there really no way to have the phone numbers 'as is' in the Excel sheet when using the pipeline to Export-Excel?

[dfinke]

I cannot repro this in my environment. Hard to pin down.

You may have tried this. If you create the spreadsheet on the server and then open the file on Windows 7, what result do you get?

Thanks for going through all of this.

[DarkLite1]

No problem Doug, I already appreciate you looking into this. I've always opened the file from the Windows 7 workstation, as I don't have an Excel installation on the server. This means that the results would be the same as previously reported. Can it be due to the .NET differences?

[dfinke]

Yeah, it looks like it is coming down to the .NET versions, but don't know if it is the root cause. The underlying mechanism this is layered on used the Microsoft Office .NET framework to read/write the Excel file. An update from .NET 4.0 to 4.5 could definitely make the difference.

If is very much suspect since it works on the 4.5 .NET box.

[DarkLite1]

Couldn't figure it out. Tried updating the .NET version and other stuff, no success until now :)

The problem lies within Export-Excel, and to be specific, this piece of code:

if([double]::tryparse($cellValue, [ref]$r)) {
    $targetCell.Value = $r
} else {
    $targetCell.Value = $cellValue
}

On my Win Srv 2012 it goes to $targetCell.Value = $r and on Win 7 it goes to $targetCell.Value = $cellValue. The same code used on both systems:

'+32 4 4444 444' | Export-Excel $file -show

Better test:

$Test = '+32 4 4444 444'
[Double]::TryParse($Test, [ref]$null)

Result:
Win Srv 2012 = True
Win 7 = False

This appears to be related to the Culture used on the system from what I can find online. But both systems are using the same Culture:

Get-Culture
LCID             Name             DisplayName                                  
----             ----             -----------                             
2067             nl-BE            Dutch (Belgium)  

Get-UICulture
LCID             Name             DisplayName                                  
----             ----             -----------                   
1033             en-US            English (United States)                      

Does this give you a clue? Why is it evaluating a [Double]::TryParse differently on both systems? I checked the region, ... all seems the same. Any help here would be greatly appreciated. As this would solve one of our major issues exporting phone numbers.

[dfinke]

Thanks for drilling down on this.

Culture seems possible but if both systems are set the same, the mystery continues. The TryParse is a pure .NET call, so that is clue. There are two methods on the TryParse, perhaps using the second one is how to make this more robust.

static bool TryParse(string s, System.Globalization.NumberStyles style, System.IFormatProvider provider, [ref] double result)

[dfinke]

Can you please try this on the server and post the results?

echo "1.2" "1" "a" "-31" "+32 44" '+32 4 4444 444' | % { 
    $result=[Double]::TryParse(
        [Convert]::ToString($_), 
        [System.Globalization.NumberStyles]::Any,
        [System.Globalization.NumberFormatInfo]::InvariantInfo, 
        [ref]$null
    )

    [pscustomobject][ordered]@{
        Value=$_
        Result=$result
    }
}

Here are mine:

Value          Result
-----          ------
1.2              True
1                True
a               False
-31              True
+32 44          False
+32 4 4444 444  False

[DarkLite1]

Same result as you, both on server and on client:

Value           Result
-----           ------
1.2               True
1                 True
a                False
-31               True
+32 44           False
+32 4 4444 444   False

So this InvariantInfo seems to be the solution. It would be great if this could be implemented in the Export-Excel module.

[dfinke]

Thanks for trying that. This looks like good news.

How difficult would it be for you to update the module after I implement?

What day this week would be good for you? I'd update in GitHub first so you'd need to grab it there.

[DarkLite1]

You're welcome Doug! Strange that I'm the only one experiencing this issue but I'm glad we (you) finally figured it out :)

I would really like to update to the new version, the only modifications I made myself are:

• Import-Excel to only import data and not empty rows/columns (remember -Raw switch we discussed)
• The UNC-path issue reported here

If these two fixes and the one from this tread could make it into the production branch I can update our old module without worries. And then there's no need for me anymore to be out of sync with your repository.

Thank you in any case for the great job you did and still do on this awesome module!

[dfinke]

Thank you, couldn't have done it without you. The problem looks solved, but don't know why it is happening.

The two modifications you made, I have not addressed in the module.

Plus, I've added new features like conditional formatting.

I'm going to add the TryParse change. Seems like you'll need to do some testing before going to production.

[dfinke]

OK, I pushed that change as well as other feature updates. If you try it, let me know if you have issues. I don't have a time frame for when I can get to the -Raw or the UNC. I punted on the UNC because I believe there is a a work around using the ProviderPath when you pass it to the function.

[DarkLite1]

Thank you Doug, again great work you delivered! Tested the function this morning and all seems to function fine! The only difference is that you changed the TableStyle from Medium=9 to Medium=6 I believe. Anyway, I like the blue better ;)