Continuing from where we left in part-1, we’ll dive further in this topic.
Copy a range of cells from one excel sheet and paste in another
[threading.thread]::CurrentThread.CurrentCulture = 'en-US' $xl1 = New-Object -ComObject Excel.Application $xl1.visible = $true $wb1 = $xl1.workbooks.open($xlsx3) $xl1.Worksheets.item(1).name = "Worksheet1" $ws1 = $wb1.worksheets.Item(1) $ws1.Activate() $lr1 = $ws1.UsedRange.rows.count $r1 = $ws1.Range("A1:F$lr1") $r1.copy() [threading.thread]::CurrentThread.CurrentCulture = 'en-US' $xl2 = New-Object -ComObject Excel.Application $xl2.visible = $true $wb2 = $xl2.workbooks.open($xlsx4) $ws2 = $wb2.worksheets.Item(1) $ws2.activate() $r2 = $ws2.Range("A1:F$lr1") $ws2.Paste($r2) $wb1.save() $wb2.save() $xl1.Workbooks.Close() $xl2.Workbooks.Close() $xl1.Quit() $xl2.Quit()
We can change the cell selection as per our need, for e.g.
$lr1 = ($ws1.UsedRange.rows.count)-1
This will count all the rows ‘r’ and subtract the last row. As a result the variable ‘$lr1’ will store the numeric value ‘r-1’. After this we can just mention the selction range as
$r1 = $ws1.Range("A1:F$lr1")
This will select all the cells containing data starting from ‘A1’ cell upto the second last row. Instead of two steps, it can be combined into one as,
$r1 = $ws1.Range("A1:F$($ws1.UsedRange.rows.count-1)")
We can paste the selection to any place in the excel by altering the range as below.
$lr2 = $r1.Rows.Count
This will store the count of rows contained in the copy selection that we have made and store it in variable ‘$lr2’.
$r2 = $ws2.Range("A2:F$lr2") $ws2.Paste($r2)
Note that the source and destination cell range should be exactly same.
Now that we are discussing about excel com object and powershell, so Powershell Excel Cookbook Ver 2 can’t go without mention. It has almost everything one can get across while working to automate excel with PowerShell.
Inserting values in specific cells
$ws1.cells.item(1,2) = "Hello"
This will store the string ‘Hello’ in cell ‘B1’. The range (1,2) correspond to cell at the intersection of first row and second column.
Sorting a column
$rsort1=$ws1.usedrange $rsort2 = $ws1.Range("A2") [void]$rsort1.sort($rsort2, 1)
First we will select all the cells where data is present and store this range in variable ‘rsort1’. After this we will pick the cell from the respective column from which onwards, we want the sorting to be done. So in this case, cells in ‘A’ column starting from ‘2’ row will be sorted in ascending manner due to parameter ‘1’.
Summing values in each column and displaying the sum at the bottom of respective column
$r1 = $ws1.usedRange $row1 = $r1.rows.count $Sumrow1 = $row1 + 1 $r2 = $ws1.Range("B2:B$row1") $r3 = $ws1.Range("C2:C$row1") $r4 = $ws1.Range("D2:D$row1") $r5 = $ws1.Range("E2:E$row1") $r6 = $ws1.Range("F2:F$row1") $functions = $xl1.WorkSheetfunction $ws1.cells.item($Sumrow1,1) = "Total" $ws1.cells.item($Sumrow1,2) = $functions.sum($r2) $ws1.cells.item($Sumrow1,3) = $functions.sum($r3) $ws1.cells.item($Sumrow1,4) = $functions.sum($r4) $ws1.cells.item($Sumrow1,5) = $functions.sum($r5) $ws1.cells.item($Sumrow1,6) = $functions.sum($r6) [void]$r1.entireColumn.Autofit()
First we will store the count of all the rows conatining data and store in variable $row1. Assuming that the first row contains the headers, we select from 2nd row to the end. After this we make use of ‘worksheetfunction’ of excel ‘xl1’ object that we have created to call ‘sum’ function. After this we will sum the values in columns ‘A’ to ‘F’ and store at their bottom. After the operation, we can ‘autofit’ the entire data range. There is another way of adjusting the column width as below, by specifying the column width.
$ws1.Columns.Item('a').columnWidth = 20
Sometimes if the datatype of values is set to text/general, the summing operation might fail. So we have to set the datatype prior to this, as follows.
$ws1.Columns.Item('a').Numberformat="0" $ws1.Columns.Item('b').Numberformat="0" $ws1.Columns.Item('c').Numberformat="0" $ws1.Columns.Item('d').Numberformat="0" $ws1.Columns.Item('e').Numberformat="0" $ws1.Columns.Item('f').Numberformat="0"
Adding chart to worksheet
Lets select chart and chart type first that we will put in the worksheet.
$ch = $ws1.shapes.addChart().chart $ch.chartType = 58
Then we will set the data range that we want to chart. We can also give a suitable title to the chart.
$ch.setSourceData($range1) $ch.HasTitle = $true $ch.ChartTitle.Text = "My chart"
Now we will select the cell range where we want to copy the chart. If we want to adjust the chart size, it can be done by alterig the range as below.
$RngToCover = $ws1.Range("A$($range2.Rows.Count + 5):F$($range.Rows.Count + 23)") $ChtOb = $ch.Parent $ChtOb.Top = $RngToCover.Top $ChtOb.Left = $RngToCover.Left $ChtOb.Height = $RngToCover.Height $ChtOb.Width = $RngToCover.Width
Below are some basic formatting operations that can be done on a cell range.
$range.select() $range.HorizontalAlignment = -4108 $range.VerticalAlignment = -4107 $range.BorderAround(1,4,1) $range.WrapText = $false $range.Orientation = 0 $range.AddIndent = $false $range.IndentLevel = 0 $range.ShrinkToFit = $false $range.ReadingOrder = -5002 $range.MergeCells = $false $range.Borders.Item(12).Weight = 2 $range.Borders.Item(12).LineStyle = 1 $range.Borders.Item(12).ColorIndex = -4105