- Excel Version
- 365
This is my second TypeScript article. Today we will see how to use functions and deal with shapes and images. The example below is not exactly production code, rather an assortment of working bits that you can use as reference when developing your own projects. In a nutshell, it creates a colourful background and then inserts three transparent shapes.
The interesting TS features shown are:
The interesting TS features shown are:
- The add shape function accepts rest parameters.
- There are global variables.
- One of the parameters for the add shape function is an arrow function, but when called a conventional function is passed.
- An arrow function is passed to the messenger function.
- The get data function returns an interface.
JavaScript:
type NtS = (ip: number) => string
var mv = 15
const rec: string[] = [];
let rv = ["o5", "r10", "u15"]
function saver(newrec: string[]) {
newrec.forEach(record => {
record = record.toLowerCase();
if (record[0] === "#") {
rec.push(record)
};
return "I am string" // method returns void
}
)
};
function main(workbook: ExcelScript.Workbook) {
let selSheet = workbook.getActiveWorksheet();
add_shape(workbook, getel, "Hexagon", "MathMultiply", "SmileyFace", "Star16")
many_cells(workbook)
let ms = getdata(workbook)
let img = workbook.getLastWorksheet().addImage(ms.RotatedIm)
let scol = selSheet.getShapes()
let LS = (scol[scol.length - 1].getName())
let mp = selSheet.getShape(LS)
mp.getFill().setTransparency(0.5)
mp.copyTo(workbook.getLastWorksheet(true))
messenger(n => n.toString(16) + "inished", mv)
}
function messenger(nt: NtS, myn: number): void {
console.log(nt(myn))
}
function getdata(workbook: ExcelScript.Workbook): ImageExport {
const Wsheet = workbook.getWorksheet("Sheet1");
const table = Wsheet.getRange("n1:x25")
const ti = table.getImage();
let imgShape = Wsheet.addImage(ti);
imgShape.incrementRotation(270);
let timage = imgShape.getImageAsBase64(ExcelScript.PictureFormat.png);
const RotatedIm = `data:image/png;base64,${timage}`;
return { RotatedIm };
}
interface ImageExport {
RotatedIm: string
}
function add_shape(workbook: ExcelScript.Workbook, gs: (index: number) => string, ...snames: string[]) {
const currSheet = workbook.getActiveWorksheet();
let sv: ExcelScript.Shape[] = [];
saver(["#C55A11", "#9946de", "#4899C5"])
for (let i = 0; i < 3; i++) {
sv[i] = currSheet.addGeometricShape(ExcelScript.GeometricShapeType[snames[i]])
sv[i].setHeight(140)
sv[i].setWidth(150)
if (gs(i)[0].toLowerCase() === gs(i)[0].toUpperCase()) { break }; // not a letter
sv[i].setLeft(currSheet.getRange(rv[i]).getLeft())
sv[i].setTop(currSheet.getRange(rv[i]).getTop())
sv[i].getFill().setForegroundColor(rec[i])
sv[i].getFill().setTransparency(0.4)
sv[i].incrementRotation(Math.floor(180 * Math.random()))
sv[i].getLineFormat().setTransparency(0.6)
}
let mg = currSheet.addGroup(sv)
mg.setPlacement(ExcelScript.Placement.oneCell)
}
function getel(ind: number) {
return rv[ind]
}
function many_cells(workbook: ExcelScript.Workbook) {
let ur = workbook.getWorksheet("Sheet1").getRange("n1:x25");
let rowCount = ur.getRowCount();
let colcount = ur.getColumnCount();
let urv = ur.getValues();
for (let i = 0; i < rowCount; i++) {
for (let j = 0; j < colcount; j++) {
if (urv[i][j] != "ff") {
let r = Math.floor(Math.random() * 250).toString(16)
if (r.length != 2) { r = "0" + r }
let g = Math.floor(Math.random() * 250).toString(16)
if (g.length != 2) { g = "0" + g }
let b = Math.floor(Math.random() * 250).toString(16)
if (b.length != 2) { b = "0" + b }
ur.getCell(i, j).getFormat().getFill().setColor("#" + r + g + b);
ur.getCell(i, j).getLeft()
}
}
}
}
