Face Detection In Excel

Share this

February 29, 2020

Face Detection In Excel


Introduction


Is it possible to detect faces from an image in Excel? I guess that your immediate reaction will be a big NO! Well, think twice, since now it seems that we can! The purpose of this article is to demonstrate what can be done if we combine a powerful Artificial Intelligence API with a C# (console) application and some (advanced) VBA code.

In the previous post, we learned how to create a C# console application using the free Visual Studio 2019 Community Edition and call that C# application from VBA. The C# code provided in that post was quite trivial since the emphasis was on the “caller” VBA code. Here we will see a much better example, which demonstrates how a higher-level language, like C#, can be used to communicate with an Artificial Intelligence API.

The entire face detection concept can be divided into three main parts:

  1. The Web API, where we will see how to get a free key for the Face Detection API.
  2. The C# code, where we call the Face Detection API and process the JSON results in a meaningful way.
  3. The VBA code, where we export the image to a temporary location (through a temporary chart), we call the C# application and use its results to draw rectangles around the faces of people included in the image.

It looks difficult, isn’t it? Well, it’s not that hard as it seems at first. However, I should warn you that I will not provide any details on how the actual face detection is done. The emphasis will be given to the connection of the individual components. So, are you ready to dive into some exciting details?

 


Face Detection API and how to get a (free) API key


The first building block is the API that does the actual hard work. Here we will use the Face Detection API that Clarifai provides. According to their site, “the Face Detection model returns probability scores on the likelihood that the image contains human faces and coordinate locations of where those faces appear with a bounding box. This model is great for anyone building an app that monitors or detects human activity”.

If you wonder how good that model in identifying faces is, I can ensure you that it does a pretty good job. I have run several successful tests. As long as the face is visible in the image, the model detects it accurately, even in small images. You can see some sample results on their site and the sample images section below.

To get a (free) API key from Clarifai, please follow the next steps. If you need more information about the Clarifai pricing policy, you can check their site.

Step 1: First of all, go to their site and click on the Sign-up button on the page’s top right corner.

Clarifai Sign Up

Step 2: Fill in the necessary information in the next form and click the Sign-up button.

Clarifai Sign Up Form

Step 3: In the next screen, you will get some welcome messages, but it is important before proceeding to anything else to verify your email, as the red toolbar on the top of the screen warns you.

Clarifai Welcome And Email Verification

Step 4: When you verify your email, the red toolbox will disappear. Then, click on the “my-first-application” box that is automatically created after your sign-up.

Clarifai First Application

Step 5: In the “my-first-application” screen, go to the section API keys. There, a key will be already created. Just click on the “Copy Key to Clipboard” button to copy the key.

Clarifai Get API Key

Step 6: Finally, to utilize the key, open the sample workbook that you will find in the Downloads section below and paste the key in the ClarifaiApiKey constant. Be careful with the quotation marks surrounding the key value!

Clarifai Get API Key In VBA Code

 


C# code


We got the free API key from the Clarifai page. Now it’s time to perform the Clarifai server’s request and read the response, i.e., the coordinates of the image’s faces. Please follow the previous post’s steps to create a new C# console application in the Visual Studio 2019 Community Edition.

The additional steps required for this C# application are given below. Note that the images are from an older version of Visual Studio that I use (2015 Enterprise). The steps, however, will be identical:

Step 1: In the new application, from the menu, select Project → Manage NuGet Packages.

Manage NuGet Packages

Step 2: In the NuGet window that opens, search install the following two packages:

  • Clarifai (current version 1.3.2).
  • Newtonsoft.Json (current version 12.0.3)

After installing these two packages, you should see something like the following image:

Installed NuGet Packages

If you wonder what these packages do, the quick answer is that they make our lives easier! The long answer is that the Clarifai package eases the communication with the Clarifai server, while the Newtonsoft.Json package helps to de-serialize the JSON response from the Clarifai server. Note that when you perform the above steps, there might be newer versions for these packages.

