How to Read/Import Excel Sheet in SharePoint List using SPServices and JQuery.

Recently I passed through an interesting requirement, had to import Excel sheet in SharePoint List.

Let’s Check how does it works.

For any demonstration I use my favorite web part Script Editor with lovely SPServices and JQuery. 

1. First Add a Custom List named it ExcelImport.

2. Create five Columns to the ExcelImport List.

First Name (Rename of Title Column), LastName, Position, Location, Country.

3. Now Next step is to add Script Editor Web Part to page.

4. Add Reference of SPServices and JQuery in Script Editor.

  (For frequent use I would suggest you to add reference in your master page)

5. Paste the below code in Script Editor.

<script src="Your JQuery Reference URL"></script>
<script src="Your SPServices Reference URL"></script>
<script>
var excel;
function GetData(cell,row){
if (window.ActiveXObject) {
try {
excel = new ActiveXObject("Excel.Application");
}
catch (e) {
alert (e.message);
}
}
else {
alert ("Opss... inconvenience caused is deeply regretted. Kindly Upload from IE");// Only work in IE Browser
return;
}

var excel_file = excel.Workbooks.Open("D:\MyExcelSheetName.xlsx");//Read excel from D drive by MyExcelSheetName Name
var sht = excel.Worksheets("Sheet1"); // Reading Sheet1 from Worksheets

for(var i=5; i<i+1; i++){ // will start reading data from 5th row
var FirstName = sht.Cells(i,3).Value; // Start reading First Name from 3rd cell
var LastName = sht.Cells(i,5).Value; // Start reading Last Name from 5th cell
var Position = sht.Cells(i,7).Value; // Start reading Position from 7th cell
var Location = sht.Cells(i,9).value; // Start reading Location from 9th cell
var Country = sht.Cells(i,11).Value; //Start reading Country from 11th cell

if (FirstName == undefined || LastName == undefined || Position == undefined || Location == undefined || Country == undefined){ // will stop reading data when excel data end.
break;
}
$().SPServices({
operation: "UpdateListItems",
webURL: _spPageContextInfo.webAbsoluteUrl,
async: false,
batchCmd: "New",
listName: "ExcelImport",
valuepairs: [["Title",FirstName],["LastName",LastName],["Position",Position],["Location",Location],["Country",Country]],
completefunc: function(xData,status){
}
});
}
alert("Data Imported");
excel_file.Close();

}

$(document).ready(function(){
$("#btnload").click(function(){
GetData(1,1);
});
});
</script>
<input type="button" style="float: left;" id="btnload" value="Import Excel"/>

6.Create your Excel file and save it to your D drive by  MyExcelSheetName.xlsx name.

Make sure your Excel Sheet must be in below format if you are using exactly above code.

7. Click on Import Excel button from your page. And your List will be updated with desired result.

Note : If you are getting Error like “Automation server can't create object”

Enable Allow ActiveX filtering.

8. Verify your List.

Cheers ...

Thanks.

by Amir Reza via Everyone's Blog Posts - SharePoint Community

Download your template file (*.stp) and expand it.

First, download the site template from the solutions gallery to a file, I usually create a directory called solutions for this procedure:

1. Navigate to the top-level site of your site collection.

2. Click Settings, and then click Site Settings.

3. In the Web Designer Galleries section, click Solutions.

4. To download the solution, click its name in the solutions gallery, and click Save. Then, in the Save As dialog box, browse to the location where you want to save the solution, click Save, and then click Close.

Now, you will rename the file from the command prompt to cabinet file or *.cab file.

Extract the files

I am using 7zip to extract the files from the cabinet file.  I will then place the files in the solutions directory in a directory with the name of the file (in this case it was TemplateX2). 

Note:

All files that are actually just cabinet files (*.cab)

• .dwp = SharePoint dashboard or web part file
• .stp =  Sharepoint list or library template
• .wsp = Windows SharePoint solution file

 

by Tony Di Leonardo via Everyone's Blog Posts - SharePoint Community

Using Content Types to Hide Edit Form Fields in a List

I was recently asked if I could make some list fields read-only because a department was having problems with users filling out fields they weren’t supposed to. They attempted to control this by creating a column called “END OF CUSTOMER INPUT SECTION. INTERNAL USE ONLY BEYOND THIS POINT.” One of the first rules I’ve learned when helping users leverage the power of SharePoint is “What the customer WANTS is not necessarily what the customer NEEDS.” Although the customer was requesting that Permissions be used to control who can edit what fields, I knew that wasn’t the solution, not the least of which because it isn’t even possible to set permissions at the column level. So how did I approach this?

