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.