可以通过以下步骤完成:
$excel = New-Object -ComObject Excel.Application
$excel.Workbooks.Open()
方法打开每个工作簿。如果工作簿有密码保护,可以在打开工作簿时提供密码:$workbook1 = $excel.Workbooks.Open("path\to\workbook1.xlsx")
$workbook2 = $excel.Workbooks.Open("path\to\workbook2.xlsx", 0, $false, $true, "password")
$workbook.Worksheets.Item()
方法选择要复制的工作表,并使用$worksheet.Copy()
方法复制到新的工作簿中:$worksheet1 = $workbook1.Worksheets.Item(1)
$worksheet2 = $workbook2.Worksheets.Item(1)
$worksheet1.Copy($newWorkbook.Worksheets.Item($newWorkbook.Worksheets.Count))
$worksheet2.Copy($newWorkbook.Worksheets.Item($newWorkbook.Worksheets.Count))
$newWorkbook.SaveAs()
方法保存新的工作簿,并使用$workbook.Close()
方法关闭所有工作簿:$newWorkbook.SaveAs("path\to\newWorkbook.xlsx")
$newWorkbook.Close()
$workbook1.Close()
$workbook2.Close()
完整的Powershell脚本如下:
$excel = New-Object -ComObject Excel.Application
$workbook1 = $excel.Workbooks.Open("path\to\workbook1.xlsx")
$workbook2 = $excel.Workbooks.Open("path\to\workbook2.xlsx", 0, $false, $true, "password")
$newWorkbook = $excel.Workbooks.Add()
$worksheet1 = $workbook1.Worksheets.Item(1)
$worksheet2 = $workbook2.Worksheets.Item(1)
$worksheet1.Copy($newWorkbook.Worksheets.Item($newWorkbook.Worksheets.Count))
$worksheet2.Copy($newWorkbook.Worksheets.Item($newWorkbook.Worksheets.Count))
$newWorkbook.SaveAs("path\to\newWorkbook.xlsx")
$newWorkbook.Close()
$workbook1.Close()
$workbook2.Close()
$excel.Quit()
这样,多个工作簿中的工作表就会合并为一个新的工作簿的工作表。你可以根据实际需求修改脚本中的路径和文件名。
腾讯技术开放日
DB・洞见
Elastic 中国开发者大会
技术创作101训练营
云+社区技术沙龙[第27期]
云+社区技术沙龙 [第30期]
云+社区技术沙龙[第10期]
领取专属 10元无门槛券
手把手带您无忧上云