AngularJS: Export Json object to Excel using AlaSQL


Hello,

I have spent significant time developing AngularJS and Angular 2 applications. But this requirement is quite new to me. Well, user wants to export to excel feature on one of our application page where we are using filtered data based upon some parameters. The design decision was on me whether to choose server side function to convert Json object in to CSV format and then export it to excel.

But I decided to choose another approach, not to involve server in exporting a Json data. I was not aware about ALASQL but later explored and found its very useful for me to implement this solution. Here I am explaining how to implement it.

What you need to import :

Just an intro about these two libraries:

AlaSQL is an open source SQL database for Javascript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It works in your browser, Node.js, and Cordova.

This library is designed for:

  • Fast in-memory SQL data processing for BI and ERP applications on fat clients
  • Easy ETL and options for persistency by data import / manipulation / export of several formats
  • All major browsers, Node.js, and mobile applications

Where js-xlsx is used to support for AngularJS export library. Both of these two has support to NG2 you can find more details about it here.

There are two ways to achieve the export:

  1. From Json object
    • alasql(‘SELECT * INTO XLS(“test.xls”,?) FROM ?’,[customStyle,$scope.items]);
  2. From html(like table)
    • alasql(‘SELECT * INTO XLSX(“myinquires.xlsx”,{headers:true})  FROM HTML(“#HtmltagID”,{headers:true})’);

It is very easy to implement. Please refer to my plnkr code to see the examples.

http://plnkr.co/edit/2UKA8yPl9Uiarr2uSwEd?p=preview

Hope this blog is beneficial for you.

 

Advertisements

AngularJS: Bind UI Select selection sequentially


Hello,

I felt lucky to work with angularJS with the integration with SharePoint(as backend) in my recent project. There was a requirement to have two drop-downs on a single page application where on selection of one; other drop-down value will be automatically filtered.  I am going to share with you guys that how I have implemented.  I understand that this may not be Ideal way of doing it but I am sure that it is quickest solution that I did in code to achieve this requirement.

HTML: The below html will generate two dropdowns and populate with country and states data.

<h3>Countries</h3>
<ui-select ng-model="ctrl.country.selected" name="country" theme="bootstrap" title="Select a Country" >
<ui-select-match placeholder="Select or search a country in the list...">{{$select.selected.name}}</ui-select-match>
<ui-select-choices repeat="country in ctrl.countries | filter: $select.search">
<span ng-bind-html="country.name | highlight: $select.search"></span>
<small ng-bind-html="country.code | highlight: $select.search"></small>
</ui-select-choices>
</ui-select>
<h3>States</h3>
<ui-select ng-model="ctrl.state.selected" name="state" theme="bootstrap" title="Select a state">
<ui-select-match placeholder="Select or search a state in the list...">{{$select.selected.name}}</ui-select-match>
<ui-select-choices repeat="s in ctrl.states | countryFilter: { concode: ctrl.country.selected.code }">
<span ng-bind-html="s.name | highlight: $select.search"></span>
<small ng-bind-html="s.concode | highlight: $select.search"></small>
</ui-select-choices>
<ui-select-no-choice>
<span ng-if="ctrl.country.selected.name">&nbsp; No state found for '{{ctrl.country.selected.name}}' country !</span>
<span ng-if="!ctrl.country.selected.name">&nbsp; Please select country !</span>
</ui-select-no-choice>
</ui-select>

Custom Filter:

If you have notice in abouve html code, I have created a custom search filter which search the states dropdown data based on the countyr selected using first dropdown. Here is the code for the filter:

app.filter("countryFilter", function () {
return function (items, props) {
var out = [];
if (angular.isArray(items)) {
var keys = Object.keys(props);
items.forEach(function (item) {
var itemMatches = false;
for (var i = 0; i < keys.length; i++) {
var prop = keys[i];
var text = props[prop].toLowerCase();
//if (item[prop].toString().toLowerCase().indexOf(text) !== -1) {
if (item[prop].toString().toLowerCase() === text) {
itemMatches = true;
break;
}
}
if (itemMatches) {
out.push(item);
}
});
} else {
// Let the output be the input untouched
out = items;
}
return out;
};
});

Demo: Plunker Demo

Output:

untitled

Hope this post will help you creating synchronous drop down using UI-Select.

Happy Coding !!

Not able to view result while building Search Query


Hello SharePointers ;),

Here is another quick finding which I just thought should be shared to save someone precious time. If you are dealing with SharePoint 2013 search and Result sources, you must try to test the query template before saving it. If you are not able to see any results instead just a red error saying below text don’t get scared I have quick solution for you.

“The Search display templates are not present on this site collection. To add them, you need to activate the “Search Server Web Parts and Templates” feature on the Site Collection Features page.

Display Error: The display template had an error. You can correct it by fixing the template or by changing the display template used in either the Web Part properties or Result Types.

Template ‘~sitecollection/_Catalogs/masterpage/DisplayTemplates/System/Control_QueryBuilderPreview.js’ not found or has sytax errors. (Load Template: ) ”

Search_Error

You must get frustrated because your search is properly configured, its crawling the data and you are able to see results while searching.  After looking at what was missing I found that in fact there was a feature missing on the Central Administration site collection. The feature is called Search WebParts.

But its not so quick that you just navigate to Site collection feature and activate it. As this feature is present at central admin site collection level so it requires extra privileges to activate it. To fix the problem, simply run the following command in an elevated SharePoint Management Shell:

Enable-SPFeature SearchWebParts -url http://<central admin url>

Bingo!! Just, re-open the “build your query” page, and everything should work properly!

Search_Error

Hope that post was able to help you. Please press like button it that saved your time.

Happy SharePointing 🙂

CSS Trick: Create tag icon with dynamic text width using Pure CSS


Hi Guys,

If you are searching for a tag icon which can be used to show few keywords on any page. Then this is the place where you can find a very simple trick to generate it with in few minutes. The icon created using this CSS trick has feature that it can take dynamic width based upon the text width. Below image is the example how it will rendered:

 

Tag Icon

 

Click here for the Demo.

Basics:

Pure CSS post tags uses at least 2 CSS tricks such as CSS triangles and CSS circles. For CSS triangles you need to manipulate borders of an element that has zero height and width. CSS circle is simpler. All you need is a square element with rounded corners set to at least half the size of the element. The border radius will then merge into a circle.

scheme

HTML

I usually markup tags with unordered list. So the markup is fairly simple:

<ul class="tags">
   <li>CSS</li>
   <li>HTML</li>
   <li>Mohit</li>
   <li>Vashishtha</li>
   <li>SharePoint</li>
 </ul>

In the CSS part :before and :after pseudo elements will do the rest of the trick and style them to achieve the styling look like a tag.

CSS

I am placing the tags list at the bottom of the post element with adjusting ULs absolute position

.tags{
	margin:0;
	padding:0;
	position:absolute;
	right:24px;
	bottom:-12px;
	list-style:none;
}

Height (and line-height) of the list item LI. Also few margins and padding and some rounded corners on the right hand side.

.tags li{
	float: left;
        height: 24px;
        line-height: 24px;
        position: relative;
        font-size: 11px;
        margin: 2px 5px 2px 12px;
        padding: 0 10px 0 12px;
        background: #0078d7;
        color: #fff;
        text-decoration: none;
        -moz-border-radius-bottomright: 4px;
        -webkit-border-bottom-right-radius: 4px;
        border-bottom-right-radius: 4px;
        -moz-border-radius-topright: 4px;
        -webkit-border-top-right-radius: 4px;
        border-top-right-radius: 4px;
}

To achieve the pointed edge we are adding a :before pseudo-element. The element has the width and height set to zero, that way we are only using it’s borders. To “draw” an arrow pointing left we are showing only the right border.

.tags li:before {
 content: "";
 float: left;
 position: absolute;
 top: 0;
 left: -12px;
 width: 0;
 height: 0;
 border-color: transparent #0078d7 transparent transparent;
 border-style: solid;
 border-width: 12px 12px 12px 0;
}

The last element to add is the :after pseudo-element. This will act as that rounded hole. What we’re doing here is creating an empty square, and we’re rounding it’s edges so we create a circle (and of course we position it with position: absolute).

.tags li:after {
 content: "";
 position: absolute;
 top: 10px;
 left: 0;
 float: left;
 width: 4px;
 height: 4px;
 -moz-border-radius: 2px;
 -webkit-border-radius: 2px;
 border-radius: 2px;
 background: #fff;
 -moz-box-shadow: -1px -1px 2px #004977;
 -webkit-box-shadow: -1px -1px 2px #004977;
 box-shadow: -1px -1px 2px #004977;
}

That was a basic html and CSS code but if someone want to make this tag as clickable then It can easily be achieved by adding anchor tag inside the LI element. Also need to add some classes to handle the hover of achor tag.

I hope this blog is helpful for you.

Happy Designing 🙂 !!

SharePoint : Custom Task Email with Outlook ribbon control “Open this Task”


Hi Guys,

It was really interesting to work with my latest client. They had an very interesting and innovated idea to automate their manual file approval process.  The requirement was to create a multistage state machine workflow where users have privilege to approve and reject documents with some comments. It was very interesting to work with end client(as they them self don’t know what they actually want).

I will not go deep into the workflow but surly will come back with another post explaining it. This post is more about a requirement which I faced during the development of the workflow. Just wanted to share the design of the Workflow:

WF

Requirement:  As a user, I should have receive an email from workflow with custom subject having document name embedded to the subject where I can directly do some action like approve/reject or comment on the document. I should see a button in email “Open this task” which will redirect me to the page where I can take actions.

1

It looks bit easy but have hidden challenges in it. Most us are aware of the fact that when a task item is created, either directly in the task list or through a Workflow an alert email would be sent saying a task has been assigned to you (provided alerts are enabled). When we look at that email in Outlook we see an additional control in the ribbon “Open this Task” under the group “Open” as shown in above figure. Let me rephrase the requirements:

  1. We have to customize the body and subject as per their request
  2. Ribbon button “Open this Task” should present when the email is sent

The above mentioned two points are two sides of the coin, which means if you are able to see one then second will not be visible to you. So the final solution’s must have some trick to achieve both cases.

Solution: 

One point is clear that we can not go along with the default workflow email feature, we have to use “SmtpClient” or “SPUtility” to create email with custom body and subjects. Now question is how one can get ribbon button using SPUtility send email function.

  • I have used “SPWorkflowTaskProperties” class in the Task Created Event and sent it two properties “SPWorkflowTaskProperties.HasCustomEmailBody = true” and “SPWorkflowTaskProperties.EmailBody= <My Custom HTML Email Body> ” With this I can satisfy requirement 1 & 2 but not 3 (listed above).  Also if I were to create a task and if I want to send an email using Event Handler on item created I may not be able to use this. So for the generic purposes this would not fit in.
  • Next I thought of modifying the alert template but then it would have an impact on all task lists which is not a recommended option.
  • So, I have decided that I have to disable the alerts for that Task list and use “SmtpClient or SPUtility” class instead to send email which can be used at different requirements such as “event handlers/workflows or any other for that matter. With this class we do have control over all the aspects “From, To, Subject, Email Body, etc”. The only problem with this to achieve the requirement # 2 (Ribbon control in outlook). I always wondered how would outlook recognize that an email is for Task, how is this ribbon control activated as soon as it sees an email.

I believed that the alert email sent has some headers which mark this as Task which is understood by outlook and displays the controls accordingly. So now the question is what are those headers. After some research I figured out the Mail message headers. Below is the code for sending an email which also includes those headers and satisfies all the above mentioned requirements (1 & 2)

/// <summary>
/// Method used to send the task created email instead of default SP WF email notification
/// </summary>
/// <param name=”web”></param>
/// <param name=”htmlBody”></param>
/// <param name=”ToEmailId”></param>
/// <param name=”listItem”></param>
/// <param name=”emailSubject”></param>
/// <returns></returns>
public bool SendTaskMail(SPWeb web, string htmlBody, string toEmailId, SPList taskList, string itemId, string emailSubject)
{
try
{
string domain = web.Site.WebApplication.OutboundMailSenderAddress.Remove(0, web.Site.WebApplication.OutboundMailSenderAddress.LastIndexOf(‘@’));
var messageHeaders = new StringDictionary();
messageHeaders.Add(“to”, toEmailId);//author.User.Email);
messageHeaders.Add(“subject”, emailSubject);
messageHeaders.Add(“content-type”, “text/html”);

messageHeaders.Add(“Message-Id”, “<3BD50098E401463AA228377848493927” + Guid.NewGuid().ToString(“D”) + domain + “>”);
messageHeaders.Add(“X-Sharing-Title”, ConvertToBase64String(“Body”));
messageHeaders.Add(“X-AlertTitle”, ConvertToBase64String(“System”));
messageHeaders.Add(“Content-Class”, “MSWorkflowTask”);
messageHeaders.Add(“X-AlertWebUrl”, ConvertToBase64String(web.Url));
messageHeaders.Add(“X-AlertServerType”, “STS”);
messageHeaders.Add(“X-AlertWebSoap”, ConvertToBase64String(web.Url + “/_vti_bin/alerts.asmx”));
messageHeaders.Add(“X-Sharing-Config-Url”, “stssync://sts/?ver=1.1&type=tasks&cmd=add-folder&base-url=” + Uri.EscapeDataString(web.Url) + “&list-url=” + Uri.EscapeDataString(taskList.RootFolder.ServerRelativeUrl) + “&guid=” + Uri.EscapeDataString(taskList.ID.ToString(“D”)));
messageHeaders.Add(“X-Sharing-Remote-Uid”, taskList.ID.ToString(“D”));
messageHeaders.Add(“X-Sharing-WssBaseUrl”, ConvertToBase64String(web.Url));
messageHeaders.Add(“X-Sharing-ItemId”, ConvertToBase64String(itemId));

SPUtility.SendEmail(web, messageHeaders, htmlBody);

return true;
}
catch (Exception ex)
{
Utilities.UpdateLogErr(ex, “Error while sending email. Please find details in audit logs”);
return false;
}
}

Hope this helps!

Thank you for your time.

Happy SharePointing 🙂 !!

SharePoint PowerShell: Upload multiple documents to sharepoint document library


Hi,

I was creating a script to replicate document library structure similar to Prod just in order to test the ShareGate Migration. Let me take a brief description about my scenario:

Description:

There is a document library present on Prod, which contains bulk document (~3000). Now I need to update metadata for all those documents using Excel sheet and ShareGate. I don’t have access to the Production library and not even replicate the Library to my Dev environment as it contains some very confidential documents.

Solution:

I have decided to create a script which will take one template document from file system and read name of document from one of columns of excel. The script will take some time(~hours in case of thousand documents) to upload the template with the new file name. The inputs to the script will be

  1. Path of the excel which contains name of documents in one column
  2. Path of the file where the template is placed on file system
  3. Url of the sharepoint web site
  4. Name of the document library

2

Code: 

# Add SharePoint Snapin to PowerShell            
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null)             
{            
 Add-PSSnapin Microsoft.SharePoint.PowerShell            
}
$exlFile = "D:\Work\Book2 - Copy.xlsx"
$fileExt = ".pdf"
#$filePath =  "D:\Work\IAEA.docx"  
$filePath = "D:\Work\pdf.pdf"
$webUrl = "http://server/sites/site"            
$docLibName = "Library Name"      

try {    
    $xl = New-Object -COM "Excel.Application"
    #$xl.Visible = $true
    $wb = $xl.Workbooks.Open($exlFile)
    #$ws = $wb.Sheets.Item('Log')
    $ws = $wb.Sheets.Item('Sheet1')
    $lines = $ws.UsedRange.Rows.Count

    $spWeb = Get-SPWeb -Identity $webUrl             
    $spWeb.AllowUnsafeUpdates = $true;            
    $lib = $spWeb.Lists[$docLibName]            
    $folder = $lib.RootFolder            
    #$sourceFileName = $filePath.Substring($filePath.LastIndexOf("\")+1)             
    $file= Get-ChildItem $filePath  
    
    $docLibraryUrl = $lib.RootFolder.ServerRelativeUrl
    $uploadfolder = $spWeb.getfolder($docLibraryUrl + $folderPathWithinDocLibrary)
            
    $fileStream = ([System.IO.FileInfo] (Get-Item $file.FullName)).OpenRead()     

    for ($line = 2; $line -le $lines; $line ++) {       
        $fileName = $ws.Columns.Item(1).Rows.Item($line).Text
        
        if($fileName -ne "")
        {
            $fileName
            #Check if File exist
            $item = $uploadfolder.Files | where{$_.Name -eq $fileName}
            if(!$item)
            {                
                #Add file            
                write-host -NoNewLine -f yellow "Addin file " $fileName " to " $folder.ServerRelativeUrl "..."                    
                $newFilePath = $docLibraryUrl + $folderPathWithinDocLibrary + "/" + $fileName
                write-host "Creating document: $newFilePath ..."
                $spFile = $uploadfolder.Files.Add($newFilePath, [System.IO.Stream]$fileStream, $true)  
                
                write-host -f Green "...Success!" 
            }
        }
    }
     #Close file stream            
    $fileStream.Close()
    $spWeb.AllowUnsafeUpdates = $false;
    $spWeb.Dispose();
    $xl.Quit()
}
finally { 
    $wb.Close()
    $xl.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
}

Output:

1

Hope that code help you finding solution.

Happy SharePointing 🙂

Mohit Vashishtha

Upload a file in SharePoint using REST and c# console application


Hi,

In my last post, I have explained how to create a sub site using REST from and Console application. In this blog  will explain how we can upload a file, which is located in file system, will be uploaded to sharepoint Document library. So if I brief about requirement here:

  • Need to upload a file to already created SharePoint site.
  • Use REST(not even CSOM) will be used in order to create.
  • API call will be send from some other machine(not SharePoint machine) which is in same domain and can access the SharePoint site.

Follow the given steps to achieve the same:

1) Create a simple console application with c# selected language.

