Situatie
In this article, we will learn how to manipulate excel rows and columns to automate a repetitive report generation in PowerShell.
- To open the ISE -> click the windows button + r to open the run window and type powershell_ise.exe
- As you can see on the right-hand side of the screen tab, Commands has a list of modules that can be imported for use.
Solutie
Pasi de urmat
Now, let us start creating an excel object that will summon the excel application to the memory when executed.
In the above code given, I have created an excel object variable called $objExcel. And The subsequent lines of code make the excel object or application visible to us and ignore/close any pop-ups when opening the excel application.
Now lets us see what happens when we execute it,
As you can see from the above image, it opens an empty excel application without any workbook. Now let us load a workbook to the excel application.
To load an excel workbook, we have to provide the path location in which the file is stored. $file variable is used to store the path location, and we can open it in the excel application by :
After executing the above script, it opens the required workbook in excel.
The file contains loan details of customers, we have to access the data at the cell level to manipulate them, to do that we need a basic understanding of Excel structure to access PowerShell.Our excel object is the bookcase which can have any number of different workbooks. And each workbook can have any number of worksheets made up of cells (row x column). Which contains the data item inside them.
Loan data is in sheet1 so in order to access the data cell in sheet1, we need to create a $sheet object.
$sheet = $workbook.Worksheets.Item(“Sheet1”)
Now lets us perform a simple total count of data in the sheet, we do that by used range of the sheet object by row.
$rowMax = ($sheet.UsedRange.Rows).count
$rowMax variable can later be used in a for loop as i < = $rowMax to loop through all the data items in the worksheet.
Since first row 1 contains the column headers, we select the column headers we want to operate or manipulate with. Let us try to find the total sum of the loan_amount of all customers.
Let us first select the loan amount column header,
$rowLamount,$colLamount = 1,2
Row x column = 1 x 2 cause Loan_amount is in cell address of row 1 and column B.
After that, we need to create a for loop which loops through each cell in column B to get the loan_amount of each customer. When we retrieve the data from each cell, it will be in string format. We need to change it to int for further manipulation of the data.
$total=0
for ($i=2; $i -le $rowMax-1; $i++)
{
$loan_amount = $sheet.Cells.Item($i,$colLamount).text
$loan_amount = $loan_amount -as [int]
$total=$loan_amount+$total
}
Now the sum of all the customer loans is in the variable $total, we want to insert the value we obtained into the excel worksheet in a cell just below the last row of the loan_amount column, which can be done by:
$sheet.Cells.Item($rowMax+2,$colLamount)= $total
After doing the required operation we need to save the file and quit it.
$workbook.SaveAs($path)
$workbook.Close
$objExcel.Workbooks.Close()
$objExcel.Quit()
Just quitting the excel object won’t clear it from the memory, we need to release the object by.
Get-Process excel | Stop-Process -Force
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
PS — Try to execute the script line by line to observe the changes.
Powershell script:
Leave A Comment?