Zilele acestea sunt foarte aglomerate pe la serviciu. O mulțime de cerințe și problemuțe care trebuie rezolvate cât mai pe loc posibil. Dar între zeci de emailuri a apărut unul care mi-a atras atenția.
Păi putem să refuzăm noi un ajutor în legătură cu Excelul? Păi nu. Așa că lăsăm totul deoparte și ne apucăm de treabă.
Descriere problemei
Se dorește ca în fiecare dimineață, datele agregate dintr-un fișier Excel să fie trimise prin email la o anumită oră către o anumită adresă de distribuție. Soluțiile existente pe internet sunt în mare parte cu VBA și o serie de operațiuni manuale, care implică deschiderea fișierelor. La altele nu m-am uitat, dar cred că se pot face și cu ceva automatizări din Power Automate. Dar cum nu toată lumea are subscripție de cloud, m-am gândit la Task Scheduler-ul de Windows. Și cum nu sunt nici fan VBA nici prea multe despre programare nu știu, m-am decis să abordez problema din PowerShell pe care-l știu de pe vremea când eram administrator de rețea și actualmente îl folosesc în administrarea de SharePoint.
Propunerea de soluție
Propun un exemplu de fișier Excel care conține o funcție de generare aleatorie de numere:
Funcția =RANDBETWEEN(1;49) generează numere aleatorii în cazul meu între 1 și 49 iar în C3 fac suma lor. Orice celulă se modifică se schimbă și numerele și suma lor.
Scriptul de refresh silent a fișierului.
Acest script presupune că aveți deja instalat pachetul Microsoft Excel pe calculator. Cu acest script se deschide fișierul din calea specificată în secțiunea 2. După care accesează prima foaie de calcul (3) și execută refresh la date (4), salvează (5), închide fișierul (6) și Excelul (7) după care eliberează memoria de obiecte (8).
# Actualizare silent date fisier Excel
# Author: Valy Greavu
# Date: 13.02.2024
# Se recomandă utilizarea în Task Scheduler pentru refresh fișiere din când în când la noile date.
# 1. Se crează o instanță Excel
$excel = New-Object -ComObject Excel.Application
# 2. Se deschide fișierul Excel. Atentie la calea fisierului.
$workbook = $excel.Workbooks.Open("P:\Temp\FisierExcel.xlsx")
# 3. Se activează prima foaie de lucru din Excel în care sunt datele care se actualizează.
# Item(2) inseamna a doua foaie de lucru in ordine.
$worksheet = $workbook.Sheets.Item(1)
$worksheet.Activate()
# 4. Se realizează refresh la date din diferite surse sau se recalculează formulele și funcțiile
$workbook.RefreshAll()
# 5. Se salvează modificările
$workbook.Save()
# 6. Se închide fișierul Excel
$workbook.Close()
# 7. Se închide aplicația Excel creată la pasul 1.
$excel.Quit()
# 8. Se eliberează obiectele COM
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel, workbook, worksheet
La fiecare rulare datele din Excel se reîmprospătează. O condiție importantă este ca acel fișier Excel să fie închis.
Scriptul de trimitere a datelor din Excel prin email
În acest script se preiau datele din fișierul Excel specificat la secțiunea 3. Înainte de preluare date ar trebui verificat dacă există sau nu instalat modulul ImportExcel în PowerShell. Dacă nu este se instalează cu linia comentată din secțiunea 1.
În acest script preiau valorile din celulele A2 și C3 (prima generată aleatoriu și suma tuturor valorilor). Specificul versiunii curente a modului Import-Excel este că trebuie să specificăm coloanele și liniile de start și de final în formatul R1C1.
În modelul meu numele foii de calcule este Sheet1.
# Preluare date din Excel și trimitere email # Author: Valy Greavu # Date: 13.02.2024 # Se recomandă rulare script Refresh fisier Excel silent.ps1 înainte de trimitere email pentru actualizare date. # Se recomandă utilizarea în Task Scheduler pentru trimitere email la o anumită dată. # 1. Se instalează modulul Import-Excel dacă nu este deja instalat. # Operatiunea se realizeaza o singura data pe un calculator. # În acest script linia de install este comentată # Install-Module -Name ImportExcel -Scope CurrentUser -Force # 2. Se importă modulul Import-Excel Import-Module ImportExcel # 3. Se specifică calea către fișierul Excel din care dorim preluarea datelor $filePath = "P:\Temp\FisierExcel.xlsx" # 4. Extrage valorile din anumite celule din foaia de calcul Sheet1 în acest exemplu celulele A2 și C3. # Se pot extrage și blocuri de valori $cellValue1 = (Import-Excel -Path $filePath -StartColumn 1 -StartRow 2 -NoHeader -EndRow 2 -EndColumn 1 -WorksheetName Sheet1).P1 # Celula A2 $cellValue2 = (Import-Excel -Path $filePath -StartColumn 3 -StartRow 3 -NoHeader -EndRow 3 -EndColumn 3 -WorksheetName Sheet1).P1 # Celula C3 # 5. Se construiește mesajul de email # În acest exemplu trimit un email din Office 365 către Outlook.com $subject = "Valori din Excel" $body = "Buna ziua,`n`nValoarea din A2: $cellValue1`nSuma numerelor din C3: $cellValue2`n`nO zi buna!" $to = "valy.greavu@outlook.com" $emailAddress = "valy.greavu@feaa.uaic.ro" # 6. Se specifică datele serverului SMTP al Outlook.com și portul # Trebuie văzut la fiecare serviciu care sunt specificațiile de SMTP # Trebuie văzut dacă serviciul utilizat suportă modern authentication (two factor authentication) pentru a compune credențialele de acces $smtpServer = "smtp.office365.com" $smtpPort = 587 # 7. În cazul în care nu automatizăm prin Task Scheduler putem folosi Prompt pentru parolă $securePassword = Read-Host -Prompt "Introdu parola" -AsSecureString # 7.1. Operatiune nerecomandata pentru ca salveaza parola in script in cazul in care dorim sa utilizam cu Task Scheduled dintr-un mediu securizat # $password = "*************" # se trece parola în clear sau # $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailAddress, (ConvertTo-SecureString -String $password -AsPlainText -Force) # 7.2. Crearea obiectului de tip credențiale de autentificare pentru serviciul de email specificat. # O altă verrsiune non-auto este să folosim comanda Get-Credential $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailAddress, $securePassword # 8. Trimiterea emailului. # În cazul în care avem mai multe adrese la care trebuie să trimitem putem să le importăm dintr-un CSV și să folosim foreach Send-MailMessage -To $to -From $emailAddress -Subject $subject -Body $body -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Credential $credential # Final script
Problemele mai mari care pot apărea sunt legate de serviciul de email utilizat pentru trimiterea datelor și tipul de autentificare suportat de SMTP. În exemplul meu folosesc un cont de Office 365 pentru a trimite datele. Sunt mai multe metode de a obține acel $credential corect, în funcție de furnizorul de email. Unele sisteme suportă inclusiv two factor authetication.
Personal nu recomand secțiunea 7.1. de salvare a parolei direct în script, dar este o opțiune pentru automatizare mai ales prin Task Scheduler.
Definirea task-urilor în Task scheduler
La final pentru automatizare se creează două task-uri noi în Excel cu programare dorită în cazul meu zilnic.
Și acțiunea de rulare a PowerShell și calea către fișier.
Cam asta ar fi. Sper să fie util cuiva.
Valy Greavu - Live Blog 