Share Point List View to Excel using C#

Step 1: Create a visual webpart
Step 2: Add a button to the webpart
Step 3: Write the below code in button click event
 
Note: Only you have to pass list name and view name, That’s it
 
 
protected void btnExport_Click(object sender, EventArgs e)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using(SPSite site=new SPSite(SPContext.Current.Web.Url))
                {
                    using(SPWeb web=site.OpenWeb())
                    {
                        web.AllowUnsafeUpdates = true;
                        string values = string.Empty;
                        SPList list = web.Lists["List Name"];
                        SPView view = list.Views["List View Name"];
                        SPListItemCollection items = list.GetItems(view);
                        DataTable dt = new DataTable();
                        StringCollection viewFields = view.ViewFields.ToStringCollection();
                        foreach (string cols in viewFields)
                        {
                            dt.Columns.Add(list.Fields.GetFieldByInternalName(cols).Title.ToString());
                        }
                        foreach (SPListItem item in items)
                        {
                            DataRow dr = dt.NewRow();
                            foreach (string fieldName in viewFields)
                            {
                                values += item[fieldName] + ",";
                            }
                            values = values.TrimEnd(values[values.Length - 1]);
                            string[] exceptionValues = values.Split(',');
                            dt.Rows.Add(exceptionValues);
                            values = string.Empty;
                        }
                        this.ExporttoExcel(dt);
                        web.AllowUnsafeUpdates = false;
                    }
                }
            });
        }
 
 
// Add the below Custom method to export the data table values to excel
 
private void ExporttoExcel(DataTable dt)
        {
            try
            {
                string str = "ExceptionRequestReport "+System.DateTime.Now+".xls";
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("Content-Disposition", "attachment; filename=" + str);
                Response.ContentType = "application/vnd.ms-excel";
                EnableViewState = false;
                StringWriter writer = new StringWriter();
                HtmlTextWriter writer2 = new HtmlTextWriter(writer);
                DataGrid grid = new DataGrid { DataSource = dt };
                grid.DataBind();
                grid.HeaderStyle.Font.Bold = true; ;
                grid.RenderControl(writer2);
                Response.Write("<style> TABLE { border:groove 1px #999; } " +
                                    "TD { border:groove 1px #D5D5D5; text-align:center } </style>");
                base.Response.Write(writer.ToString());
                base.Response.End();
            }
            catch (Exception)
            {
               
            }
        }

Comments

Post a Comment

Popular posts from this blog

SharePoint 2013 Keyword Query (KQL) Content Class Property Restrictions

Using property restrictions in your KQL queries, you can restrict your search to only pull back certain things like calendar events for instance. This is a very powerful way to limit search results  and get exactly what you are looking for. You can use these in your query like this: " lunch contentclass:STS_ListItem_Events " This will return only calendar events with the word "lunch" in them.  Pretty powerful and pretty simple. Here is a list of available content class items: STS_Site –  Site Collection STS_Web  –  Site (Web) STS_List_850  –  Page Library STS_ListItem_850  –  Page STS_List_DocumentLibrary  –  Document Library STS_ListItem_DocumentLibrary  –  Document Library Items STS_List  –  Custom List STS_ListItem  –  Custom List Item STS_List_Links  –  Links List STS_ListItem_Links  –  Links List Item STS_List_Tasks  –  Tasks List ...
Read more

Filtering the Sharepoint List Taxonomy Column using Rest API

I ran into a strange issue today when using the SharePoint 2013 REST API for Lists with Managed Metadata columns    but I just had a ‘clever workaround’ moment with SharePoint’s oData/REST implementation when it comes to filtering list items based on taxonomy (managed metadata) columns. Now I do not consider myself a developer, so this article is probably a little verbose for some readers, but should be helpful to power users or IT pros. 1. To filter the Taxonomy columns in SharePoint list using Rest API call we need to use the Post method. 2. The Rest API call will give errors some times when the Rest API Query string crosses limit 260 characters that time we will use the Query in the body. Those things are clearly explained in the below example clearly. 3. WE can use the CAMEL Query in the Rest API call this example come under that. < script src = "https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js" ></ script > < scri...
Read more
Clearing the InfoPath Cache When a new version of the form is detected, such as when a new version of the form is installed in a SharePoint Forms library, the cached form will be deleted and the new one installed in it's place. You can also manually clear the Infopath cache by deleting the files. Or you can do it via command line by:             Start > run > Infopath /cache clearall
Read more