Solved

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

Posted on 2009-07-15
4
489 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
Comment Utility
In VBA it is 'Chr(10)' so it might be the same in ASP.
0
 

Author Comment

by:yr2yr
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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;…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

763 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

9 Experts available now in Live!

Get 1:1 Help Now