Give background color to excel sheet in asp.net

3
votes

I am performing export to excel in asp.net without using any third party controls. How can i give background color to my exported excel sheet?

The background color may (not sure) be different according to some cells range. Say from Cell 0- 5 (Cell A-E in excel) is red color, 6-12 is green and so on and so forth.

How can i achieve the same?

public static void DataSetToExcel(System.Data.DataSet dtExport, System.Web.HttpResponse response, string strFileName)
{
    //Clean up the response Object
    response.Clear();
    response.Charset = "";

    //Set the respomse MIME type to excel
    response.ContentType = "application/vnd.ms-excel";

    //Opens the attachment in new window
    response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName.ToString() + ".xls;");
    response.ContentEncoding = Encoding.Unicode;
    response.BinaryWrite(Encoding.Unicode.GetPreamble());

    //Create a string writer
    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

    //Create an htmltextwriter which uses the stringwriter
    System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

    //Instantiate the datagrid

    System.Web.UI.WebControls.GridView dgrExport = new System.Web.UI.WebControls.GridView();

    //Set input datagrid to dataset table
    dgrExport.DataSource = dtExport.Tables[0];

    //bind the data with datagrid
    dgrExport.DataBind();

    //Make header text bold
    dgrExport.HeaderStyle.Font.Bold = true;

    //bind the modified datagrid
    dgrExport.DataBind();

    //Tell the datagrid to render itself to our htmltextwriter
    dgrExport.RenderControl(htmlWrite);

    //Output the HTML
    response.Write(stringWrite.ToString());


    response.End();
}
4
c#asp.netvisual-studioexport-to-excel
It maybe useful to share some of your code - are you building and XLS or an XLSX or something else, for example. – Rowland Shaw
So you're not actually generating an Excel file, but a HTML file (with a fake MIME type/filename to encourage it to load into Excel). – Rowland Shaw
but finally an excel is only generated with Open-Save-Cancel button – xorpower
You may want to consider instead generating a file in the relevant Open XML standard (there's some information on MSDN) – Rowland Shaw
so giving background color to the same is not possible? – xorpower

4 Answers

0
votes

In the HTML definition for Excel there seems to be way though I didn't try it, see http://www.c-sharpcorner.com/UploadFile/kaushikborah28/79Nick08302007171404PM/79Nick.aspx and check the official documentation (help file about HTML for Excel) at http://msdn.microsoft.com/en-us/library/Aa155477%28office.10%29.aspx

Better alternative for creating Excel files is to use for example OpenXML from Microsoft (free library) see http://msdn.microsoft.com/en-us/office/bb265236 and http://openxmldeveloper.org/

0
votes

Can you just set the item style of the columns, like this:

GridView1.Columns[0].ItemStyle.BackColor = Color.PeachPuff;
GridView1.Columns[1].ItemStyle.BackColor = Color.Red;
0
votes

I have seen lot of blogs where people wants to change the back ground color of excel sheet. Here is the solution (Working). Please try this.

Below listed code will change the back ground & foreground color of your excel sheet and the data whatever you have exported to the excel.

    Response.Write("<HTML><HEAD>");
    Response.Write("<style> BODY { background-color:lightyellow; } TD { background-color:lightgrey; } </style>");        
    Response.Write("</HEAD><BODY>");        
    Response.Write(stringWrite.ToString());
    Response.Write("</BODY></HTML>");
    Response.End();

Implemented Code

0
votes

//Make header text bold

 dgrExport.HeaderStyle.Font.Bold = true;
 dgrExport.HeaderStyle.BackColor = Color.Black;                            
 dgrExport.HeaderStyle.ForeColor = Color.White;