1. ACF Excel Charts - Introduction

1. ACF Excel Charts - Introduction
2. ACF Excel Charts — JSON Options Reference
   1. Top-level chart object
   2. Anchor
   3. Series (per entry in series[])
      1. Series style (shape & marker)
      2. Per-point overrides (dPt)
   4. Data labels (per-series and chart-level defaults)
   5. Axes (axes.category, axes.value, axes.secondaryValue)
   6. Legend
   7. Plot Area
   8. Color (fill, line)
   9. Plot options (plot)
   10. Enums (accepted spellings)
   11. Examples
       1. (concise)
       2. Simple Line chart
       3. Stacked, dynamic series chart (Varying series count and column count)

This builds on the existing Excel functions in the ACF Plugin (introduced in v1.7.8.1). We’ve made a few design decisions and notes:

• There’s a single entry point for charts: EXCEL_PlaceChart(sheetID, jsonOptions). All configurable parameters live in jsonOptions. Keeping everything in one JSON object makes it easier for developers/designers to reason about and reuse chart definitions.

• Typical sequence:

  • Gather data for the workbook (SQL, APIs, imports, parameters, etc.).
  • Create the workbook with EXCEL_Create(...) and keep sheet names in variables for easy reference. You’ll receive a workbook ID.
  • Get the sheet ID for the target sheet (one sheet is already added): EXCEL_GetSheetID(workbookID, sheetNumber).
  • Populate the sheet with data.
  • Build the jsonOptions object.
  • Call EXCEL_PlaceChart(sheetID, jsonOptions).
  • Close the workbook with EXCEL_Close(workbookID).

• For cell-range references, use EXCEL_SheetAddress(sheetName, -rowFrom, -colFrom, -rowTo, -colTo). Negative row/column values produce absolute addresses (e.g., $C$1:$C$10). Absolute ranges keep charts linked to the same source data even if the chart is moved or copied.

• The anchor element in jsonOptions defines placement: row and col mark the upper-left cell, and width/height set the size. Excel’s default column width is ~10 “user units” per cell; height uses the same unit (not linked to row height). For pie/doughnut charts, a square (e.g., 50×50) often looks best (legend placement may influence this). For other types, a height of roughly width / 2 is a good starting point.

• anchor also supports offsetX and offsetY for sub-cell positioning (including negative values). These offsets are applied against the sheet’s actual metrics at render time, so you can align a chart precisely (e.g., centering within a column).

Below is the reference for the jsonOptions object.

2. ACF Excel Charts — JSON Options Reference ↑

This documents all keys recognized by the current JSON parser for charts.

2.1. Top-level chart object ↑

2.2. Anchor ↑

2.3. Series (per entry in series[]) ↑

2.3.1. Series style (shape & marker) ↑

2.3.2. Per-point overrides (dPt) ↑

2.4. Data labels (per-series and chart-level defaults) ↑

2.5. Axes (axes.category, axes.value, axes.secondaryValue) ↑

2.6. Legend ↑

2.7. Plot Area ↑

2.8. Color (fill, line) ↑

2.9. Plot options (plot) ↑

2.10. Enums (accepted spellings) ↑

2.11. Examples ↑

2.11.1. (concise) ↑

JSON({
  type: "column",
  name: "Revenue by Month",
  anchor: { col: 2, row: 5, width: 80, height: 40 },
  series: [
    {
      name: "2024",
      categories: "'Sheet1'!$A$5:$A$16",
      values: "'Sheet1'!$B$5:$B$16",
      stack: "stacked",
      style: { fill: { color: "1F77B4" }, line: { color: "000000", widthPt: 0.75 } },
      colors: ["1F77B4","FF7F0E","2CA02C"],  // optional per-point
      dataLabels: { show: true, showValue: true, separator: "\n" }
    }
  ],
  axes: {
    value: { numberFormat: "#,##0" }
  },
  legend: { show: true, position: "bottom" },
  plot: { gapWidth: 160 }
})

2.11.2. Simple Line chart ↑

Allready populated this:

Here is the test-function:

function ChartTest2 ()

  array string hd = {"Quarter", "2024", "2025"};
  array string cat = {"Q1", "Q2", "Q3", "Q4"};
  array int valB = {10000, 11000, 12000, 13000}; 
  array int valC = {9000,14000, 16000, 13000}; 


  int cnt = sizeof (cat); 
  int i; 

  string dir = desktop_directory() + "ExcelTestCharts/";
  string res = create_directory(dir); 
  string path = dir+format("TestChart2_%s.xlsx", string(usec())); 
  string sheetName = "Sheet1";
  int wb = Excel_Create(path, sheetName); 
  int s = Excel_GetSheetID(wb, 1); 
  res = ExcelSetReportStyleSheet(wb);

  Excel_setCell(s, 1, 1, "Excel Test 2", "H1"); 

  Excel_SetColumns(s, 2, 1, hd, "CH"); 
  int startTable = 3; 

  for (i=1, cnt)
    int row = startTable+i-1; 
    Excel_setCell(s, row, 1, cat[i], "datoTx"); 
    Excel_setCell(s, row, 2, valB[i], "dataNum0"); 
    Excel_setCell(s, row, 3, valC[i], "dataNum0"); 
    
  end for
  int endTable = starttable+i-2; 

