在Powershell中阅读Excel表格 [英] Read Excel sheet in Powershell
问题描述
下面的脚本读取Excel文档的工作表名称....
我如何改进它,以便它可以提取列B的所有内容(启动从行5 - 所以第1-4行被忽略)在每个工作表中并创建一个对象?
例如如果工作表1(称为伦敦)中的列B具有以下值:
Marleybone
Paddington
维多利亚
Hammersmith
和工作表2中的列C(称为)诺丁汉具有以下值: / p>
Alverton
Annesley
Arnold
Askham
我想创建一个如下所示的对象:
城市,面积
伦敦,Marleybone
伦敦,帕丁顿
伦敦维多利亚州
伦敦,Hammersmith
诺丁汉,阿尔卑斯特
诺丁汉,Annesley
诺丁汉,阿诺德
诺丁汉,Askham
这是我的代码所有
sheetname = @()
$ excel = new-object -com excel.application
$ wb = $ excel.workbooks.open(c:\users\administrator\my_test.xls)
($ i = 1 ; $ i -le $ wb.sheets.count; $ i ++)
{
$ sh eetname + = $ wb.Sheets.Item($ⅰ)请将.Name;
}
$ sheetname
这假设内容位于每个工作表的B列中(因为不清楚如何确定每个工作表上的列),而该列的最后一行也是工作表的最后一行。
$ xlCellTypeLastCell = 11
$ startRow,$ col = 5,2
$ excel = new -object -com excel.application
$ wb = $ excel.workbooks.open(c:\users\administrator\my_test.xls)
($ i = 1; $ i -le $ wb.sheets.count; $ i ++){
$ sh = $ wb.Sheets.Item($ i)
$ endRow = $ sh.UsedRange.SpecialCells($ xlCellTypeLastCell ).Row
$ city = $ sh.Cells.Item($ startRow,$ col).Value2
$ rangeAddress = $ sh.Cells.Item($ startRow + 1,$ col).Address )+:+ $ sh.Cells.Item($ endRow,$ col).Address()
$ sh.Range($ rangeAddress).Value2 | foreach {
New-Object PSObject -Property @ {City = $ city; Area = $ _}
}
}
$ excel.Workbooks.Close()
The below script reads the sheet names of an Excel document....
How could I improve it so it could extract all the contents of column B (starting from row 5 - so row 1-4 are ignored) in each worksheet and create an object?
E.g. if column B in worksheet 1 (called London) has the following values:
Marleybone
Paddington
Victoria
Hammersmith
and column C in worksheet 2 (called) Nottingham has the following values:
Alverton
Annesley
Arnold
Askham
I'd want to create a object that from that looks like this:
City,Area
London,Marleybone
London,Paddington
London,Victoria
London,Hammersmith
Nottingham,Alverton
Nottingham,Annesley
Nottingham,Arnold
Nottingham,Askham
This is my code so far:
clear all
sheetname = @()
$excel=new-object -com excel.application
$wb=$excel.workbooks.open("c:\users\administrator\my_test.xls")
for ($i=1; $i -le $wb.sheets.count; $i++)
{
$sheetname+=$wb.Sheets.Item($i).Name;
}
$sheetname
This assumes that the content is in column B on each sheet (since it's not clear how you determine the column on each sheet.) and the last row of that column is also the last row of the sheet.
$xlCellTypeLastCell = 11
$startRow,$col=5,2
$excel=new-object -com excel.application
$wb=$excel.workbooks.open("c:\users\administrator\my_test.xls")
for ($i=1; $i -le $wb.sheets.count; $i++){
$sh=$wb.Sheets.Item($i)
$endRow=$sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
$city=$sh.Cells.Item($startRow,$col).Value2
$rangeAddress=$sh.Cells.Item($startRow+1,$col).Address() + ":" +$sh.Cells.Item($endRow,$col).Address()
$sh.Range($rangeAddress).Value2 | foreach {
New-Object PSObject -Property @{City=$city;Area=$_}
}
}
$excel.Workbooks.Close()
这篇关于在Powershell中阅读Excel表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!