Click here to Skip to main content
15,949,741 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I wanted first column cells of excel sheet is text and other columns are number.

What I have tried:

Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "" + Result + ""));
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            string style = string.Empty;
            //To Export all pages
            GridView1.AllowPaging = false;


            GridView1.HeaderRow.BackColor = Color.White;
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                cell.BackColor = GridView1.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {

                    string value = cell.Text;
                    string text = value[0].ToString();

                    if (text == "0")
                    {
                        cell.Text = cell.Text;
                    }
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = GridView1.RowStyle.BackColor;
                    }

                    cell.CssClass = "textmode";
                    //cell.Style.Add("style", "mso-number-format:\\@");

                }
            }

            GridView1.RenderControl(hw);
           
            //style to format numbers to string
            style = @"<style> .textmode {  mso-number-format:\@;} </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
Posted
Comments
Karthik_Mahalingam 5-Oct-17 6:29am    
Member 12962919 5-Oct-17 6:31am    
can you explain
Karthik_Mahalingam 5-Oct-17 6:35am    
using closedxml you can achieve almost all the tasks which can be done using interop
Richard Deeming 5-Oct-17 12:21pm    
You are not exporting data to Excel. You are simply generating an HTML fragment, and sending a header to tell the browser to pretend that it's an Excel file.

Excel will do what it can to convert that HTML to a proper Excel file. But you have virtually no control over how it does that, or what the final result looks like.

If you want control over the file, you need to generate and return a real Excel file. You do that using a library like ClosedXML, as Karthik said.
Member 12962919 6-Oct-17 2:49am    
but if i used ClosedXML then it should be necessary that excel is installed on client system like interop.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900