Форум системных администраторов

General => Главный => Тема начата: Opium от 27 Июля 2012, 12:14:37

Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 27 Июля 2012, 12:14:37
собственно суть такая:

есть файл эксель, с кучей данных
например, надо сделать рассылку по пятистам почтовым адресам, которые находятся в первом столбце.
через foreach всё понятно, но как задать последний элемент цикла? тоесть узнать количество итераций?
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: shs от 27 Июля 2012, 12:47:02
собственно суть такая:

есть файл эксель, с кучей данных
например, надо сделать рассылку по пятистам почтовым адресам, которые находятся в первом столбце.
через foreach всё понятно, но как задать последний элемент цикла? тоесть узнать количество итераций?
А зачем знать количество итераций?

Выполняй до первой пустой ячейки (или ячейки не содержащйе почтовый адрес).
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 27 Июля 2012, 12:47:38
опишу понятнее

если опустить всю лабуду по открытию файла, то надо организивать цикл

$a = ...

foreach ($i in $a)
{
#блок команд
}

предположим в столбце 500 строк, количество которых может меняться
как вот найти $a чтоб не задавать его руками?
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 27 Июля 2012, 12:48:05
собственно суть такая:

есть файл эксель, с кучей данных
например, надо сделать рассылку по пятистам почтовым адресам, которые находятся в первом столбце.
через foreach всё понятно, но как задать последний элемент цикла? тоесть узнать количество итераций?
А зачем знать количество итераций?

Выполняй до первой пустой ячейки (или ячейки не содержащйе почтовый адрес).

ммм, чот солнце меня совсем растопило... как? ::)
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: shs от 27 Июля 2012, 12:51:06
ммм, чот солнце меня совсем растопило... как?
help about_while


Updated: 27 July 2012, 13:54:44

while (!$содержимое_ячейки=читаем_значение) {
...
}
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 27 Июля 2012, 13:15:58
угу, понял, сейчас попробую


Updated: 27 July 2012, 14:40:24

я не догоняю объектную модель экселя
какое должно быть условие?

while ($a -ne $null), где, например,

$objexcel.visible = $true
$fp = "С:\test.xlsx"
$wb = $objexcel.Workbooks.Open($fp)
$ws = $wb.activesheet
$cell = $ws.Cells
$a = $cell.Item(1,1).text
?
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: E от 27 Июля 2012, 15:07:15
У страницы Excel(Worksheet) есть свойство UsedRange, возвращающее все использованные на данный момент ячейки.
Activesheet.UsedRange.Rows.Count - число строк на активной странице;
Activesheet.UsedRange.Columns.Count - число столбцов на активной странице;
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 27 Июля 2012, 17:04:29
E, спасибо большое=)
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: shs от 28 Июля 2012, 06:41:41
угу, понял, сейчас попробую


Updated: 27 July 2012, 14:40:24

я не догоняю объектную модель экселя
какое должно быть условие?

while ($a -ne $null), где, например,

$objexcel.visible = $true
$fp = "С:\test.xlsx"
$wb = $objexcel.Workbooks.Open($fp)
$ws = $wb.activesheet
$cell = $ws.Cells
$a = $cell.Item(1,1).text
?
в данном случае можно  проверять так:  while (!$a)  или while ($a match ".+@.+")
PS обрати внимание на то, что цикл while проверяет условие до тела цикла, а переменная, которую он проверяет получает своё значение внутри цикла.



Updated: 28 July 2012, 07:45:59

У страницы Excel(Worksheet) есть свойство UsedRange, возвращающее все использованные на данный момент ячейки.
Activesheet.UsedRange.Rows.Count - число строк на активной странице;
Activesheet.UsedRange.Columns.Count - число столбцов на активной странице;

Used range может указывать на более широкий диапазон, нежели диапазон заполненный нужными данными
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: ЗиС от 30 Июля 2012, 00:39:47
Used range может указывать на более широкий диапазон, нежели диапазон заполненный нужными данными
надо сделать рассылку по пятистам почтовым адресам, которые находятся в первом столбце
Именно по этой причине я бы скопировал диапазон из 500 ячеек, размещенных в одном столбце, в новую книгу Excel и именно в ней запустил макрос. Потому что если записать в ячейке Z9999 значение, а потом стереть - Used Range должен остаться излишне большим.
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Kazun от 30 Июля 2012, 08:51:32
$ex = new-object -com Excel.Application
$wb = $ex.Workbooks.Open("D:\12.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws.Range("A65536").End(-4162).Row
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: shs от 30 Июля 2012, 09:29:42
Именно по этой причине я бы скопировал диапазон из 500 ячеек, размещенных в одном столбце, в новую книгу Excel и именно в ней запустил макрос.
Можно не копировать, можно просто поименовать нужный диапазон. Но все это лишние действия. Зачем это делать, когда можно просто проверять очередное считанное значение на пустоту или на патерн email-адреса? ;)
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: Opium от 01 Августа 2012, 11:18:41
$ws.Range("A65536").End(-4162).Row

