I have blogged before about this Excel Nuget package where you don’t need to use interop and have Excel installed on the server. And my journey to start this Azure Function. This is because the most recent Excel format uses xml under the cover in a zipped file stored as a file with an xlsx extension. Since you do not have hard disk access in a serverless environment like Azure Functions you need to generate the Excel in memory (or store stuff in blob storage). I chose the in memory to leave no footprints or take up space in the cloud.

I wanted to use an Azure function to have it run in the cloud. Not being dependent on a Server which needs updates, reboots etc. Since the database already is in the Azure Cloud (Azure SQL) this seems a good/perfect fit.

I got the option to go for Azure function v1 or v2 which is in preview. So this was a nice opportunity to use the v2 and .Net Core/Standard. https://docs.microsoft.com/en-us/azure/azure-functions/functions-versions

The v2 also has support for the Office365 Graph. But that was out of (my) scope.

I took a timer based project because I wanted it to send an overview of invoices on a monthly basis. The Timer based project has a timer as data annotation based on CRON scheduling. There is however a small difference. Instead of 5 “fields” the Azure function has 6. It also let’s you schedule the seconds.

https://en.wikipedia.org/wiki/Cron#CRON_expression

So not just: minutes, hours, day of month, month, day of week, year but seconds, minutes, hours, day of month, month, day of week, year. Of course the order is really important. https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-timer#cron-expressions

I used this Nuget for the Excel export https://www.nuget.org/packages/EPPlus/

it has .Net Core support and will work perfectly.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Net.Mail;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using OfficeOpenXml;

namespace MonthlyMailInvoices
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static void Run([TimerTrigger("10 0 0 1 * *")]TimerInfo myTimer, TraceWriter log)
        {
            log.Info($"C# Timer trigger function executed at: {DateTime.Now}");

            var com = new SqlCommand("SELECT * FROM [dbo].[INVOICES] where invoicedate > @startdt and invoicedate < @enddt");

            com.Parameters.AddWithValue("startdt", DateTime.Now.AddMonths(-1));
            com.Parameters.AddWithValue("enddt", DateTime.Now.AddDays(-1));

            var dt = new DataTable();

            using (var con = new SqlConnection("connectionstring goes here"))
            {
                con.Open();
                com.Connection = con;
                var da = new SqlDataAdapter(com);
                da.Fill(dt);

                log.Info($"start: {DateTime.Now.AddMonths(-1)} and end { DateTime.Now.AddDays(-1) } gave {dt.Rows.Count}");
            }

            using (var wb = new ExcelPackage())
            {
                wb.Workbook.Worksheets.Add("Our company");
                var ws = wb.Workbook.Worksheets[0];

                FillData(ws, dt, "Our company B.V.");

                var msg = new MailMessage();
                msg.To.Add("mymail@companydomain.com");
                msg.Subject = "Montly invoices";
                msg.From = new MailAddress("the@cloud.com");
                msg.Body = $"Invoices from {DateTime.Now.AddMonths(-1)} to { DateTime.Now.AddDays(-1) } in the Excel attachment.";
                var ms = new MemoryStream(wb.GetAsByteArray());
                ms.Position = 0;

                //msg.Attachments.Add(new Attachment(ms, "Invoices.xlsx", "application/vnd.ms-excel"));
                msg.Attachments.Add(new Attachment(ms, "Invoices.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
                var smtp = new SmtpClient
                {
                    Host = "smtp.gmail.com",
                    Port = 587,
                    EnableSsl = true,
                    Credentials = new NetworkCredential("my@gmailaccount.com", "incorrectpassword")
                };
                smtp.Send(msg);
            }
        }

        private static void FillData(ExcelWorksheet ws, DataTable dt, string title)
        {
            ws.Cells[1, 1].Value = title;

            ws.Cells[2, 1].Value = "Invoice nr";
            ws.Cells[2, 2].Value = "Invoice date";
            ws.Cells[2, 3].Value = "Amount inc. VAT";
            ws.Cells[2, 4].Value = "VAT";
            ws.Cells[2, 5].Value = "Amount exc. VAT";

            int row = 3;

            foreach (DataRow dr in dt.Rows)
            {
                ws.Cells[row, 1].Value = dr[0].ToString();
                ws.Cells[row, 2].Value = dr[1].ToString();
                ws.Cells[row, 3].Value = dr[2].ToString();
                ws.Cells[row, 4].Value = dr[3].ToString();
                ws.Cells[row++, 5].Value = dr[4].ToString();
            }
        }
    }
}

