用于从Excel工作簿中检索所有非空块内容的命令？(Command to retrieve all non

我有一个关于300行和10行大小的DataRepo表，大约300个这样的XLSX格式的Excel文件。 我需要读取每个Excel文件并将其存储为CSV（因为原始XLSX文件已被KeyError损坏，Python / R中的其他方法导致KeyError，除非使用Excel手动重新保存）。

我目前正在使用$Sheet.Cells.Item(row, col).Text来获取单个值作为文本但需要整个块：要么我需要在块上进行2循环并将其保存为CSV或找到一些就绪方法对于$Sheet ，任何现成的PowerShell方法都可用？ PowerShell中有哪些循环选项？

如何使用PowerShell检索Excel工作表中的所有非空内容？

$XLSDoc = 'C:\Users\hhh\Desktop\1.xlsx'
$SheetName = "DataRepo"
$Excel = New-Object -ComObject "Excel.Application"
$Workbook = $Excel.Workbooks.Open($XLSDoc)
$Sheet = $Workbook.Worksheets.Item($SheetName)

#Get data:
$Sheet.Cells.Item(1,2).Text
 
 我可以在PowerShell中执行类似于VBA的操作吗？ 
 
Dim i As Integer
Dim j As Integer
i = 1
j = 1

Do While i < 10
    Do While j < 10
        Sheet.Cells.Item(i, j).Text
        j = j + 1
    Loop
    i = i + 1
Loop
I have a sheet DataRepo about the size of 300 rows and 10 rows, about 300 such Excel files in XLSX format. I need to read each Excel file and store it as a CSV (because original XLSX files are corrupted with KeyError, other methods in Python/R resulting to KeyError unless resaving manually with Excel).
 
I am currently using $Sheet.Cells.Item(row, col).Text to get single value as text but the need over the whole block: either I need to 2-loop over the block and save it CSV or find some ready method for the $Sheet, any ready PowerShell method available? Which looping options available in PowerShell?
 
How can I retrieve all non-empty content in an Excel sheet with PowerShell?
 
$XLSDoc = 'C:\Users\hhh\Desktop\1.xlsx'
$SheetName = "DataRepo"
$Excel = New-Object -ComObject "Excel.Application"
$Workbook = $Excel.Workbooks.Open($XLSDoc)
$Sheet = $Workbook.Worksheets.Item($SheetName)

#Get data:
$Sheet.Cells.Item(1,2).Text
 
Can I do something similar to VBA in PowerShell?
 
Dim i As Integer
Dim j As Integer
i = 1
j = 1

Do While i < 10
    Do While j < 10
        Sheet.Cells.Item(i, j).Text
        j = j + 1
    Loop
    i = i + 1
Loop

                
最满意答案
                
                    
                        
 使用类似的东西将每个工作表导出到单独的CSV： 
 
$wbName = $Workbook.Name
$wbPath = $Workbook.Path
$Workbook.Worksheets | ForEach-Object {
  $csvName = Join-Path $wbPath ('{0}_{1}.csv' -f $wbName, $_.Name)
  $_.SaveAs($csvName, 6)
}
Use something like this to export each worksheet to a separate CSV:
 
$wbName = $Workbook.Name
$wbPath = $Workbook.Path
$Workbook.Worksheets | ForEach-Object {
  $csvName = Join-Path $wbPath ('{0}_{1}.csv' -f $wbName, $_.Name)
  $_.SaveAs($csvName, 6)
}