Solved

exporto to excel column text

Posted on 2010-09-16
16
693 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
  • 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now