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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.