Excel Formatting Changes mid-spreadsheet and won't reformat

Posted on 2005-05-04
Last Modified: 2011-03-29
Halfway down my spreadsheet, all columns change from (numbers, or whatever formatting they are) to text (with little green triangles) and when I highlight an entire column, and try to force the format to currency, the top half formats just fine, but all of the lower cells remain text.

History: I have written a report in VB.NET Web App that runs an query and populates a DB grid and includes an "export to excel" button.  When the user clicks the button, it grabs the entire DBgrid (which works and looks good) and dumps it to excel like this:

    Private Sub ExportGridToExcel()
            Response.Charset = ""
            Response.ContentType = "application/"
            Dim sw As New System.IO.StringWriter
            Dim hw As New System.Web.UI.HtmlTextWriter(sw)
            Response.AddHeader("Content-Disposition", "inline; filename=ExcelDrop.xls”)
            LogIt("Data Exported Successfully!")
        Catch ex As Exception
            LogIt("Data error encountered: " & ex.Message.ToString)
        End Try
    End Sub

My spreadsheet looks and functions perfectly until the 304th line, where the entire rest of the spreadsheet stops formatting.  It is easy for me to find by scrolling down, because suddenly every cell has a little green triangle, and all of the "numbers" shift to the left side of the cell.  So I tried saving to an excel file, and opening it in Excel itself, and then clicking a PRICE column and formatting it to currency.  It formats the first half, but the bottom cells don't respond at all.  Of course I can click any one of the little green triangles, and tell it to "convert to number" and then that cell will format with the rest of the column, but I can't expect my end users to click several hundred green triangles each time they run a report.

I took a look at my SQL code that pupulates the DBgrid, and tried doing a TRIM function, but it didn't change anything.  I can find nothing on line to tell my why this is happening.

I am looking for one of two solutions:

1. Preferably, I would like some help getting the data to the excel sheet so that each entire column is the same.

2. As a workaround, while I figure out that solution, is there a way to tell the whole spreadsheet to get rid of the triangles and force all of the cells to just join whatever format the column is?
Question by:EasyWriter
    LVL 1

    Accepted Solution

    Hi EasyWriter,

    as a workaround, to get rid of greenies, you can try to change: Tools>Options>Error Checking and tick out Number stored as text.

    LVL 2

    Author Comment

    Well, I finally figured it out.  It was caused by a field that had a '-A' in the data.  When I removed the '-' it worked fine.  Must be some kind of a formatting code for Excel or something, perhaps combined with other characters when converting.  In any case, I got it to work by changing the data.  

    Since you are the only one who responded, Jimy, I'm going to go ahead and give you the points for your help.  Thanks!
    LVL 1

    Expert Comment

    Hi EasyWriter,

    I'm glad you find out what caused you problem.

    Thx for points.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now