First I looked at the list settings. 112 columns. Yikes! This many columns would be a nightmare to fill out. Imagine creating a new list item and being presented with 112 fields. No wonder users ignored warnings and continued to fill out the form. After filling out a mind-numbing 75 fields what’s another 35 fields?!

Next I asked how this list was used and who were the users who interacted with the list (the audience). The process was explained this way: a user requests a new project by filling out a new list item. The list manager is notified via an Alert that a new project was requested. The list manager assigns the project to a Project Liaison, who works with a finance person and a design person who will input additional information. With 4 user groups identified, we then placed them into four functional areas:

1. Customer

2. Finance and Approvals

3. ADT Design Status

4. Project Status

What Fields do the Users Need to See?

Now that we have identified 4 user groups, the next question is “What fields do each group need to see?” I asked for a list of column names, in the correct order, for each user group. With that in hand I determined that the best approach to meet the customer’s NEED (only display certain columns to a certain audience) was through the use of Content Types.

Creating Content Types

Here is how to create a Content Type:

1. Site Settings -> Site content types

2. Click Create

3. Give it the name of one of your user groups

4. Select “List Content Type” and “Item” under Parent Content Type (because we are creating a list Content Type).

5. When you create your first Content Type, create a new group with a period in front of it (.Demand in the example above). This way the new Content Type will sort to the top of the Site Content Types page. (Thanks to Susan Hanley for this tip!)

6. Click OK

Do this for each of your user groups.

Configuring Our List to Use Content Types

1. List Settings -> Advanced settings

2. Select “Yes” under “Allow management of content types?”

3. Click OK

Adding Content Types

From the List Settings page, click “Add from existing site content types”

Select .Demand from the dropdown to see the Content Types you just created. Add them one by one. Click OK.

Assign List Columns to Content Types

1. Click on a Content Type

2. Click “Add from existing site or list columns”

3. Select columns from: List Columns

4. Add the appropriate columns then click OK

5. Repeat for each Content Type

You can click “Column order” if you need to change the order of a column.

Because the Customer user group will be creating a new list item, make “Customer” the default content type by clicking on “Change new button order and default content type” and changing the Position from Top to 1:

Wrapping Things Up

With the Content Types added, this is the new user experience:

1. A Customer creates a new Item. They are presented with only the fields they need to fill out (37 fields instead of 112)

2. The Item is saved as a Customer content type. When a member of another user group edits the item it will open up in the Customer content type (or whatever the content type happened to be when the item was previously edited and saved). Users are trained to select the appropriate Content Type from the dropdown list in order to see their pertinent fields:

The “Item” content type is still available for the list manager who needs access to every field.

To improve the user experience we can add instructions to the default display form:

1. From the List tab click the Form Web Parts dropdown and select Default Display Form

2. Add a Content Editor web part and add the appropriate instructions. This is what mine looks like:

Now the user experience is much friendlier with only the necessary fields displayed!

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community

SharePoint App using Type Script and Angular JS

SharePoint App using Type Script and Angular JS

What is Typescript 

• It adds Static Typing and structuring (class, modules, etc..) to JavaScript.
• Type Annotations
• Static type checking
• Type Definitions
• Compile-time checking
• Open Source
• Supported by angular2 

Strongly typed

Typescript defines the type of the member variable and function parameters. The type will be removed while translation to JavaScript. It enables the compiler to catch type error at compile time and provides IDE intellisense.

Javascript	Typescript
function test(a ,b ){                 return a*b;       }  alert(test('one','two'))	function test(a:number,b:number):number{                 return a*b; } alert(test(10,20));

Compiler

The code we write in TypeScript is compiled into JavaScript and map file. Map file used to map the JavaScript and TypeScript file lines to debug TypeScript.

Compile:  tsc test.ts à test.js

While compiling the TypeScript code into JavaScript, type annotations are removed.      

var a:number = 3;                            var a = 3;

var b:string = 'abc';                         var b = 'abc';

DefinitelyTyped

The  DefinitelyTyped adds definitions for the existing many JavaScript libraries in the form of interfaces.

• Describes the types defined in the external libraries (d.ts)
• Not Deployed only used for development purpose
• It is used to check the types 

  Alternatives

• Dart
• CoffeeScript
• ClojureScript
• Fay

Module  & Export keyword

 Modules are same as namespace in C#, to avoid the name collisions and get the functionality of IIFE. 

Typescript with Module 

To make the internal aspects of the module accessible outside of the module we need to declare with export keyword, As the model and service are accessed by the angular controller we used the keyword export. 

