Автор Тема: [Powershell] Помогите найти последний элемент в столбце Excel  (Прочитано 10252 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
собственно суть такая:

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

Оффлайн shs

  • Модератор
  • Ветеран
  • *****
  • Сообщений: 4401
  • Рейтинг: 89
    • Просмотр профиля
    • ShS's blog
  • Откуда: Default city
собственно суть такая:

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

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

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
опишу понятнее

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

$a = ...

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

предположим в столбце 500 строк, количество которых может меняться
как вот найти $a чтоб не задавать его руками?

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
собственно суть такая:

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

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

ммм, чот солнце меня совсем растопило... как? ::)

Оффлайн shs

  • Модератор
  • Ветеран
  • *****
  • Сообщений: 4401
  • Рейтинг: 89
    • Просмотр профиля
    • ShS's blog
  • Откуда: Default city
ммм, чот солнце меня совсем растопило... как?
help about_while


Updated: 27 July 2012, 13:54:44

while (!$содержимое_ячейки=читаем_значение) {
...
}

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
угу, понял, сейчас попробую


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
?
« Последнее редактирование: 27 июля 2012, 13:40:24 от Opium »

Оффлайн E

  • Новичок
  • *
  • Сообщений: 3
  • Рейтинг: 0
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Russia
У страницы Excel(Worksheet) есть свойство UsedRange, возвращающее все использованные на данный момент ячейки.
Activesheet.UsedRange.Rows.Count - число строк на активной странице;
Activesheet.UsedRange.Columns.Count - число столбцов на активной странице;

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
E, спасибо большое=)

Оффлайн shs

  • Модератор
  • Ветеран
  • *****
  • Сообщений: 4401
  • Рейтинг: 89
    • Просмотр профиля
    • ShS's blog
  • Откуда: Default city
угу, понял, сейчас попробую


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 может указывать на более широкий диапазон, нежели диапазон заполненный нужными данными

Оффлайн ЗиС

  • Новичок
  • *
  • Сообщений: 2
  • Рейтинг: 0
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Корусант
Used range может указывать на более широкий диапазон, нежели диапазон заполненный нужными данными
надо сделать рассылку по пятистам почтовым адресам, которые находятся в первом столбце
Именно по этой причине я бы скопировал диапазон из 500 ячеек, размещенных в одном столбце, в новую книгу Excel и именно в ней запустил макрос. Потому что если записать в ячейке Z9999 значение, а потом стереть - Used Range должен остаться излишне большим.

Оффлайн Kazun

  • Пользователь
  • **
  • Сообщений: 73
  • Рейтинг: 9
    • Просмотр профиля
  • Откуда: Иваново
$ex = new-object -com Excel.Application
$wb = $ex.Workbooks.Open("D:\12.xlsx")
$ws = $wb.Worksheets.Item(1)
$ws.Range("A65536").End(-4162).Row

Оффлайн shs

  • Модератор
  • Ветеран
  • *****
  • Сообщений: 4401
  • Рейтинг: 89
    • Просмотр профиля
    • ShS's blog
  • Откуда: Default city
Именно по этой причине я бы скопировал диапазон из 500 ячеек, размещенных в одном столбце, в новую книгу Excel и именно в ней запустил макрос.
Можно не копировать, можно просто поименовать нужный диапазон. Но все это лишние действия. Зачем это делать, когда можно просто проверять очередное считанное значение на пустоту или на патерн email-адреса? ;)

Онлайн Opium

  • Старожил
  • ****
  • Сообщений: 507
  • Рейтинг: 19
  • Пол: Мужской
    • Просмотр профиля
  • Откуда: Мск
$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

всё, ничего не понимаю
« Последнее редактирование: 01 августа 2012, 13:35:56 от Opium »

Оффлайн shs

  • Модератор
  • Ветеран
  • *****
  • Сообщений: 4401
  • Рейтинг: 89
    • Просмотр профиля
    • ShS's blog
  • Откуда: Default city
вдогонку

почему вот этот код отрабатывается только и исключительно 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 и цикл не выполнится.

Оффлайн hanza

  • Новичок
  • *
  • Сообщений: 1
  • Рейтинг: 0
    • Просмотр профиля
  • Откуда: Чебоксары
Долго воевал с этой задачей. Нашел такое решение:

$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 - поиск в обратном порядке)