Export Data to excel sheet with Styles [Resolved]

Posted by Prabhukiran345 under C# on 1/28/2014 | Points: 10 | Views : 2831 | Status : [Member] | Replies : 4
Hi,

I got a scenario that i want to export my data to excel sheet with applying some styles to it.. And also want to display the name of the person who exported the sheet and time at the bottom of the sheet.


Thanks in Advance..

Regards,
Prabhu Kiran

Thanks,
Prabhu Kiran Bommareddy



Responses

Posted by: kgovindarao523-21772 on: 1/28/2014 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
Hi,
Try this Code.
Please Mark as answer if you satisfied


private void ExportToExcel()
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Details.xls");
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

Table objTable = new Table();
TableHeaderRow tblRow = new TableHeaderRow();

TableHeaderCell cell0 = new TableHeaderCell();
cell0.BackColor = System.Drawing.Color.LightGray;
cell0.Text = "Name";
cell0.BorderStyle = BorderStyle.Solid;
tblRow.Cells.Add(cell0);

TableHeaderCell cell1 = new TableHeaderCell();
cell1.BackColor = System.Drawing.Color.LightGray;
cell1.BorderStyle = BorderStyle.Solid;
cell1.Text = "Department";
tblRow.Cells.Add(cell1);
foreach (var item in YOURLIST )
{
tr = new TableRow();

TableCell tc1 = new TableCell();
tc1.BorderStyle = BorderStyle.Solid;
tc1.Text = item.Name;
tr.Cells.Add(tc1);

TableCell tc2 = new TableCell();
tc2.BorderStyle = BorderStyle.Solid;
tc2.Text = item.Department;
tr.Cells.Add(tc2);
}

//Print Details
Table tb = new Table();
TableRow tpr = new TableRow();
TableCell tc = new TableCell();
tc.Attributes.Add("colspan", "6");
tc.Attributes.Add("align", "right");
//Displaying Name and Date here
tc.Text = string.Concat("<br/>Printed By:<b> ", Environment.UserName, "</b><br/> On: <b>", System.DateTime.Now, "</b><br/><br/> ");
tpr.Cells.Add(tc);
tb.Rows.Add(tpr);


objTable.RenderControl(htmlWrite);
tb.RenderControl(htmlWrite);

HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}



Thank you,
Govind

Prabhukiran345, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: kgovindarao523-21772 on: 1/28/2014 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
Yes,

Try with
User.Identity.Name


Thank you,
Govind

Prabhukiran345, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Prabhukiran345 on: 1/28/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Govind,
Thanks for your fast response. Is there any alternate method rather than using
Environment.UserName
Because i am not getting the name after hosting my website.

Regards,
Prabhu Kiran B

Thanks,
Prabhu Kiran Bommareddy

Prabhukiran345, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Snaveen on: 2/13/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

If you want to apply styles while export your datacontrol information into excel then refer below sample code to achieve your goal.

protected void Export(string fileName, GridView gv)

{
//HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}.xls", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
System.Web.UI.WebControls.Table table = new System.Web.UI.WebControls.Table();

// add the header row to the table
if (gv.HeaderRow != null)
{
PrepareControlForExport(gv.HeaderRow);
gv.HeaderRow.Style.Add("border", "solid 1px #336633");
table.Rows.Add(gv.HeaderRow);
TableRow tRow = gv.HeaderRow;
tRow.Style[System.Web.UI.HtmlTextWriterStyle.BackgroundColor] = "#336633";
tRow.Style[System.Web.UI.HtmlTextWriterStyle.Color] = "White";
}

// add each of the data rows to the table
for (int i = 0; i < gv.Rows.Count; i++)
{
GridViewRow row = gv.Rows[i];
PrepareControlForExport(row);
row.Style.Add("border", "solid 1px #336633");
row.BackColor =System.Drawing.Color.FromName("#EEFDB9");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#FFFFCC");
row.Cells[1].Style.Add("background-color", "#FFFFCC");
row.Cells[2].Style.Add("background-color", "#FFFFCC");
row.Cells[3].Style.Add("background-color", "#FFFFCC");
row.Cells[4].Style.Add("background-color", "#FFFFCC");
row.Cells[5].Style.Add("background-color", "#FFFFCC");
row.Cells[6].Style.Add("background-color", "#FFFFCC");
row.Cells[7].Style.Add("background-color", "#FFFFCC");
row.Cells[8].Style.Add("background-color", "#FFFFCC");
row.Cells[9].Style.Add("background-color", "#FFFFCC");
row.Cells[10].Style.Add("background-color", "#FFFFCC");
row.Cells[11].Style.Add("background-color", "#FFFFCC");
row.Cells[12].Style.Add("background-color", "#FFFFCC");
row.Cells[13].Style.Add("background-color", "#FFFFCC");
row.Cells[14].Style.Add("background-color", "#FFFFCC");
row.Cells[15].Style.Add("background-color", "#FFFFCC");
}


table.Rows.Add(row);
table.CssClass = "exampleDiv";
}

// add the footer row to the table
if (gv.FooterRow != null)
{
PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}

// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}


Prabhukiran345, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response