Learning how to code

Quan Tong

"Life is all about sharing. If we are good at something, pass it on." - Mary Berry

Learning how to code

2023-01-09

If you find yourself doing the same task repeatedly, consider learning how to code.

For instance, a friend of mine had to perform the following tasks on daily basis:

logging into a internal portal
selecting certain menus
exporting some .xlsx reports
opening various excel files
clicking on the “Refresh All” button to update external data
sending an email

Here’s the code that I assisted him with: https://github.com/quantonganh/ims

The initial steps can be done by using chromedp. To help my friend overcome the first obstacle, I explored various Golang libraries, such as:

the ones designed for working with .xlsx: https://github.com/qax-os/excelize
or calling COM: https://github.com/go-ole/go-ole

Unfortunately, none of these libraries provided the effective in addressing the issue.

Finally, I discovered that using a powershell script to refresh the data:

 1$Excel=New-Object -ComObject Excel.Application
 2$Excel.Visible = $true
 3
 4for ($i=0; $i -lt $($args.Length - 1); $i++)
 5{
 6    $inputWb = $Excel.Workbooks.Open($($args[$i]))
 7}
 8
 9$outputWb=$Excel.Workbooks.Open($($args[$($args.Length - 1)]))
10$outputWb.RefreshAll()
11While (($outputWb.Sheets | ForEach-Object {$_.QueryTables | ForEach-Object {if($_.QueryTable.Refreshing){$true}}}))
12{
13    Start-Sleep -Seconds 1
14}
15$outputWb.Save()
16$outputWb.Close()
17$Excel.Quit()

as well as calling this from Golang was an effective solution.

The second challenge involved the inability to use the internal Wi-Fi network to send emails due to ISP blocking port 587. In order to address this issue, I utilized the netsh command to switch to a different Wi-Fi network that could be used to send the email, and then switch back.

Additionally, I employed a technique to ensure a connection to the SMTP port prior to sending the email:

1		retry(time.Second, 30*time.Second, func() bool {
2			address := net.JoinHostPort(conf.SMTP.Host, strconv.Itoa(conf.SMTP.Port))
3			log.Printf("connecting to the %s", address)
4			_, err := net.Dial("tcp", address)
5			if err == nil {
6				return true
7			}
8			return false
9		})

 1func retry(interval, timeout time.Duration, f func() bool) {
 2	ticker := time.NewTicker(interval)
 3	defer ticker.Stop()
 4	to := time.NewTimer(timeout)
 5	defer to.Stop()
 6	for {
 7		select {
 8		case <-ticker.C:
 9			if f() {
10				return
11			}
12		case <-to.C:
13			log.Info().Msgf("timed out after %s", timeout.String())
14			return
15		}
16	}
17}

Tags: golang chromedp powershell

Edit on GitHub