After installing these 2 packages, paste the following code into the Program.cs:

using System;
using System.IO;
using Clarifai.API;
using Clarifai.API.Responses;
using Clarifai.DTOs.Inputs;
using Clarifai.DTOs.Models.Outputs;
using Clarifai.DTOs.Predictions;
using Newtonsoft.Json;

/*
------------------------------------------------------------------------------
The code below can be used to detect the faces from a given image.
It uses the Clarifai API for the face detection and outputs the coordinates
of the faces detected in the image (top left corner and bottom right corner).
 
Written By:    Christos Samaras
Date:          28/02/2020
E-mail:        [email protected]
Site:          https://www.myengineeringworld.net
------------------------------------------------------------------------------
*/

namespace FaceDetectionDemo
{
	class Program
	{
		static void Main(string[] args)
		{
			// Check the number of the arguments (it should be 2).
			// The first is the API key and the second is the image path.
			if (args.Length != 2)
			{
				Console.WriteLine("ERROR: Invalid input parameters!");
				return;
			}

			// Check if the image exists (second argument).
			if (!File.Exists(args[1]))
			{
				Console.WriteLine("ERROR: The image does not exist!");
				return;
			}

			// Create the Clarifai client using the API key (first argument).
			ClarifaiClient client = new ClarifaiClient(args[0]);

			// Configure the request by sending the input image in the Clarifai server.
			ClarifaiResponse<ClarifaiOutput> response =
				client.PublicModels.FaceDetectionModel
				.Predict(new ClarifaiFileImage(File.ReadAllBytes(args[1])))
				.ExecuteAsync()
				.Result;

			// Check the response.				
			if (response.IsSuccessful)
			{
				try
				{
					// De-serialize the response into a JSON object.
					dynamic json = JsonConvert.DeserializeObject(response.RawBody);

					// Get the regions (e.g. each region corresponds to a box containing the face).
					dynamic boundingBoxes = json["outputs"][0]["data"]["regions"];

					// Loop through all the regions and go deeper to get the bounding box from each region.
					foreach (dynamic boundingBox in boundingBoxes)
					{
						// Top-left corner.						
						double topLeftCornerX = (double)boundingBox["region_info"]["bounding_box"]["left_col"];
						double topLeftCornerY = (double)boundingBox["region_info"]["bounding_box"]["top_row"];

						// Bottom-right corner.						
						double bottomRightCornerX = (double)boundingBox["region_info"]["bounding_box"]["right_col"];
						double bottomRightCornerY = (double)boundingBox["region_info"]["bounding_box"]["bottom_row"];

						// Write the corners.
						Console.WriteLine($"{topLeftCornerX} {topLeftCornerY} {bottomRightCornerX} {bottomRightCornerY}");
					}
				}
				catch (Exception)
				{
					// A parsing error occurred.
					Console.WriteLine("ERROR: Parsing error!");
				}
			}
			else
			{
				// A request/response error occurred (invalid API key or other).
				Console.WriteLine("ERROR: Request/response error");
			}
		}
	}
}

The code takes two inputs, the API key and the full path of the image file we want to analyze. Both inputs are sent from VBA (we will see that in the next part). After performing some initial checks, it creates the Clarifai client object using the API key provided. Then, it sends the image to the Clarifai server and checks its response. If the response is successful, the code de-serializes the response into a JSON object, reads the necessary information, and writes it into the console.

The final step in this section is to build the solution following the previous post’s steps.

 


VBA code


The final part of this tutorial is the VBA section. Here we use the ExecuteAndCapture function we learned in the previous post. However, apart from that function, the VBA code does some quite interesting things. First of all, there is no “easy” way to export an embedded image from the spreadsheet to an external file. For this reason, a temporary chart is created, which mimics the size of the image. Then, the image is copied in the chart area of the chart, and, finally, the chart is exported as an image to a predetermined location.

