Click here to Skip to main content
15,887,485 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to export grid view to excel using office Openxml. Please suggest how I can Freeze header and columns in excel sheet. Tried below code.Please suggest if any other way to do this.

What I have tried:

C#
public static void ExportMehod(GridView gvde, string excelName, string Sheetname)
        {

            try
            {
                string MethodName = "ExportToExcel_Open_XML(GridView gvde,string excelName, string excelName)";
                using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
                {
                    HttpResponse Response = System.Web.HttpContext.Current.Response;
                    OfficeOpenXml.ExcelWorksheet ws;
                    ws = pck.Workbook.Worksheets.Add(Sheetname);
                    ws.View.FreezePanes(4, 3);
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=Delivery_excellence_Dashboard.xls");
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.ms-excel";
                    gvde.HeaderStyle.BackColor = Color.Black;
                    gvde.HeaderStyle.ForeColor = Color.White;
                    using (StringWriter sw = new StringWriter())
                    {
                        HtmlTextWriter hw = new HtmlTextWriter(sw);
                        //To Export all pages
                        gvde.AllowPaging = false;
                        gvde.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();
                    }
                }
            }
            catch (Exception ex)
            {
                Errormsg = ex.ToString();
            }
        }
Posted
Updated 5-May-16 20:53pm
v3

1 solution

Simply google: "openxml excel freeze top row"

OpenXml,Excel,C#[^]

c# - Freeze Panes in OpenXml SDK 2.0 for Excel document - Stack Overflow[^]


[^]

First three links give detailed solution for which you didn't have to wait for 18 hours for someone to spend 10 seconds looking for this to help you. Next time, google first, ask later.

Short version: add pane.State = PaneStateValues.Frozen
 
Share this answer
 
Comments
Member 10678104 7-May-16 1:38am    
Thanks for reply but already tried this one.

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