Excel SUMIF function

- by mhdr - Leave a Comment 
SUMIF(range, criteria, [sum_range])

range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

criteria   Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

References
SUMIF function
Excel SUMIF Function
How to use SUMIF in Excel – formula examples to conditionally sum cells

Name Ranges in Excel

- by mhdr - Leave a Comment

Method 1 : Create Name Range using Name Box
Delete Name Range using Name Manager
Method 2 : Create Name Range using Name Manager
Method 3 : Create Name Range using Define Name
Edit Name Range using Name Manager

References
https://support.office.com/en-us/article/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
https://exceljet.net/glossary/named-range
https://www.ablebits.com/office-addins-blog/2017/07/11/excel-name-named-range-define-use/

Write a Siemens S7 plc driver with C# and Sharp7

- by mhdr - Leave a Comment

Connection to the plc

// Create and connect the client
var client = new S7Client();
int result = client.ConnectTo("127.0.0.1", 0, 1);
if(result == 0)
{
    Console.WriteLine("Connected to 127.0.0.1");
}
else
{
    Console.WriteLine(client.ErrorText(result));
}
 
// Disconnect the client
client.Disconnect();

Read from DB

Console.WriteLine("\n---- Read DB 1");
 
byte[] db1Buffer = new byte[18];
result = client.DBRead(1, 0, 18, db1Buffer);
if(result != 0)
{
    Console.WriteLine("Error: " + client.ErrorText(result));
}
int db1dbw2= S7.GetIntAt(db1Buffer, 2);
Console.WriteLine("DB1.DBW2: " + db1dbw2);
 
double db1ddd4 = S7.GetRealAt(db1Buffer, 4);
Console.WriteLine("DB1.DBD4: " + db1ddd4);
 
double db1dbd8 = S7.GetDIntAt(db1Buffer, 8);
Console.WriteLine("DB1.DBD8: " + db1dbd8);
 
double db1dbd12 = S7.GetDWordAt(db1Buffer, 12);
Console.WriteLine("DB1.DBD12: " + db1dbd12);
 
double db1dbw16 = S7.GetWordAt(db1Buffer, 16);
Console.WriteLine("DB1.DBD16: " + db1dbw16);

Write to DB

db1Buffer = new byte[12];
const int START_INDEX = 4;
S7.SetRealAt(db1Buffer, 4 - START_INDEX, (float)54.36);
S7.SetDIntAt(db1Buffer, 8 - START_INDEX, 555666);
S7.SetDWordAt(db1Buffer, 12 - START_INDEX, 123456);
result = client.DBWrite(1, START_INDEX, db1Buffer.Length, db1Buffer);
if (result != 0)
{
    Console.WriteLine("Error: " + client.ErrorText(result));
}

ReadMultiVar

// Read multi vars
 
var s7MultiVar = new S7MultiVar(client);
byte[] db1 = new byte[18];
s7MultiVar.Add(S7Consts.S7AreaDB, S7Consts.S7WLByte, 1, 0, 18, ref db1);
byte[] db3 = new byte[18];
s7MultiVar.Add(S7Consts.S7AreaDB, S7Consts.S7WLByte, 3, 0, 18, ref db3);
result = s7MultiVar.Read();
if (result != 0)
{
    Console.WriteLine("Error on s7MultiVar.Read()");
}
db1dbw2 = S7.GetIntAt(db1, 2);
Console.WriteLine("DB1.DBW2.0 = {0}", db1dbw2);
 
db1ddd4 = S7.GetRealAt(db1, 4);
Console.WriteLine("DB1.DBW4.0 = {0}", db1ddd4);
 
db1dbd8 = S7.GetDIntAt(db1, 8);
Console.WriteLine("DB1.DBW8.0 = {0}", db1dbd8);
 
db3dbw2 = S7.GetIntAt(db3, 2);
Console.WriteLine("DB3.DBW2.0 = {0}", db3dbw2);
 
db3dbd4 = S7.GetRealAt(db3, 4);
Console.WriteLine("DB3.DBW4.0 = {0}", db3dbd4);
 
db3dbd8 = S7.GetDIntAt(db3, 8);
Console.WriteLine("DB3.DBW8.0 = {0}", db3dbd8);

WriteMultiVars

// Write multi vars
s7MultiVar = new S7MultiVar(client);
const int DB1_START_INDEX = 2;
db1 = new byte[10];
S7.SetIntAt(db1, 2 - DB1_START_INDEX, 50);
S7.SetRealAt(db1, 4 - DB1_START_INDEX, (float)36.5);
S7.SetDIntAt(db1, 8 - DB1_START_INDEX, 123456);
s7MultiVar.Add(S7Consts.S7AreaDB, S7Consts.S7WLByte, 1, DB1_START_INDEX, db1.Length, ref db1);
 
const int DB3_START_INDEX = 2;
db3 = new byte[10];
S7.SetIntAt(db3, 2 - DB3_START_INDEX, -50);
S7.SetRealAt(db3, 4 - DB3_START_INDEX, (float)-25.36);
S7.SetDIntAt(db3, 8 - DB3_START_INDEX, -123456);
s7MultiVar.Add(S7Consts.S7AreaDB, S7Consts.S7WLByte, 3, DB3_START_INDEX, db3.Length, ref db3);
result = s7MultiVar.Write();
if (result != 0)
{
    Console.WriteLine("Error on s7MultiVar.Read()");
}

References
https://www.mesta-automation.com/how-to-write-a-siemens-s7-plc-driver-with-c-and-sharp7/
https://github.com/mesta1/Sharp7-example/blob/master/Sharp7Example/Program.cs

Deploy .NET Core app on Ubuntu

- by mhdr - Leave a Comment

check if it runs

dotnet run

Deploy in a build directory

dotnet publish --output “ /var/www/build” --configuration release

Go to the build directory and run the application

dotnet ForExample.dll

Create the service file

sudo nano /etc/systemd/system/kestrel-ForExample-test.service

[Unit]
Description=Example .NET Web API App running on Ubuntu
[Service]
WorkingDirectory=/var/www/ForExample/ForExample
ExecStart=/usr/bin/dotnet /var/www/build/ForExample.dll
Restart=always
# Restart service after 10 seconds if the dotnet service crashes:
RestartSec=10
KillSignal=SIGINT
SyslogIdentifier=dotnet-example
User=www-data
Environment=ASPNETCORE_ENVIRONMENT=Production
Environment=DOTNET_PRINT_TELEMETRY_MESSAGE=false
[Install]
WantedBy=multi-user.t

Register the service

sudo systemctl enable kestrel-ForExample-test.service

Start the service and verify that it’s running.

sudo systemctl start kestrel-mebgispanel-test.service
sudo systemctl status kestrel-mebgispanel-test.service

Check the server logs

sudo journalctl -fu kestrel-helloapp.service

References
https://medium.com/faun/ubuntu-servers-and-asp-net-core-project-deployment-using-nginx-d9a3a1f6ac82

Install .NET Core SDK on Linux Ubuntu 18.04 – x64

- by mhdr - Leave a Comment 
wget -q https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
sudo add-apt-repository universe
sudo apt-get update
sudo apt-get install apt-transport-https
sudo apt-get update
sudo apt-get install dotnet-sdk-3.0

References
https://dotnet.microsoft.com/download/linux-package-manager/ubuntu18-04/sdk-current