Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to export a grid to excel. This is how I am doing...

Dim myHtmlWriter As HtmlTextWriter
myHtmlWriter = New HtmlTextWriter(myStringWriter)

My question is, the above method exports the grid to default excel sheet. Suppose I have a excel file/spreadsheet sitting in my project as 'myexcel.xls'. I would like to export my grid on this excel sheet i.e.(myexcel.xls). Is there a way to do this? I appreciate your help.
Posted
Comments
Philippe Mori 23-May-15 22:56pm    
MaureenMcDuffie 18-Jun-15 4:22am    
You cannot export the GridView into an existing excel file with HtmlTextWriter. The only thing that you can do with it is export the HTML content of the GridView control into a new file which then you can name it with .xls extension. This is a known workaround which is useful in some cases and it works because most of the Excel applications (including MS Excel) will be able to open this file.

But in order to add a new sheet to an existing excel file you will have to go with some other approach. Unfortunately the Philippe's suggestion (using OpenXML SDK) will not suite you because that is able to manipulate with only .xlsx.
So instead take a look at this[^] article. That sample demonstrates how after using the HtmlTextWriter you can convert that HTML content into an excel file in C#.
Also take a look at how you can read the existing excel (both .xls and .xlsx) files in C#.
Note that both samples use the same C# library for manipulating with excel files.

 
Share this answer
 
Comments
MrCodeIt 22-Jan-15 18:21pm    
@Abhinav, the first link gave me a start. I can't use the way it is done in that link because I am trying to export on this excel sheet(eg. template) that I have in the project which has formatted and customized cells(like a template). If I use the way it is in this link it will export to the excel with the correct file name but without the customization of my template. So, I used Appendtext method of FileInfo Class. It exports the grid and looks like my template but it opens on word but doesn't open on excel.
Export to excel from grid view in asp.net c#

Code:-
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGrid();
}
}

private void BindGrid()
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900