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:

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:

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

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

$Excel=New-Object -ComObject Excel.Application
$Excel.Visible = $true

for ($i=0; $i -lt $($args.Length - 1); $i++)
{
    $inputWb = $Excel.Workbooks.Open($($args[$i]))
}

$outputWb=$Excel.Workbooks.Open($($args[$($args.Length - 1)]))
$outputWb.RefreshAll()
While (($outputWb.Sheets | ForEach-Object {$_.QueryTables | ForEach-Object {if($_.QueryTable.Refreshing){$true}}}))
{
    Start-Sleep -Seconds 1
}
$outputWb.Save()
$outputWb.Close()
$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:

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

func retry(interval, timeout time.Duration, f func() bool) {
	ticker := time.NewTicker(interval)
	defer ticker.Stop()
	to := time.NewTimer(timeout)
	defer to.Stop()
	for {
		select {
		case <-ticker.C:
			if f() {
				return
			}
		case <-to.C:
			log.Info().Msgf("timed out after %s", timeout.String())
			return
		}
	}
}

Tags: golang chromedp powershell

Edit on GitHub
Previous