I have a powershell script that opens an excel file, re-assigns a blank password, and then saves the file. I want to add a task to the script to remove the first 4 rows of the excel file before saving it.
我有一个powershell脚本打开一个excel文件，重新分配一个空白密码，然后保存该文件。我想在脚本中添加一个任务，以便在保存之前删除excel文件的前4行。
1 个解决方案
#1
6
You can't use OleDB for deleting data from Excel document. As per MSDN docmentation:
您不能使用OleDB从Excel文档中删除数据。根据MSDN docmentation：
Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operation
虽然Jet OLE DB提供程序允许您在Excel工作簿中插入和更新记录，但它不允许DELETE操作
What you can do is use Exel's COM interface to delete rows. Remember to release COM object too. Like so,
你可以做的是使用Exel的COM接口删除行。记得也要释放COM对象。像这样，
$file = c:\temp\MyExcelFile.xls
# Star Excel, hide window
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM
#1
6
You can't use OleDB for deleting data from Excel document. As per MSDN docmentation:
您不能使用OleDB从Excel文档中删除数据。根据MSDN docmentation：
Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operation
虽然Jet OLE DB提供程序允许您在Excel工作簿中插入和更新记录，但它不允许DELETE操作
What you can do is use Exel's COM interface to delete rows. Remember to release COM object too. Like so,
你可以做的是使用Exel的COM接口删除行。记得也要释放COM对象。像这样，
$file = c:\temp\MyExcelFile.xls
# Star Excel, hide window
$excel = new-object -com Excel.Application -Property @{Visible = $false}
$workbook = $excel.Workbooks.Open($file) # Open the file
$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet
[void]$sheet.Cells.Item(1, 1).EntireRow.Delete() # Delete the first row
$workbook.Close($true) # Close workbook and save changes
$excel.quit() # Quit Excel
[Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # Release COM