powershell对excel操作文档的一些基础性操作

powershell对excel操作文档的一些基础性操作：

我的windows电脑上有一个excel文档的目录为"C:\test.xlsx"，我现在要对它执行以下操作：

①在页面底端插入页码，格式为 “当前第X页 / 共X页”，居中
②将$3:$3设置为打印标题

③将B6：C10区域的字体颜色设置为红色

④echo当前所打开的excel文档的文件名（包括完整文件名、扩展名、仅文件名）

⑤获取指定目录C:\myExcel下所有的excel文档，并将其转换为同名的PDF文档，然后保存在同一目录下。

@BitGPT

一、如何通过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

“Devil组”引证GPT后的撰写：
对excel文档的一些基础性操作的PowerShell代码：

① 在页面底端插入页码，格式为“当前第X页 / 共X页”，居中

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open("C:\test.xlsx")
$Worksheet = $Workbook.Worksheets.Item(1)
$TotalPages = $Worksheet.PageSetup.Pages.Count

for ($i=1; $i -le $TotalPages; $i++) {
    $PageNumber = "Page " + $i + " of " + $TotalPages
    $Worksheet.PageSetup.CenterFooter = $PageNumber
}

$Workbook.Save()
$Excel.Quit()

② 将$3:$3设置为打印标题

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open("C:\test.xlsx")
$Worksheet = $Workbook.Worksheets.Item(1)
$Worksheet.PageSetup.PrintTitleRows = "$3:$3"

$Workbook.Save()
$Excel.Quit()

③ 将B6：C10区域的字体颜色设置为红色

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open("C:\test.xlsx")
$Worksheet = $Workbook.Worksheets.Item(1)
$Range = $Worksheet.Range("B6:C10")

$Range.Font.ColorIndex = 3

$Workbook.Save()
$Excel.Quit()

④ echo当前所打开的excel文档的文件名（包括完整文件名、扩展名、仅文件名）

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open("C:\test.xlsx")

Write-Host "File name: $($Workbook.FullName)"
Write-Host "File name without extension: $($Workbook.Name)"

$Workbook.Save()
$Excel.Quit()

⑤ 获取指定目录C:\myExcel下所有的excel文档，并将其转换为同名的PDF文档，然后保存在同一目录下。

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false

$FolderPath = "C:\myExcel"
$ExcelFiles = Get-ChildItem $FolderPath -Filter *.xlsx

foreach ($ExcelFile in $ExcelFiles) {
    $Workbook = $Excel.Workbooks.Open($ExcelFile.FullName)
    $PdfFilePath = $ExcelFile.FullName -replace "\.xlsx$", ".pdf"
    $Workbook.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, $PdfFilePath)
    $Workbook.Close()
}

$Excel.Quit()

该回答引用自ChatGPT

①在页面底端插入页码，格式为 “当前第X页 / 共X页”，居中

#打开Excel文档
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\test.xlsx")
#获取工作表和页数
$worksheet = $workbook.Worksheets.Item(1)
$pageCount = $worksheet.PageSetup.Pages.Count
#在每个页面的底部插入页码
for($i=1; $i -le $pageCount; $i++){
    $worksheet.PageSetup.LeftFooter = "当前第" + $i + "页 / 共" + $pageCount + "页"
    $worksheet.PageSetup.CenterFooter = ""
    $worksheet.PageSetup.RightFooter = ""
}
#保存并关闭Excel文档
$workbook.Save()
$workbook.Close()
$excel.Quit()

②将$3:$3设置为打印标题

#打开Excel文档
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\test.xlsx")
#获取工作表和要设置为打印标题的区域
$worksheet = $workbook.Worksheets.Item(1)
$range = $worksheet.Range("A3:Z3")
#设置为打印标题
$range.Rows(1).Select()
$excel.ActiveWindow.SelectedSheets.PageSetup.PrintTitleRows = "$3:$3"
#保存并关闭Excel文档
$workbook.Save()
$workbook.Close()
$excel.Quit()

③将B6：C10区域的字体颜色设置为红色

#打开Excel文档
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\test.xlsx")
#获取工作表和要设置字体颜色的区域
$worksheet = $workbook.Worksheets.Item(1)
$range = $worksheet.Range("B6:C10")
#设置字体颜色为红色
$range.Font.ColorIndex = 3
#保存并关闭Excel文档
$workbook.Save()
$workbook.Close()
$excel.Quit()

④echo当前所打开的excel文档的文件名（包括完整文件名、扩展名、仅文件名）

#打开Excel文档
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\test.xlsx")
#输出文件名
Write-Host $workbook.FullName    #完整文件名和扩展名
Write-Host $workbook.Name       #仅文件名
#保存并关闭Excel文档
$workbook.Save()
$workbook.Close()
$excel.Quit()

