#@&cls&powershell "type %~s0|out-string|iex"&pause&exit
$r='a5'
$c='f1'
$d='f5'
$t='abc'
dir 123.xls|%{
$a=(new-object -comobject excel.application).workbooks.open($_.fullname)
$b=$a.sheets.item(1)
$b.range($r).EntireRow.insert()
$b.range($c).Entirecolumn.insert()
$row=$b.range($d).row
$column=$b.range($d).column
$b.cells.item($row,$column)=$t
$b.Range($d).interior.colorindex=6
$b.Range($d).font.colorindex=3
$b.Range($d).font.bold=$true
$a.save()
$a.close() 
}
复制代码

<#*,:&cls
@echo off
cd /d "%~dp0"
powershell -NoProfile -ExecutionPolicy RemoteSigned -Command ". ([ScriptBlock]::Create((Get-Content -LiteralPath \"%~0\" -ReadCount 0 | Out-String ))) "
pause
exit /b
#>
# 功能: 处理当前目录的xls文件

$r = '6:6' # 要插入的行
$c = 'G:G' # 要插入的列
$d = 'F5'  # 更改单元格
$t = '批处理之家' # 更改的内容

$xlShiftDown = -4121
$xlShiftToRight = -4161
$xlFormatFromLeftOrAbove = 0
$xlFormatFromRightOrBelow = 1

$excel = New-Object -ComObject excel.application
$excel.Visible = $true #显示excel窗口
$excel.DisplayAlerts = $false
Get-ChildItem .\*.xls, .\*.xlsx | ForEach-Object {
  $workbook = $excel.workbooks.open($_.fullname)
  $worksheet = $workbook.sheets.item(1) # 操作第一表格 Sheet1
  $worksheet.range($c).insert($xlShiftToRight, $xlFormatFromLeftOrAbove) #插入列
  $worksheet.range($r).insert($xlShiftDown, $xlFormatFromLeftOrAbove) #插入行
  $range = $worksheet.Range($d)
  $range.Value() = $t #更改内容
  $range.interior.colorindex = 6 #单元格背景色
  $range.font.colorindex = 3 #单元格字体色
  $range.font.bold = $true #加粗
  [void]$workbook.save() #保存xls
  $workbook.close() 
}
$excel.Quit()
复制代码

$worksheet.Columns("B").Delete()
$worksheet.Rows(2).Delete()
$worksheet.Range("G7").ClearContents()
复制代码