а что означает эта строка?

PS обрати внимание на то, что цикл while проверяет условие до тела цикла, а переменная, которую он проверяет получает своё значение внутри цикла.

пока обошёлся без цикла
примерно так

$objexcel.visible = $true
$fp = "f:\powershelltest\рассылка\adressbook.xlsx"
$filename = "f:\powershelltest\test.txt"
$wb = $objexcel.Workbooks.Open($fp)
$ws = $wb.activesheet
$cell = $ws.Cells
do
{
$b++;
$a = $cell.Item($b,1).text
$smtpserver = "smtp.gmail.com"
 $msg = new-object Net.Mail.MailMessage
 $att = new-object Net.Mail.Attachment($filename)
 $smtp = new-object Net.Mail.SmtpClient($smtpServer)
 $smtp.EnableSsl = $True
 $smtp.Credentials = New-Object System.Net.NetworkCredential("login", "pass"); # Put username without the @GMAIL.com or – @gmail.com
 $msg.From = "email_adress_name"
 $msg.To.Add($a)
 $msg.Subject = "Monthly Report"
 $msg.Body = "Good Morning, Last month’s LOGINS & GROUPCALLS FOR ALL GIDS IN SYSTEM IS ATTACHED"
 $msg.Attachments.Add($att)
 $smtp.Send($msg)
}
while($a -match ".+@.+")

собственно выскакивает такая ошибка

Цитировать
Исключение при вызове "Add" с "1" аргументами: "Параметр 'addresses' не может быть пустой строкой.
Имя параметра: addresses"
C:\Documents and Settings\xagen\Local Settings\Temp\4784243f-34d8-44d2-a250-60c34892edf5.ps1:19 знак:13
+  $msg.To.Add <<<< ($a)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
Исключение при вызове "Send" с "1" аргументами: "Необходимо указать получателя."
C:\Documents and Settings\xagen\Local Settings\Temp\4784243f-34d8-44d2-a250-60c34892edf5.ps1:23 знак:12
+  $smtp.Send <<<< ($msg)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

при приравнивании $b к единице вначале - тоже самое
с циклом пока запутался, поэтому просто его выкинул


Updated: 01 August 2012, 14:22:57

вдогонку

почему вот этот код отрабатывается только и исключительно 1 раз (использую PowerGUI)

$objexcel = New-Object -ComObject excel.application
$objexcel.visible = $true
$fp = "f:\powershelltest\рассылка\adressbook.xlsx"
$wb = $objexcel.Workbooks.Open($fp)
$ws = $wb.activesheet
$cell = $ws.Cells
$b = 1
While ($a -match ".+@.+")
{
$b++
foreach ($i in $b)
{
$a = $cell.Item($b,1).text
Write-Host $a
}
}


Updated: 01 August 2012, 14:35:56

всё, ничего не понимаю
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: shs от 02 Августа 2012, 08:49:47
вдогонку

почему вот этот код отрабатывается только и исключительно 1 раз (использую PowerGUI)


Код: [Выделить]
$objexcel = New-Object -ComObject excel.application
$objexcel.visible = $true
$fp = "f:\powershelltest\рассылка\adressbook.xlsx"
$wb = $objexcel.Workbooks.Open($fp)
$ws = $wb.activesheet
$cell = $ws.Cells
$b = 1
While ($a -match ".+@.+")
{
$b++
foreach ($i in $b)
{
$a = $cell.Item($b,1).text
Write-Host $a
}
}


всё, ничего не понимаю
Потому что перед входом в цикл ты проверяешь содержимое переменной $a на соответствие шаблону, однако к этому моменту переменная $a еще не инициализированна, в результате проверка вернет $false и цикл не выполнится.
Название: [Powershell] Помогите найти последний элемент в столбце Excel
Отправлено: hanza от 14 Августа 2020, 00:01:51
Долго воевал с этой задачей. Нашел такое решение:

$Table.UsedRange.Find("*", [Type]::Missing, [Type]::Missing, [Type]::Missing, [Type]::Missing, 2).Row()
поиск последней строки ("*" - любой элемент, поиск по строкам, по умолчанию, 2 - поиск в обратном порядке)

$Table.UsedRange.Find("*", [Type]::Missing, [Type]::Missing, [Type]::Missing, 2, 2).Column()
поиск последнего столбца ("*" - любой элемент, 2 - поиск по столбцам, 2 - поиск в обратном порядке)