Powershell对Excel⽂件的⼏种操作⽅法
⼀、如何通过powershell打开⼀个Excel表格，
1$file_excel = Get-Item ("D:\Powershell_test\test.xlsx")
2$Excel = New-Object -ComObject Excel.Application
3$Excel.Visible = $true
4$WorkBook = $Excel.Workbooks.Open($file_excel)
5$WorkSheet = $Workbook.Sheets.Item("Status")
6$SheetRow = $WorkSheet.UsedRange.Rows.Count
1、第⼀⾏是通过Get-Item把D盘Powershell_test⽂件夹下的test.xlsx这个对象赋给$file_excel这个变量。
2、第⼆⾏是新建⼀个Excel对象，并将值赋给$Excel变量
3、第三⾏是如果希望屏幕上显⽰Excel图形界⾯，设值为$true，否则为$false，或者直接不写这⾏代码。
4、第四⾏是将test.xlsx加载进Excel程序
5、第五⾏是锁定要操作的sheet，“Status”为sheet名称，请根据实际情况替换“status”
6、第六⾏中的$SheetRow统计出来的⾏数往往不准确，如果表格中的最后⼏⾏数据被认为的“清除内容”了，⽽不是整⾏删除了，那么统计出来的数字就会不准。
7、如果想要准确的数字，可以使⽤如下循环的⽅法获得：
1$excel_count =0
2for($b = 1; $b-le$SheetRow; $b++)
3 {
4if($WorkSheet.Cells.item($b,1).text -ne"") #$b代表⾏数变量，数字1代表的是表格中的列，通过对$b的循环，获得有效⾏数
5    {powershell创建目录
6$excel_count++
7    }
8 }
最后的$excel_count的值就是准确的。
⼆、设置表格的边框
$WorkSheet.Cells.item(1,1).borders.LineStyle = 1
1、$WorkSheet.Cells.item(1,1)中的（1,1）代表的是第⼀⾏中的第⼀列⽅格。请根据实际需要替换
三、设置表格底⾊
$WorkSheet.Cells.item($excel_next_valid_row,1).font.size = 10
1、等号右边的10⽤来调节颜⾊，不同的颜⾊数字不⼀样，请根据实际需求对10进⾏变化。
四、关闭Excel的进程函数，⼀般放在脚本的最上⽅，当Excel处理完毕需要保存并关闭Excel进程时，需要调⽤这个函数，具体见下⾯的代码
function close-excel ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref))
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
五、保存Excel并关闭Excel进程
1$WorkBook.save()
2$Excel.Workbooks.Close()
3$Excel.Quit()
4 close-excel($WorkSheet)
5 close-excel($WorkBook)
6 close-excel($Excel)
1、如果需要将Excel另存为，需要将$WorkBook.save()变为$WorkBook.savesa("c:\123\"),⽂件将会被另存在C盘⽬录123下。
六、Copy整列数据
1$file = “C:\test\test.xlsx”
2$Excel = New-Object -ComObject excel.application
3$Excel.visible = $true
4$Workbook = $excel.Workbooks.open($file)
5$Worksheet = $Workbook.WorkSheets.item(“test”)
6$worksheet.activate()
7$range = $WorkSheet.Range(“A1:B1”).EntireColumn
8$range.Copy() | out-null text
9$Worksheet = $Workbook.Worksheets.item(2)
10$Range = $Worksheet.Range(“D1”)
11$Worksheet.Paste($range)
1、上⾯的操作是将test.xlsx中的第⼀个sheet中的A1和B1的整列数据拷贝到第⼆个sheet的D1开始的列，如果是拷贝⾏的话，把代码的第七⾏中的EntireColumn换成EntireRow。如拷贝A1到A100的数据，$range = $WorkSheet.Range(“A1:A100”).EntireRow
七、在Excel中搜索特定字符串，到后返回⾏标，该代码可以替换以for循环为基础的搜索。⽐for循环要快的多
1#以下代码可以在电脑屏幕中输出多个符合搜索条件的⾏标，如果要输出多个列标，第10⾏代码可以替换为$Column = $Target.Column()
2 #如果要限定搜多范围，⽐如只在C列搜索，则第4⾏换成$Range = $WorkSheet.Range("C:C")
3$SearchString = 'abc'    #在excel中搜索“abc”这个字符串
4$Range = $WorkSheet.UsedRange
5$Target = $Range.Find($SearchString)
6
7$First = $Target
8Do
9 {
10$row_NO = $w()
11    Write-Host $row_NO
12$Target = $Range.FindNext($Target)
13 }
14While ($Target-ne$w -w)
⼋、删除sheet中的所有有内容的⾏
1$Sheetrow=$WorkSheet.UsedRange.Rows.Count
2$Worksheet.Rows.Delete($sheetrow)
九、删除sheet中的指定⾏
$Worksheet.Range("A2:A9").EntireRow.delete()
⼗、查看指定列的有效⾏
$ExcelPath = Get-Item 'F:\xxx.xlsx'
$xldown = -4121 # see: msdn.microsoft/en-us/library/bb241212(v=office.12).aspx
$xlup = -4162
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$WordBook = $Excel.Workbooks.Open($ExcelPath)
$WorkSheet = $Excel.WorkSheets.item("Sheet1")
$WorkSheet.activate()
# Find the last used cell
$lastRow = $lls.Range("A1048576").End($xlup).row

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。