How to print a text before writing grid data to Excel

Question

I am trying to print a text " This is my customer information" into an Excel file before writing GridView data to Excel. This text should be in top and remaining data should display below this text.

Can anyone help me? How can I do it? Please check my current code. This code just writes my GridView data to an Excel file.

protected void ExportToExcel(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                //To Export all pages
                GridView1.AllowPaging = false;

                Search(null, null);

               // 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)
                    {


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

                    }


                    //table.Rows.Add(title);

                }
                GridView1.Columns[7].Visible = false;
                GridView1.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();
            }
        }

Add the text to the gridview then put the grid in excel? You have a header row, where do you want the text to appear within the sheet? — blaze_125, Sep 01 '17 at 12:54
This text should be in top row of excel. I dont want to show in gridview this text, — atc, Sep 01 '17 at 12:55
My advice to you is to use either Microsoft Access Redistributables (https://stackoverflow.com/questions/23041021/how-to-write-some-data-to-excel-file-xlsx) or DocumentFormat.OpenXML (https://www.nuget.org/packages/DocumentFormat.OpenXml/) or some type of OpenXML wrapper to write your documents. Creating them by writing directly into a file yourself is asking for trouble. — David Yates, Sep 01 '17 at 13:47

blaze_125 · Accepted Answer · 2017-09-01T13:48:36.060

Boils down to

Response.Write("Some cool text added before the grid");

before

    Response.Output.Write(sw.ToString());

.

using System;
using System.Drawing;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Collections.ObjectModel;


namespace AddTextToDataGridExportExcel_46000670
{
    public partial class Default : System.Web.UI.Page
    {
        static ObservableCollection<gridEntry> gridDataSource = new ObservableCollection<gridEntry>();
        protected void Page_Load(object sender, EventArgs e)
        {
            fillDataSource();
            initializeGridView();
        }

        private void fillDataSource()
        {
            gridDataSource.Add(new gridEntry(1));
            gridDataSource.Add(new gridEntry(2));
            gridDataSource.Add(new gridEntry(3));
            gridDataSource.Add(new gridEntry(4));
            gridDataSource.Add(new gridEntry(5));
        }
        private void initializeGridView()
        {
            GridView1.DataSource = gridDataSource;
            GridView1.DataBind();
        }


        protected void ExportToExcel(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                //To Export all pages
                GridView1.AllowPaging = false;
                addRowToGrid();//add text within the grid
                initializeGridView();

                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)
                    {
                        if (row.RowIndex % 2 == 0)
                        {
                            cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                        }
                        else
                        {
                            cell.BackColor = GridView1.RowStyle.BackColor;
                        }
                        cell.CssClass = "textmode";
                    }
                }

                GridView1.RenderControl(hw);

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

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


        private void addRowToGrid()
        {
            gridDataSource.Insert(0, new gridEntry { col1 = "this is my added text" });
        }

        private GridView addRowToGridAgain(GridView gv)
        {
            return gv;
        }

        protected void btn_ClickMe_Click(object sender, EventArgs e)
        {
            ExportToExcel(null, null);
        }
    }

    public class gridEntry
    {
        public string col1 { get; set; }
        public string col2 { get; set; }
        public string col3 { get; set; }
        public string col4 { get; set; }
        public string col5 { get; set; }
        public string col6 { get; set; }
        public string col7 { get; set; }
        public string col8 { get; set; }

        public gridEntry()
        {
            col1 = string.Empty;
            col2 = string.Empty;
            col3 = string.Empty;
            col4 = string.Empty;
            col5 = string.Empty;
            col6 = string.Empty;
            col7 = string.Empty;
            col8 = string.Empty;
        }

        public gridEntry(int index)
        {
            col1 = "col1 " + index.ToString();
            col2 = "col2 " + index.ToString();
            col3 = "col3 " + index.ToString();
            col4 = "col4 " + index.ToString();
            col5 = "col5 " + index.ToString();
            col6 = "col6 " + index.ToString();
            col7 = "col7 " + index.ToString();
            col8 = "col8 " + index.ToString();
        }
    }

}

Below is YOUR snippet, fixed

//GridView1.Columns[7].Visible = false; was throwing an error, so I commented it out. That's up to you to figure out, or ask a new question for it

Don't know what Search(null, null); does, so I commented it out

protected void ExportToExcelPosterWay(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;

        //Search(null, null);
        initializeGridView();

        // 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)
            {


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

            }


            //table.Rows.Add(title);

        }
        //GridView1.Columns[7].Visible = false;
        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Write("Some cool text added before the grid");
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

How to print a text before writing grid data to Excel

1 Answers1