Export to Excel in c#

Arun T

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nicholas Barns12y ago
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");
}
0
Tirthak Shah12y ago
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.Count; 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
0
Balakrishnan P12y ago
You can Export to excel or CSV using libraries such as RKLib etc, it is much easier and same as responsewrite.
0
Arun T12y ago
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.
0
Ring Zhong12y ago
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.
0