Export to Excel in c#

Question

Hi Ppl,
I am exporting the Gridview to Excel using HttpContext.Current.Response.Output.Write. In Gridview i am having the Date column. While Exporting to Excel in Excel sheet i cant able to do shorting in Datecolumn.

To know the Format of the Cell. I checked the corresponding Excel Column Formate Type, it showing as General.

If any one knows the answer pls post it..

Thank you for your Time

Arun T

Nicholas Barns · Answer

Hi, can you post the code you use to export GridView to Excel. I'm not sure, but I think you are using a common workaround in which you are actually generating a HTML file with an excel file extension. If this is true then note that you cannot manipulate with a cell format property in that file (I didn't try but if I were to guess I think that each cell will be of General format). Also you can try another approach by using this C# Excel library , something like the following: protected void btnexport_Click(object sender, EventArgs e) { StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); gridview.RenderControl(htw); ExcelFile excel; var option = LoadOptions.HtmlDefault; using (var htmlStream = new MemoryStream(option.Encoding.GetBytes(sw.ToString()))) excel = ExcelFile.Load(htmlStream, option); excel.Save(Response, "Book1.xls"); }

Tirthak Shah · Answer

public override void VerifyRenderingInServerForm(Control control) { /* Verifies that the control is rendered */ } protected void btnexport_Click(object sender, EventArgs e) { Response.ClearContent(); Response.AddHeader("content-disposition", "attachment;filename=Candidate_Inquiry.xls"); Response.ContentType = "applicatio/excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htm = new HtmlTextWriter(sw); gvcategory.BorderWidth = 1; gvcategory.AllowPaging = false; BindCategory();(Bind Your Griedview Here) for (int i = 0; i { gvcategory.HeaderRow.Cells[i].Style.Add("background-color", "#CCCCCC"); gvcategory.HeaderRow.Enabled = false; } gvcategory.RenderControl(htm); Response.Write(sw.ToString()); Response.End(); } This Is the Code Try It. It Will Definately Help You.Its Works For Me. Thanks, Tirthak Shah

Balakrishnan P · Answer

You can Export to excel or CSV using libraries such as RKLib etc, it is much easier and same as responsewrite.

Arun T · Answer

Thanks for your Information , But i am exporting Grid to Excel using Response.Write. So i cant able to use Workbook in existing code... Please share if you have any information about it.

Ring Zhong · Answer

Hi, refer the code in the post http://forums.asp.net/t/1255489.aspx ALSO you can use another method, it's easier to operate: Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; sheet.InsertDataTable((DataTable)this.gridview.source, true, 1, 1); refer: http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html hope it helps.

Export to Excel in c#

Answers (5)