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

mattegolAsked:
Who is Participating?
 
RichardSchollarCommented:
Try replacing your inner loop with this:

which I hope is concatenating an apostrophe to the start of what will be each cell's value once in Excel (the apostrophe at the start is a prefix character denoting text - it doesn't form part of the actual cell contents).

Note, however, that my expertise is wholly contained within Excel: I'm afraid I have never used ASP.NET.

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

Open in new window

0
 
RichardSchollarCommented:
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
0
 
mattegolAuthor Commented:
Yes that's exactly my problem. I'll take a look at it.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
mattegolAuthor Commented:
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."
0
 
mattegolAuthor Commented:
That makes the numbers show correctly but there is an ' before all data. Which is not 100% good
0
 
RichardSchollarCommented:
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
0
 
mattegolAuthor Commented:
I am using it only as a display medium,
0
 
RichardSchollarCommented:
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)?
0
 
mattegolAuthor Commented:
yes like this '123456789123456789 and like 'test and so on..
0
 
RichardSchollarCommented:
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
0
 
mattegolAuthor Commented:
Chr(160) works fine except its not the best solution because of the breaking-space in every cell

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.