Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

exporto to excel column text

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
enrique_aeo
Asked:
enrique_aeo
  • 6
  • 5
  • 4
  • +1
3 Solutions
 
klakkasCommented:
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
 
13598Commented:
You may want to try using FormatCSVField for that column and make it a string.
0
 
enrique_aeoAuthor Commented:
hi 13598  portion of code that I do that?
hi klakkas in the database is stored at 0.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
13598Commented:
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
 
redpipeCommented:
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
 
enrique_aeoAuthor Commented:
Hi 13598,
logic changes, you can help me with the code, that is, where should I make the modification
0
 
klakkasCommented:
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
 
13598Commented:
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
 
enrique_aeoAuthor Commented:
hi klakkas
ExportGridView(gvwQryInscripciones, "Excel-Name", defaultExcludedColumns, ?????)
've added a new parameter, the call to the function that I should add then?
0
 
klakkasCommented:
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
 
enrique_aeoAuthor Commented:
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
 
klakkasCommented:
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
 
enrique_aeoAuthor Commented:
but it not working
exampleDATA.jpg
0
 
klakkasCommented:
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
 
13598Commented:
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
 
enrique_aeoAuthor Commented:
I now accept and agree with the solutions of the experts, because it opens the question?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now