• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Worf

Excel TypeScript: Functions and Shapes

Excel Version
  1. 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 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.
3shapes.png



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()
            }
        }
    }
}
Author
Worf
Views
672
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Worf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top