Solved

exporto to excel column text

Posted on 2010-09-16
16
708 Views
Last Modified: 2013-11-26
Hi experts, I have this code to export to excel
    Sub ExportGridView(ByVal grdView As GridView, ByVal filename As String, ByVal excludedColumnList As ArrayList)
        '// Clear response content & headers
        Response.Clear()
        Response.ClearContent()
        Response.ClearHeaders()


        '// Add header
        Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls")
        Response.Charset = String.Empty
        Response.Cache.SetCacheability(System.Web.HttpCacheability.Public)
        Response.ContentType = "application/vnd.xls"

        '// Create stringWriter
        Dim stringWrite As New System.IO.StringWriter()

        '// Create HtmlTextWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)


        '// Remove controls from Column Headers
        If (grdView.HeaderRow IsNot Nothing And grdView.HeaderRow.Cells IsNot Nothing) Then
            Dim ct As Integer
            For ct = 0 To grdView.HeaderRow.Cells.Count - 1 Step ct + 1
                ' Save initial text if found
                Dim headerText As String = grdView.HeaderRow.Cells(ct).Text

                ' Check for controls in header
                If grdView.HeaderRow.Cells(ct).HasControls() Then
                    ' Check for link button
                    If grdView.HeaderRow.Cells(ct).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.DataControlLinkButton" Then
                        ' link button found, get text
                        headerText = (CType(grdView.HeaderRow.Cells(ct).Controls(0), LinkButton)).Text
                    End If

                    ' Remove controls from header
                    grdView.HeaderRow.Cells(ct).Controls.Clear()
                End If

                ' Reassign header text
                grdView.HeaderRow.Cells(ct).Text = headerText
            Next

        End If
        '// Remove footer
        If (grdView.FooterRow IsNot DBNull.Value) Then

            grdView.FooterRow.Visible = False
        End If

        '// Remove unwanted columns (header text listed in removeColumnList arraylist)
        Dim field As DataControlField
        For Each field In grdView.Columns

            If (excludedColumnList.Contains(field.HeaderText)) Then

                field.Visible = False
            End If
        Next

        '// Call gridview's renderControl
        grdView.RenderControl(htmlWrite)

        '// Write Response to browser
        Response.Write(stringWrite.ToString())

        Response.End()
    End Sub
but I have a problem with this data have the data to zero, eg
says: 8141883
should read 08141883
ZERO is being lost
excelEXPORTADO.jpg
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 7

Expert Comment

by:klakkas
ID: 33692458
One way to go is to store the numbers in like this ="0814"

Putting =" " will force Excel to read the value as string instead of number, so it will keep your leading zero.

0
 
LVL 16

Expert Comment

by:13598
ID: 33693631
You may want to try using FormatCSVField for that column and make it a string.
0
 

Author Comment

by:enrique_aeo
ID: 33694184
hi 13598  portion of code that I do that?
hi klakkas in the database is stored at 0.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 16

Expert Comment

by:13598
ID: 33694776
Look what I found:

Format Issue when Exporting DataGrid to Excel

When you export a DataGrid to Excel it loses its format. This means that maybe your DataGrid has a string field which consisted of numbers, say '002345'. But when you export the grid and see it in an Excel file you will find that the number changed to '2345'.
You can solve this problem using Cascading Style Sheets.
Dim strFileName, strFilePath AsString
Dim oStringWriter AsNew System.IO.StringWriter
Dim oHtmlTextWriter AsNew System.Web.UI.HtmlTextWriter(oStringWriter)
Dim objStreamWriter As StreamWriter
Dim strStyle AsString = "<style>.text { mso-number-format:\@; } </style>"
objStreamWriter = File.AppendText(strFilePath)
DataGrid1.RenderControl(oHtmlTextWriter)
objStreamWriter.WriteLine(strStyle)
objStreamWriter.WriteLine(oStringWriter.ToString())
objStreamWriter.Close()
Most of you might be thinking about that "mso-number-format" stuff in between the code. This is the style in which the column will be exported. For this reason, you need to inject the attribute into the DataGrid column for which you want to change the display format.
DataGrid1.DataBind()
Dim strStyle AsString = "<style>.text { mso-number-format:\@; } </style>"
For intTemp AsInteger = 1 To ds.Tables(0).Rows.Count - 1
DataGrid1.Items(intTemp).Cells(0).Attributes.Add("class", "text")
Next
You can export in many formats. All you need to know is the mso-number-format:\@;. You can easily find the format by opening an Excel file and typing a number in one of the cells. Now if you want to save this number as a Social Security Pattern (xxx-xx-xxxx), right click on the cell and select a pattern that saves it as a Social Security Number. Next save the Excel file to XML format. Open the XML file in Notepad and see what style the column SSN uses. The SSN style is something like this: mso-number-format:000\-00\-0000.
Simply substitute the new style in the strStyle variable and that's it.
0
 
LVL 8

Expert Comment

by:redpipe
ID: 33695951
Another approach is to export the data in the DataGridView through ADO and declaring the columntype when you CREATE the table (ExcelSheet) and format it when you INSERT the data into the table (row, column) as explained here http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_22059574.html
0
 

Author Comment

by:enrique_aeo
ID: 33696485
Hi 13598,
logic changes, you can help me with the code, that is, where should I make the modification
0
 
LVL 7

Accepted Solution

by:
klakkas earned 500 total points
ID: 33699566
You should modify your code like this.

Take note of the new arrayList in the definition, where you define which columns you want to store as Text (keep the leading zeros).

