Powershell 对Excel文件的几种操作方法

F2967527 2022-06-26 发布于北京

展开全文

一、如何通过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 2 for($b = 1; $b -le $SheetRow; $b++) 3 { 4 if($WorkSheet.Cells.item($b,1).text -ne '') #$b代表行数变量，数字1代表的是表格中的列，通过对$b的循环，获得有效行数 5 { 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 8 Do 9 { 10 $row_NO = $Target.row() 11 Write-Host $row_NO 12 $Target = $Range.FindNext($Target) 13 } 14 While ($Target -ne $NULL -and $Target.row -ne $First.row)

八、删除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: http://msdn.microsoft.com/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 = $Worksheet.cells.Range('A1048576').End($xlup).row