和下面的结果是基本一样的,只是下面链接是用WPS的JavaScript宏写的,这个是通过powershell调用COM来操控word和excel,语法和VBA是类似的
批量生成word模板
代码
# 打开word和excel进程
$word = New-Object -ComObject Word.Application
$excel = New-Object -ComObject excel.application
# 设置操作是否可见
$excel.visible = $false
$word.visible = $true
# 打开测试文档
#$WordFilePath = "C:\Users\Lenovo\Desktop\test.docx"
$ExcelFilePath = "C:\Users\Lenovo\Desktop\test.xlsx"
#$doc = $word.Documents.open($WordFilePath)
$doc=$word.documents.Add()
$workbook = $excel.Workbooks.open($ExcelFilePath)
# excel工作簿,读取行数
$worksheet = $workbook.Worksheets.item(1)
$row_num = $worksheet.UsedRange.Rows.Count
# 通过循环将第一列(id)输入到powershell的数组中,第二列摘要也是一样操作
$id_list = @()
$brief_list = @()
for ($i=2; $i -le $row_num; $i++)
{
$tmp1 = $worksheet.Cells.item($i,1).Text
$tmp2 = $worksheet.Cells.item($i,2).Text
$id_list += $tmp1
$brief_list += $tmp2
}
# 关闭工作簿退出excel程序
$workbook.close()
$excel.Quit()
# 设置标题1,标题2,折叠,分页符
$wdStyleHeading1 = -2
$wdStyleHeading2 = -3
$wdCollapseStart = 1
$wdSectionBreakNextPage = 2
for ($item=0; $item -lt $id_list.Length; $item++)
{
$selection=$word.Selection #光标选中内容
$selection.TypeText($id_list[$item]+'序号') #输入内容
$selection.Style = $wdStyleHeading1 #设置标题1
$selection.TypeParagraph() #键入回车
$selection.TypeText($brief_list[$item]+'序号')
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"特病单议申请")
$selection.Style = $wdStyleHeading2 #设置标题2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"出院记录")
$selection.Style = $wdStyleHeading2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"抢救记录")
$selection.Style = $wdStyleHeading2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"会诊记录")
$selection.Style = $wdStyleHeading2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"手术记录")
$selection.Style = $wdStyleHeading2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.TypeText($id_list[$item]+"特殊情况")
$selection.Style = $wdStyleHeading2
$selection.TypeParagraph()
$selection.TypeParagraph()
$selection.collapse($wdCollapseStart) #折叠避免内容被分页符替换
$selection.InsertBreak($wdSectionBreakNextPage) #插入分页符
}
$doc.SaveAs2("C:\Users\Lenovo\Desktop\result.docx")
$word.Quit()
Get-Process *wps* | stop-process
- 和上面用JavaScript写的宏比起来,在由于JavaScript宏并不完善,文档不全,不知道怎么样让word调用excel,但是在powershell中就没有这个问题,用powershell的数组为桥梁将excel的内容读取到数组中,在word中对数组的内容进行迭代操作