Create a sharepoint Hosted App and the necessary typescript definitley file using Nuget Package Manger. 

Edit the project and include the lines

<PropertyGroup>

    <TypeScriptSourceMap>true</TypeScriptSourceMap>

  </PropertyGroup>

<Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)\TypeScript\Microsoft.TypeScript.targets" />

Save and reload the project.

Include the DefinitelyTyped by Package manager console

• Install-Package angularjs.TypeScript.DefinitelyTyped

Project.ts  

ProjectService.ts

$Inject

  Without injecting the program works well until the Js gets Minifed. The minification process will probably alter the names of the parameters and it results in unknown inject of the Angular.

The $inject is a special property of AngularJS to determine that what are the services need to be injected at runtime. It should be marked as static and  it is an array of string. The order of the array string and the constructor parameter should be matched. 

ProjectCtrl.ts 

App.ts 

Build the project and combine TS file.

Open the element.xml in the app folder remove all the .ts file path and include the test.js and test.map file. 

Default.aspx

Download Here

by Krishna via Everyone's Blog Posts - SharePoint Community

How to localize Search Display Templates

A quick and easy way to localize strings in a display template is to simply write out the translated values in the page. This way, you don't have to use separate resource files for the client side (e.g. like http://ift.tt/1jLcHKF).

Step 1: Write out the current resource string to a JavaScript variable in the page

string script = String.Format("var rsMyResource = '{0}';", ResourceService.GetLocalizedString("MyResource"));
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "rsTS", script, true);

Step 2: Insert the varaible in the display template

<span class="srLink"><a href="_#=openUrl=#_">_#=rsMyResource=#_</a></span>

Of course, this is not recommend for a large number of resources.

Get more infos like this daily: http://ift.tt/1SAPJaw

by Oliver Pistor via Everyone's Blog Posts - SharePoint Community

SharePoint Host Named Site Collection Creator CodePlex Update

During the weekend, one of my CodePlex projects that SharePoint Administrators seem to love has received a major update by my colleague Joseph Passineau . For those of you that don’t know what the SharePoint Host Named Site Collection Creator is, here is a small definition from the site:

The SharePoint Host named Site Collection (HNSC) Creator is a Codeplex Project that allows SharePoint Admins to create HNSC via a GUI instead of PowerShell. This project has two ways to be used. One of them is a Windows Forms application that needs no installation, and the second one is a SharePoint 2013 farm solution that plugs in the Central Admin for a native SharePoint experience.

Here are some screenshots:

February 19^th, 2016 Update

New release of the SharePoint 2013 Farm Solution

• Supports choosing a content database.
• There is a page to manage host header managed path.
• We also added 2 scripts to make it easier to install the solution or to update it.
• Fixed bugs

You can download it on CodePlex at: http://ift.tt/1ggrC8E

The post SharePoint Host Named Site Collection Creator CodePlex Update appeared first on Absolute SharePoint Blog by Vlad Catrinescu.

by Vlad Catrinescu via Absolute SharePoint Blog by Vlad Catrinescu

[Sponsored] Review of Aquaforest Searchlight: Automated OCR Software for SharePoint and Office 365

Review requested by SharePoint-Community.net sponsor Aquaforest, but thoughts are my own

The strong Search engine included in SharePoint is one of the reasons that enterprises around the world embrace SharePoint. Instead of relying on navigation to look for an item or document, users now rely on search every day. Not only in the enterprise but in every day personal use as well. When is the last time you navigated categories on eBay, Wikipedia, Craigslist, etc.?

However, a lot of documents, especially PDFs and some scanned documents are not searchable because even if they are in PDF format, they are simply an image inside a PDF. All this valuable information is not searchable, and search based features including the new DLP in SharePoint 2016 will not be able to function on those documents. In order to be able to search inside those documents you need an OCR (Optical Character Recognition) solution, and that's what we will be reviewing today. We will be reviewing a product called Aquaforest Searchlight that works on SharePoint 2010, 2013 as well as Office 365

• Audit document stores to determine which documents require processing.
• Document Stores are monitored to deal with new and updated documents.
• Dashboard provides a convenient summary of the state of all managed stores.
• Provides detailed conversion reporting.
• High Performance Multi-Core Support.
• Convenient GUI which enables management of all stores via a single interface
• OCR Support for over 100 languages including Chinese, Korean and Japanese

Review

Aquaforest Searchlight is a client side application, meaning that you don't install anything on SharePoint the server, all the hard work is done on a client computer. Before starting to go in the product, let's take a look at what my goal will be for this review. I uploaded a TIFF file named Dracula that contains an extract of the novel by Bram Stoker.

