CipherIS
asked on
ASP.NET - C# - set gridview cell as text and export to excel as text
I am populating a grid with data from a database. I am then exporting that data to excel. The problem I am having is that a column will have a value "001" but it exports as "1". How can I define it so it is text when it exports?
protected void ExportToExcel(DataTable dt)
{
string sFileName = "Data_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";
GridView gv = new GridView();
gv.AllowPaging = false;
gv.DataSource = dt;
gv.DataBind();
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/vnd.openxmlformats";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
//Change the Header Row Foreground to white color
gv.HeaderRow.Style.Add("color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
//Change to Blue
gv.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
}
int j = 1;
//This loop is used to apply stlye to cells based on particular row
foreach (GridViewRow gvrow in gv.Rows)
{
gvrow.BackColor = Color.White;
if (j <= gv.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();
}
ASKER
Huh? Where would that go?
ASKER
I walked through the values in the DataTable and GridView and both contain 001. The problem is when it opens in excel that it formats it to a number.
Any ideas?
Any ideas?
Hi, try this
protected void Button1_Click(object sender, EventArgs e)
{
this.gvw1.Columns[0].Heade rText = "The new header";
}
protected void Button1_Click(object sender, EventArgs e)
{
this.gvw1.Columns[0].Heade
}
You should be able to format the column in question to be text using:
.EntireColumn.NumberFormat = "@";
.EntireColumn.NumberFormat
ASKER
@jjester1 - where would i put that code ".EntireColumn.NumberForma t = "@"; "
@samirbhogayta - why are you modifying the headertext?
@samirbhogayta - why are you modifying the headertext?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured it out!
also, if you need to pad leading zeros or format, check this info:
http://msdn.microsoft.com/en-us/library/dd260048.aspx