The VBA code then calls the C# application using the API key and the temporary image path and waits to receive its output. Based on the returned values, either stops (case of error) or draws the rectangles around the people’s faces included in the image embedded on the spreadsheet. The latter requires some simple mathematics since the C# application returns the coordinates of the top-left and bottom-right corner of the rectangles, and we have to adjust them based on the image location.

Finally, the VBA code deletes the temporary image and informs the user about the number of faces detected in the image. Apart from the DetectFacesInImage macro, which performs the face detection, the ClearRectanglesFromImage macro cleans up the rectangle from the selected image.

Option Explicit

'Here you set the (free) API key from Clarifai.
Const ClarifaiApiKey As String = ""

Sub DetectFacesInPicture()
 
    '-------------------------------------------------------------------------------------------
    'This macro does the following:
    '
    '1. Based on a selected image, it creates a temporary image file relative to the workbook.
    '   To create that image, it uses a temporary chart.
    '
    '2. Using the ExecuteAndCapture function, it calls an external C# console application.
    '   The C# application sends the temporary image on the Clarifai server (request).
    '   Then, it reads the JSON response from the server, i.e. the faces detected in the image.
            
    '3. The macro then reads the output of the C# application and creates red rectangles
    '   around the faces detected in the image (using the coordinates from the C# application).
    
    '4. Finally, the macro deletes the temporary chart and the temporary image and informs the
    '   user about the number of the faces detected.
    
    'NOTE: Ensure that you entered a valid Clarifai API key in the ClarifaiApiKey constant.
    '      Otherwise, the macro will NOT work.
    
    'Written By:    Christos Samaras
    'Date:          28/02/2020
    'Last Updated:  16/01/2021
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '-------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables
    Dim sht                 As Worksheet
    Dim img                 As Variant
    Dim appPath             As String
    Dim chartName           As String
    Dim imagePath           As String
    Dim cmd                 As String
    Dim results             As String
    Dim facesArray          As Variant
    Dim coordinatesArray    As Variant
    Dim i                   As Integer
    Dim cnt                 As Integer
    Dim topLeftCornerX      As Double
    Dim topLeftCornerY      As Double
    Dim bottomRightCornerX  As Double
    Dim bottomRightCornerY  As Double
    
    'First, ensure that the Clarifai API key is set (e.g. it is not empty).
    If ClarifaiApiKey = vbNullString Then
        MsgBox "The Clarifai API key is empty!", vbExclamation, "Invalid API key"
        Exit Sub
    End If
    
    'Set the worksheet that will contain the images.
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("Images")
    If Err.Number <> 0 Then
        MsgBox "The given worksheet does not exist!", vbExclamation, "Invalid Sheet Name"
        Exit Sub
    End If
    Err.Clear
                        
    'Check that there is an image selected.
    If TypeName(Application.Selection) <> "Picture" Then
        MsgBox "You must select an image to proceed!", vbExclamation, "Image Not Selected"
        Exit Sub
    End If
     
    'Assign the image to the img variable.
    'NOTE: if the img variable was not of type Variant, but of type Shape, this wouldn't work.
    Set img = sht.Shapes(Application.Selection.Name)
        
    'Create the path of the image that will be created (relative to the workbook).
    imagePath = ThisWorkbook.Path & "\" & "TestFaceImage.jpg"
    
    'Set the C# application path (relative to the workbook).
    appPath = ThisWorkbook.Path & "\C# Face Detection\" & "FaceDetectionDemo.exe"
    
    'Check that the C# app exist.
    If FileExists(appPath) = False Then
        MsgBox "The C# app " & vbNewLine & appPath & vbNewLine & "doesn't exist!", vbExclamation, "C# Path Error"
        Exit Sub
    End If
    
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Create a new (empty) chart sheet.
    ThisWorkbook.Charts.Add
    
    'Move the chart to a new location (in the worksheet).
    ActiveChart.Location xlLocationAsObject, sht.Name
    
    'Ensure that the chart has no border.
    Selection.Border.LineStyle = 0
        
    'Get the chart name
    chartName = Application.Substitute(ActiveChart.Name, "Images ", "")
    
    'Alternative method.
    'chartName = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)
    
    With sht
        
        'Set the chart size equal to the image size.
        With .Shapes(chartName)
            .Width = img.Width
            .Height = img.Height
        End With
    
        'Copy the image.
        .Shapes(img.Name).Copy
        
        'Paste the image to the chart area.
        With ActiveChart
            .ChartArea.Select
            .Paste
        End With
        
        'Export the chart as a jpg image to the predefined location.
        .ChartObjects(1).Chart.Export Filename:=imagePath, FilterName:="jpg"
        
        'Delete the chart from the worksheet.
        .Shapes(chartName).Delete
    
    End With
    
    'Create the command that will call the executable based on the inputs provided.
    'The parameters are the API key and the path of the image that was exported from Excel.
    'Note the space between the parameters (" ").
    cmd = Chr(34) & appPath & Chr(34) & " " & ClarifaiApiKey & " " & Chr(34) & imagePath & Chr(34)
    
    'Execute and read the (hidden console) results.
    results = ExecuteAndCapture(cmd)
    
    'Check if there is any error in the results.
    If InStr(1, Trim(results), "ERROR", vbTextCompare) > 0 Then
    
        'Error detected! Report and exit.
        MsgBox Trim(results), vbExclamation, "C# Application Error"
        Exit Sub
        
    End If
    
    'Split the results into a string array because there might be more than one face in the image.
    facesArray = Split(results, vbNewLine)
    
    'Count the faces that will be detected.
    cnt = 0
    
    'Loop through each line of the string array.
    For i = LBound(facesArray) To UBound(facesArray)
        
        'If the line is not empty, continue.
        If facesArray(i) <> vbNullString Then
            
            'Split the line into the corresponding coordinates (based on the C# output).
            coordinatesArray = Split(facesArray(i), " ")
                            
            'The C# output returns 4 values, which are the coordinates of the 2 corners containing the face.
            'Top-left corner.
            topLeftCornerX = CDbl(coordinatesArray(0))
            topLeftCornerY = CDbl(coordinatesArray(1))
            
            'Bottom-right corner.
            bottomRightCornerX = CDbl(coordinatesArray(2))
            bottomRightCornerY = CDbl(coordinatesArray(3))
            
            'Add a rectangle shape in the image based on the given corners.
            With sht.Shapes.AddShape(msoShapeRectangle, _
                    img.Left + (img.Width * topLeftCornerX), _
                    img.Top + (img.Height * topLeftCornerY), _
                    img.Width * (bottomRightCornerX - topLeftCornerX), _
                    img.Height * (bottomRightCornerY - topLeftCornerY))
                
                'Format the rectangle (red, thick line) in order to be visible.
                .Fill.Visible = msoFalse
                With .Line
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Transparency = 0
                    .Weight = 2.5
                End With
                
            End With
            
            'Count the faces that were detected.
            cnt = cnt + 1
            
        End If
        
    Next i
    
    'Delete the temporary image that was created.
    If FileExists(imagePath) = True Then
        Kill imagePath
    End If
    
    'Re-select the image.
    img.Select
        
    'Re-enable the screen.
    Application.ScreenUpdating = True
    
    'Inform the user about the number of faces detected.
    If cnt = 0 Then
        MsgBox "No face was detected in the selected image!", vbExclamation, "Fail"
    ElseIf cnt = 1 Then
        MsgBox "A face was detected in the selected image!", vbInformation, "Success"
    Else
        MsgBox cnt & " faces were detected in the selected image!", vbInformation, "Success"
    End If

