problems formatting data in OWC spreadsheet control version 10.0

[Brian Hman]

Hi,

I have a spreadsheet OWC embedded in one of my pages. I am populating it
according to the example found in this article:
http://dotnetjunkies.com/WebLog/bsblog/archive/2004/04/27/12229.aspx

Some of the values are not displaying in the Spreadsheet component the way I
need them to, and when I use the "Export to Excel Button" some of the values
become negative numbers. Note that my values are in Hours and Minutes.

Here's one example:
This value appears in the XML: 60383:46
This OWC Spreadsheet then formats it to: 5120:14:00
Then when I click on the export to excel button I get: -214.676

I really need the original value, 60383:46, to be displayed just like it is
in the XML. Is this number somehow too large for Excel and that's why
I'm having problems? Or do I need to provide some formatting information
to the cell to get the value to display properly?

Here's my code in case it helps:
<script language="VBScript">
Dim conn
Dim rst
Dim strSQL
Dim count
Dim r
Dim c
Dim myData
Dim range
conn="Provider=MSPERSIST"
Set rst = CreateObject("ADODB.Recordset")
strSql =
"http://localhost:81/D/X.ashx?dimCnt...groupType=ALLRECORDS&cubeType=PAP&subject=271"
rst.Open strSql, conn, 3
For count = 0 to rst.fields.count - 1
r = r + 1
With document.Spread1.ActiveSheet.Cells(1, r)
.Value = rst.Fields(count).Name
.Font.Bold = True
End with
Next

myData = rst.GetRows()
returnedRows = UBound(mydata, 2) + 1
For r = 1 to returnedRows
For c = 1 to rst.Fields.Count
document.Spread1.ActiveSheet.Cells(r+1, c).value = myData(c-1, r-1)
Next
Next
rst.close
set rst = Nothing

</script>

Thanks,
Brian Hman

 

[Wei-Dong XU [MSFT]]

Hi,

From my research, this value 60383:46 will be automatically converted by
Spreadsheet to 5120:14:00. For displaying it, we will need to perform some
decorations on the value, for example #60383:46 and 60383:46#.

Another way is: we could store the value 60383:46 in the varchar type
column in one database table and then specify this table as the spreadsheet
data source. Then this value will be rendered very well in the spreadsheet;
after be exported to excel, this value is also rendered very smoothly. No
error or modification will be caused.

Please feel free to let me know if you have any further question on this
matter.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.