Exportar Gridview a Excel, problema de formato
Amigo tengo este problema que ya me tiene loco.
Estoy exportando la informacion de un gridview a excel, estoy programando en asp.net 2005.
Para realizar la exportacion tengo este codigo.
ds = obj.DameDataset("select top(10) tarjeta,vigenciatar,monto,fechaela,email,cliente from cobros_tob", con)
dg1.DataSource = ds.Tables(0)
dg1.DataBind()
Dim sb As StringBuilder = New StringBuilder()
Dim sw As IO.StringWriter = New IO.StringWriter(sb)
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
Dim pagina As Page = New Page
Dim form As New HtmlForm
pagina.EnableEventValidation = False
pagina.EnableViewState = False
pagina.DesignerInitialize()
pagina.Controls.Add(form)
form.Controls.Add(dg1)
pagina.RenderControl(htw)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8
Response.Charset = "iso-8859-1"
Response.AddHeader("Content-Disposition", "attachment;filename=Poliza.xls")
'Response.Charset = "UTF-8"
Response.ContentEncoding = Encoding.Default
Response.Write(sb.ToString())
Response.End()
Hasta aca el exportar va muy bien, el problema esta en que tengo informacion como por ejemplo el numero de una tarjeta de credito, esto esta en el campo tarjeta, la informacion de este campo es hasta de 16 caracteres y esta al exportarlo a excel pasa de esta manera.
5.40776E+15 siendo lo correcto algo como 5407761234567893
Como hago para que al exportar la informacion desde el grid a excel esta pase tal y como se ve en el grid, como puedo formatear esto.
Estoy exportando la informacion de un gridview a excel, estoy programando en asp.net 2005.
Para realizar la exportacion tengo este codigo.
ds = obj.DameDataset("select top(10) tarjeta,vigenciatar,monto,fechaela,email,cliente from cobros_tob", con)
dg1.DataSource = ds.Tables(0)
dg1.DataBind()
Dim sb As StringBuilder = New StringBuilder()
Dim sw As IO.StringWriter = New IO.StringWriter(sb)
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
Dim pagina As Page = New Page
Dim form As New HtmlForm
pagina.EnableEventValidation = False
pagina.EnableViewState = False
pagina.DesignerInitialize()
pagina.Controls.Add(form)
form.Controls.Add(dg1)
pagina.RenderControl(htw)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8
Response.Charset = "iso-8859-1"
Response.AddHeader("Content-Disposition", "attachment;filename=Poliza.xls")
'Response.Charset = "UTF-8"
Response.ContentEncoding = Encoding.Default
Response.Write(sb.ToString())
Response.End()
Hasta aca el exportar va muy bien, el problema esta en que tengo informacion como por ejemplo el numero de una tarjeta de credito, esto esta en el campo tarjeta, la informacion de este campo es hasta de 16 caracteres y esta al exportarlo a excel pasa de esta manera.
5.40776E+15 siendo lo correcto algo como 5407761234567893
Como hago para que al exportar la informacion desde el grid a excel esta pase tal y como se ve en el grid, como puedo formatear esto.
1 respuesta
Respuesta de hfpg2001
1
Ya el problema lo resolvi esta resuelto es un boceto pero en principio lo que tienes que hacer es cambiar los formatos de toda la columna.
Esta colocado con las referencias de mi maquina, puerto y demas.
Sobre llenar el dataset tienes que hacer un for para apuntar a las celdas y llenalrlas.. eso es para colocar la data, este ejemplo hace lo mismo pero con data colocada.
Imports
System. Data. SqlClient
Imports
System. Data
Imports
Microsoft. Office. Interop
Partial
Class Default2
Inherits System.Web.UI.Page
Dim connex As New SqlConnection
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New DataSet
connex.ConnectionString =
"Data Source=P5630;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa"
connex.Open()
Dim sqlda As New SqlDataAdapter
sqlda.SelectCommand =
New SqlCommand
sqlda.SelectCommand.Connection = connex
sqlda.SelectCommand.CommandText =
"select * from tc"
sqlda.Fill(ds)
GridView1.DataSource = ds.Tables(0)
connex.Close()
GridView1.DataBind()
Dim strCurrentDir As String = Server.MapPath(".") + "\"
Dim strFile As String = "excelprueba.xls"
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim chartRange As Excel.Range
xlApp =
New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets.Item(
"Hoja1")
'add data
xlWorkSheet.Cells(4, 2) =
""
xlWorkSheet.Cells(4, 3) =
"Student1"
xlWorkSheet.Cells(4, 4) =
"Student2"
xlWorkSheet.Cells(4, 5) =
"Student3"
xlWorkSheet.Cells(5, 2) =
"Term1"
xlWorkSheet.Cells(5, 3) =
"80"
xlWorkSheet.Cells(5, 4) =
"65"
xlWorkSheet.Cells(5, 5) =
"1234567891234567"
xlWorkSheet.Columns(
"E").NumberFormat = "########"
'xlWorkSheet.Columns("E").NumberFormat = "Numerico"
xlWorkSheet.Cells(6, 2) =
"Term2"
xlWorkSheet.Cells(6, 3) =
"78"
xlWorkSheet.Cells(6, 4) =
"72"
xlWorkSheet.Cells(6, 5) =
"1234567891234567"
xlWorkSheet.Cells(7, 2) =
"Term3"
xlWorkSheet.Cells(7, 3) =
"82"
xlWorkSheet.Cells(7, 4) =
"80"
xlWorkSheet.Cells(7, 5) =
"1234567891234567"
xlWorkSheet.Cells(8, 2) =
"Term4"
xlWorkSheet.Cells(8, 3) =
"75"
xlWorkSheet.Cells(8, 4) =
"1234567891234567"
xlWorkSheet.Cells(8, 5) =
"1234567891234567"
xlWorkSheet.Cells(9, 2) =
"Total"
xlWorkSheet.Cells(9, 3) =
"315"
xlWorkSheet.Cells(9, 4) =
"1234567891234567"
xlWorkSheet.Cells(9, 5) =
"1234567891234567"
chartRange = xlWorkSheet.Range(
"b2", "e3")
chartRange.Merge()
chartRange.FormulaR1C1 =
"MARK LIST"
chartRange.HorizontalAlignment = 3
chartRange.VerticalAlignment = 3
chartRange = xlWorkSheet.Range(
"b4", "e4")
chartRange.Font.Bold =
True
chartRange = xlWorkSheet.Range(
"b9", "e9")
chartRange.Font.Bold =
True
chartRange = xlWorkSheet.Range(
"b2", "e9")
ChartRange. BorderAround(Excel. XlLineStyle.xlContinuous, _
Excel. XlBorderWeight. XlMedium, Excel. XlColorIndex. _
XlColorIndexAutomatic, Excel. XlColorIndex. XlColorIndexAutomatic)
xlWorkSheet.SaveAs(strCurrentDir & strFile)
XlWorkBook. Close()
XlApp. Quit()
ReleaseObject(xlApp)
ReleaseObject(xlWorkBook)
ReleaseObject(xlWorkSheet)
Dim strMachineName As String
strMachineName = Request.ServerVariables(
"SERVER_NAME")
Me.label.Text = "<A href=http://" + strMachineName + ":3415/WebSiteSQL/" + strFile + ">Download Report</a>"
'Dim sb As StringBuilder = New StringBuilder()
'Dim sw As IO.StringWriter = New IO.StringWriter(sb)
'Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
'Dim pagina As Page = New Page
'Dim form As New HtmlForm
'pagina.EnableEventValidation = False
'pagina.EnableViewState = False
'pagina.DesignerInitialize()
'pagina.Controls.Add(form)
'form.Controls.Add(GridView1)
'pagina.RenderControl(htw)
'Response.Clear()
'Response.Buffer = True
'Response.ContentType = "application/vnd.ms-excel"
'Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8
'Response.Charset = "iso-8859-1"
'Response.AddHeader("Content-Disposition", "attachment;filename=C:\vbexcel.xls")
''Response.Charset = "UTF-8"
'Response.ContentEncoding = Encoding.Default
'Response.Write(sb.ToString())
'Response.End()
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj =
Nothing
Catch ex As Exception
obj =
Nothing
Finally
GC.Collect()
End Try
End Sub
End
Class
Esta colocado con las referencias de mi maquina, puerto y demas.
Sobre llenar el dataset tienes que hacer un for para apuntar a las celdas y llenalrlas.. eso es para colocar la data, este ejemplo hace lo mismo pero con data colocada.
Imports
System. Data. SqlClient
Imports
System. Data
Imports
Microsoft. Office. Interop
Partial
Class Default2
Inherits System.Web.UI.Page
Dim connex As New SqlConnection
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New DataSet
connex.ConnectionString =
"Data Source=P5630;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=sa"
connex.Open()
Dim sqlda As New SqlDataAdapter
sqlda.SelectCommand =
New SqlCommand
sqlda.SelectCommand.Connection = connex
sqlda.SelectCommand.CommandText =
"select * from tc"
sqlda.Fill(ds)
GridView1.DataSource = ds.Tables(0)
connex.Close()
GridView1.DataBind()
Dim strCurrentDir As String = Server.MapPath(".") + "\"
Dim strFile As String = "excelprueba.xls"
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim chartRange As Excel.Range
xlApp =
New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets.Item(
"Hoja1")
'add data
xlWorkSheet.Cells(4, 2) =
""
xlWorkSheet.Cells(4, 3) =
"Student1"
xlWorkSheet.Cells(4, 4) =
"Student2"
xlWorkSheet.Cells(4, 5) =
"Student3"
xlWorkSheet.Cells(5, 2) =
"Term1"
xlWorkSheet.Cells(5, 3) =
"80"
xlWorkSheet.Cells(5, 4) =
"65"
xlWorkSheet.Cells(5, 5) =
"1234567891234567"
xlWorkSheet.Columns(
"E").NumberFormat = "########"
'xlWorkSheet.Columns("E").NumberFormat = "Numerico"
xlWorkSheet.Cells(6, 2) =
"Term2"
xlWorkSheet.Cells(6, 3) =
"78"
xlWorkSheet.Cells(6, 4) =
"72"
xlWorkSheet.Cells(6, 5) =
"1234567891234567"
xlWorkSheet.Cells(7, 2) =
"Term3"
xlWorkSheet.Cells(7, 3) =
"82"
xlWorkSheet.Cells(7, 4) =
"80"
xlWorkSheet.Cells(7, 5) =
"1234567891234567"
xlWorkSheet.Cells(8, 2) =
"Term4"
xlWorkSheet.Cells(8, 3) =
"75"
xlWorkSheet.Cells(8, 4) =
"1234567891234567"
xlWorkSheet.Cells(8, 5) =
"1234567891234567"
xlWorkSheet.Cells(9, 2) =
"Total"
xlWorkSheet.Cells(9, 3) =
"315"
xlWorkSheet.Cells(9, 4) =
"1234567891234567"
xlWorkSheet.Cells(9, 5) =
"1234567891234567"
chartRange = xlWorkSheet.Range(
"b2", "e3")
chartRange.Merge()
chartRange.FormulaR1C1 =
"MARK LIST"
chartRange.HorizontalAlignment = 3
chartRange.VerticalAlignment = 3
chartRange = xlWorkSheet.Range(
"b4", "e4")
chartRange.Font.Bold =
True
chartRange = xlWorkSheet.Range(
"b9", "e9")
chartRange.Font.Bold =
True
chartRange = xlWorkSheet.Range(
"b2", "e9")
ChartRange. BorderAround(Excel. XlLineStyle.xlContinuous, _
Excel. XlBorderWeight. XlMedium, Excel. XlColorIndex. _
XlColorIndexAutomatic, Excel. XlColorIndex. XlColorIndexAutomatic)
xlWorkSheet.SaveAs(strCurrentDir & strFile)
XlWorkBook. Close()
XlApp. Quit()
ReleaseObject(xlApp)
ReleaseObject(xlWorkBook)
ReleaseObject(xlWorkSheet)
Dim strMachineName As String
strMachineName = Request.ServerVariables(
"SERVER_NAME")
Me.label.Text = "<A href=http://" + strMachineName + ":3415/WebSiteSQL/" + strFile + ">Download Report</a>"
'Dim sb As StringBuilder = New StringBuilder()
'Dim sw As IO.StringWriter = New IO.StringWriter(sb)
'Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
'Dim pagina As Page = New Page
'Dim form As New HtmlForm
'pagina.EnableEventValidation = False
'pagina.EnableViewState = False
'pagina.DesignerInitialize()
'pagina.Controls.Add(form)
'form.Controls.Add(GridView1)
'pagina.RenderControl(htw)
'Response.Clear()
'Response.Buffer = True
'Response.ContentType = "application/vnd.ms-excel"
'Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8
'Response.Charset = "iso-8859-1"
'Response.AddHeader("Content-Disposition", "attachment;filename=C:\vbexcel.xls")
''Response.Charset = "UTF-8"
'Response.ContentEncoding = Encoding.Default
'Response.Write(sb.ToString())
'Response.End()
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj =
Nothing
Catch ex As Exception
obj =
Nothing
Finally
GC.Collect()
End Try
End Sub
End
Class
