Export-Excel: Numeric values not correct

[DarkLite1]

Consider the following code:

if (!($TempFolder = $env:TEMP + '\TestFolder')) {
    New-Item -Path $TempFolder -ItemType Directory
}

$F = New-Object System.IO.FileStream ($TempFolder + '\File1.txt'), Create, ReadWrite
$F.SetLength(763905024)
$F.Close()

$F = New-Object System.IO.FileStream ($TempFolder + '\File2.txt'), Create, ReadWrite
$F.SetLength(1631405056)
$F.Close()

$F = New-Object System.IO.FileStream ($TempFolder + '\File3.txt'), Create, ReadWrite
$F.SetLength(271360)
$F.Close()

$F = New-Object System.IO.FileStream ($TempFolder + '\File4.txt'), Create, ReadWrite
$F.SetLength(417006592)
$F.Close()

$ExcelFile = $env:TEMP + '\Excel.xlsx'
if (Test-Path -Path $ExcelFile) {
    Remove-Item -Path $ExcelFile -Force
}

Get-ChildItem -Path $TempFolder | 
    Select-Object Name, Length, @{Name="Mb";Expression={'{0:N2}' -f ($_.Length/1Mb)}} |
        Export-Excel $ExcelFile -Show

This results in:

As you can see, the values behind the comma are discarded.
Example: 0,26 becomes 26 in the Excel file.

Also, some cells are stored as text rather then number. see C3.

[DarkLite1]

In issue #64 we did some tests which fixed some issues we had. However, when I do the same test for the value '1.555,83' it doesn't recognize it as a number. For value '0,26' it is correctly recognized but converted to '26' as you can see below:

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

    [pscustomobject][ordered]@{
        OriginalValue = $_
        NewValue      = if ($result) {$R} else {$_}
        isNumber      = $result
    }
}

Result:

OriginalValue          NewValue   isNumber
-------------          --------   --------
0,26                         26       True
1.555,83               1.555,83      False
1.2                         1,2       True
1                             1       True
a                             a      False
-31                         -31       True
+32 44                   +32 44      False
+32 4 4444 444   +32 4 4444 444      False

[DarkLite1]

I think I found it, InvariantInfo needs to be changed to CurrentInfo to respect the systems settings.

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

    [pscustomobject][ordered]@{
        OriginalValue = $_
        NewValue      = if ($result) {$R} else {$_}
        isNumber      = $result
    }
}

This generates the correct values being:

OriginalValue          NewValue    isNumber
-------------          --------    --------
0,26                       0,26        True
1.555,83                1555,83        True
1.2                          12        True
1                             1        True
a                             a       False
-31                         -31        True
+32 44                   +32 44       False
+32 4 4444 444   +32 4 4444 444       False

This is correct, even the '1.2' being converted on my system to '12'. This is because the dot separater is used for thousands. Please let me know what you think?

[dfinke]

I'll review. Nicely played, correctly handles US zip code "07670" and maintains the leading zero.

Oops, I lied, looked at the wrong column in the output. The leading zero is stripped.

[DarkLite1]

Hi Doug, that is correct, the leading zero would be stripped because it means nothing and is handled as a number in Excel. This is actually default behavior of Excel, you can see this when you open a new Excel worksheet and type in a cell for example 007 it will result in 7, because it's treated as a number and Excel doesn't know it's James Bond ;).

So, in short. to have a US ZIP code like 07670 being shown with a leading zero, these are the options:

• Handle it as Excel Text format instead of Excel format General (numerical value)
  (I don't know if you can do this with your module already)
• Handle it as a Numeric value (as it is now) and format in Excel with leading zero's
  (Maybe this type of formatting is supported in your module? Or can be?)

The same is true for a phone number. When there are spaces between the numbers (+32 4 444 44 44) it's considered correctly as being a String but when it's without spaces (+3244444444) it is correctly considered as being a numerical value. But what we really want is Excel to always show this field as Text just as your US Postal Codes.

At this moment, your code already provides us with a way of formatting all fields as text by using -Numberformat "@" as indicated here. However, as you can see below there is a bug in the code as it is indeed saved as Text after is has been wrongly converted to a number.

$ExcelFile = $env:TEMP + '\Excel.xlsx'
if (Test-Path -Path $ExcelFile) {
    Remove-Item -Path $ExcelFile -Force
}

$Result = echo '07670' "0,26" "1.555,83" "1.2" "1" "a" "-31" "+32 44" '+32 4 4444 444' '+3244444444' | % { 
    $R = $null
    $result=[Double]::TryParse(
        [Convert]::ToString($_), 
        [System.Globalization.NumberStyles]::Any,
        [System.Globalization.NumberFormatInfo]::CurrentInfo, 
        [ref]$R
    )

    [pscustomobject][ordered]@{
        OriginalValue = $_
        NewValue      = if ($result) {$R} else {$_}
        isNumber      = $result
    }
}


$Result  | Export-Excel $ExcelFile -Show -Verbose -Numberformat "@"

Result:

So I guess the best solution is to tell the module to treat these values as Excel Text format and not as a Number. We need to iron out the conversion bug above first. But the best thing would be to be able to define the format per property, so not all properties are treated as Text. Maybe something like this would work:

$Result  | Select-Object @{N='asText'; E={'@'+$_.OriginalValue}},OriginalValue, NewValue |
       Export-Excel $ExcelFile -Show

When there is an @ sign in front, we then check for it in the module Export-Excel and if we find that it starts with @ we will handle that property as Text instead of General. In any case, what we see is default Excel behavior. I'm open for other suggestions, but I don't think CurrentInfo is wrong.

I would love to hear what you think about this.

[dfinke]

Thanks!

I need to do more investigation. This looks to be on the right track. I'll get back to you and we can discuss further.

[Apanoiu]

Same issue here, I need to make a report based on db data, and one column contains numbers with leading zeros which needs to be stored as text.
I have tried to add a "'" prefix but excel is displaying it.
I am looking forward for this feature to be implemented.

[DarkLite1]

Took me more than a week to reorganize the code, add some better verbose and stuff. Anyhow, it's now possible to have numbers the way you want them by using '-NoNumberConversion *' as added in PR #187 . Waiting for approval/review from the boss to make sure I didn't miss anything ;)

[pezmannen]

So, I'm having problems with numbers showing up like this: 1,49545E+14 instead of this: 149544511912832. Will this latest fix resolve this?

[DarkLite1]

If the pull request gets accepted then you can use the parameter '-NoNumberConversion' to send your data to Excel. By using this parameter you bypass the conversion from a value to a number, that is built in to the function.

So in theory, if you do:
Export-Excel -Path File.xlsx -NoNumberConversion *

Then Export-Excel will export all data 'as is' and it doesn't convert anything. So this should avoid Excel from truncating the number from '149544511912832' to '1,49545E+14', because it's passed as a string and not as a number and is shown as plain text.

[dfinke]

@pezmannen These changes are going to take a while to test. Let me know if you want try out an early version? Plus, if you run into issues, you can report back.

[pezmannen]

@dfinke I'd love to test, and I'll report any issues. Thanks!

[lw-schick]

I have the same problem with numbers - 0,04 is converted to 4.00.

Is there any plan to merge this? I would like to install a new version instead of using the forked version DarkLite1....

[dfinke]

It's been merged and published to the PowerShell gallery