Export data from SharePoint Silverlight application to Excel using REST API

This demo will show how to export data from SharePoint Silverlight application to Excel template or excel sheet.
Steps:
1.       Create REST API application using MVC4 architecture (URL based navigation).
2.       Integrate URL directly in Silverlight hyperlink control in XAML.
Step:
  1. Create one MVC4 application using visual studio 2010 frame work.
  2. Create one ExportController.cs class file under  Controller  folder in mvc4 project. This class will work as a API application between SharePoint Silverlight and REST API.
  3. Create one more ExportRepository.cs file under DataLayer folder.
  4. Add one more folder to the solution name as GenerateExcel folder add class file in this folder. File name is ExportToExcel.cs
ExportController.cs
[HttpGet]
        [ActionName("SharedDocuments")]
        public HttpResponseMessage SharedDocuments([FromUri]string SiteName, [FromUri]int Id)
        {
            try
            {
                var repository = new SASRepository();
                ListItemCollection sharedDcoumentsCollection= repository. GetSharedDcouments (SiteName, Id);
                repository.Dispose();
                if (sharedDcoumentsCollection.Count==0)
                {
                    return Request.CreateResponse(HttpStatusCode.NoContent);
                }
                else
                {
                    string filePath = System.Web.HttpContext.Current.Server.MapPath(string.Format("~/Data/{0}_SharedDocuments_{1}.xlsx", ProjectName, DateTime.Now.Ticks));
                    ExportToExcel exportToExcel = new ExportToExcel(sharedDcoumentInfo, System.Web.HttpContext.Current, filePath);
                    exportToExcel.Create();
                    exportToExcel.GenerateSharedDocuments();

                    string zipFilePath = System.Web.HttpContext.Current.Server.MapPath("~/Data/" + Path.GetFileNameWithoutExtension(filePath) + ".zip");
                    using (ZipFile zip = new ZipFile())
                    {
                        zip.AddFile(filePath, ".");
                        zip.Save(zipFilePath);
                    }

                    FileStream stream = new FileStream(filePath, FileMode.Open);
                    HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
                    response.Content = new StreamContent(stream);
                    response.Headers.CacheControl = new CacheControlHeaderValue();
                    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = Path.GetFileName(filePath) };
                    response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");

                    return response;
                }
            }
            catch (HttpResponseException)
            {
                throw;
            }
            catch (Exception ex)
            {
                var resp = new HttpResponseMessage(HttpStatusCode.InternalServerError)
                {
                    Content = new StringContent(ex.Message)
                };
                throw new HttpResponseException(resp);
            }
        }

ExportRepository.cs
internal ListItemCollection GetSharedDcouments(string SiteName, int StepID)
        {
            string Url = "http://teams.honeywell.com/sites/" + SiteName;
            ClientContext clientContext = new ClientContext(Url);
            Web site = clientContext.Web;
            List list = site.Lists.GetByTitle("Shared Documents");
            CamlQuery WPC10TasksQuery = new CamlQuery();
            WPC10TasksQuery.ViewXml = @"<view>"
                                            +"<Query>"
                                            + "<Where>"
                                                + "<Eq>"
                                                    + "<FieldRef Name='StepID' />"
                                                    + "<Value Type='Number'>"
                                                    + ItemID
                                                    + "</Value>"
                                                + "</Eq>"
                                            + "</Where>"
                                            + "</Query>"
                                     + "</View>";
            ListItemCollection sharedDocumentsCollection = list.GetItems(sharedDocumentsCollection);
           
            clientContext.Load(sharedDocumentsCollection,
                 items => items.Include(item => item["TaskType"],
                    item => item["TaskName"],
                     item => item["CALCStatus"],
                    item => item["REMARKS"]));
           
            clientContext.ExecuteQuery();
          
            return sharedDocumentsCollection;
        }


ExportToExcel.cs
using System;
using System.IO;
using System.Web;
using ClosedXML.Excel;
using Microsoft.SharePoint.Client;

