Click here to Skip to main content
15,861,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have successfully export data in excel file.
I want hide one column in excel?
And for one column I need to manupulated data from code side to before export?

So basically if I will get data in variable for that column I can do.
but i didn't get data after tried multiple things.

C#
public void ExportToExcel()
        {
            var gv = new GridView();
            gv.DataSource = Session["filltoexcel"]; // get data
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=Demodata.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter objStringWriter = new StringWriter();
            HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
            gv.RenderControl(objHtmlTextWriter);
            Response.Output.Write(objStringWriter.ToString());
            Response.Flush();
            Response.End();
        }


What I have tried:

C#
gv.Columns[3].Visible = false;

C#
gv.Columns.RemoveAt(1);

C#
gv.Columns.Remove();

C#
gv.Columns["StrDateCreated"].Visible = false;


i want this data in variable (after line 4 from above method), i tried but didn't luck.
C#
var value= gv.Columns["StrDateCreated"]
that will help to manupuated data internally.
Posted
Updated 21-Dec-22 23:01pm

You are not exporting an Excel file; you are sending an HTML table back to the client, and "lying" about the content type to trick the browser into opening it in Excel.

Excel will display a warning that the file is "corrupt", and will then attempt to convert the HTML to a real Excel file. You will have virtually no control over the formatting or structure of the file it creates.

Instead, you should generate a real Excel file and send that to the client instead. There are numerous open-source libraries you can use to do this - for example, ClosedXML[^] or the OpenXML SDK[^].

The ClosedXML library has numerous examples which will show you how to achieve your desired result, including:
Inserting Data · ClosedXML/ClosedXML Wiki · GitHub[^]
Deliver an Excel file in ASP.NET · ClosedXML/ClosedXML Wiki · GitHub[^]
 
Share this answer
 
as i told export to excel is fine, but want to hide few column(s).
 
Share this answer
 
Comments
Richard Deeming 22-Dec-22 5:11am    
1) If you want to reply to a solution, click the "Have a Question or Comment?" button under that solution and post a comment. Do not post your comment as a "solution" to your question.

2) As I told you, you are not "exporting to Excel"; you are sending HTML with an incorrect MIME type, and asking Excel to deal with it. If you want control over the generated file, or you want to get rid of the "corrupt file" warning when you open it, you need to export to a real Excel file.

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