Reputation: 247
I am working on a web page which has a gridview control. I need the data to be transferred to .xls format. I am able to create the .xls file and transfer the data, but the problem is that I need the gridcells to be displayed in the background in the excel sheet. Right now, it is only showing a blank background without the gridcells. Otherwise the gridview is getting transferred fine. Due to this issue, printing the .xls file is a problem. Tables with more columns are not compressing, but getting printed over 2-3 pages. My code is as follows:
public static void ExportToXLS(string fileName, GridView gv,string companyName,string reportTitle , string period)
{
//For writing to XLS file
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
Table tableReport = new Table();
tableReport.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
ReportList.PrepareControlForExport(gv.HeaderRow);
tableReport.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
ReportList.PrepareControlForExport(row);
tableReport.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
ReportList.PrepareControlForExport(gv.FooterRow);
tableReport.Rows.Add(gv.FooterRow);
}
//Takes value of company name
System.Web.UI.WebControls.Label labelCompany = new System.Web.UI.WebControls.Label();
labelCompany.Text = companyName;
labelCompany.Font.Bold = true;
//Takes value of report title
System.Web.UI.WebControls.Label labelReport = new System.Web.UI.WebControls.Label();
labelReport.Text = reportTitle;
labelReport.Font.Bold = true;
//Takes value of report period
System.Web.UI.WebControls.Label labelPeriod = new System.Web.UI.WebControls.Label();
labelPeriod.Text = period;
// render the htmlwriter into the response
htw.Write("<center>");
labelCompany.RenderControl(htw);
htw.Write("<br/>");
labelReport.RenderControl(htw);
htw.Write("<br/>");
labelPeriod.RenderControl(htw);
htw.Write("</center>");
htw.Write("<br/>");
tableReport.RenderControl(htw);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
Any suggestions?
Upvotes: 0
Views: 5637
Reputation: 288
I tried with the export to excel, seems that you first need to create an using part Excel = using Microsoft.Office.Interop.Excel
, when u click on the button, just create an excel class having workbook and worksheet property and then using gridview property of Gridview.Row[].cell[].Text.
, u can dynamically store every value in the gridview to the worksheet and then write it to a FILE
....
Upvotes: 0
Reputation: 81
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Upvotes: 0
Reputation: 18290
First of all Refer this: How to export nested gridview to excel/word with gridlines on the child grid, Hope it will help you to solve your problem.
Ref: How to export gridview to excel in a console type application?
You can refer to the Export To Excel control in the below link. It’s a custom control. Hope it can help you.
http://exporttoexcel.codeplex.com/
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
grdExport.AllowPaging = false;
oMailing.GetData(out ODs);
grdExport.DataSource = ODs;
grdExport.DataBind();
//Change the Header Row back to white color
grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");
for (int i = 0; i < grdExport.Rows.Count; i++)
{
GridViewRow row = grdExport.Rows;
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
row.Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
}
}
grdExport.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
Upvotes: 0
Reputation:
Ref: Export grid
Try this:
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
grdExport.AllowPaging = false;
oMailing.GetData(out ODs);
grdExport.DataSource = ODs;
grdExport.DataBind();
//Change the Header Row back to white color
grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");
grdExport.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
Upvotes: 1
Reputation: 12361
All of the answers will get you what you want - you're just missing the formatting part.
At the end of the day, what you are really creating is an HTML file (with an HTML Table) that Excel can read. The RESPONSE headers in @BhaskarreddyMule is what "forces" the client to treat the file as an "xls" file and if it has Excel run and open it (but the bottom line is that its not really a "native" Excel file.
Now that's out of the way, think in HTML. Style your columns, rows, and text content as you would in HTML. That's how you'd control the format (i.e. old school "nowrap" to prevent wrapping cell content, font-size, etc. etc.).
I haven't done this in a while (I've moved on to Excel XML and VB.Net XML literals when I need to do this) so I'm not sure how much level of control you have with RenderControl
...or if you have to go even further "old school" and build the HTML table string from scratch.....
Upvotes: 0