Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 

Poprzedni temat «» Następny temat
Przesunięty przez: umiejead
12-03-2020, 12:59
Rozpoznawianie twarzy w ... Excelu (i z kawałkiem w C)
Autor Wiadomość
Starszy Forumowicz

Wersja: Win Office 2013
Posty: 59
Wysłany: 12-03-2020, 12:36   Rozpoznawianie twarzy w ... Excelu (i z kawałkiem w C)

Nie mam tu makra "do poprawki" ale gotowy kod i opis ale przypadek wydał mi sie na tyle ciekawy że postanowiłem w całości przekleić - może komuś się przydać też do innych zastosowań - proszę Admina o nie stosowanie kar cielesnych...;-)


Face Detection In Excel

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, as well as how to call that C# application from VBA. The C# code provided in that post was quite trivial since the emphasis was given 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 concept can be divided into three main parts:

The Web API, where we will see how to get a free key for the Face Detection API.
The C# code, where we call the Face Detection API and process the JSON results in a meaningful way.
The VBA code, where we export the image to a temporary location (through a temporary chart), 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 on 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 is provided by Clarifai. 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, as well as in 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 top right of the page.

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 request to the Clarifai server and read the response, i.e. the coordinates of the faces contained in the image. Please follow the steps provided in the previous post 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 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 in 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:        xristos.samaras@gmail.com
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!");
// Check if the image exists (second argument).
if (!File.Exists(args[1]))
Console.WriteLine("ERROR: The image does not exist!");
// 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 =
.Predict(new ClarifaiFileImage(File.ReadAllBytes(args[1])))
// Check the response.               
if (response.IsSuccessful)
// 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!");
// A request/response error occurred (invalid API key or other).
Console.WriteLine("ERROR: Request/response error");
// End of the code.

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 steps provided in the previous post.

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 not an “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 path of the temporary image and waits to receive its output. Then, based on the returned values, either stops (case of error) or draws the rectangles around the faces of the people 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, there is the ClearRectanglesFromImage macro that cleans up the rectangle form 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
'E-mail:        xristos.samaras@gmail.com
'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
'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.
'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.
'Paste the image to the chart area.
With ActiveChart
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.
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 & " " & imagePath
'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.
'Re-enable the screen.
Application.ScreenUpdating = True
'Inform the user about the number of the 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"
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
'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 rectnage.
cnt = cnt + 1
'Delete the rectangle.
End If
End If
Next i
'Inform the user about the number of the 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"
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 that 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.


The zip file contains an executable based on the above C# code, as well as a sample workbook containing the VBA code for drawing rectangles around the faces of people that are included in the images embedded in the worksheets of the sample file. The workbook can be opened with Excel 2007 or newer.
Nobody's Perfect
ID posta: 383595 Skopiuj do schowka
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl


Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe