导出时excel中的条件格式

本文介绍了导出时excel中的条件格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 你好， i需要将行的背景颜色设置为绿色，这是我从gridview导出的。 它必须是绿色其中start_date = system.date.add（1） i在gridview中做了它但是我需要在excel中导出它时做同样的事情 我的代码如下请求帮助我这个。 [格式化代码 - 开始] protected void btnShow_Click（ object sender，EventArgs e） { 尝试 { 如果 （txtsdate.Text！= && txtedate.Text！= ） { string [] dtarr1 = new string [ 3 ]; dtarr1 = txtsdate.Text.Split（' - '）; string dfmain = dtarr1 [ 1 ] + - + dtarr1 [ 0 ] + - + dtarr1 [ 2 ]; string [] dtarr2 = new string [ 3 ]; dtarr2 = txtedate.Text.Split（' - '）; string dtmain1 = dtarr2 [ 1 ] + - + dtarr2 [ 0 ] + - + dtarr2 [ 2 ]; DataSet ds1 = new DataSet（）; Hashtable ht = new Hashtable（ 2 ）; ht.Add（ @ ddate，Convert.ToDateTime（dfmain））; ht.Add（ @ edate，Convert.ToDateTime（dtmain1））; ds1 = c.getstoredata（ DailyVacancyReport，ht）; if （ds1.Tables [ 0 ]。Rows.Count > 0 ） { GridView1.DataSource = ds1; GridView1.DataBind（）; Button6.Visible = true ; } else { Response.Write（ < script> alert（'找不到记录。请尝试另一个日期！'）< / script>）; } } } catch （例外情况） {} } protected void GridView1_RowDataBound（ object sender，GridViewRowEventArgs e） { if （e.Row.RowType == DataControlRowType.DataRow） { 标签l =（标签）e.Row.FindControl（ lblsdate）; string dttm = Convert.ToString（DateTime.Now.AddDays（ 1 ）.ToShortDateString（））; string [] dtarr1 = new string [ 3 ]; dtarr1 = dttm.Split（' /'）; string dfmain = dtarr1 [ 1 ] + - + dtarr1 [ 0 ] + - + dtarr1 [ 2 ]; string dttm1 = l.Text; string [] dtarr2 = new string [ 3 ]; dtarr2 = dttm1.Split（' - '）; string dfmain1 = dtarr2 [ 0 ] + - + Convert.ToInt16（dtarr2 [ 1 ]）+ - + dtarr2 [ 2 ]; if （dfmain1 == dfmain） { e.Row.BackColor = System.Drawing.Color。绿色; } if （（DataBinder.Eval（e.Row.DataItem， 小时）！= DBNull.Value）） { doc + = Convert.ToDouble（DataBinder.Eval（e） .Row.DataItem， Hours））; } } if （e.Row.RowType == DataControlRowType.Footer） {标签lbldoctors =（标签）e.Row.FindControl（ lbltotal）; lbldoctors.Text = doc.ToString（）; } } 受保护 void Button6_Click（ object sender，EventArgs e） { try { string [] dtarr1 = new string [ 3 ]; dtarr1 = txtsdate.Text.Split（' - '）; string dfmain = dtarr1 [ 1 ] + - + dtarr1 [ 0 ] + - + dtarr1 [ 2 ]; string [] dtarr2 = new string [ 3 ]; dtarr2 = txtedate.Text.Split（' - '）; string dtmain1 = dtarr2 [ 1 ] + - + dtarr2 [ 0 ] + - + dtarr2 [ 2 ]; fdoc = 0 ; DataSet ds1 = new DataSet（）; Hashtable ht = new Hashtable（ 2 ）; ht.Add（ @ ddate，Convert.ToDateTime（dfmain））; ht.Add（ @ edate，Convert.ToDateTime（dtmain1））; ds1 = c.getstoredata（ DailyVacancyReport，ht）; DataTable dtTemp = new DataTable（）; // 创建标题行 dtTemp .Columns.Add（ ）; dtTemp.Columns.Add（ EvolveID ）; dtTemp.Columns.Add（ HospitalName ）; dtTemp.Columns.Add（ ContactName ）; dtTemp.Columns.Add（ 职位名称） ; // dtTemp.Columns.Add（ Specialty ）; dtTemp.Columns.Add（ Hours ）; DataRow drAddItem; for （ int i = 0 ; i < ds1.Tables [ 0 ]。Rows.Count; i ++） { string s = ds1.Tables [ 0 ]。行[i] [ JobTitleName]。ToString（）; string t = ds1.Tables [ 0 ]。行[i] [ 专业]。ToString（）; string vs = string .Concat（s， - ，t）; drAddItem = dtTemp.NewRow（）; drAddItem [ 0 ] = ; drAddItem [ 1 ] = ds1.Tables [ 0 ]。行[i] [ EvolveID]。ToString（）; drAddItem [ 2 ] = ds1.Tables [ 0 ]。行[i] [ HospitalName]。ToString（）; drAddItem [ 3 ] = ds1.Tables [ 0 ]。行[i] [ ContactName]。ToString（）; drAddItem [ 4 ] = vs; // drAddItem [5] = ds1.Tables [0] .Rows [i] [Specialty ] .ToString（）; drAddItem [ 5 ] = ds1.Tables [ 0 ]。行[i] [ Hours]。ToString（）; if （ds1.Tables [ 0 ]。行[i] [ Hours]。ToString（）！= ） { fdoc + = Convert.ToDouble（ds1.Tables [ 0 ]。行[i] [ 小时]。ToString（））; } dtTemp.Rows.Add（drAddItem）; } if （ds1.Tables [ 0 ]。 Rows.Count > 0 ） { drAddItem = dtTemp.NewRow（ ）; drAddItem [ 4 ] = 总; drAddItem [ 5 ] = + fdoc + ; dtTemp.Rows.Add（drAddItem）; } // 临时网格 DataGrid dg = new DataGrid（）; dg.DataSource = dtTemp; dg.DataBind（）; ExportToExcel（ DataSheet.xls，dg）; dg = null ; dg.Dispose（）; } catch （例外情况） { Response.Write（ex.Message）; } } private void ExportToExcel（ string strFileName，DataGrid dg） { Response.ClearContent（）; Response.AddHeader（ content-disposition， attachment; filename = + strFileName）; Response.ContentType = application / excel; System.IO.StringWriter sw = new System.IO.StringWriter（）; HtmlTextWriter htw = new HtmlTextWriter（sw）; dg.RenderControl（htw）; Response.Write（sw.ToString（））; Response.End（）; } [格式化代码 - 结束] 解决方案 您好， 请通过以下讨论，它可能会给你解决方案： http://www.tek-tips.com/viewthread.cfm?qid=1684754 [ ^ ] http://go4answers.webhost4life.com/Example/aspnet-gridview-export-excel-column-101909.aspx [ ^ ] http://forums.asp.net/t/1134560.aspx/1 [ ^ ] 谢谢大家前几天我解决了..再次感谢 hello there,i need to set backcolor of row as green which is i am exporting from gridview.it must be green where start_date=system.date.add(1)i did it in gridview but i need to do the same while exporting it in excelmy code is as under please help me with this.[Formatted Code - Start] protected void btnShow_Click(object sender, EventArgs e) { try { if (txtsdate.Text != "" && txtedate.Text != "") { string[] dtarr1 = new string[3]; dtarr1 = txtsdate.Text.Split('-'); string dfmain = dtarr1[1] + "-" + dtarr1[0] + "-" + dtarr1[2]; string[] dtarr2 = new string[3]; dtarr2 = txtedate.Text.Split('-'); string dtmain1 = dtarr2[1] + "-" + dtarr2[0] + "-" + dtarr2[2]; DataSet ds1 = new DataSet(); Hashtable ht = new Hashtable(2); ht.Add("@ddate", Convert.ToDateTime(dfmain)); ht.Add("@edate", Convert.ToDateTime(dtmain1)); ds1 = c.getstoredata("DailyVacancyReport", ht); if (ds1.Tables[0].Rows.Count > 0) { GridView1.DataSource = ds1; GridView1.DataBind(); Button6.Visible = true; } else { Response.Write("<script>alert('There is no record found. Please try another Date!')</script>"); } } } catch (Exception ex) { } } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { Label l = (Label)e.Row.FindControl("lblsdate"); string dttm = Convert.ToString(DateTime.Now.AddDays(1).ToShortDateString()); string[] dtarr1 = new string[3]; dtarr1 = dttm.Split('/'); string dfmain = dtarr1[1] + "-" + dtarr1[0] + "-" + dtarr1[2]; string dttm1 = l.Text; string[] dtarr2 = new string[3]; dtarr2 = dttm1.Split('-'); string dfmain1 = dtarr2[0] + "-" + Convert.ToInt16(dtarr2[1]) + "-" + dtarr2[2]; if (dfmain1 == dfmain) { e.Row.BackColor = System.Drawing.Color.Green; } if ((DataBinder.Eval(e.Row.DataItem, "Hours") != DBNull.Value)) { doc += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "Hours")); } } if (e.Row.RowType == DataControlRowType.Footer) { Label lbldoctors = (Label)e.Row.FindControl("lbltotal"); lbldoctors.Text = doc.ToString(); } } protected void Button6_Click(object sender, EventArgs e) { try { string[] dtarr1 = new string[3]; dtarr1 = txtsdate.Text.Split('-'); string dfmain = dtarr1[1] + "-" + dtarr1[0] + "-" + dtarr1[2]; string[] dtarr2 = new string[3]; dtarr2 = txtedate.Text.Split('-'); string dtmain1 = dtarr2[1] + "-" + dtarr2[0] + "-" + dtarr2[2]; fdoc = 0; DataSet ds1 = new DataSet(); Hashtable ht = new Hashtable(2); ht.Add("@ddate", Convert.ToDateTime(dfmain)); ht.Add("@edate", Convert.ToDateTime(dtmain1)); ds1 = c.getstoredata("DailyVacancyReport", ht); DataTable dtTemp = new DataTable(); //Creating Header Row dtTemp.Columns.Add(" "); dtTemp.Columns.Add("EvolveID"); dtTemp.Columns.Add("HospitalName"); dtTemp.Columns.Add("ContactName"); dtTemp.Columns.Add("Job Title"); //dtTemp.Columns.Add("Speciality"); dtTemp.Columns.Add("Hours"); DataRow drAddItem; for (int i = 0; i < ds1.Tables[0].Rows.Count; i++) { string s = ds1.Tables[0].Rows[i]["JobTitleName"].ToString(); string t = ds1.Tables[0].Rows[i]["Speciality"].ToString(); string vs = string.Concat(s,"-", t); drAddItem = dtTemp.NewRow(); drAddItem[0] = " "; drAddItem[1] = ds1.Tables[0].Rows[i]["EvolveID"].ToString(); drAddItem[2] = ds1.Tables[0].Rows[i]["HospitalName"].ToString(); drAddItem[3] = ds1.Tables[0].Rows[i]["ContactName"].ToString(); drAddItem[4] = vs;// drAddItem[5] = ds1.Tables[0].Rows[i]["Speciality"].ToString(); drAddItem[5] = ds1.Tables[0].Rows[i]["Hours"].ToString(); if (ds1.Tables[0].Rows[i]["Hours"].ToString() != "") { fdoc += Convert.ToDouble(ds1.Tables[0].Rows[i]["Hours"].ToString()); } dtTemp.Rows.Add(drAddItem); } if (ds1.Tables[0].Rows.Count > 0) { drAddItem = dtTemp.NewRow(); drAddItem[4] = "Total"; drAddItem[5] = "" + fdoc + ""; dtTemp.Rows.Add(drAddItem); } //Temp Grid DataGrid dg = new DataGrid(); dg.DataSource = dtTemp; dg.DataBind(); ExportToExcel("DataSheet.xls", dg); dg = null; dg.Dispose(); } catch (Exception ex) { Response.Write(ex.Message); } } private void ExportToExcel(string strFileName, DataGrid dg) { Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=" + strFileName); Response.ContentType = "application/excel"; System.IO.StringWriter sw = new System.IO.StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); dg.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); }[Formatted Code - End] 解决方案 Hello,Please go through below discussion, it might give you resolution:http://www.tek-tips.com/viewthread.cfm?qid=1684754[^]http://go4answers.webhost4life.com/Example/aspnet-gridview-export-excel-column-101909.aspx[^]http://forums.asp.net/t/1134560.aspx/1[^]thanks everybody i solved it many days ago.. thanks again 这篇关于导出时excel中的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！