PowerShell从Excel文件中选择单元格范围并转换为CSV [英] PowerShell select range of cells from Excel file and convert to CSV

问题描述

我想从Excel工作表中提取一定范围的单元格，并以CSV格式输出它们，以备将来使用.到目前为止，我已经写了这个:

I want to extract a range of cells from an Excel sheet and output them in CSV form for further use. So far I have written this:

script.ps1 :

$excel = New-Object -ComObject Excel.Application
$WB = $excel.Workbooks.Open('c:\users\me\desktop\temp\nouveau dossier\superstore.xls')
$WS = $WB.Sheets.Item(1)
$data = $WS.Range("A1", "E10")
$data | select text | Export-Csv 'YATry.csv' -NoTypeInformation -Delimiter ';'
$excel.Quit()

但是我得到的是这个

"Text"  
"Row ID"  
"Order ID"  
"Order Date"  
"Ship Date"  
"Ship Mode"  
"1"  
"CA-2016-152156"  
"2016-11-08"   
"2016-11-11"  
"Second Class"  
"2"  
"CA-2016-152156"  
"2016-11-08"  
"2016-11-11"  
"Second Class"  
"3"  
"CA-2016-138688"  
"2016-06-12"  
"2016-06-16"  
"Second Class"
[...]

我想念什么?

推荐答案

尝试使用此方法作为替代方法.我还没有在您的环境中尝试过此方法，但是类似的方法对我也有效.希望这会有所帮助.

Try this as an alternative method. I haven't tried this out in your environment, but something similar worked for me. Hope this helps.

$excel = New-Object -ComObject Excel.Application
$WB = 
  $excel.Workbooks.Open('c:\users\me\desktop\temp\nouveau dossier\superstore.xls')
$WS = $WB.Sheets.Item(1)
$data = $WS.Range("A1", "E10")
$XLcsv = 6       #  Parameter to tell SaveAs to produce a CSV format
$data.worksheet.SaveAs('c:\users\me\desktop\temp\nouveau dossier\YATry.csv', $XLcsv)
$excel.Quit()

SaveAs是Excel应用程序中的一种方法，可通过单击另存为"来执行交互式用户的操作.第二个参数告诉SaveAs生成什么格式，在这种情况下为CSV.

SaveAs is a method in the Excel application that does what the interactive user does by clicking 'Save As'. The second parameter tells SaveAs what format to generate, in this case CSV.

棘手的部分是，为了获得对SaveAs方法的访问权限，必须说$ data.worksheet而不是$ data.

The tricky part is figuring out that you have to say $data.worksheet instead of $data in order to gain access to the SaveAs method.

这篇关于PowerShell从Excel文件中选择单元格范围并转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！