I could not test it locally because I had some issues with logins for my localdb. So I hit publish to deploy it on Azure. However republishing failed. I found the answer (as always) on StackOverflow. I had to add “MSDEPLOY_RENAME_LOCKED_FILES” and set it to 1 (true).

app-settings

Tony gave the correct solution.

I also had issues with the Excel generating in memory and having the Memorystream to a byte array and providing the right Mime type. Found that too on SO.

The last bit was to automate deployment. I had my code in VSTS (git) and configured a CI/CD pipeline (build + release) But had issues to grant myself (personal account) global admin rights from our company account in order to be able to access Azure resources to deploy. It was a matter of time before the Azure rights/roles changes are active. It’s a nice small serverless function which you can (should) add to source control and ci/cd to automate the latest builds to a test or production environment in the cloud.


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

Yeah! I am exited! Passed the MVC 70-486 this morning.

You can see it instantly on my Acclaim page or just view the screenshot below:

image

I want to earn an MCSD cert. So I am following the app builder path:

image

According to my learning dashboard I have just passed 1 of 2 requirements for the app builder cert. But when I click details, it shows I have both… that’s strange, but I have asked @MSLearning so I will have an answer soon.

I am already looking forward to the next exam and next cert.

If I would pass 70-532 Developing Microsoft Azure Solutions and after that, will study for 70-535 Architecting Microsoft Azure Solutions, then I’d get the:

MCSA: Cloud Platform Microsoft Certified Solutions Associate

What is your certification path for 2018?

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

I wanted to try Microsoft Flow for the first time. So I thought of a use-case and came up with a thing that has been bothering me for a while. When I have an epic moment while gaming on my Xbox, I can capture it with a quick double tap on the Xbox icon and the ‘x’ button.

The next step is to get it in the cloud. Because auto-upload is still not a thing yet. (please upvote)

So if the recording is done, you get a notification and keep the xbox icon pressed to put it up on OneDrive.

So it’s in the cloud. But it’s private… I wanted it to be available on YouTube

Auto-Publish Xbox recording on YouTube

This is my use-case! I have seen several options (how ironically) on YouTube about using the YouTube app for Xbox. But I want to auto-upload and have it set to private. So that I only have to occasionally look into my YT account and crop, caption, publish them. But it will be the easiest option, because the files are already at the right destination.

YouTube is limited in FlowAfbeeldingsresultaat voor microsoft flow

The YouTube connector/triggers/actions are limited… So I need to pull in something more externally to get it to work. I hope that they will add more capabilities in the future for flow and YT

flow-youtube-limited

There are no actions, so you can’t upload from Flow to YouTube (yet) I have added it as an idea for flow. Since I decided that using Flow was a requirement (to get to know the product) I made this flow:

flow

Sorry for the Dutch. But it says: when a file is made, make a file on Dropbox.

I have specified the video/xbox dir for onedrive and made a new dir on Dropbox as target.

The final step for automation to YouTube

So Flow lacks some options… I had an old but still active IFTTT account (if this, than that) but that also won’t work. So I searched some more and found Zapier. It appears that I could also leave out flow and just use OneDrive to YouTube there. But I wanted to use Flow, so I kept the Dropbox step. So I configured Zapier to auto upload a new Dropbox file and it’s done.

ZapierAfbeeldingsresultaat voor zapier

I found out that they are hiring, but they use a totally different stack then what I prefer. Winking smile so perhaps it’s something for you dear reader. They have some great docs about creating an Zapier app with the node.js sdk. But as I have said. I like a different stack. So here is a C# approach with webhooks for Zapier. They should/could add (unsupported) C# docs to their official site.


So I finally have a semi auto upload to YouTube. A quick tap-tap on the xbox button on the controller and hit the X to record the last 30 seconds. Send it to Onedrive and from there it’s auto to dropbox and automatically to YouTube (as private) video ready for captioning an publishing it. Maybe someday when I have some spare time, I’d automate it even more or better but for now: good luck and take care!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

I have just passed the 70-483 exam. The next one will be the 70-486. I thought that that would make me an app builder.

Microsoft Certified Solutions Developer: App Builder 2017

 

But that is (probably) not the case…

snip_20170131150257

Passing 70-486 would give me: “Microsoft® Certified Solutions Associate: Web Applications”

and after that, I am still required to get one of the electives. Because I passed the 70-345 and 70-355 too soon Crying face

So next stop: 70-486!

 

Good luck!

ps. will the Microsoft Certified Solutions Developer Universal Windows Platform become obsolete after march 2017?

Pin on pinterest Plus on Googleplus Post on LinkedIn