vstooffice-jsexcel-addinsexcel-chartsoffice-js-helpers

How to take chart target position as input from the user in an Excel VSTO Add-in through a textfield?


I want to create a Bar chart on Excel Add-in, but I want to get the target position of the chart from the user in a textfield, how can I use that value and assign it to the chart.setPosition ? Is it possible to add the parameter for the setPosition function as a variable that contains the value taken as input from the textfield?

Here is my code:


  function createBarChart() {

        Excel.run(function (context) {



            const sheet = context.workbook.worksheets.getItem("Sheet1");

            const salesTable = sheet.tables.getItem("Table1");

            const dataRange = salesTable.getDataBodyRange();

            let chart = sheet.charts.add("ColumnClustered", dataRange, "Auto");



            chart.setPosition("A9", "F20");

            chart.title.text = "Farm Sales Bar chart";

            chart.legend.position = "Right";

            chart.legend.format.fill.setSolidColor("white");

            chart.dataLabels.format.font.size = 15;

            chart.dataLabels.format.font.color = "black";

            let points = chart.series.getItemAt(0).points;

            points.getItemAt(0).format.fill.setSolidColor("pink");

            points.getItemAt(1).format.fill.setSolidColor("indigo");



            return context.sync();

        })

            .catch(function (error) {

                console.log("Error: " + error);

                if (error instanceof OfficeExtension.Error) {

                    console.log("Debug info: " + JSON.stringify(error.debugInfo));

                }

            });

    }


Solution

  • You could use

    <input type="text" id="leftTop"/>
    <input type="text" id="bottomRight"/>
    

    in the HTML for user to enter the param, and use

    let value = $("#leftTop").val();
    let lt = String(value);
    value = $("#bottomRight").val();
    let br = String(value);
    chart.setPosition(lt, br);
    

    to parse the textfield into chart.setPosition.

    Here's a sample code, you could run it by import into ScriptLab.