End Sub

Sub ClearRectanglesFromImage()

    '-----------------------------------------------------------------
    'This macro deletes the rectangle shapes from the selected image.
    '-----------------------------------------------------------------
        
    'Declaring the necessary variables
    Dim sht     As Worksheet
    Dim img     As Variant
    Dim i       As Integer
    Dim cnt     As Integer

    'Set the worksheet that will contain the images.
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("Images")
    If Err.Number <> 0 Then
        MsgBox "The given worksheet does not exist!", vbExclamation, "Invalid Sheet Name"
        Exit Sub
    End If
    Err.Clear
                        
    'Check that there is an image selected.
    If TypeName(Application.Selection) <> "Picture" Then
        MsgBox "You must select an image to proceed!", vbExclamation, "Image Not Selected"
        Exit Sub
    End If
         
    'Assign the image to the img variable.
    Set img = sht.Shapes(Application.Selection.Name)
                    
    'Count the rectangles that will be deleted.
    cnt = 0
    
    'Loop through all the shapes in the worksheet.
    'Note: there will always be at least one shape (the selected image).
    For i = sht.Shapes.Count To 1 Step -1
        
        'Check if the shape is actually an auto shape and a rectangle.
        'If there is no check for the auto shape property, then the selected image will be also deleted.
        If sht.Shapes(i).Type = msoAutoShape And sht.Shapes(i).AutoShapeType = msoShapeRectangle Then
        
            'Ensure that the rectangle is contained in the image.
            'The top-left corner and the bottom-right corner is within the image boundaries.
            If sht.Shapes(i).Top >= img.Top And _
                sht.Shapes(i).Left >= img.Left And _
                sht.Shapes(i).Top + sht.Shapes(i).Height <= img.Top + img.Height And _
                sht.Shapes(i).Left + sht.Shapes(i).Width <= img.Left + img.Width Then
                
                'Count the rectangle.
                cnt = cnt + 1
                
                'Delete the rectangle.
                sht.Shapes(i).Delete
                
            End If
            
        End If
        
    Next i
         
    'Inform the user about the number of rectangles deleted.
    If cnt = 0 Then
        MsgBox "No rectangle was deleted in the selected image!", vbExclamation, "Fail"
    ElseIf cnt = 1 Then
        MsgBox "A rectangle was deleted in the selected image!", vbInformation, "Success"
    Else
        MsgBox cnt & " rectangles were deleted in the selected image!", vbInformation, "Success"
    End If
     