⑤获取指定目录C:\myExcel下所有的excel文档，并将其转换为同名的PDF文档，然后保存在同一目录下。

#获取C:\myExcel目录下的所有Excel文档
$excelFiles = Get-ChildItem "C:\myExcel" -Filter *.xlsx
#打开每个Excel文档并将其转换为PDF
foreach($file in $excelFiles){
    #打开Excel文档
    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.Open($file.FullName)
    #转换为PDF并保存
    $pdfPath = $file.FullName.Replace(".xlsx", ".pdf")
    $workbook.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, $pdfPath)
    #关闭Excel文档
    $workbook.Close()
    $excel.Quit()
}

参考chatGPT的内容和自己的思路，以下是针对您提出的五个要求的详细代码：

1.在页面底端插入页码，格式为“当前第X页 / 共X页”，居中

# 加载Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# 打开测试文档
$workbook = $excel.Workbooks.Open("C:\test.xlsx")

# 获取工作表对象
$worksheet = $workbook.Worksheets.Item(1)

# 获取页脚对象
$footer = $worksheet.PageSetup.CenterFooter

# 设置页码格式
$footer.Text = "当前第 &[Page] 页 / 共 &[Pages] 页"

# 设置页码居中
$footer.CenterHorizontally = $true

# 保存并关闭文档
$workbook.Save()
$workbook.Close()

# 退出Excel
$excel.Quit()

2.将$3:$3设置为打印标题

# 加载Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# 打开测试文档
$workbook = $excel.Workbooks.Open("C:\test.xlsx")

# 获取工作表对象
$worksheet = $workbook.Worksheets.Item(1)

# 设置打印标题为第3行
$worksheet.PageSetup.PrintTitleRows = "$3:$3"

# 保存并关闭文档
$workbook.Save()
$workbook.Close()

# 退出Excel
$excel.Quit()

3.将B6：C10区域的字体颜色设置为红色

# 加载Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# 打开测试文档
$workbook = $excel.Workbooks.Open("C:\test.xlsx")

# 获取工作表对象
$worksheet = $workbook.Worksheets.Item(1)

# 获取B6:C10区域的Range对象
$range = $worksheet.Range("B6:C10")

# 设置字体颜色为红色
$range.Font.ColorIndex = 3

# 保存并关闭文档
$workbook.Save()
$workbook.Close()

# 退出Excel
$excel.Quit()

4.输出当前所打开的Excel文档的文件名（包括完整文件名、扩展名、仅文件名）

# 加载Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# 打开测试文档
$workbook = $excel.Workbooks.Open("C:\test.xlsx")

# 输出文件名（包括完整文件名、扩展名、仅文件名）
Write-Host "完整文件名：$($workbook.FullName)"
Write-Host "扩展名：$($workbook.Name.Split(".")[-1])"
Write-Host "仅文件名：$($workbook.Name.Split(".")[0])"

# 关闭文档
$workbook.Close()

# 退出Excel
$excel.Quit()

5.获取指定目录C:\myExcel下所有的Excel文档，并将其转换为同名的PDF文档，然后保存在同一目录下

# 加载Excel对象
$excel = New-Object -ComObject Excel.Application

# 设置Excel对象不可见
$excel.Visible = $false

# 获取指定目录下的所有Excel文件
$excelFiles = Get-ChildItem "C:\myExcel" -Filter *.xlsx

# 循环处理每个Excel文件
foreach ($file in $excelFiles) {
    # 打开Excel文档
    $workbook = $excel.Workbooks.Open($file.FullName)

    # 获取文件名（不包含扩展名）
    $fileName = $file.BaseName

    # 设置PDF文件名
    $pdfName = "$fileName.pdf"

    # 转换为PDF格式
    $workbook.ExportAsFixedFormat([Microsoft.Office.Interop.Excel.XlFixedFormatType]::xlTypePDF, `
                                    $pdfName)

    # 关闭Excel文档
    $workbook.Close()

    # 输出转换成功信息
    Write-Host "已将 $fileName.xlsx 转换为 $pdfName"
}

# 退出Excel对象
$excel.Quit()

以上代码会在指定目录下查找所有.xlsx格式的Excel文件，并循环处理每个文件，将其转换为同名的.pdf文件，最后输出转换成功的信息。需要注意的是，在转换为PDF格式前，需要先打开Excel文档，并将其保存，否则无法进行转换。

回答不易，还请能采纳！！！

感谢各位朋友，明明赏金不多你们却还写的那么认真，无奈答案只能采纳一个人的，其余的朋友我也给你们一一打赏了，请别嫌弃。