1

2) Next is to install the Newtonsoft.Json. Navigate to Visual studio menu: “View” > “Other Windows” > “Package Manager Console” and Then type the following: “Install-Package Newtonsoft.Json”

2

3) Now install RestSharp using the package manager. Type the following: “Install-Package RestSharp”

3

4) Now open the Program.cs file of console application and paste the code below:

string siteurl = “http://sharepoitsite/sites/subsite&#8221;; //site on which file needs to be uploaded (don’t put / at end)
string documentlibrary = “Documents”; //Document library where file needs to be uploaded

string filePath = @”C:\Users\mohit.vashishtha\Desktop\test.html”;

byte[] binary = System.IO.File.ReadAllBytes(filePath);
string fname = System.IO.Path.GetFileName(filePath);
string result = string.Empty;
//Url to upload file
string resourceUrl = string.Format(“{0}/_api”,siteurl);

RestClient RC = new RestClient(resourceUrl);
NetworkCredential NCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
RC.Authenticator = new NtlmAuthenticator(NCredential);

Console.WriteLine(“Creating Rest Request”);
RestRequest Request = new RestRequest(“contextinfo?$select=FormDigestValue”, Method.POST);
Request.AddHeader(“Accept”, “application/json;odata=verbose”);
Request.AddHeader(“Body”, “”);

string ReturnedStr = RC.Execute(Request).Content;
int StartPos = ReturnedStr.IndexOf(“FormDigestValue”) + 18;
int length = ReturnedStr.IndexOf(@”””,”, StartPos) – StartPos;
string FormDigestValue = ReturnedStr.Substring(StartPos, length);

Console.WriteLine(“Uploading file Site……”);

resourceUrl = string.Format(“/web/GetFolderByServerRelativeUrl(‘{0}’)/Files/add(url='{1}’,overwrite=true)”, documentlibrary ,fname);
Request = new RestRequest(resourceUrl, Method.POST);
Request.RequestFormat = DataFormat.Json;
Request.AddHeader(“Accept”, “application/json;odata=verbose”);
Request.AddHeader(“X-RequestDigest”, FormDigestValue);
Console.WriteLine(“File is successfully uploaded to sharepoint site.”);
Console.ReadLine();

5) Now application is ready to run. Make sure that all the namespace issues are resolved.

6) It will take some time to create site but output window will display the details it got from the Request.

In case of already existing site:

54

Hope this information is helpful to you. Thanks for time.

Happy SharePointing 🙂