export to excel error

[pks888]

When the length of the numerical data in new-htmltable is too long, clicking the "Export to Excel" button will result in incorrect data displayed in Excel. For example, when I try to export a 16-digit number, the last few digits of the data in Excel are different from the original data.

Is there a way to explicitly set the data export method as a text string?

[PrzemyslawKlys]

Are you sure the data in powershell was string, not a number? Did you use "HTML" or "JavaScript" datastore? Have you tried both?

Generally it should work, but if it doesn't then changing the behavior is probably not not possible at this moment without going into JS and fixing it.

[pks888]

Are you sure the data in powershell was string, not a number? Did you use "HTML" or "JavaScript" datastore? Have you tried both?

Generally it should work, but if it doesn't then changing the behavior is probably not not possible at this moment without going into JS and fixing it.

datasource is a datatable， query from oracle database. both html or javascript datastore I have tried, but there is still an abnormality when exporting to Excel. However, the exports to CSV and plain text are right. I have looked up some materials and it is possible that Excel will lose precision when dealing with extremely large numbers, which leads to abnormal data in the part exceeding the digit limit.

[PrzemyslawKlys]

Generally, if there is an issue with auto conversion, someone would need to write code that autogenerates Javascript that "fixes" detection properly.

Here's an example of changing the background. It's not easy, and it may be even harder to do what you want.

• https://datatables.net/extensions/buttons/examples/html5/excelCellShading

Look at the generated HTML source and see how the data is written for the specific part you want, and try to add "" to it, how to play with JS code before "exporting". Maybe it's as simple as getting the data from oracle and in PowerSHell changing the type to double or string or ...

But without simplified example that shows this I don't even know how to help you.

[pks888]

Here is a powershell code that can reproduce the problem:

$dt = New-Object System.Data.DataTable "MyDataTable"

$col1 = New-Object System.Data.DataColumn ID,([int])
$col2 = New-Object System.Data.DataColumn Name,([string])
$col3 = New-Object System.Data.DataColumn OrderNum,([string])

$dt.Columns.Add($col1)
$dt.Columns.Add($col2)
$dt.Columns.Add($col3)


$row = $dt.NewRow()
$row.ID = 1
$row.Name = "Alice"
$row.OrderNum = '8012345678923456789'
$dt.Rows.Add($row)

$row = $dt.NewRow()
$row.ID = 2
$row.Name = "Bob"
$row.OrderNum = '01234567891234567'
$dt.Rows.Add($row)


$dt | Format-Table
Dashboard -FilePath "d:\111.html" -ShowHTML {
    New-HTMLTable -DataTable $dt
}

Screenshot displayed in Excel after I click the export to Excel button:

try add datastore as JavaScript, the problem still exists.

Dashboard -FilePath "d:\111.html" -ShowHTML {
    New-HTMLTable -DataTable $dt -DataStore JavaScript
}

Generally, if there is an issue with auto conversion, someone would need to write code that autogenerates Javascript that "fixes" detection properly.

Here's an example of changing the background. It's not easy, and it may be even harder to do what you want.

• https://datatables.net/extensions/buttons/examples/html5/excelCellShading

Look at the generated HTML source and see how the data is written for the specific part you want, and try to add "" to it, how to play with JS code before "exporting". Maybe it's as simple as getting the data from oracle and in PowerSHell changing the type to double or string or ...

But without simplified example that shows this I don't even know how to help you.