asked on

Set data type in excel column

Hi,

I create an excel file from a dataset which works fine, but if the file has a large number the result isn't good, how can I set the column properties so taht the numbers is displayed as it should..

Public Sub exportToexCEl(ByVal ds As DataSet)

            '*******************************
            'Purpose: Creates an excel file from the current dataset.
            '*******************************

            Dim strFilename As String
            Dim strDateTime As String

            Dim dt As DataTable = ds.Tables(0)

            strDateTime = Format(Date.Now, "yyyy-MM-dd_HHmm") 'used in excel file name

            strFilename = Session("EventnameForFile")
            strFilename = strFilename & "_" & strDateTime.ToUpper & ".xls" 'the full file name

            Dim attachment As String = "attachment; filename=" & strFilename

            Try

                Response.ClearContent()
                Response.AddHeader("content-disposition", attachment)
                Response.ContentType = "application/vnd.ms-excel"
                Response.ContentEncoding = UTF8Encoding.Default

                Dim tab As String = ""

                Dim dc As DataColumn

                For Each dc In dt.Columns
                    Response.Write(tab + dc.ColumnName)
                    tab = vbTab
                Next
                Response.Write(vbLf)
                Dim i As Integer
                For Each dr As DataRow In dt.Rows
                    tab = ""
                    For i = 0 To dt.Columns.Count - 1
                        Response.Write(tab + dr(i).ToString().Replace(vbCrLf, " "))
                        tab = vbTab
                    Next
                    Response.Write(vbLf)
                Next

                Response.End()

Open in new window

RichardSchollar

I assume you are running into the problem of Excel's limited precision - does this sound correct? So any numbers over 15 digits will be only show significant digits to 15 digits and zeros beyond (and will also get shown in scientific notation). If this is the case, to preserve the digits beyond the 15th character, you will need to ensure they are entered as Text into the cell ie as a String and perhaps also ensure the cell's formatting is text before you enter the value.

Richard

mattegol

ASKER

Yes that's exactly my problem. I'll take a look at it.

mattegol

ASKER

Where in my code do I do this?

"you will need to ensure they are entered as Text into the cell ie as a String and perhaps also ensure the cell's formatting is text before you enter the value."

ASKER CERTIFIED SOLUTION

RichardSchollar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattegol

ASKER

That makes the numbers show correctly but there is an ' before all data. Which is not 100% good

RichardSchollar

You could correct that subsequently in Excel (eg via Excel VBA) but I presume you'd rather do this all thru your existing ASP code. I am afraid I am unfamiliar with how the code creates the Excel file so I can't advise how to implement my other suggestion of making sure all the Excel cells are formatted as Text prior to writing values to them.

It's worth noting that 15+ digit number can only exist as text in Excel - so you won't be able to do any arithmetic processing with them after transcribing them to Excel (if you are using Excel as just a display medium this shouldn't matter, however).

Richard

mattegol

ASKER

I am using it only as a display medium,

RichardSchollar

And the apostrophes were still showing up in each cell? Were they appearing at the start of each cell or was there any spaces or other (potentially invisible) characters before the apostrophes (and then the cell's actual contents)?

mattegol

ASKER

yes like this '123456789123456789 and like 'test and so on..

RichardSchollar

Probably only a partial solution (and I'm sure there must be a 'proper' way of doing this), but what if you concatenated an ascii character 160 instead of the apostrophe (ie so a non-breaking space). Code for which may be like this (but check if Chr() is a valid function for you - it is in VBA!):

For i = 0 To dt.Columns.Count - 1
Response.Write(tab + Chr(160) + dr(i).ToString().Replace(vbCrLf, " "))
tab = vbTab
Next

mattegol

ASKER

Chr(160) works fine except its not the best solution because of the breaking-space in every cell