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

Back in 2012, I found some code online which should have been a Nuget package. I tried to reach out to the original author (even searched for him/her today) but could not find any contact info.

That person created a library to generate QR codes. I packaged it for nuget which was just 1,5 years old back then.

The package is still out there. I don’t have any code on my system for years from that lib. But you can still grab the package here: https://www.nuget.org/packages/MessagingToolkit.QRCode/ 

Or from the package manager in Visual Studio with:

Install-Package MessagingToolkit.QRCode

It has been downloaded over 61.000 times now! So Twitt88 did a great job coding it!


I am porting an other 4 to 6 year old library to .Net Standard 1.4. The current status is up on GitHub https://github.com/jphellemons/PhotoBucketNetStandard

And the Nuget package has been submitted. This one is originally build by Mark Schall so most of the credits are for him. I only rewrote the stuff that is not available in .Net Standard or requires other namespaces.


Let’s all port libs to .Net Standard!

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

There are various options to build a word document in your C# code, I choose the lightweight docx nuget package. There are some really good examples on the Codeplex website of docx http://docx.codeplex.com/ if you look at the “Advanced Examples”

The thing is that Office products tend to localize variables such as table designs:

image

I am dutch, but I cannot translate it to a value of the enum `TableDesign`

If I would translate it, it would be something like listtable 7 colorful accent 6. It is still (even after this test file) not a valid entry of the table design enumeration.

using (DocX document = DocX.Create("C:/Temp/tables.docx"))
{
    foreach (TableDesign td in (TableDesign[])Enum.GetValues(typeof(TableDesign)))
    {
        Table aTable = document.InsertTable(5, 5); // because office preview is also 5 x 5
        aTable.Design = td;
        aTable.Rows[0].Cells[0].Paragraphs.First().Append(td.ToString());
    }
    document.Save();
}

 

The “source” (all 6 lines of code, lol) is available on github https://github.com/jphellemons/DocxTables but perhaps more important is the output file. This might save you some time. https://github.com/jphellemons/DocxTables/raw/master/tables.docx

 

Good luck with this small, but awesome nuget package!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

To start serving ads in your Windows Phone application you can install the Microsoft advertising SDK. But I believe it is not mandatory. You do not need to reference it in your project. So please take a look at the screenshot below and make sure you do not check that box!

2014-04-25 15_30_38-Reference Manager - Zaklamp

I recommend using the (not yet final) Adrotator V2. It is the successor of V1 and has an XML configuration file which can be put somewhere online (remote) so that you can edit advertising preferences without needing to update the whole app and push it to your users.

You can get it from nuget as long as you make sure that you can view the pre-releases or put this in the packagemanager console:

Install-Package AdRotatorWP -Pre

That will pull down the package. The readme file will open in a tab and tell you to manually add more DLL’s for mobfox, smaato etc. (only adduplex is included because they have a nuget package).

You should mark your XML config file in the project explorer as content and copy to output always and check the app’s permissions, because mobfox requires <Capability Name="ID_CAP_IDENTITY_DEVICE" />  as found on http://wpunifiedad.codeplex.com/discussions/436059

It took me a while to figure that one out. You have to add this namespace in the XAML in order to use the adrotator component.

<phone:PhoneApplicationPage
    xmlns:adRotator="clr-namespace:AdRotator;assembly=AdRotator"
...>

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn
0 Comments

Here is a small C# code snippet to upload an XML file to an explicit SSL secured FTP server.

When you search nuget for FTP you will get:

image

But which one do you need? Not all of them support FTPS. Well the checkbox gives it away.

If you search for FTPS you will only get these seven results (at this moment):

image

So you need the System.Net.FtpClient from https://netftp.codeplex.com/

And here is the C# code to upload an XDocument to an FTPS server:

private void UploadFile(XDocument xDoc, string filename)
{
    using (FtpClient fc = new FtpClient())
    {
        fc.Credentials = new NetworkCredential(username, password);
        fc.Host = hostname;
        fc.EncryptionMode = FtpEncryptionMode.Explicit;
        fc.ValidateCertificate += fc_ValidateCertificate;
        fc.Connect();
        fc.SetWorkingDirectory("/uploaddir");

        using (var ftpStream = fc.OpenWrite(filename, FtpDataType.Binary))
        {
            if (ftpStream != null)
            {
                xDoc.Save(ftpStream);
                ftpStream.Close();
            }
        }
    }
}

static void fc_ValidateCertificate(FtpClient control, FtpSslValidationEventArgs e)
{
    e.Accept = true;
}

Self signed certificates will otherwise always cause an exception in the ftp libraries because the certificates are not really valid. So you manually have to “validate” it.


Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn