EasyWriter
asked on
Excel Formatting Changes mid-spreadsheet and won't reformat
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()
Try
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWrit er(sw)
DG2.RenderControl(hw)
Response.AddHeader("Conten t-Disposit ion", "inline; filename=ExcelDrop.xls”)
Response.Write(sw.ToString )
Response.Flush()
Response.Close()
Response.End()
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?
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()
Try
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWrit
DG2.RenderControl(hw)
Response.AddHeader("Conten
Response.Write(sw.ToString
Response.Flush()
Response.Close()
Response.End()
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi EasyWriter,
I'm glad you find out what caused you problem.
Thx for points.
Regards,
Jimy
I'm glad you find out what caused you problem.
Thx for points.
Regards,
Jimy
ASKER
Since you are the only one who responded, Jimy, I'm going to go ahead and give you the points for your help. Thanks!