After leaving it a few hours, I could find the file by the title in Office 365, but when searching for "Munich", Office 365 returned nothing! Let's try to fix that by using Aquaforest Searchlight.

After installing the application and the pre-requisites, we will need to add a Library in Aquaforest. A "Library" does not equal to a document library, it can be an entire Site Collection.

After we click the "Add New Library" button we are guided through a wizard so we can select the exact settings we want for our Library.

On the Library Settings page we have multiple choices

1. Is it a SharePoint On-Premises, Office 365 or File Share Library that we want to add
2. Do you only want to Audit, or Audit and OCR. Audit means that Searchlight will analyze how many documents are not searchable, while Audit and OCR will find those documents, and then make them searchable.
3. We can select the number of cores that we want the application to use. The application will use 1 core / document, so if we give it 10 cores, it can process 10 documents simultaneously. If you plan to OCR thousands, or millions of documents on the first run, maybe it's a good idea to run it on a virtual server for the initial "transformation" and then move a lower performing machine for day to day.
4. Since the application will of course modify the document in order to make it readable, we can select if we want to turn versioning on if it's off, publish a major version with the new searchable document, and of course describe the check-in comment. The "original" version will be kept as a past version, depending on the versioning rules you have on the library.

We then go to the Document Settings where we can specify the behavior for each document type and filter which documents get OCR'd.

1. For PDFs we can select if we want to process them if they are already fully searchable, partially or not at all searchable.
2. For the TIFF files, we can select if we process them, and if we delete the original, as the Searchlight application converts them to PDF files.
3. We have the same settings for BMP, JPEG and PNG Files.
4. We can select where the Temp Folder location is. The temp folder is where Searchlight will download files while it does the magic to make them searchable.
5. We can select a date range for the library, so we don't OCR all the old documents that provide no additional value in the Search Engine.
6. This is a setting I personally loved seeing there, we can choose to retain all the original metadata on the document. So if a document gets downloaded, and re-uploaded but searchable, those columns will remain the same! With the "Check in Comment" we selected previously, here is what it will look like in the Version History. Modified / Modified By did not change even if the OCR took place a few days later!

After the Document Settings are in place, we can select where we put our Archives, if we decide to keep them of course. The archives are all the original documents before Searchlight made them Searchable.

We then go to the OCR Settings. In the OCR Settings we have two different options we can use, the Aquaforest OCR engine, or the IRIS OCR Engine

I have asked Aquaforest what the difference is, and the main difference is that the Extended OCR Engine works with multiple languages and supports more languages than the Aquaforest OCR one. So if you need to translate documents in more languages, make sure to select the extended choice. Both engines have multiple choices such as rotating the image, or deskewing the documents.

After we select our OCR properties, we can move on to create a schedule for the library

We can either run this job manually, or run it every day or hour, to keep our documents always searchable! After this, we select our Email settings if we want to receive emails when a job is done, or fails.

After we finish and we start the job, the Aquaforest Searchlight tool will first audit the document library and report on its Searchability (How much % of the library is indexable) and then start transforming the documents into Searchable ones.

After the job is done and we wait for the Office 365 crawler to crawl the Site Collection, I could successfully crawl the "Dracula" document and find text from inside it

Conclusion

In this blog post we had an overview of the Aquaforest Searchlight tool that allows enterprises to make their PDF and image documents searchable, in order to provide additional value in SharePoint. I found the Searchlight application really easy to use, and the 10 or so documents I have uploaded have been transformed pretty fast, even if I only gave it one core to do all the OCR. Some things that you will need to be careful about are making sure that your temp and Archive folders have enough space on them if you need to OCR thousands of documents, as every document that gets downloaded can fill up the C: drive pretty fast.

The thing that I loved most is the fact that the application can turn on versioning, while making sure the important metadata such as "Modified/Created By" and "Created / Modified" do not change after a document is transformed. That would have been a deal breaker for most companies where those four columns are of significant importance.

I didn't really find anything I didn't like in the Aquaforest Searchlight tool, as it does everything that it says it does and I am happy to see it work with SharePoint 2010/ 2013 / SharePoint Online, and has been tested with SharePoint 2016 RC so I am sure it will work with it once the product is released!

With Data Loss Prevention becoming a more important topic for every company, and with the DLP Features in SharePoint 2016 / SharePoint online relying on search, having your documents in fully searchable format is a real plus. If you're looking for an OCR Solution for SharePoint, make sure to check out Aquaforest Searchlight by clicking on the logo below:

by Vlad Catrinescu via Everyone's Blog Posts - SharePoint Community