Excel export of datagrid is changing a value...

Posted on 2004-11-03
Last Modified: 2008-02-26
When I use the following function to export the contents of a datagrid to excel, the results are changed when I open the excel document.

For example the field with value '09-4720' will display as 'Sep-20'...  It's doing an auto convert somewhere in the process.

Any ideas of how to keep this change from happening?


Here is the function I'm using to export the data:

    Private Sub Imagebutton4_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Imagebutton4.Click
            Dim SE As String
            SE = Session("SortExpression")
            dgdInv.AllowPaging = False
            dgdInv.AllowSorting = False
            Dim stringWrite As New System.IO.StringWriter
            Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
            Dim w As System.Web.UI.WebControls.Unit
            dgdInv.Width = w
            Dim scr As String = "<script>function window.onafterprint(){history.back(1);}</script>"
            Dim strHTML As String = stringWrite.ToString()

            ' send to Excel
            Response.AddHeader("Content-Disposition", "attachment;filename=BurdenData.xls")
            Response.ContentType = "application/"
            ' send to Printer
            'Response.ContentType = "text/HTML"


    End Sub
Question by:Weller0123
    LVL 8

    Accepted Solution

    You'd have to change the cell formatting, which I believe you'd have to do with the COM API for Excel (which can still be delivered via ASP.NET).

    Awesome article to get you started:
    LVL 28

    Assisted Solution

    Hi Weller0123,

    there is no chaning the value through the export. the changing is in the excel when you open it, because the formatting of the cell is General by default and excel tryes to find the format of the cell itself
    one way is to manually convert the all column cells to text formatting: select the complete column right click ->Format Cell->from the list select Text
    other way, if you want to read the excel file programatically, and there you see the conversion, you have to change the value of this key in the registry
    in this key is saved a number of rows that jet engine has to move through to understand what is the type of the hole column
    so if your first column is text you can set it to 1 and it will return the correct values


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now