Excel Formatting Changes mid-spreadsheet and won't reformat
Posted on 2005-05-04
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)
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?