I'm reading some value from XML:
<DocumentSetNumber>20151027301060001</DocumentSetNumber>
$DSN = $xml.metaBI.DocumentSetNumber
and then writing to Excel using PowerShell:
$ExcelWorkSheet.Cells.Item($Row,1).Value2 = $DSN
But in Excel I'm getting this number: 2.0151E+16
If I change Excel format I can get this: 20151027301060000
It's not the same number, and I need to get exactly the same.
I tried to play with Excel formats and also use Tostring() on variable, but without luck.
What also can I try?
PS. If I copy and paste this value manually it works only I use
match destination formatting
option in Excel.
How I can set this option from PowerShell?
You are going to have to put this long DSN into the cell as a string. Excel has a 15 digit precision and rounds off any numbers longer than 15 digits to the nearest placeholder.
Given your code, it seems that prefacing the $DSN with a single quote (e.g. ' or ASCII 039) as a Range.PrefixCharacter property should suffice. Alternately, you could change the number format of the receiving cell to Text but I think a forced prefix character is best. Since a single quote (aka tick) is used as a quoting operator in PowerShell, we need to bring it in as a [char]39.
$tic = [char]39
$DSN = $xml.metaBI.DocumentSetNumber
$ExcelWorkSheet.Cells.Item($Row,1).Value2 = $tic + $DSN
# write-host $tic$dsn <~~ this outputs correctly but apparently not correct for XL cell value. see comments.
That should change 20151027301060001 to '20151027301060001 and Excel will accept it as string text without trying to force it into a number.