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.

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open ($ExcelFile)
$WorkBook.sheets | Select-Object -Property Name
$WorkSheet = $WorkBook.Sheets.Item(1)
$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

$currentDir = "Give here the path of the current folder where scripts are stored"
Import - Module $currentDir "\PSExcel"
$ExcelFile = $currentDir + "\filename.xlsx"
$objExcel = New - Excel - Path $ExcelFile
$WorkBook = $objExcel | Get - Workbook
# Loop through all items in the excel
ForEach($Worksheet in @($Workbook.Worksheets)) {
    $totalNoOfRecords = $Worksheet.Dimension.Rows
    $totalNoOfItems = $totalNoOfRecords - 1
    # Declare the starting positions first row and column names
    $rowNo, $colFirstName = 1, 1
    $rowNo, $colLastName = 1, 2
    if ($totalNoOfRecords - gt 1) {
        #Loop to get values from excel file
        for ($i = 1; $i - le $totalNoOfRecords - 1; $i++) {
            $firstName = $WorkSheet.Cells.Item($rowNo + $i, $colFirstName).text
            $lastName = $WorkSheet.Cells.Item($rowNo + $i, $colLastName).text
        }
    }
}

Search text in excel file

Function Search - Excel($Source, $SearchText) {
    $objExcel = New - Excel - Path $Source
    $WorkBook = $objExcel | Get - Workbook
    Foreach($Sheet in $WorkBook.Worksheets) {
        $Dimension = $Sheet.Dimension
        $RowStart = 2
        $ColumnStart = 1
        # Column where we need to search
        for text in our
        case searching
        for FirstName
        $RowEnd = $Dimension.End.Row
        $ColumnEnd = $Dimension.End.Column
        for ($Row = $RowStart; $Row - le $RowEnd; $Row++) {
            $Value = $Sheet.Cells.Item($Row, $ColumnStart).Value
            if ($Value) {
                if ($Value.trim() - eq $searchText.trim()) {
                    Write - Host $searchText "found in excel file"
                    return $true
                }
            }#If value is not null
        }#End of
            for each row
        $objExcel.Dispose()
    }
}
Search - Excel - Source $ExcelFile - SearchText "Sachin"#
Text to be searched

Thanks for reading.