Solved

sharePoint 2013 usage for -HR's Salary slips Updates

Posted on 2013-10-28
11
394 Views
Last Modified: 2014-01-22
Hi,
I have prapared a  basic folder structure in sharepoint 2013 doc library.
customise permission on each folder.
How can I make it bit easy for HR to upload there monthly salary of all 100 employees.
suggest any easy way/tool if there ...
Rgds/-
0
Comment
Question by:sharepointDepot
  • 6
  • 5
11 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39607940
Hi,
could you please explain / provide further details about:
- Strucuture of the folders in Sharepoint ( eg each employee has his own folder)
- Stucture of the source folder (eg all files in one local folder)
- Are there any meta data associated with the documents

In general you might use Windows explorer to connect to the library. Other options involve scripting (vb script or Powershell).

Thanks and HTH
Rainer
0
 

Author Comment

by:sharepointDepot
ID: 39607996
Hi,
details-
-each employee has his own folder access only
-structure of source folder is on D:\ drive,
!-- Hr actually maintains 1 single Excel ,but  he has several sheets inside for every individual, like Emp1,Emp2,Emp3...
there he clicks->Page Layouts->Print area->Set Print Area->File->Save As->save as type ->PDF
--!
-Metadata .....Emp Name + Emp ID

need any easier tool
Rgds/-
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39608165
Hi,

I think the best option (in regards to time, costs and coding) is to create a Powershell script.
This script will use SharePoint Client Object model as well as Excel Interop to dynamically create the PDF files and upload them to SharePoint including the meta data.

Are you able to provide a sample Excel file (just for three dummy employees with fictive numbers and names) as well as the related PDF files for these three dummy employees?

This solution might take some time to be coded and without a sample it would be really hard (nearly impossible) to generate a working solution.

Thanks and HTH
Rainer
0
 

Author Comment

by:sharepointDepot
ID: 39608346
Hi,
A sample  file with dummy data is attached here-.... http://sdrv.ms/1dHH4yS
I this file, say Hr clicks User1(sheet), then 1 month's salary chart he will select, and do the above steps, and save it as pdf document.
Now this way, 100 pdf are there in his folder.
suggest if any way out for sending this into sharepoint library folders.
Rgds/-
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39617327
Hi,

here we go.

First of all the script - explanations inline and afterwards:
# Global objects
$scriptPath = (Split-Path -Parent $MyInvocation.MyCommand.Path) 
 
#-----------------------------------------------------
# Helper function to load SharePoint Client Object Model DLLs 
# Original source: http://soerennielsen.wordpress.com/2013/08/25/use-csom-from-powershell/
function AddCSOM(){

     # Try to load SharePoint client dlls from Script execution folder
     try {
     $a = [System.Reflection.Assembly]::LoadFile("$scriptPath\Microsoft.SharePoint.Client.dll")
     $ar = [System.Reflection.Assembly]::LoadFile("$scriptPath\Microsoft.SharePoint.Client.Runtime.dll")
    }
    catch
    {
    }
     if( !$a ){
         $a = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
     }
     if( !$ar ){
         $ar = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
     }
 
     if( !$a -or !$ar ){
         throw "Could not load Microsoft.SharePoint.Client.dll or Microsoft.SharePoint.Client.Runtime.dll"
     }
 
 
     #Add overload to the client context.
     #Define new load method without type argument
     $csharp =     "
      using Microsoft.SharePoint.Client;
      namespace SharepointClient
      {
          public class PSClientContext: ClientContext
          {
              public PSClientContext(string siteUrl)
                  : base(siteUrl)
              {
              }
              // need a plain Load method here, the base method is a generic method
              // which isn't supported in PowerShell.
              public void Load(ClientObject objectToLoad)
              {
                  base.Load(objectToLoad);
              }
          }
      }"
 
     $assemblies = @( $a.FullName, $ar.FullName,     "System.Core")
     #Add dynamic type to the PowerShell runspace
     Add-Type -TypeDefinition $csharp -ReferencedAssemblies $assemblies
}
 
