Excel Formatting Changes mid-spreadsheet and won't reformat

Posted on 2005-05-04
Medium Priority
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/vnd.ms-excel"
            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
  • 2

Accepted Solution

Jimy earned 2000 total points
ID: 13940272
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.


Author Comment

ID: 13940769
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!

Expert Comment

ID: 13955446
Hi EasyWriter,

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

Thx for points.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
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 …
Loops Section Overview

840 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