CODEWITHSUNDEEP
c#asp.netexcel
asp developer
asp developer

Reputation: 247

Exporting gridview to .xls

I am working on a web page which has a gridview control. I need the data to be transferred to .xls format. I am able to create the .xls file and transfer the data, but the problem is that I need the gridcells to be displayed in the background in the excel sheet. Right now, it is only showing a blank background without the gridcells. Otherwise the gridview is getting transferred fine. Due to this issue, printing the .xls file is a problem. Tables with more columns are not compressing, but getting printed over 2-3 pages. My code is as follows:

public static void ExportToXLS(string fileName, GridView gv,string companyName,string reportTitle , string period)
    {
        //For writing to XLS file
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {

                Table tableReport = new Table();
                tableReport.GridLines = gv.GridLines;

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    ReportList.PrepareControlForExport(gv.HeaderRow);
                    tableReport.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    ReportList.PrepareControlForExport(row);
                    tableReport.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    ReportList.PrepareControlForExport(gv.FooterRow);
                    tableReport.Rows.Add(gv.FooterRow);
                }

                //Takes value of company name
                System.Web.UI.WebControls.Label labelCompany = new System.Web.UI.WebControls.Label();
                labelCompany.Text = companyName;
                labelCompany.Font.Bold = true;

                //Takes value of report title
                System.Web.UI.WebControls.Label labelReport = new System.Web.UI.WebControls.Label();
                labelReport.Text = reportTitle;
                labelReport.Font.Bold = true;

                //Takes value of report period
                System.Web.UI.WebControls.Label labelPeriod = new System.Web.UI.WebControls.Label();
                labelPeriod.Text = period;

                //  render the htmlwriter into the response
                htw.Write("<center>");
                labelCompany.RenderControl(htw);
                htw.Write("<br/>");
                labelReport.RenderControl(htw);
                htw.Write("<br/>");
                labelPeriod.RenderControl(htw);
                htw.Write("</center>");
                htw.Write("<br/>");
                tableReport.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

Any suggestions?

Upvotes: 0

Views: 5637

Answers (5)

Sandeep
Sandeep

Reputation: 288

I tried with the export to excel, seems that you first need to create an using part Excel = using Microsoft.Office.Interop.Excel, when u click on the button, just create an excel class having workbook and worksheet property and then using gridview property of Gridview.Row[].cell[].Text., u can dynamically store every value in the gridview to the worksheet and then write it to a FILE....

Upvotes: 0

Bhaskarreddy Mule
Bhaskarreddy Mule

Reputation: 81

  Response.Clear();
  Response.Buffer = true;
  Response.AddHeader("content-disposition",attachment;filename=GridViewExport.xls");
  Response.Charset = "";
  Response.ContentType = "application/vnd.ms-excel";

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

Upvotes: 0

Niranjan Singh
Niranjan Singh

Reputation: 18290

First of all Refer this: How to export nested gridview to excel/word with gridlines on the child grid, Hope it will help you to solve your problem.

Ref: How to export gridview to excel in a console type application?

You can refer to the Export To Excel control in the below link. It’s a custom control. Hope it can help you.

http://exporttoexcel.codeplex.com/

Styling exported grid:

 protected void btnExportExcel_Click(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";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            grdExport.AllowPaging = false;
            oMailing.GetData(out ODs);
            grdExport.DataSource = ODs;
            grdExport.DataBind();

            //Change the Header Row back to white color
            grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");

            //Apply style to Individual Cells
            grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");

            grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");

            grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");

            grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");

            grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");

            for (int i = 0; i < grdExport.Rows.Count; i++)
            {
                GridViewRow row = grdExport.Rows;

                //Change Color back to white
                row.BackColor = System.Drawing.Color.White;

                //Apply text style to each Row
                row.Attributes.Add("class", "textmode");

                //Apply style to Individual Cells of Alternating Row
                if (i % 2 != 0)
                {
                    row.Cells[0].Style.Add("background-color", "#C2D69B");
                    row.Cells[1].Style.Add("background-color", "#C2D69B");
                    row.Cells[2].Style.Add("background-color", "#C2D69B");
                    row.Cells[3].Style.Add("background-color", "#C2D69B");
                    row.Cells[4].Style.Add("background-color", "#C2D69B");
                }

            }
            grdExport.RenderControl(hw);
            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

Upvotes: 0

user1286221
user1286221

Reputation:

Ref: Export grid

Try this:

protected void btnExportExcel_Click(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";

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        grdExport.AllowPaging = false;

        oMailing.GetData(out ODs);

        grdExport.DataSource = ODs;

        grdExport.DataBind();

        //Change the Header Row back to white color

        grdExport.HeaderRow.Style.Add("background-color", "#FFFFFF");

        //Apply style to Individual Cells

        grdExport.HeaderRow.Cells[0].Style.Add("background-color", "green");

        grdExport.HeaderRow.Cells[1].Style.Add("background-color", "green");

        grdExport.HeaderRow.Cells[2].Style.Add("background-color", "green");

        grdExport.HeaderRow.Cells[3].Style.Add("background-color", "green");

        grdExport.HeaderRow.Cells[4].Style.Add("background-color", "green");

        grdExport.RenderControl(hw);

        //style to format numbers to string

        string style = @"<style> .textmode { mso-number-format:\@; } </style>";

        Response.Write(style);

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

        Response.Flush();

        Response.End();

    }

Upvotes: 1

EdSF
EdSF

Reputation: 12361

All of the answers will get you what you want - you're just missing the formatting part.

At the end of the day, what you are really creating is an HTML file (with an HTML Table) that Excel can read. The RESPONSE headers in @BhaskarreddyMule is what "forces" the client to treat the file as an "xls" file and if it has Excel run and open it (but the bottom line is that its not really a "native" Excel file.

Now that's out of the way, think in HTML. Style your columns, rows, and text content as you would in HTML. That's how you'd control the format (i.e. old school "nowrap" to prevent wrapping cell content, font-size, etc. etc.).

I haven't done this in a while (I've moved on to Excel XML and VB.Net XML literals when I need to do this) so I'm not sure how much level of control you have with RenderControl...or if you have to go even further "old school" and build the HTML table string from scratch.....

Upvotes: 0

Related Questions