#-----------------------------------------------------
# Helper function to release unmanaged COM objects 
function Release-Ref ($ref) { 
([System.Runtime.InteropServices.Marshal]::ReleaseComObject( 
[System.__ComObject]$ref) -gt 0) 
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers()  
} 
#----------------------------------------------------- 
#-----------------------------------------------------
# Helper function for uploading
function UploadFileSCOM($pFileName, $pFilePath, $pUsername, $pEmployeeId, $pDocLibName, $spClientContext) {
    $fileToUpload = New-Object Microsoft.SharePoint.Client.FileCreationInformation
    $fileToUpload.Content = [System.IO.File]::ReadAllBytes($pFilePath)
    $fileToUpload.Url = $siteUrl + "/" + $pDocLibName + "/" + $pUsername +  "/" + $pFileName
    $fileToUpload.Overwrite = $true

    $docLib = $spClientContext.Web.Lists.GetByTitle($pDocLibName)

    $uploadedFile = $docLib.RootFolder.Files.Add($fileToUpload)

    $uploadedFile.ListItemAllFields["EmpName"] = $pUsername
    $uploadedFile.ListItemAllFields["EmpId"] = $pEmployeeId
    $uploadedFile.ListItemAllFields.Update()
    $spClientContext.ExecuteQuery()
} 

# Base variables
$basePath="F:\EE\EEHRExcelDynamic\"
$importFile="Contoso-Fictitious Employee Salary.xlsx"
$siteUrl = "http://dev.dse.local/sites/ee"
$documentLibraryName = "EEHR"
 
# Constants
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]
$searchString = "PAYSLIP FOR THE MONTH OF"

# Needs to be set if Office Install is different than regional settings 
$newci = [System.Globalization.CultureInfo]"en-US"
[system.threading.Thread]::CurrentThread.CurrentCulture = $newci 

# Lets begin
$now = Get-Date
Write-Host "Started execution: $now"

# Initialize CSOM
AddCSOM

# Load the SharePoint site
$context = New-Object SharepointClient.PSClientContext($siteUrl)
 
# Initialize Excel COM
$objExcel = New-Object -ComObject Excel.Application
$objExcel.visible = $false
$wb = Get-Item("$basePath$importFile")
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
$workbook.Saved = $true

$counter = 0
$numberOfWorksheets = $workbook.Worksheets.Count
 
# Now loop through each sheet
foreach($currentSheet in $workbook.Worksheets)
{
    $counter += 1
    Write-Progress -Activity "Processing worksheets" -status "$counter of $numberOfWorksheets" -percentcomplete ($counter / $numberOfWorksheets*100)
    # Get the range in Excel which has content
    $completeRange = $currentSheet.UsedRange
 
    # Search for the table header identifier
    $foundMonths = $completeRange.Find($searchString)
 
    # If at least one occurence found process the first one
    if ($foundMonths -ne $null)
    {
        # Get the cell information (row/column)
        $firstResult = $foundMonths.AddressLocal($false,$false)

        # Now get the table header string to get the info about month and year
        $cellValue = $foundMonths.Value()

        Write-Host "Found reference in sheet $($currentSheet.Name) - cell $firstResult - value $cellValue" 
        # Remove the leading text
        $datePart = $cellValue.Replace($searchString,"").Trim()
        # Get the month and trim
        $monthName = $datePart.Split("-")[0].Trim()
        # Get the year and trim
        $yearName = $datePart.Split("-")[1].Trim()
        # Get the user name (from the sheet name)
        $userName = $currentSheet.Name
        # Build the filename
        $fileName = $userName+"_"+$yearName+"_" + $monthName + ".pdf"
        # Build the complete path for local storage
        $filepath = Join-Path -Path $basePath -ChildPath ($fileName)
 
        # Now calculate the print area based on the header cell
        $newRange = $foundMonths.Row - 3
        $printArea = "A"+$newRange+":I"+$($newRange+24)
        
        # Now get the employee ID
        $empIdRow = $foundMonths.Row + 2
        $employeeId = $currentSheet.Cells.Item($empIdRow, 8).Value()

        # Set the print area
        $currentSheet.PageSetup.PrintArea = $printArea 
 
        # Now export the print area to the local disk
        $currentSheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
 
        # Now upload the file to SharePoint
        UploadFileSCOM $fileName $filepath $userName $employeeId $documentLibraryName $context
 
        # Now cleanup the local file
        Remove-Item $filepath
    }

    # Now we have to loop through all monthly reports in the sheet
    do {
        # Try to find the search string again
        $foundMonths = $completeRange.FindNext($foundMonths)

        # Get the current full cell value to compare with the initial one
        $currentResult = $foundMonths.AddressLocal($false,$false)

        # If the findnext returns the same result -> exit
        if ($firstResult -eq $currentResult) { break }

        # Its an additional report -> further processing
        Write-Host "Found reference in sheet $($currentSheet.Name) - cell $currentResult - value $cellValue"

        # Now get the table header string to get the info about month and year
        $cellValue = $foundMonths.Value()
        # Remove the leading text
        $datePart = $cellValue.Replace($searchString,"").Trim()
        # Get the month and trim
        $monthName = $datePart.Split("-")[0].Trim()
        # Get the year and trim
        $yearName = $datePart.Split("-")[1].Trim()
        # Get the user name (from the sheet name)
        $userName = $currentSheet.Name
        # Build the filename
        $fileName = $userName+"_"+$yearName+"_" + $monthName + ".pdf"
        # Build the complete path for local storage
        $filepath = Join-Path -Path $basePath -ChildPath ($fileName)
 
        # Now calculate the print area based on the header cell
        $newRange = $foundMonths.Row - 3
        $printArea = "A"+$newRange+":I"+$($newRange+24)
        
        # Now get the employee ID
        $empIdRow = $foundMonths.Row + 2
        $employeeId = $currentSheet.Cells.Item($empIdRow, 8).Value()

        # Set the print area
        $currentSheet.PageSetup.PrintArea = $printArea 
 
        # Now export the print area to the local disk
        $currentSheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
 
        # Now upload the file to SharePoint
        UploadFileSCOM $fileName $filepath $userName $employeeId $documentLibraryName $context
 
        # Now cleanup the local file
        Remove-Item $filepath
    }
    until ($foundMonths -eq $null)
}
Write-Host "Processing finshed"

