Read Excel File Using PSExcel In PowerShell

 Recently we got one requirement from a client where we need to read data from Excel (shared in a common location) and update it in a SharePoint list.
 
There is one external job which will update the Excel file in a shared location every day. We need to sync this Excel data with list in SharePoint. The external job didn't have access to the SharePoint environment because of access restriction.
 
As part of this requirement initially we used “New-Object –ComObject” excel Com objects to perform activities related to Excel files, like read/search data in excel file.
  1. $objExcel = New-Object -ComObject Excel.Application  
  2. $WorkBook = $objExcel.Workbooks.Open ($ExcelFile)  
  3. $WorkBook.sheets | Select-Object -Property Name  
  4. $WorkSheet = $WorkBook.Sheets.Item(1)  
  5. $totalNoOfRecords = ($WorkSheet.UsedRange.Rows).count  
When we wanted to run the code in a test environment we observed that server did not have Excel COM objects installed. We searched for some suggestions/articles where we can do the actions related to Excel without installing COM objects.
 
Below is the approach we followed to solve this issue. 
 
Usually we use "Microsoft.Office.Interop.Excel" to read data from Excel files. But in most of the production environments Excel COM objects will not be installed because of the performance issue. So we can use “PSExcel” power shell scripts to perform actions related to Excel.
 
Download the required “PSExcel” module (attached as Zip file) and save it in your system by extracting the Zip folder.
 
$currentDir = "Give here the path of the current folder where scripts are stored".
 
Then import the PSExcel module by using Import-Module command
 
Import-Module$currentDir"\PSExcel"
 
Below are the two activities which we have done as part of this requirement
 

 Read data from the excel file

  • Give the path of the file where Excel file is stored
  • Then create an instance of the Excel file
  • Get each worksheet data from the Excel
  • Then loop through each item in the worksheet and get values of the required column.

Search value in the Excel file

 
As part of this requirement we have to search word in shared Excel file. 
  • Give the path of the file where Excel file is stored
  • Then create an instance of the Excel file
  • Get each worksheet data from the Excel
  • Pass the word to be searched to the function as parameter
  • In this we are searching for “Sachin” in the column “FirstName”
If the value matches then return true, if not false.
 
 
Read excel file data
  1. $currentDir = "Give here the path of the current folder where scripts are stored"  
  2. Import - Module $currentDir "\PSExcel"  
  3. $ExcelFile = $currentDir + "\filename.xlsx"  
  4. $objExcel = New - Excel - Path $ExcelFile  
  5. $WorkBook = $objExcel | Get - Workbook  
  6. # Loop through all items in the excel  
  7. ForEach($Worksheet in @($Workbook.Worksheets)) {  
  8.     $totalNoOfRecords = $Worksheet.Dimension.Rows  
  9.     $totalNoOfItems = $totalNoOfRecords - 1  
  10.     # Declare the starting positions first row and column names  
  11.     $rowNo, $colFirstName = 1, 1  
  12.     $rowNo, $colLastName = 1, 2  
  13.     if ($totalNoOfRecords - gt 1) {  
  14.         #Loop to get values from excel file  
  15.         for ($i = 1; $i - le $totalNoOfRecords - 1; $i++) {  
  16.             $firstName = $WorkSheet.Cells.Item($rowNo + $i, $colFirstName).text  
  17.             $lastName = $WorkSheet.Cells.Item($rowNo + $i, $colLastName).text  
  18.         }  
  19.     }  
  20. }  
Search text in excel file
  1. Function Search - Excel($Source, $SearchText) {  
  2.     $objExcel = New - Excel - Path $Source  
  3.     $WorkBook = $objExcel | Get - Workbook  
  4.     Foreach($Sheet in $WorkBook.Worksheets) {  
  5.         $Dimension = $Sheet.Dimension  
  6.         $RowStart = 2  
  7.         $ColumnStart = 1  
  8.         # Column where we need to search  
  9.         for text in our  
  10.         case searching  
  11.         for FirstName  
  12.         $RowEnd = $Dimension.End.Row  
  13.         $ColumnEnd = $Dimension.End.Column  
  14.         for ($Row = $RowStart; $Row - le $RowEnd; $Row++) {  
  15.             $Value = $Sheet.Cells.Item($Row, $ColumnStart).Value  
  16.             if ($Value) {  
  17.                 if ($Value.trim() - eq $searchText.trim()) {  
  18.                     Write - Host $searchText "found in excel file"  
  19.                     return $true  
  20.                 }  
  21.             }#If value is not null  
  22.         }#End of  
  23.             for each row  
  24.         $objExcel.Dispose()  
  25.     }  
  26. }  
  27. Search - Excel - Source $ExcelFile - SearchText "Sachin"#  
  28. Text to be searched  
Thanks for reading.