c# - Export to excel from Gridview with color codes -
i'm using color code based on percentage logic in gridview. while exporting excel dont know how maintain color code in excel. please guide
below code rowdatabound
protected void grd_qadetails_rowdatabound(object sender, gridviewroweventargs e) { if (e.row.rowtype == datacontrolrowtype.datarow) { var datarowview = e.row.dataitem datarowview; datarow row = datarowview.row; var current = (row["current month percentage"] double?) ?? null; double? yellow = (double?)convert.todouble(row["yellow"]); double? green = (double?)convert.todouble(row["green"]); if (current != null) { if (current >= green) e.row.cells[6].backcolor = color.green; if (current >= yellow && current < green) e.row.cells[6].backcolor = color.yellow; if (current < yellow) e.row.cells[6].backcolor = color.red; } } } below code export excel
private void exportthroughweb(datatable dt, string filename) { try { if (dt.rows.count > 0) { string filename = filename + ".xls"; using (system.io.stringwriter tw = new system.io.stringwriter()) { system.web.ui.htmltextwriter hw = new system.web.ui.htmltextwriter(tw); datagrid dggrid = new datagrid(); dggrid.datasource = dt; dggrid.databind(); //get html control. dggrid.rendercontrol(hw); //write html browser. response.contenttype = "application/vnd.ms-excel"; response.appendheader("content-disposition", "attachment; filename=" + filename + ""); this.enableviewstate = false; response.write("<meta http-equiv=content-type content=\"text/html; charset=utf-8\">" + environment.newline); response.write(tw.tostring()); response.write("</body>"); response.write("</html>"); response.end(); } } }
you need attach event handler rowdatabound
please modify code follows:
private void exportthroughweb(datatable dt, string filename) { try { if (dt.rows.count > 0) { string filename = filename + ".xls"; using (system.io.stringwriter tw = new system.io.stringwriter()) { system.web.ui.htmltextwriter hw = new system.web.ui.htmltextwriter(tw); gridview dggrid = new gridview(); dggrid.datasource = dt; dggrid.rowdatabound += new gridviewroweventhandler(grd_qadetails_rowdatabound); dggrid.databind(); //get html control. dggrid.rendercontrol(hw); //write html browser. response.contenttype = "application/vnd.ms-excel"; response.appendheader("content-disposition", "attachment; filename=" + filename + ""); this.enableviewstate = false; response.write("<meta http-equiv=content-type content=\"text/html; charset=utf-8\">" + environment.newline); response.write(tw.tostring()); response.write("</body>"); response.write("</html>"); response.end(); } } hope helps..
my tested example: update
<%@ page title="home page" language="c#" masterpagefile="~/site.master" autoeventwireup="true" codebehind="default.aspx.cs" inherits="webapp1._default" %> <asp:content id="headercontent" runat="server" contentplaceholderid="headcontent"> </asp:content> <asp:content id="bodycontent" runat="server" contentplaceholderid="maincontent"> <asp:gridview runat="server" id="grddetails" onrowdatabound="grd_qadetails_rowdatabound"> </asp:gridview> <asp:button runat="server" id="btnsubmit" onclick="exporttogrid"/> </asp:content> code behind:
using system; using system.collections.generic; using system.linq; using system.web; using system.web.ui; using system.web.ui.webcontrols; using system.data; using system.drawing; namespace webapp1 { public partial class _default : system.web.ui.page { datatable dtdetails = new datatable(); protected void grd_qadetails_rowdatabound(object sender, gridviewroweventargs e) { if (e.row.rowtype == datacontrolrowtype.datarow) { var datarowview = e.row.dataitem datarowview; datarow row = datarowview.row; var current = (row["dosage"] int?) ?? null; if (current != null) { if (current >= 1 && current < 10) e.row.cells[1].backcolor = color.green; else if (current >= 10 && current < 20) e.row.cells[1].backcolor = color.yellow; else e.row.cells[1].backcolor = color.red; } } } protected void page_load(object sender, eventargs e) { dtdetails.columns.add("dosage", typeof(int)); dtdetails.columns.add("drug suggested", typeof(string)); dtdetails.columns.add("patient name", typeof(string)); dtdetails.columns.add("date", typeof(datetime)); dtdetails.columns.add("type", typeof(string)); dtdetails.columns.add("payment mode", typeof(string)); dtdetails.columns.add("appointment status", typeof(string)); dtdetails.columns.add("location", typeof(string)); dtdetails.rows.add(1, "indocin", "david", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(2, "enebrel", "sam", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(3, "hydralazine", "christoff", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(4, "combivent", "janet", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(5, "dilantin", "melanie", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(6, "indocin", "david", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(7, "enebrel", "sam", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(8, "hydralazine", "christoff", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(9, "combivent", "janet", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(10, "dilantin", "melanie", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(11, "indocin", "david", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(12, "enebrel", "sam", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(13, "hydralazine", "christoff", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(14, "combivent", "janet", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(15, "dilantin", "melanie", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(16, "indocin", "david", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(17, "enebrel", "sam", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(18, "hydralazine", "christoff", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(19, "combivent", "janet", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(20, "dilantin", "melanie", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(21, "indocin", "david", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(22, "enebrel", "sam", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(23, "hydralazine", "christoff", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(24, "combivent", "janet", datetime.now, "casuality", "cash", "pending", "kolkatta"); dtdetails.rows.add(25, "dilantin", "melanie", datetime.now, "casuality", "cash", "pending", "kolkatta"); if (!ispostback) { grddetails.datasource = dtdetails; grddetails.databind(); } } protected void exporttogrid(object sender, eventargs e) { exporttogrid(dtdetails); } private void exporttogrid(datatable dt) { if (dt.rows.count > 0) { string filename = "avinash" + ".xls"; using (system.io.stringwriter tw = new system.io.stringwriter()) { system.web.ui.htmltextwriter hw = new system.web.ui.htmltextwriter(tw); gridview dggrid = new gridview(); dggrid.datasource = dt; dggrid.rowdatabound += new gridviewroweventhandler(grd_qadetails_rowdatabound); dggrid.databind(); //get html control. dggrid.rendercontrol(hw); //write html browser. response.contenttype = "application/vnd.ms-excel"; response.appendheader("content-disposition", "attachment; filename=" + filename + ""); this.enableviewstate = false; response.write("<meta http-equiv=content-type content=\"text/html; charset=utf-8\">" + environment.newline); response.write(tw.tostring()); response.write("</body>"); response.write("</html>"); response.end(); } } } }![enter image description here][1] } 
Comments
Post a Comment