All the work is done at the end of the sub, where the actual Cell.Text is modified.
Sub ExportGridView(ByVal grdView As GridView, ByVal filename As String, ByVal excludedColumnList As ArrayList, ByVal ColumnsStoredAsText As ArrayList)
        '// Clear response content & headers
        Response.Clear()
        Response.ClearContent()
        Response.ClearHeaders()


        '// Add header
        Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls")
        Response.Charset = String.Empty
        Response.Cache.SetCacheability(System.Web.HttpCacheability.Public)
        Response.ContentType = "application/vnd.xls"

        '// Create stringWriter
        Dim stringWrite As New System.IO.StringWriter()

        '// Create HtmlTextWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)


        '// Remove controls from Column Headers
        If (grdView.HeaderRow IsNot Nothing And grdView.HeaderRow.Cells IsNot Nothing) Then
            Dim ct As Integer
            For ct = 0 To grdView.HeaderRow.Cells.Count - 1 Step ct + 1
                ' Save initial text if found
                Dim headerText As String = grdView.HeaderRow.Cells(ct).Text

                ' Check for controls in header
                If grdView.HeaderRow.Cells(ct).HasControls() Then
                    ' Check for link button
                    If grdView.HeaderRow.Cells(ct).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.DataControlLinkButton" Then
                        ' link button found, get text
                        headerText = (CType(grdView.HeaderRow.Cells(ct).Controls(0), LinkButton)).Text
                    End If

                    ' Remove controls from header
                    grdView.HeaderRow.Cells(ct).Controls.Clear()
                End If

                ' Reassign header text
                grdView.HeaderRow.Cells(ct).Text = headerText
            Next

        End If
        '// Remove footer
        If (grdView.FooterRow IsNot DBNull.Value) Then

            grdView.FooterRow.Visible = False
        End If

        '// Remove unwanted columns (header text listed in removeColumnList arraylist)
        Dim field As DataControlField
        For Each field In grdView.Columns

            If (excludedColumnList.Contains(field.HeaderText)) Then
                field.Visible = False
            End If
        Next

        For i As Integer = 0 To grdView.HeaderRow.Cells.Count - 1
            If ColumnsStoredAsText.Contains(grdView.HeaderRow.Cells(i).Text) Then
                For Each c As GridViewRow In grdView.Rows
                    c.Cells(i).Text = "=""" + c.Cells(i).Text + """"
                Next
            End If
        Next


        '// Call gridview's renderControl
        grdView.RenderControl(htmlWrite)

        '// Write Response to browser
        Response.Write(stringWrite.ToString())

        Response.End()
    End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:13598
ID: 33701914
Try changing this line in your code
 '// Write Response to browser
        Response.Write(stringWrite.ToString())

to:
  '// Write Response to browser
        Response.Write(strStyle + stringWrite.ToString())

Then in your code where you bind your gridview add this:
grdView.DataBind()

Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"
For intTemp As Integer = 1 To ds.Tables(0).Rows.Count - 1
   grdView.Items(intTemp).Cells(0).Attributes.Add("class", "text")
Next
0
 

Author Comment

by:enrique_aeo
ID: 33702854
hi klakkas
ExportGridView(gvwQryInscripciones, "Excel-Name", defaultExcludedColumns, ?????)
've added a new parameter, the call to the function that I should add then?
0
 
LVL 7

Assisted Solution

by:klakkas
klakkas earned 500 total points
ID: 33715085
Hello enrique.

Sorry for the delay, but I don't work on weekends.

In that last parameter, you should put a string array with the column names of the columns that you want to be stored as text.

So, if for example your datagrid data has 1 column named 'PhoneNumber' for which you want to maintain the leading zeros, you should say this:

dim ColumnsStoredAsText as new arrayList
ColumnsStoredAsText.add ("PhoneNumber")

ExportGridView(gvwQryInscripciones, "Excel-Name", defaultExcludedColumns, ColumnsStoredAsText)

This way, all the numeric columns in your grid will be stored as numbers, EXCEPT from the PhoneNumber column, which will be stored as text.
0
 

Author Comment

by:enrique_aeo
ID: 33716245
i have this error
The control 'ctl00_cphCentro_TabContainer1_TabPanel1_gvwQryInscripciones' of type 'GridView' must be placed inside a form tag with runat = server.
i attached the error and the code
exportFROMgridTOexcel.txt
errEXPORTOtoGRID.JPG
0
 
LVL 7

Expert Comment

by:klakkas
ID: 33716274
Add this sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)
        Return
    End Sub

in your class to solve the problem
0
 

Author Comment

by:enrique_aeo
ID: 33716307
but it not working
exampleDATA.jpg
0
 
LVL 7

Assisted Solution

by:klakkas
klakkas earned 500 total points
ID: 33716357
It is not working because the column name you want to keep zeros is not named PhoneNumber, it is named DNI.

So, change:
        Dim ColumnsStoredAsText As New ArrayList
        ColumnsStoredAsText.Add("PhoneNumber")
To:
        Dim ColumnsStoredAsText As New ArrayList
        ColumnsStoredAsText.Add("DNI")
0
 
LVL 16

Expert Comment

by:13598
ID: 33716360
If you go by what I posted:
'When you export a DataGrid to Excel it loses its format'
Then 'fixing' the datagrid will not work because it will lose its format.
Use my solution you need to tell the export to excel to make it string by using  mso-number-format:\@;
0
 

Author Comment

by:enrique_aeo
ID: 33716977
I now accept and agree with the solutions of the experts, because it opens the question?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

627 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