AnnualReport_DemoFurnitures
Back to ListDescription: Full package with report generation to Excel, adding 3 different chart types to the report. Stacked column and line-chart on sheet 1, 2 and 3. Multiple doughnuts on sheet 4.
FileMaker Prototype:
Set Variable [$res; ACF_Run("AnnualReport_DemoFurnitures";)]
Category: EXCEL
The AnnualReport_DemoFurnitures Function is located near the bottom of the programlisting listing, with all its support function defined above.
- function _BuildPivotSheet - are used to generate sheet 1, 2 and 3 in the excel spreadsheet.
- function _PlaceLineChart - are used to place a line-chart on sheet 1, 2 and 3. (Below the stacked column chart)
- function _PlaceStackedColumnChart are used to place a stacked colum chart on sheet 1, 2 and 3.
- function _BuildSellerDetailSheet are used to build the seller detail sheet 4.
- function _PlaceDonut - are used for all the doughnut charts on sheet 4.
- function lookup_color: A helper function to lookup the color for a product or category in the doughnut, so the same product or category uses the same colors on all doughnuts.
- function getColor: Get a color from a 20x color map by index.
- find_in_array, min3, min2 and max helper functions used in the calculations.
The full demo application (FileMaker application), along with the ACF-Plugin 1.7.8.2 (Mac/Win) can be downloaded here:
Function source:
Package AnnualReportDemo "Annual report Excel Spreadsheet demo";
// ============================================================
// Annual Report workbook for "Demo Furnitures Inc"
// Sheets:
// 1) By Category x Period
// 2) By Seller x Period
// 3) By Product x Period
// ============================================================
function find_in_array ( array string arr, string key)
int i;
for ( i = 1, sizeof(arr))
if ( arr[i] == key) then
return i;
end if
end for
return -1;
end
function min3 (int a, int b, int c)
int res;
res = (a<b?a:b);
res = (res<c?res:c);
return res;
end
function min2 (int a, int b)
int res;
res = (a<b?a:b);
return res;
end
function max (int a, int b)
int res;
res = (a>b?a:b);
return res;
end
function getColor (int idx)
array string colormap = {
"1F77B4","FF7F0E","2CA02C","D62728","9467BD",
"8C564B","E377C2","7F7F7F","BCBD22","17BECF",
"AEC7E8","FFBB78","98DF8A","FF9896","C5B0D5",
"C49C94","F7B6D2","C7C7C7","DBDB8D","9EDAE5"
};
idx = mod(idx, 20);
idx = (idx==0?20:idx);
return colormap [idx];
end
// Return up to topK sheet row numbers ranked by totals (desc)
// NOTE: arrays are passed by reference in ACF, so copy before sorting.
function _SelectTopKRows ( array int dataRows, array float totalsForRows, int topK ) // -> array int
array int dr = {dataRows}; // local copies
array float tr = {totalsForRows};
if (sizeof (dr) != sizeof (tr)) then
throw format ("SelectTopKRows, arrays are not equal size, dr=%d, tr=%d", sizeof (dr), sizeof (tr));
end if
// Sort by totals DESC, carrying dataRows along
Sort_array ( > tr, dr );
// Trim to requested K (also safe-guard 20 for colormap)
int K = Min3(topK, 20, sizeof(dr));
array int out;
int i;
for (i=1, K)
out[] = dr[i];
end for
return out;
end
// Returns chart height (points) so caller can compare with table height
function _PlaceDonut ( int sheetID,
string sheetName,
int anchorRow, int anchorCol,
float chartW, float chartH,
string chartTitle,
string catRange, string valRange, array string colors ) // -> float
print format("PlaceDonut: %s, colors: %s\n", chartTitle, implode (",",colors));
JSON j = JSON(
"type", "doughnut",
"name", chartTitle,
"anchor", JSON(
"col", anchorCol,
"row", anchorRow,
"width", chartW,
"height", chartH,
"offsetX", 0.0
),
"legend", JSON("show", true, "position", "bottom"),
"plotArea", JSON("fill", JSON("alpha", 0.0))
);
j["series[]"] = JSON(
"name", chartTitle,
"categories", catRange,
"values", valRange,
"colors", colors
);
Print "-"*40+" Place chart: " + chartTitle + " "+ "-"*40+"\n";
print string(j) + "\n";
EXCEL_PlaceChart(sheetID, j);
return chartH;
end
// Helper to lookup colors per product or category.
function lookup_color ( array string arr, array string colors, string key)
int x = find_in_array ( arr, key);
if (x>0) then
return colors[x];
end if
return "white";
end
function _BuildSellerDetailSheet ( int wb, int sheetID, string sheetName, string tableName ) // -> string
// Ratio to translate chart height (points) -> approx. row count (our empirical ratio)
float kRowsPerAnchorHeight = 33.0 / 72.0; // ≈ 0.458333 rows/pt-height unit
int i;
// ---- Column widths: table (A..D) + spacer (E) + charts area (F..J)
array float cw;
cw[] = 26; // A: Category
cw[] = 14; // B: Value
cw[] = 26; // C: Product
cw[] = 14; // D: Value
cw[] = 3; // E: spacer
cw[] = 14; cw[] = 14; cw[] = 14; // F..H (left doughnut width base)
cw[] = 2; // I: spacer between pies
cw[] = 14; cw[] = 14; cw[] = 14; // J..L (right doughnut width base)
Excel_SetColumnWidth(sheetID, cw);
// ---- Sellers
array string sellers;
string res = ExecuteSQL(
"SELECT DISTINCT Seller FROM " + tableName + " ORDER BY Seller
INTO :sellers"
);
// Assign colors to Products. To ensure all doughnut's have the same product colors.
array string products;
res = ExecuteSQL(
"SELECT DISTINCT Product FROM " + tableName + " ORDER BY Product
INTO :products"
);
array string prodcolors;
for (i=1, sizeof(products))
prodcolors[] = getColor(i);
end for
// Assign colors to categories. To ensure all doughnut's have the same category colors.
array string Categories;
res = ExecuteSQL(
"SELECT DISTINCT Category FROM " + tableName + " ORDER BY Category
INTO :Categories"
);
array string catColors;
int j;
for (j=1, sizeof(Categories))
catColors[] = getColor(i+j); // contine with indexes where prod left off.
end for
// Sheet data
print "CatColors: " + implode(",", catcolors)+"\n";
if (sizeof(sellers) == 0) then
Excel_setCell(sheetID, 1, 1, "No data", "cText");
return "OK";
end if
int rTop = 1;
int s;
for (s=1, sizeof(sellers))
string seller = sellers[s];
// Headline
Excel_setCell(sheetID, rTop, 1, "Seller: " + seller, "H2");
// ---- Data: Category totals for this seller
array string catName; array float catSum;
res = ExecuteSQL(
"SELECT Category, SUM(InvoiceAmount)
FROM "+ tableName + "
WHERE Seller = :seller
GROUP BY Category
ORDER BY SUM(InvoiceAmount) DESC
INTO :catName, :catSum"
);
// ---- Data: Product totals for this seller
array string prodName; array float prodSum;
res = ExecuteSQL(
"SELECT Product, SUM(InvoiceAmount)
FROM "+tableName+"
WHERE Seller = :seller
GROUP BY Product
ORDER BY Product DESC
INTO :prodName, :prodSum"
);
// ---- Table headers
int headerRow = rTop + 1;
Excel_setCell(sheetID, headerRow, 1, "Category", "H2");
Excel_setCell(sheetID, headerRow, 2, "Value", "H2");
Excel_setCell(sheetID, headerRow, 3, "Product", "H2");
Excel_setCell(sheetID, headerRow, 4, "Value", "H2");
// ---- Write rows (side-by-side; independent lengths OK)
int rowsAB = sizeof(catName);
int rowsCD = sizeof(prodName);
int rowsMax = Max(rowsAB, rowsCD);
array string pColor, cColor;
for (i=1, rowsMax)
int row = headerRow + i;
if (i <= rowsAB) then
Excel_setCell(sheetID, row, 1, catName[i], "cText");
Excel_setCell(sheetID, row, 2, catSum[i], "cNum");
cColor[] = lookup_color ( categories, catColors, catName[i]);
end if
if (i <= rowsCD) then
Excel_setCell(sheetID, row, 3, prodName[i], "cText");
Excel_setCell(sheetID, row, 4, prodSum[i], "cNum");
pColor[] = lookup_color ( products, prodcolors, prodName[i]);
end if
end for
// ---- Totals row (sum each side if present)
int sumRow = headerRow + rowsMax + 1;
if (rowsAB > 0) then
string rAB = Excel_SheetAddress(headerRow+1, 2, headerRow+rowsAB, 2);
Excel_setCell(sheetID, sumRow, 1, "Total", "cSum");
Excel_setCellFormula(sheetID, sumRow, 2, "SUM(" + rAB + ")", "cSum");
end if
if (rowsCD > 0) then
string rCD = Excel_SheetAddress(headerRow+1, 4, headerRow+rowsCD, 4);
Excel_setCell(sheetID, sumRow, 3, "Total", "cSum");
Excel_setCellFormula(sheetID, sumRow, 4, "SUM(" + rCD + ")", "cSum");
end if
// ---- Charts to the right (two doughnuts)
// Compute square sizes from column widths (F..H and J..L)
float wLeft = (sizeof(cw)>=8 ? cw[6]+cw[7]+cw[8] : 42.0);
float wRight = (sizeof(cw)>=12 ? cw[10]+cw[11]+cw[12] : 42.0);
float hLeft = wLeft; // square doughnut
float hRight = wRight;
int chartRowAnchor = headerRow; // top aligned with the table header
int leftCol = 6; // F
int rightCol = 10; // J
// Category donut ranges (A/B columns)
string catCats = Excel_SheetAddress(sheetName, -(headerRow+1), -1, -(headerRow+rowsAB), -1);
string catVals = Excel_SheetAddress(sheetName, -(headerRow+1), -2, -(headerRow+rowsAB), -2);
// Product donut ranges (C/D columns)
string prodCats = Excel_SheetAddress(sheetName, -(headerRow+1), -3, -(headerRow+rowsCD), -3);
string prodVals = Excel_SheetAddress(sheetName, -(headerRow+1), -4, -(headerRow+rowsCD), -4);
float h1 = 0.0, h2 = 0.0;
if (rowsAB > 0) then
h1 = _PlaceDonut(sheetID, sheetName, chartRowAnchor, leftCol, wLeft, hLeft,
"Category share (" + seller + ")", catCats, catVals, cColor);
end if
if (rowsCD > 0) then
h2 = _PlaceDonut(sheetID, sheetName, chartRowAnchor, rightCol, wRight, hRight,
"Product share (" + seller + ")", prodCats, prodVals, pColor);
end if
// ---- Advance rTop to next free row:
// translate chart height (points) into rows using your ratio
int chartRows = int(Max(h1, h2) * kRowsPerAnchorHeight);
int tableRows = (sumRow - rTop) + 1;
int advance = Max(tableRows, chartRows) + 2; // extra gap
rTop = rTop + advance;
end for
return "OK";
end
// ------------------------------------------------------------
// Place a stacked-column chart below the pivot table
// sheetID : target sheet
// chartName : label for the chart (e.g., "Periods – Category")
// headerRow : header row (the one with "Period" captions). In your code: 4
// rowBase : first data row. In your code: headerRow + 1 (i.e., 5)
// noD : number of data rows (unique dims)
// noP : number of period columns
// dataRows : array of sheet row numbers to chart (subset of rowBase..rowBase+noD-1)
// cw : column-widths array you already set (index 1=first column)
// ------------------------------------------------------------
function _PlaceStackedColumnChart ( int sheetID,
string chartName,
int headerRow,
int rowBase,
int noD,
int noP,
array int dataRows,
array float cw ) // -> void
// ---- Colors (max 20 series)
array string colormap = {
"1F77B4","FF7F0E","2CA02C","D62728","9467BD",
"8C564B","E377C2","7F7F7F","BCBD22","17BECF",
"AEC7E8","FFBB78","98DF8A","FF9896","C5B0D5",
"C49C94","F7B6D2","C7C7C7","DBDB8D","9EDAE5"
};
// ---- Compute chart geometry
// Period columns are 2..(noP+1). Sum their widths for chart width.
float chartW = 0.0;
int c;
for (c=2, 1+noP)
chartW += cw[c];
end for
if (chartW < 50.0) then chartW = 50.0; end if
// A small X offset so the chart aligns nicely after the dimension column
float xoff = cw[2] / 2.0;
// Place chart 2 rows below the last data row
int lastDataRow = rowBase + noD - 1;
int anchorRow = lastDataRow + 3; // leave a blank row
int anchorCol = 2; // start in column B, right after labels
// ---- Build base chart JSON
JSON chartOptions = JSON(
"type", "column",
"name", chartName,
"anchor", JSON(
"col", anchorCol,
"row", anchorRow,
"width", chartW,
"height", chartW / 2.0,
"offsetX", xoff
),
"legend", JSON(
"show", true,
"overlay", false,
"position","bottom"
),
"plotArea", JSON(
"fill", JSON( "color","grey", "tint",0.85, "alpha",0.5 ),
"line", JSON( "color","blue" )
)
);
// ---- Series (stacked)
int cntRows = sizeof(dataRows);
if (cntRows > 20) then cntRows = 20; end if
int r;
int endCol = noP + 1; // last period column (Total is at noP+2 and not charted)
for (r=1, cntRows)
int srow = dataRows[r]; // absolute sheet row of this series
// Series name is the row label in column 1
string sname = Excel_GetCell(sheetID, srow, 1);
// Ranges: categories = headerRow across periods; values = srow across periods
string catRange = Excel_SheetAddress(chartName, -headerRow, -2, -headerRow, -endCol);
string valRange = Excel_SheetAddress(chartName, -srow, -2, -srow, -endCol);
chartOptions["series[]"] = JSON(
"name", sname,
"categories", catRange,
"values", valRange,
"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);
// ---- Place chart
EXCEL_PlaceChart(sheetID, chartOptions);
return chartW / 2.0; // For next chart placement offsetY
end
// ------------------------------------------------------------
// Place a multi-series LINE chart below the first chart
// Returns the chart height (so you can add to offsetY for more charts)
//
// Params:
// sheetID, chartName, headerRow, rowBase, noD, noP, dataRows, cw, topK, offsetY
// - offsetY: vertical offset in points from the table anchor (stacked chart height)
// ------------------------------------------------------------
float MaxY, MinY;
function _PlaceLineChart ( int sheetID,
string chartName,
int headerRow,
int rowBase,
int noD,
int noP,
array int dataRows,
array float totalsForRows,
array float cw,
int topK,
float offsetY ) // -> float
// Colors (max 20 series)
array string colormap = {
"1F77B4","FF7F0E","2CA02C","D62728","9467BD",
"8C564B","E377C2","7F7F7F","BCBD22","17BECF",
"AEC7E8","FFBB78","98DF8A","FF9896","C5B0D5",
"C49C94","F7B6D2","C7C7C7","DBDB8D","9EDAE5"
};
// --- Geometry from period columns (2..noP+1)
float chartW = 0.0;
int c;
for (c=2, 1+noP)
if (c <= sizeof(cw)) then chartW += cw[c]; end if
end for
if (chartW < 50.0) then chartW = 50.0; end if
float chartH = chartW / 2.0; // keep same aspect as first chart
float xoff = (sizeof(cw) >= 2 ? cw[2] : 12) / 2.0;
// Anchor row/col same as first chart, but shifted down by offsetY (+ small gap)
int lastDataRow = rowBase + noD - 1;
int anchorRow = lastDataRow + 2; // leave a blank row after table
int anchorCol = 2;
int gapRows = 1; // small spacing between charts
float kRowsPerAnchorHeight = 33.0 / 72.0;
int extraRows = int(offsetY * kRowsPerAnchorHeight) + gapRows;
anchorRow += extraRows;
float pad = (MaxY - MinY) * 0.10;
MinY = MinY - pad;
MaxY = MaxY + pad;
JSON chartOptions = JSON(
"type", "line",
"name", chartName + " – Trend",
"anchor", JSON(
"col", anchorCol,
"row", anchorRow,
"width", chartW,
"height", chartH,
"offsetX", xoff
),
"legend", JSON("show",true,"overlay",false,"position","bottom"),
"plotArea", JSON(
"fill", JSON("color","white","alpha",0.0),
"line", JSON("color","blue")
),
"axes", JSON(
"value", JSON("min", MinY, "max", MaxY, "showMajorGrid",true,
"majorGridlines", JSON(
"line", JSON(
"color", "grey",
"alpha", 0.25,
"widthPt", 0.25
))),
"category", JSON("showMajorGrid", false)
),
"markers", JSON("show", true, "size", 3)
);
// --- Choose Top-K by grand total (column noP+2)
int totalCol = noP + 2;
// --- Series
int r, cntRows = sizeof(dataRows);
int endCol = noP + 1; // last period column (Total is noP+2)
string catRange = Excel_SheetAddress(chartName, -headerRow, -2, -headerRow, -endCol);
for (r=1, cntRows)
int srow = dataRows[r];
string name = Excel_GetCell(sheetID, srow, 1);
string vals = Excel_SheetAddress(chartName, -srow, -2, -srow, -endCol);
chartOptions["series[]"] = JSON(
"name", name,
"categories", catRange,
"values", vals,
"style", JSON(
"line", JSON(
"color", colormap[r],
"widthPt", 2
),
"fill", JSON( // keep lines unfilled (transparent under/over)
"alpha", 0.0
)
)
);
end for
Print "Linechart:\n" + string(chartOptions);
EXCEL_PlaceChart(sheetID, chartOptions);
return chartH;
end
// ------------------------------------------------------------
// Helper: Build one pivot sheet (Dimension x Period)
// dimensionField must be one of: "Category", "Seller", "Product"
// ------------------------------------------------------------
function _BuildPivotSheet ( int wb, int sheetID, string title, string tableName, string dimensionField ) // -> string
// 1) Get all distinct Periods in sorted order (columns)
array string periods;
string res = ExecuteSQL(
"SELECT DISTINCT Period FROM " + tableName + " ORDER BY Period
INTO :periods"
);
int noP = sizeof(periods);
if (noP == 0) then
// No data: write a simple header and return
Excel_setCell(sheetID, 1, 1, title, "H1");
Excel_setCell(sheetID, 3, 1, "No data", "cText");
return "OK";
end if
// 2) Get the aggregated data in one pass: Dimension, Period, SUM
array string dimArr, perArr;
array float sumArr;
res = ExecuteSQL(
"SELECT " + dimensionField + ", Period, SUM(InvoiceAmount) " +
"FROM " + tableName + " " +
"GROUP BY " + dimensionField + ", Period " +
"ORDER BY " + dimensionField + ", Period \n" +
"INTO :dimArr, :perArr, :sumArr"
);
int n = sizeof(dimArr);
// 3) Build list of unique dimension values (rows), in order encountered
array string dimsUnique;
int i;
for (i=1, n)
if ( find_in_array(dimsUnique, dimArr[i]) < 0 ) then
dimsUnique[] = dimArr[i];
end if
end for
int noD = sizeof(dimsUnique);
// 4) Make a 2D pivot (dimsUnique x periods) – store as a flat array row-major
// Initialize all to 0
array float data;
int r, c;
for (r=1, noD)
for (c=1, noP)
data[] = 0.0;
end for
end for
// map indexes
int rowIx, colIx, posx;
for (i=1, n)
rowIx = find_in_array(dimsUnique, dimArr[i]);
colIx = find_in_array(periods, perArr[i]);
if (rowIx>0 && colIx>0) then
posx = (rowIx-1)*noP + colIx;
data[posx] = sumArr[i];
end if
end for
// 5) Write heading + header row
Excel_setCell(sheetID, 1, 1, "Annual report – Demo Furnitures Inc", "H1");
Excel_setCell(sheetID, 2, 1, title, "H2");
// Column headers: A = dimension title, then one column per Period, then a Total
int headerRow = 4;
Excel_setCell(sheetID, headerRow, 1, dimensionField, "H2");
for (c=1, noP)
Excel_setCell(sheetID, headerRow, c+1, periods[c], "H2");
end for
Excel_setCell(sheetID, headerRow, noP+2, "Total", "H2");
// Set some column widths (first wider, then medium, end for total)
array float cw;
cw[] = 28; // dimension column
for (c=1, noP) cw[] = 12; end for
cw[] = 14; // total column
Excel_SetColumnWidth(sheetID, cw); // ref
// 6) Write rows
int rowBase = headerRow + 1;
float rowSum;
array int dataRows;
array float totalsForRows;
MaxY = -1.0E10;
MinY = 1.0E10;
for (r=1, noD)
int outRow = rowBase + (r-1);
// Dimension label
dataRows[] = outrow;
Excel_setCell(sheetID, outRow, 1, dimsUnique[r], "cText");
// Values across periods
rowSum = 0.0;
for (c=1, noP)
posx = (r-1)*noP + c;
float v = data[posx];
Excel_setCell(sheetID, outRow, c+1, data[posx], "cNum");
rowSum += data[posx];
if (v < MinY) then MinY = v; end if
if (v > MaxY) then MaxY = v; end if
end for
totalsForRows[] = rowSum;
string formula = format("SUM(%s)", Excel_SheetAddress(outRow, 2, outRow, noP+1));
Excel_setCellFormula(sheetID, outRow, noP+2, formula, "cNum");
end for
// 7) Sum row at bottom (Excel formulas)
int sumRow = rowBase + noD;
Excel_setCell(sheetID, sumRow, 1, "Total", "cSum");
// Period sums
for (c=1, noP)
// =SUM( R[rowBase]:R[rowBase+noD-1] in column c+1 )
string f = format("SUM(%s)", Excel_SheetAddress(rowBase, c+1, rowBase + noD - 1, c+1));
Excel_setCellFormula(sheetID, sumRow, c+1, f, "cSum");
end for
// Grand total
string fTot = format("SUM(%s)", Excel_SheetAddress(rowBase, noP+2,rowBase + noD - 1, noP+2));
Excel_setCellFormula(sheetID, sumRow, noP+2, fTot, "cSum");
double h = _PlaceStackedColumnChart(
sheetID,
title,
headerRow,
rowBase,
noD,
noP,
dataRows,
cw // the same widths array we already computed/set
);
// Second chart (lines) — pass h so it stacks right below
h += _PlaceLineChart(sheetID, title, headerRow, rowBase, noD, noP, dataRows, totalsForRows, cw, 12, h);
return "OK";
end
function AnnualReport_DemoFurnitures () // -> string (path)
string res;
// ---- Choose/save path
string path = desktop_directory() + format("AnnualReport-DemoFurnitures_%s.xlsx", string(now(), "%Y-%m-%d-%H%I%S"));
if (file_exists(path)) then
res = delete_file(path);
end if
// ---- Create workbook and a base sheet (sheet 1 exists by default)
string bycat = "By Category", bySel = "By Seller", byPro = "By Product";
string bySelDet = "By Seller details";
int wb = Excel_Create(path, byCat); // Creates workbook + first sheet
int s1 = EXCEL_GetSheetID(wb, 1); // Get SheetID of the first sheet (ref)
int s2 = Excel_Add_Worksheet(wb, bySel); // Add 2nd sheet (ref)
int s3 = Excel_Add_Worksheet(wb, byPro); // Add 3rd sheet
int s4 = Excel_Add_Worksheet(wb, bySelDet);
// ---- Minimal styles
excel_addstyle (wb, "H1", json("Font", "Arial","FontSize", 18, "Decoration", "Bold"));
excel_addstyle (wb, "H2", json("Font", "Arial", "FontSize", 12, "Decoration", "Bold", "FillColor", "DDDDDD"));
excel_addstyle (wb, "cNum", json("NumFormatCode", 37)); // Excel built-in: #,##0.00_);(#,##0.00)
excel_addstyle (wb, "cText", "Default"); // default text
excel_addstyle (wb, "cSum", "cNum");
Excel_SetStyleOptions(wb, "cSum", json("Decoration", "Bold", "FillColor", "EEEEEE", "Borders", json("Top", linetype_thin)));
// ---- Build the four sheets
res = _BuildPivotSheet(wb, s1, byCat, "DemoInvoices", "Category");
res = _BuildPivotSheet(wb, s2, bySel, "DemoInvoices", "Seller");
res = _BuildPivotSheet(wb, s3, byPro, "DemoInvoices", "Product");
res = _BuildSellerDetailSheet(wb, s4, bySelDet, "DemoInvoices");
excel_close(wb); // write file
$$ReportPath = path;
return "OK";
end
function OpenFileInExcel ( string path )
// Location of the Excel App
string excel, launch;
if (isWindows) then
path = substitute (path, "\\", "/");
end if
string basepath = regex_replace("^(.+)/(.+\..+)$", path, "\1");
string filename = regex_replace("^(.+)/(.+\..+)$", path, "\2");
if (isWindows) then
excel = "start excel";
launch = format ("%s %s", excel,filename);
else
excel = "/Applications/Microsoft Excel.app";
if ( ! directory_exists ( excel ) ) then
return "Excel not installed";
end if
launch = format ("open -a \"%s\" %s", excel,filename);
end if
print launch;
return SystemCommand ( launch, basepath);
end
Example
Set Variable [$res; ACF_Run("AnnualReport_DemoFurnitures";)]