Asp.net code behind not to export empty columns from a gridview to excel

Question

My asp.net page has grid-view with 5 columns. I export them to excel but I get empty column as well. Here is my code in C# asp.net code behind;

protected void ExportToExcel(object sender, EventArgs e)
{
    string nowT = DateTime.Now.ToString("yyyy-MM-dd");
    string excelNameExport = "attachment;filename=" + nowT + "_LT_Report.xls";
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", excelNameExport);
    Response.ContentEncoding = System.Text.Encoding.UTF8;
    Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());
    Response.ContentType = "application/vnd.ms-excel";

    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        mygrid.AllowPaging = false;
        this.gvBind();

        for (int i = 0; i < mygrid.Columns.Count; i++)
        {
            if (mygrid.Columns[i].HeaderText == "TARİH")
            {  
            }
            else if (mygrid.Columns[i].HeaderText == "SAAT")
            {
            }
            else if (mygrid.Columns[i].HeaderText == "ADI SOYADI")
            {
            }
            else if (mygrid.Columns[i].HeaderText == "SON_4_KNO")
            {
            }
            else if (mygrid.Columns[i].HeaderText == "DURUMU")
            {
            }
            else
            {
                mygrid.Columns.RemoveAt(i);
                this.mygrid.Columns[i].Visible = false;
                mygrid.DataBind();
            }
        }
        mygrid.DataBind();
        mygrid.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Everything works fine. When I export to excel it looks like this:

enter image description here

Excel always has those additional columns (and rows). You can't get rid of them. — krillgar, May 17 '15 at 01:22
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, May 17 '15 at 05:18
@krillgar, I know excel has all these columns but how to stop formatting those columns? — NTMS, May 17 '15 at 06:27
Hi @JohnSaunders, now titile becomes more difficult. Is it possible to use title as "In asp.net, not to export empty columns from a grid-view to excel"? — NTMS, May 17 '15 at 06:31
You could use a title like that, but you may not need to. People searching for ASP.NET questions will use the tag to search with. My point is that the information you would have conveyed in the title is already conveyed by using the tag. Putting that information in the title is redundant at best. — John Saunders, May 17 '15 at 07:04

score 1 · Answer 1 · answered May 17 '15 at 06:32

1

If you open up even a blank Excel document, I believe that you still see those extra cells/columns. This seems to be more a function of how Excel works than any issue with your code.

answered May 17 '15 at 06:32

John Hodge

1,645
1
13
13

my major problem that I don't want those extra columns to be formatted? I see many of examples that those unwanted cells/columns not formatted. In my example after column E formatted as well. – NTMS May 17 '15 at 06:41
I don't want red back-color on first row from E columns to the end and the borders become solid black – NTMS May 17 '15 at 06:55
I would probably try opening up the Excel file that is being generated and seeing how it is getting written. I suspect that it's just an HTML table that it's sticking an Excel content-type tag onto. You might be able to figure out from this how to reverse the formatting. – John Hodge May 17 '15 at 07:07
I couldn't find. I also include my generated excel in my post as well as the code. Even I remove empty columns but still generated excel shows formatted empty columns. – NTMS May 17 '15 at 07:52
Problem comes from HeaderStyle, RowStyle, AlternatingRowStyle, FooterStyle, PagerStyle. I have to re-print these styles after binding to grid and before sending to mygrid.RenderControl(hw); – NTMS May 17 '15 at 09:11
Ok. I change my code. Excel to Word like (Response.ContentType = "application/vnd.ms-word";) and it worked. – NTMS May 17 '15 at 09:55

score 0 · Answer 2 · answered May 17 '15 at 09:56

0

Ok. I change my code. Excel to Word like (Response.ContentType = "application/vnd.ms-word";) and it worked.

answered May 17 '15 at 09:56

NTMS

816
7
22

So you're exporting to Word instead of Excel? – John Hodge May 17 '15 at 12:47
Yes. In word I get the report exactly as I want. In word I cant change the excel grid text. It shows only "Times New Roman". In my code " mygrid.RenderControl(hw);" when I debug the hw its perfect html condition. But in "Response.Output.Write(sw.ToString());" there is a lot of backslash (and \r\n \n \r\n as well as \" ). I couldn't get rid of them to show word text as "Segoe UI" font type. But at least I get my report. Thanks. – NTMS May 17 '15 at 20:04

Asp.net code behind not to export empty columns from a gridview to excel

2 Answers2