// Place the chart. 

chartOptions = JSON(
  "type", "line",
  "name", "Sales by Quarter",
  "anchor", JSON(
    "col", 12,
    "row", 23,
    "width", 60,
    "height", 40
  ),
  "series", JSONarray(
    JSON(   // Series 1
      "name", "2024",
      "categories", Excel_SheetAddress(sheetName, -startTable, -1, -endTable, -1),
      "values", Excel_SheetAddress(sheetName, -startTable, -2, -endTable, -2),
      "style", JSON(
        "fill", JSON(
          "color", "red",
          "tint", 0.25, 
          "alpha", 0.5
        ),
        "line", JSON(
          "color", "red",
          "widthPt", 2
        )
      )
    ),
    JSON( // Series 2
      "name", "2025",
      "categories", Excel_SheetAddress(sheetName, -startTable, -1, -endTable, -1),
      "values", Excel_SheetAddress(sheetName, -startTable, -3, -endTable, -3),
      "style", JSON(
        "fill", JSON(
          "rgb", "blue",
          "tint", 0.25
        ),
        "line", JSON(
          "rgb", "blue",
          "widthPt", 2
        )
      )
    )
  ),
  "legend", JSON(
    "show", true,
    "overlay", false, 
    "position", "bottom",
    "fill", JSON(
      "rgb", "yellow", "alpha", 0.2
    )
  ),
  "plotArea", JSON(
    "fill", JSON(
      "color", "grey",
      "tint", 0.85, 
      "alpha", 0.5
    ), 
    "line", JSON(
      "color", "blue"
    )
  )
);
  excel_placechart ( s, chartOptions); 
  EXCEL_Close (wb); 
  return "OK"; 
end

Result:

2.11.3. Stacked, dynamic series chart (Varying series count and column count) ↑

// Graph below on sheet 1. 

    array string colormap = {
  "1F77B4","FF7F0E","2CA02C","D62728","9467BD",
  "8C564B","E377C2","7F7F7F","BCBD22","17BECF",
  "AEC7E8","FFBB78","98DF8A","FF9896","C5B0D5",
  "C49C94","F7B6D2","C7C7C7","DBDB8D","9EDAE5"
    };

    float chartW = 0.0; 
    for (i= 1, DataColumns)
        chartW += cw1[i+2];
    end for

    chartW = (chartW < 50.0?50.0:chartW); // Guard for 1 month chart....
    float xoff = cw1[3]/2.0; 

    JSON chartOptions = JSON(
  "type", "column",
  "name", "Periodes - Customer segment",
  "anchor", JSON(
    "col", 2,
    "row", r+3,
    "width", chartW,
    "height", chartW/2.0, 
    "offsetX", xoff
  ),
  
  "legend", JSON(
    "show", true,
    "overlay", false, 
    "position", "bottom",
    "fill", JSON(
      "rgb", "yellow", "alpha", 0.2
    )
  ),
  "plotArea", JSON(
    "fill", JSON(
      "color", "grey",
      "tint", 0.85, 
      "alpha", 0.5
    ), 
    "line", JSON(
      "color", "blue"
    )
  )
);

// Add the series to the jsonOption object. 
int cntRows = sizeOf(dataRows); 
cntRows = (cntRows>20?20:cntRows); // Guard against color table. Max 20 series. 
int endcol = DataColumns + 2; 
for (r=1, cntRows)
    int soro = dataRows[r]; 
    chartOptions["series[]"] =  JSON(
      "name", Excel_GetCell(s1, soro, 1),
      "categories", Excel_SheetAddress(sheet1, -5, -3, -5, -endcol), 
      "values", Excel_SheetAddress(sheet1, -soro, -3, -soro, -endcol),//"Sheet1!$B$2:$B$13",
      "stack","stacked", 
      "style", JSON(
        "fill", JSON(
          "color", colormap[r],
          "tint", 0.25, 
          "alpha", 1.0
        ),
        "line", JSON(
          "color", "black",
          "widthPt", 1
        )
      )
    );
end for

  print string(chartoptions); 
  excel_placechart ( s1, chartOptions);