# Now release client object
$context.Dispose()

# Close the workbook - not saving changes (print areas)
$workbook.Saved = $true
$objExcel.Workbooks.close()
$objExcel.Quit()

# Now cleanup COM objects
$a = Release-Ref($currentSheet) 
$a = Release-Ref($workbook) 
$a = Release-Ref($objExcel)

Open in new window


Prerequisites:
- You have installed Powershell on the machine where your want to process the file
- You have installed Excel (I tested with Excel 2010 and 2013)
- You have installed SharePoint Client Object model

There are a couple of base variables, which you have to adjust:
$basePath -> Thats the working folder, in which the Excel file to be processed should be in
$importFile -> Thats the Excel file we want to process
$siteUrl -> Thats the URL of the site where your document library is in
$documentLibraryName -> Thats the title/path of your document library

Assumptions:
- The name of the sheet is used as part of the filename as well as the employee name column as well as the folder name
- The name of the sheet is based on alphanumeric characters - no spaces or other "special" characters (e.g. dots ".", ampersands "&" or apostrophe "'")
- All reports inside a sheet will be processed and the files in SharePoint gets overwritten

To run the script just copy&paste the code into notepad, make the variable adjustments and save the file as xxx.ps1. Then open Powershell and execute the ps1 file OR copy&paste to Powershell ISE, make variable adjustments and run the code (in ISE you have the possibility to set breakpoints ...)

If you have any problem/issue with the script or if especially the assumption of the sheet / user name is not correct - please add a comment and I would be happy to help you out.

HTH
Rainer
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39628780
Hi,
did you have any chance to test the script? Any issues?

Thanks
Rainer
0
 

Author Comment

by:sharepointDepot
ID: 39629229
Hi,
will test today eod.
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39681114
Hi sharepointDepot,

any news / further issues on this?

Thanks.
Rainer
0
 

Author Comment

by:sharepointDepot
ID: 39720161
k
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39720338
???
0
 

Author Comment

by:sharepointDepot
ID: 39794449
Hi,
Still used normal folder structure there in sharepoitn doc library.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction Chart.js, used properly, can visually add a difference to your charting applications. It engages your visitors and allows them to interact with data they otherwise wouldn't be able to without expensive and complicated systems. For this…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now