namespace SharedDocuments.RestAPI.Report
{
    public class ExportToExcel
    {
        public int _irow;
        public int irow
        {
            get { return _irow; }
            set { _irow = value; }
        }
        private ListItemCollection sharedDcoumentInfo;
        private string filePath;
        private string tamplatePath;

        public ExportToExcel(ListItemCollection sharedDcoumentInfo, HttpContext httpContext, string filePath)
        {
            this. sharedDcoumentInfo = sharedDcoumentInfo;
            tamplatePath = httpContext.Server.MapPath("~/Assets/SharedDocuments_Template.xlsx");
            this.filePath = filePath;
        }
        public void Create()
        {
            FileInfo templateFile = new FileInfo(tamplatePath);
            templateFile.CopyTo(filePath);
        }
        public void GenerateSharedDocuments()
        {
            try
            {
                using (XLWorkbook workbook = new XLWorkbook(filePath, XLEventTracking.Disabled))
                {
                    var ws = workbook.Worksheet("SharedDocumentsSheet");
                    irow = 1;
                        
ListItemCollection DcoumentInfo = sharedDcoumentInfo;
                        foreach (ListItem item in DcoumentInfo)
                        {
                            if (item["TaskType"].ToString() == QualityHeading)
                            {

                                object temp = item["TaskName"];
                                ws.Cell(irow, 1).Value = null != temp ? temp.ToString() :                    string.Empty;

                                temp = item["CALCStatus"];
                                ws.Cell(irow, 2).Value = null != temp ? temp.ToString() : string.Empty;

                                temp = item["REMARKS"];
                                ws.Cell(irow, 3).Value = null != temp ? temp.ToString() : string.Empty;

                                irow++;
                            }
                        }

                                           
                    workbook.Save();
                }
                GC.Collect();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }
        }
    }
}

html controls in silver-light application:

 <Hyperlink Name="hlExportToExcel" NavigateUrl="http://localhost:8080/testsite/SharedDocuments?SiteName=TestSite&Id=2">
</Hyperlink>

 Note: hear in the "NavigateUrl" before "?" SharedDocuments is the REST API method name after "?" SiteName & Id these are the input to the REST API service method.

Comments

Post a Comment

Popular posts from this blog

Sending email using Powershell script

Image
 Below is a script that can be used to send email using Powershell. If ((Get-PSSnapIn -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )   { Add-PSSnapIn -Name Microsoft.SharePoint.PowerShell } $currentDate = Get-Date -Format g $HeaderOne $HeaderTwo $HeaderThree $HeaderFour $Outputreport = "<HTML><TITLE> Test E-Mail notification </TITLE> <head> <style>body {font-family: Calibri;font-size: 14px;}table {border-collapse: collapse;}table, th, td {border: 1px solid black;font-family: Calibri,Verdana;font-size: 14px;}th {background-color: #4CAF50;color:white;}</style></head>                      <BODY>                                           <H2> Test E-Mail notification on $($currentDate) CET </H2>  ...
Read more

Convert List Collection to DataTable using C#

public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)         {             PropertyDescriptorCollection properties =                TypeDescriptor.GetProperties(typeof(T));             System.Data.DataTable table = new System.Data.DataTable();             foreach (PropertyDescriptor prop in properties)                 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);             foreach (T item in data)             {                 DataRow row = table.NewRow();                 foreach (PropertyDescriptor prop in properties)                ...
Read more

Difference Between Site Template and Site Definition

Difference Between Site Template and Site Definition Before going into direct comparison, let me define both: Site Templates  : Are  snapshots of  sites at a point in time. When a user customizes a site from the UI or SPD, the custom template consists of the difference between the original state of the site (determined by its definition) and the state of the site or list when the custom template is generated. Custom templates remain tied to a particular site definition (for example, the site definition for SharePoint Web sites or Meeting Workspace Web sites), so that if the site definition is not present or is changed, the custom template cannot work. Site Definitions  : As the name implies, A Site definition is "core definition of a site”. Each site definition emerges through a combination of files that are placed in the 12 hive of WFE during installation of SharePoint. Site definition files include .xml, .aspx, .ascx, and .master page files, as well as docum...
Read more