Solved

Grid view - Export to Excel without hyperlink

Posted on 2007-04-09
6
2,639 Views
Last Modified: 2012-08-14
We are working on ASP.Net 2.0 and VB.Net.
We have gridview whichis bounded to the dataset. The first column of the grid view is hyperlinked. We would like to export the grid into excel. When we do the same, we getting the hyperlink in the excel as well. We would like to remove the hyperlink when exported to excel. Please let me know how to do this? Also any code sample will be of great help
0
Comment
Question by:harsha74
6 Comments
 
LVL 3

Expert Comment

by:viveksg
ID: 18880410
You can use regular expressions just before exporting to eliminate hyperlinks.
I think you are using stringwriter for exporting. Try the below code.

Regex.Replace(oStringWriter.ToString(),@"(<a[^>]*>)|(</a>)"," ",RegexOptions.IgnoreCase)

If you still have any doubt, pls post that.
0
 

Author Comment

by:harsha74
ID: 18880724
We are still getting the hyperlink. Pl see the code we are using. Pl let me know what's the problem in the code.
Protected Sub ExcelImage_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ExcelImage.Click
       
        'Variable Declaration

        Dim sw As StringWriter = New StringWriter()
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
       
        Try
            Response.Clear()
            Response.ContentType = "application/vnd.excel"
            Response.Charset = ""
            Response.AddHeader("content-disposition", "attachment; FileName=MyFile.xls")
            'To bind the dataset to the datagrid, to export the same to excel

            grdSummaryReport.RenderControl(htw)
            Regex.Replace(sw.ToString(), "(<a[^>]*>)|(</a>)", " ", RegexOptions.IgnoreCase)
            Response.Write(sw.ToString())
            Response.End()
        Catch ex As Exception
            lblErrorMsg.Text = ex.Message
        Finally
                  End Try
    End Sub
0
 
LVL 3

Expert Comment

by:viveksg
ID: 18880801
Instead of
Regex.Replace(sw.ToString(), "(<a[^>]*>)|(</a>)", " ",  
                         RegexOptions.IgnoreCase)
Response.Write(sw.ToString())

use

Response.Write(Regex.Replace(sw.ToString(),@"(<a[^>]*>)|
                           (</a>)"," ",RegexOptions.IgnoreCase))
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Accepted Solution

by:
viveksg earned 500 total points
ID: 18880805
or full code

Protected Sub ExcelImage_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ExcelImage.Click
       
        'Variable Declaration

        Dim sw As StringWriter = New StringWriter()
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
       
        Try
            Response.Clear()
            Response.ContentType = "application/vnd.excel"
            Response.Charset = ""
            Response.AddHeader("content-disposition", "attachment; FileName=MyFile.xls")
            'To bind the dataset to the datagrid, to export the same to excel

            grdSummaryReport.RenderControl(htw)
         
            Response.Write(Regex.Replace(sw.ToString(), "(<a[^>]*>)|(</a>)", " ", RegexOptions.IgnoreCase))

            Response.End()
        Catch ex As Exception
            lblErrorMsg.Text = ex.Message
        Finally
                  End Try
    End Sub
0
 

Expert Comment

by:babalooey
ID: 21156473
Works great - just what I was looking for.  Be sure to include the regular expressions namespace on the page. C#: using System.Text.RegularExpressions;
0
 
LVL 1

Expert Comment

by:icastel080801
ID: 21674438
Excellent solution.  Simple and functional.  Thank you.
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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

19 Experts available now in Live!

Get 1:1 Help Now