Solved

How do I get Gridview Export to Excel not to add extra rows

Posted on 2009-07-15
4
494 Views
Last Modified: 2012-05-07
I have simple web page with gridview that I want to export to Excel.  One of the cells has several embedded <BR />.  This data displays fine in web page, but I get an extra row in excel for each <br />.  I have tried all the replace <br /> with vbcrlf and many other attempts, but none produces a single excel row for a gridview row that has cell with embedded carriage returns.  I know I'm not the only one having this problem, but can't seem to find right answer.  Any help would be sincerely appreciated.  I have added all the code and attached snapshot of web page output and Excel output.


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">

            <Columns>

                <asp:BoundField HeaderText="col1" DataField="field_a" HtmlEncode="False" />

                <asp:BoundField HeaderText="col2" DataField="field_b" />

            </Columns>

        </asp:GridView>

    </div>

    <asp:Button ID="Button1" runat="server" Text="Export" />

    </form>

</body>

</html>
 

Imports System.Data

Partial Class _Default

    Inherits System.Web.UI.Page
 

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim mydatatable As New DataTable

        mydatatable.Columns.Add("field_a", Type.GetType("System.String"))

        mydatatable.Columns.Add("field_b", Type.GetType("System.String"))

        Dim myrow As DataRow

        myrow = mydatatable.NewRow

        myrow("field_a") = "<div >WAS7 installed in lab <br />Jimtest2<br /><br />Jimtest3</div>"

        myrow("field_b") = "filed b row 1"

        mydatatable.Rows.Add(myrow)

        myrow = mydatatable.NewRow

        myrow("field_a") = "filed a row 2"

        myrow("field_b") = "filed b row 3"

        mydatatable.Rows.Add(myrow)

        GridView1.DataSource = mydatatable

        GridView1.DataBind()

    End Sub
 

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        GridViewExportUtil.Export("C:\Customers.xls", Me.GridView1)

    End Sub
 
 

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound

        If e.Row.RowType = DataControlRowType.DataRow Then

            

        End If
 

    End Sub

End Class
 

Imports System

Imports System.Data

Imports System.Configuration

Imports System.IO

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls
 

Public Class GridViewExportUtil
 

    Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)

        HttpContext.Current.Response.Clear()

        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))

        HttpContext.Current.Response.ContentType = "application/ms-excel"

        Dim sw As StringWriter = New StringWriter

        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

        '  Create a form to contain the grid

        Dim table As Table = New Table

        table.GridLines = gv.GridLines

        '  add the header row to the table

        If (Not (gv.HeaderRow) Is Nothing) Then

            GridViewExportUtil.PrepareControlForExport(gv.HeaderRow)

            table.Rows.Add(gv.HeaderRow)

        End If

        '  add each of the data rows to the table

        For Each row As GridViewRow In gv.Rows

            GridViewExportUtil.PrepareControlForExport(row)

            table.Rows.Add(row)

        Next

        '  add the footer row to the table

        If (Not (gv.FooterRow) Is Nothing) Then

            GridViewExportUtil.PrepareControlForExport(gv.FooterRow)

            table.Rows.Add(gv.FooterRow)

        End If

        '  render the table into the htmlwriter

        table.RenderControl(htw)

        '  render the htmlwriter into the response

        HttpContext.Current.Response.Write(sw.ToString)
 

        HttpContext.Current.Response.End()

    End Sub
 

    ' Replace any of the contained controls with literals

    Private Shared Sub PrepareControlForExport(ByVal control As Control)

        Dim i As Integer = 0

        Do While (i < control.Controls.Count)

            Dim current As Control = control.Controls(i)

            If (TypeOf current Is LinkButton) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))

            ElseIf (TypeOf current Is ImageButton) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))

            ElseIf (TypeOf current Is HyperLink) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))

            ElseIf (TypeOf current Is DropDownList) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))

            ElseIf (TypeOf current Is CheckBox) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))

                'TODO: Warning!!!, inline IF is not supported ?

            End If

            If current.HasControls Then

                GridViewExportUtil.PrepareControlForExport(current)

            End If

            i = (i + 1)

        Loop

    End Sub

End Class

Open in new window

GV2Excel.doc
0
Comment
Question by:yr2yr
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:SaWey
ID: 24866577
In VBA it is 'Chr(10)' so it might be the same in ASP.
0
 

Author Comment

by:yr2yr
ID: 24870569
Tried replacing <br /> with chr(10) and other forms of cr lf - They all get stripped out and I end up with single line cell in Excel.  I want to export in a way so that embedded crlf will create multi-line cell in Excel.  I have simplied it even farther - I created a csv file with embedded crlf and it loads in Excel as multiple rows instead of single row with first cell having multiple lines.  Text wrap is on in Excel.
Am I trying to do something that cannot be done? - I just want to export multi-line data to Excel and have it show up in Excel as single row with muilt-line cells based on crlf chars.
0
 
LVL 2

Expert Comment

by:SaWey
ID: 24873283
Are you using it like
xlWorkSheet.Cells(1, 1) = "This is the first line" & Chr(10) & "and this is the second line"
0
 

Accepted Solution

by:
yr2yr earned 0 total points
ID: 24873429
Yes I was doing as you indicated.  
I have found a resolution - I decided to export in CSV format - the key was to enclosed any fileld that I had line feed in quotes - then import to excel - works just fine.

Thanks for your time.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

22 Experts available now in Live!

Get 1:1 Help Now