Month: November 2019

Box Annotations in Bokeh

from bokeh.sampledata.glucose import data
from bokeh.plotting import figure, show, output_file
from bokeh.models import BoxAnnotation

output_file("box_annotation.html", title="box_annotation.py example")

TOOLS = "pan,wheel_zoom,box_zoom,reset,save"

#reduce data size
data = data.loc['2010-10-06':'2010-10-13']

p = figure(x_axis_type="datetime", tools=TOOLS)

p.line(data.index.to_series(), data['glucose'], line_color="gray", line_width=1, legend_label="glucose")

low_box = BoxAnnotation(top=80, fill_alpha=0.1, fill_color='red')
mid_box = BoxAnnotation(bottom=80, top=180, fill_alpha=0.1, fill_color='green')
high_box = BoxAnnotation(bottom=180, fill_alpha=0.1, fill_color='red')

p.add_layout(low_box)
p.add_layout(mid_box)
p.add_layout(high_box)

p.title.text = "Glucose Range"
p.xgrid[0].grid_line_color=None
p.ygrid[0].grid_line_alpha=0.5
p.xaxis.axis_label = 'Time'
p.yaxis.axis_label = 'Value'

show(p)

References
https://docs.bokeh.org/en/latest/docs/user_guide/annotations.html#box-annotations

Add horizontal or vertical line (`Span`) in Bokeh

from datetime import datetime as dt
import time

from bokeh.sampledata.daylight import daylight_warsaw_2013
from bokeh.plotting import figure, show, output_file
from bokeh.models import Span

output_file("span.html", title="span.py example")

p = figure(x_axis_type="datetime", y_axis_type="datetime")

p.line(daylight_warsaw_2013.Date, daylight_warsaw_2013.Sunset,
       line_dash='solid', line_width=2, legend_label="Sunset")
p.line(daylight_warsaw_2013.Date, daylight_warsaw_2013.Sunrise,
       line_dash='dotted', line_width=2, legend_label="Sunrise")

start_date = time.mktime(dt(2013, 3, 31, 2, 0, 0).timetuple())*1000
daylight_savings_start = Span(location=start_date,
                              dimension='height', line_color='green',
                              line_dash='dashed', line_width=3)
p.add_layout(daylight_savings_start)

end_date = time.mktime(dt(2013, 10, 27, 3, 0, 0).timetuple())*1000
daylight_savings_end = Span(location=end_date,
                            dimension='height', line_color='red',
                            line_dash='dashed', line_width=3)
p.add_layout(daylight_savings_end)

p.title.text = "2013 Sunrise and Sunset times in Warsaw"
p.yaxis.axis_label = 'Time of Day'

show(p)

References
http://docs.bokeh.org/en/latest/docs/user_guide/annotations.html#spans
https://stackoverflow.com/questions/39426976/how-to-add-horizontal-line-span-to-the-box-plot-in-bokeh
https://stackoverflow.com/questions/28797330/infinite-horizontal-line-in-bokeh

Excel SUMIF function

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

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

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