End Sub 

 


Sample images


The 3 images that follow show how accurate the face detection can be, considering the different resolutions, sizes, and environments of each image used as input.

Sample Images With Faces Detected 1

Sample Images With Faces Detected 2

Sample Images With Faces Detected 3

If you need more results, download the sample workbook and the C# application, get a free API key from Clarifai and test your images.

 


Face detection demonstration video 


In the video that follows, I am trying to analyze the different sections of this tutorial.

 


Downloads


Download

The zip file contains an executable based on the above C# code and a sample workbook containing the VBA code for drawing rectangles around the faces of people included in the images embedded in the worksheets of the sample file. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

 


Final thoughts


In this article, we learned how to use a powerful Artificial Intelligence API directly from Excel. I know that it was not an easy tutorial, but I think it showed several key concepts. Somebody might argue that the C# application is not needed in this example since the API request/response could be processed through VBA. That is a fair argument, and we have already seen examples of calling an external API from VBA (e.g., Google APIs)! However, I suggest checking the C# code. Look how easy and straightforward it is! Only with a few lines of code, all the complexity of the API communication is gone!

Last but not least, considering Microsoft’s intention to move Office scripting from VBA to JavaScript language, don’t be surprised if interactions with Artificial Intelligence APIs and other powerful Web APIs become more popular soon. I think that these cases show the “real power” of JavaScript (or TypeScript), not the typical Office actions.

 


Read also


Call A C# Console App From VBA

Page last updated: 16/01/2021

Page last modified: March 18, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>