从Excel拉列 [英] Pulling Columns from Excel

问题描述

ExampleData

我正在尝试从Excel工作表中总共提取6列.我正在使用堆栈上另一篇文章中的修改后的脚本，它看起来像这样.

I'm trying to pull from a total of 6 columns from an excel worksheet. I'm using a modified script from another post on stack, it looks like this.

$strPath = "C:\Users\User\Documents\EXEL\fj.xls"
$AssetInv = "C:\Users\User\Documents\EXEL\fj.txt"

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

$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("Daily Price Quote - Better Of -")
$intRowMax =  ($worksheet.UsedRange.Rows).count
#$StartRow = 2                       
#$site = 1                         
#$state = 3                     
#$retailprice = 18              
#$yourprice = 20
#$SavingsTotal = 21

Write "`Site City      State      retailprice     yourprice    savingstotal" | Out-File $AssetInv
Write "--------------- ------------------- -------------------- -----------------------------" | 
      Out-FIle $AssetInv -Append

Write-Host "Processing: " $intRowMax "rows"

for ($intRow = 7 ; $intRow -le $intRowMax ; $intRow++) {
     $site = $worksheet.cells.item($intRow, 10).value2
     $city = $worksheet.cells.item($intRow, 2).value2
     $state = $worksheet.cells.item($intRow, 3).value2
     $retailprice = $worksheet.cells.item($intRow, 18).value2
     $yourprice = $worksheet.cells.item($intRow, 20).value2
$SavingsTotal = $worksheet.cells.item($intRow, 21).value2


     if (($site -ge 1 ))  {
         "{0, -15} {1, -30} {2, -25} {3, -25}" -f $site, $city, $state, $retailprice, $yourprice, $Savingstotal | 
          Out-File $AssetInv -Append
     }
 }

$objexcel.quit()

目前，我没有任何数据，之前我最多只能处理3条记录.关于我在做什么错的任何见解?

Currently I am pulling no data, earlier I had it working for a maximum of 3 records. Any insight as to what I am doing wrong?

推荐答案

好吧，我们将从您的代码开始获取所需的工作表.然后，我们将选择UsedRange以获取具有数据的单元格范围.然后，我们将通过ForEach循环运行该操作，因为前5行具有垃圾/标头信息，所以将跳过前5行.对于每一行，我们将创建一个新对象，其属性设置为关联的单元格(类似于您对所有变量所做的操作).所有这些对象将被收集到一个数组中，我们将其输出到CSV文件.如果您不喜欢CSV，则可以将其通过管道传输到Format-Table，然后通过管道传输到Out-File(可能必须通过管道传输到out-string，然后通过out-file ...这不是我经常做的事情).

Ok, we'll start with your code to get the worksheet we want. Then we'll select the UsedRange to get the range of cells that have data. Then we'll run that through a ForEach loop, skipping the first 5 rows since they have garbage/header info. For each row we'll make a new object, with properties set to the associated cell (kind of like what you do with all your variables). All of those objects will be collected in an array, which we output to a CSV file. If you don't like the CSV, you could pipe it to Format-Table, and pipe that to Out-File (might have to pipe to out-string, then out-file... it's not something I do often).

$strPath = "C:\Users\User\Documents\EXEL\fj.xls"
$AssetInv = "C:\Users\User\Documents\EXEL\fj.txt"

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

$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("Daily Price Quote - Better Of -")

$UsedRange = $worksheet.usedrange

$Data = ForEach($Row in ($UsedRange.Rows|Select -skip 5)){
    New-Object PSObject -Property @{
        'Site' = $Row.Cells.Item(1).Value2
        'City' = $Row.Cells.Item(2).Value2
        'State' = $Row.Cells.Item(3).Value2
        'Retail Price' = $Row.Cells.Item(18).Value2
        'Your Price' = $Row.Cells.Item(20).Value2
        'Total Savings' = $Row.Cells.Item(21).Value2
    }
}

$Data | Where{[int]::Parse($_.Site) -ge 1} | Select Site,City,State,'Retail Price','Your Price','Total Savings' | Export-Csv -NoTypeInformation -Path $AssetInv

$objExcel.quit()

好的一面是，您还剩下$ Data，这是您需要使用其他所有数据时要使用的所有数据(查找保存X％以上的项目或费用不到5美元或其他任何价格.

The plus side is that you also are left with $Data which is all the data you want to be able to use in case you need to do anything else with it (look for items that you save over X%, or items that cost less than $5 or whatever.

这篇关于从Excel拉列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！