- Excel Version
- 365
Typescript is the language of Excel scripts, available at certain Office 365 licenses. Unfortunately, my company chose to disable this feature, so at this time I cannot use it.
However, we can write TS outside of Excel. This article shows how to output TS data to a text file that Excel can read.
The code finds the convex hull for a bidimensional set of points; read more about the Graham algorithm in the link below.
Here are the main features of this project:
However, we can write TS outside of Excel. This article shows how to output TS data to a text file that Excel can read.
The code finds the convex hull for a bidimensional set of points; read more about the Graham algorithm in the link below.
Here are the main features of this project:
- The points can be code generated or read from file.
- The results are written to a text file so that they can be retrieved by other applications.
- I used Visual Studio to edit the TS code, the TS Compiler to generate the corresponding JavaScript and finally Node to run it.
- A VBA macro imports the data and creates a chart to visually represent the solution.
Graham scan - Wikipedia
en.wikipedia.org
VBA Code:
Sub Graham()
Dim ob As Shape, rng As Range, sht As Worksheet, lr%, fp%
Workbooks.OpenText Filename:="C:\Users\en_sa\helloworld\example.txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True
Set sht = ActiveSheet
Set rng = sht.Columns("a:a").Find("end", LookIn:=xlValues)
fp = Split(rng.Address, "$")(2)
lr = Evaluate("=MAX((B:B<>"""")*(ROW(B:B)))")
Range(Cells(fp + 1, 1), Cells(fp + 1, 2)).Copy Cells(lr + 1, 1)
Set ob = sht.Shapes.AddChart2(240, xlXYScatterLines)
ob.Chart.SetSourceData Source:=Range("example!$A$1:$B$" & (fp - 1))
Application.CutCopyMode = False
ob.Chart.SeriesCollection.NewSeries
With ob.Chart.FullSeriesCollection(2)
.Name = "=""sec"""
.XValues = "=example!$A$" & (fp + 1) & ":$A$" & lr + 1
.Values = "=example!$B$" & (fp + 1) & ":$B$" & lr + 1
.ChartType = xlXYScatterLines
End With
With ob.Chart
.FullSeriesCollection(1).ChartType = xlXYScatter
.Axes(xlValue).MinimumScale = -1
.Axes(xlCategory).MaximumScale = 11
.HasTitle = False
End With
End Sub
JavaScript:
interface point {
x:number
y:number
angle:number
discarded:boolean
zero:boolean
}
import { readFileSync, writeFileSync } from 'fs';
import { join } from 'path';
let st=""
let xarr:number[]=[]
let yarr:number[]=[]
const ipt:string="file" // data from file or not
if(ipt==="file"){reader()}
else{
for(let i=0;i<35;i++){
xarr[i]=Math.random()*10
yarr[i]=Math.random()*10
st=st+xarr[i]+"\t"+yarr[i]+"\n"
}
}
st=st+"end\n"
let points:point[]=[]
for (let i = 0; i < xarr.length; i++) {
const el: point = {
x: Number(xarr[i]),
y: Number(yarr[i]), angle:0 ,
discarded: false, zero:false
};
points.push(el)
}
var vsort = (a: point,b: point) => {
if (b.y < a.y) return 1;
if (b.y > a.y) return -1;
else {
if (b.x < a.x) return 1;
if (b.x > a.x) return -1;
return 0
}
};
points.sort(vsort)
console.log("point zero="+points[0])
for(let i=0;i<xarr.length;i++){
points[i].angle=arcos(Number(points[0].x),Number(points[i].x),Number(points[0].y),Number(points[i].y))
}
var byangle=(a:point,b:point)=>{
if(b.angle<a.angle)return 1
if(b.angle>a.angle)return -1
else return 0
}
points.sort(byangle)
points[0].zero=true
console.log(points[points.length-1])
let curr=0
let i=0 // safety
do {
let c1=ccw(points[curr].x,points[curr+1].x,points[curr+2].x,points[curr].y,points[curr+1].y,points[curr+2].y)
if(c1){curr++}
else{console.log("removed"+points.splice(curr+1,1)) // remove point
curr-=1
}
i++;
}
while (i < 250 && curr<points.length-2);
for(i=0;i<points.length;i++){
console.log(points[i].x+" "+points[i].y)
st=st+points[i].x+"\t"+points[i].y+"\n"
}
syncWF('./example.txt',st)
// end of main
function arcos(x1: number, x2: number, y1: number, y2: number) {
let x = x2 - x1
let y = y2 - y1
let res= Math.acos(x / Math.sqrt(x * x + y * y))
if(isNaN(res))res=0
return res}
function ccw(x1:number,x2:number,x3:number,y1:number,y2:number,y3:number ) // check if it is a left turn
{let bvar= (x2-x1)*(y3-y1)-(y2-y1)*(x3-x1)>0
return bvar
}
function reader(){
let rv=syncread('./input.txt');
let sarr=rv.split("\n")
console.log("len="+sarr.length)
let i:number
for(i=0;i<sarr.length;i++){
let ele=sarr[i].split("\t")
let temp1=!isNaN(Number(ele[0]))
let temp2=!isNaN(Number(ele[1]))
if(temp1&&temp2){
xarr[i]=Number(ele[0])
yarr[i]=Number(ele[1])
console.log(i+": "+xarr[i]+" "+yarr[i])
st=st+xarr[i]+"\t"+yarr[i]+"\n"
}
}
}
function syncWF(fname: string, data: any) {
/**
* flags:
* - w = Open file for reading and writing. File is created if not exists
* - a+ = Open file for reading and appending. The file is created if not exists
*/
writeFileSync(join(__dirname, fname), data, { flag: 'w', });
return 1;
}
function syncread(filename: string) {
const cont = readFileSync(join(__dirname, filename), 'utf-8');
return cont;
}