SampleExcelDoc
Back to ListDescription: Generate a sample Excel Spreadsheet
FileMaker Prototype:
Set Variable [$res; ACF_Run("SampleExcelDoc"; string_path)]
Category: EXCEL
Function source:
Function SampleExcelDoc (string path)
string res;
if ( path == "") then
return "No path";
end if
if ( file_exists ( path)) then
if ( confirm ("File exists, do you really wnat to overwrite it?")) then
res = delete_file (path);
else
return "File Exists";
end if
end if
int x = Excel_create ( path);
Excel_SetMetaData (x, JSON ("Author", "Me(Ole)","Created", now(), "Changed", now()));
// Get Sheet ID
int s = EXCEL_GetSheetID ( x, 1);
// Create the Excel styles for our WorkBook.
Excel_AddStyle ( x, "H1"); // Cloned from Default
Excel_SetStyleOptions (x, "H1", JSON ("Font", "Areal", "FontSize", 20, "Decoration", "Bold"));
Excel_AddStyle ( x, "H2", "H1"); // Cloned from H1
Excel_SetStyleOptions ( x, "H2", JSON ("FontSize", 14));
// Right aligned sub_header (Set rightmost cell to get a flush-right text)
Excel_AddStyle ( x, "H2R", "H2"); // Cloned from H2
Excel_SetStyleOptions ( x, "H2R", JSON ("Alignment", "Right"));
// Table column headers.
Excel_AddStyle ( x, "CH", "H1");
Excel_SetStyleOptions ( x, "CH",
JSON ("FontSize", 12, "FillColor", "DDDDDD", "Borders",JSON("Bottom", linetype_thin)));
Excel_AddStyle ( x, "Ctitles", "H1"); // Cloned from H1
Excel_SetStyleOptions (x, "Ctitles", JSON ("FontSize", 16,
"Decoration", "Bold", "FillColor", "DDDDDD", "Borders",JSON("Bottom", linetype_thin)));
Excel_AddStyle(x, "data", JSON ("Font", "Calibri", "FontSize", 12, "Alignment", "Top"))
Excel_AddStyle(x, "wrap", "data");
Excel_SetStyleOptions ( x, "wrap", JSON ("TextWrap", true));
Excel_AddStyle (x, "num", "data");
Excel_SetStyleOptions ( x, "num", JSON ("NumFormat", "### ##0.00"));
Excel_AddStyle (x, "SumLine", "num");
Excel_SetStyleOptions ( x, "SumLine", JSON ("Decoration", "Bold", "Borders",
JSON ("Top", linetype_thin,"TopColor", "blue", "Bottom", linetype_double, "BottomColor", "blue")));
Excel_AddStyle (x, "cdata", "data");
Excel_SetStyleOptions ( x, "cdata", JSON ("Alignment", "Top, center"));
// Set the column width.
array float cw = {10,30,40,10,15.5};
Excel_SetColumnWidth (s, cw);
// Sheet content
Excel_setCell ( s, 1, 1, "Excel sheet header", "H1");
string h1 = Excel_GetCell ( s, 1, 1);
print "Cell A1: " + h1 + "\n";
Excel_mergecells ( s,1,1,0,5); // SheetID, row, column, rowspan, colspan
Excel_setCell ( s, 2, 1, "Excel sheet subheader ", "H2");
Excel_mergecells ( s,2,1,0,5); // SheetID, row, column, rowspan, colspan
array string ctitles = {"Line", "Product Name", "Description", "Count", "Line Sum"};
Excel_SetColumns ( s, 3, 1, ctitles, "CH");
int i=1, currentRow = 4 ;
int firstrow = currentRow;
string description = "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.";
Excel_setCell ( s, currentRow, 1, i, "cdata");
Excel_setCell ( s, currentRow, 2, "Computer accessory", "data");
Excel_setCell ( s, currentRow, 3, description, "wrap");
Excel_setCell ( s, currentRow, 4, 22 ,"data");
Excel_setCell ( s, currentRow, 5, 340.5, "num");
currentRow++;i++;
Excel_setCell ( s, currentRow, 1, i, "cdata");
Excel_setCell ( s, currentRow, 2, "Cable management clips", "data");
Excel_setCell ( s, currentRow, 3, "Short description", "wrap");
Excel_setCell ( s, currentRow, 4, 100 ,"data");
Excel_setCell ( s, currentRow, 5, 540.5, "num");
currentRow++;i++;
Excel_setCell ( s, currentRow, 1, i, "cdata");
Excel_setCell ( s, currentRow, 2, "Other clips", "data");
Excel_setCell ( s, currentRow, 3, "Short description2", "wrap");
Excel_setCell ( s, currentRow, 4, 200 ,"data");
Excel_setCell ( s, currentRow, 5, 33.5, "num");
int lastrow = currentRow;
currentRow++;i++;
Excel_setCell ( s, currentRow, 1, "SUM for this order", "SumLine");
Excel_mergecells ( s,currentRow,1,0,4);
string formula = format("SUM(%s)", excel_sheetaddress (firstrow,5,currentRow-1,5));
Excel_setCellFormula ( s, currentRow, 5, formula, "SumLine");
// Testing merge cells.
currentRow = 9;
Excel_setCell ( s, currentRow, 1, "Testing Merge Cells", "Ctitles");
Excel_setCell ( s, currentRow, 2, "b", "Ctitles");
currentRow++;
Excel_setCell ( s, currentRow, 1, "c", "Ctitles");
Excel_setCell ( s, currentRow, 2, "d", "Ctitles");
Excel_mergeCells ( s, currentRow-1, 1, 2, 2); // SheetID, row, column, rowspan, colspan
// Add a sample sheet for the styles defined in the sheet.
int s2 = Excel_add_Worksheet ( x, "Sample Styles");
array string styles = Excel_Liststyles ( x);
int stylecount = sizeof ( styles );
array float cw2 = {10,50};
Excel_SetColumnWidth (s2, cw2);
Excel_setCell ( s2, 1, 1, "Sample styles defined in this sheet", "H1");
for (i = 1, stylecount)
Excel_setCell ( s2, i+3, 1, i, "cdata");
Excel_setCell ( s2, i+3, 2, styles[i], styles[i]);
end for
// Conditional expressen types - Colors are fill colors.
JSON threeColorSliding = JSON (
"Type", "ThreeColorSliding",
"TargetRange", excel_sheetaddress (firstrow,4,lastrow,4),
"MinColor", "yellow",
"MediumColor", "red",
"MaxColor", "blue",
"MinType", "value", // Can also be value, needing "MinValue" tag too.
"MinValue", 1.0,
"MediumType", "value",
"MediumValue", 100.0,
"MaxType", "value", // Can also be value, needing "MaxValue" tag too.
"MaxValue", 200.0,
"Transparency", 10); // Percent
JSON twoColorSliding = JSON (
"Type", "TwoColorSliding",
"TargetRange", "D4:D5",
"MinColor", "yellow",
"MaxColor", "blue",
"MinType", "min",
"MaxType", "max",
"Transparency", 10);
JSON expresision = JSON (
"Type", "Expression",
"TargetRange", excel_sheetaddress(6,1,6,5), // A6:E6
"Expression", excel_sheetaddress(-6, -5)+"<0", // Becomes $E$6<0
"Color", "red",
"Transparency", 10); // Percent
Excel_SetConditionalFormatting (s, expresision);
excel_close(x);
return "OK";
endThe SampleExcelDoc Function is just a demonstration of the new .xlsx Excel spreadsheet commands.
Example
Set Variable [$res; ACF_Run("SampleExcelDoc"; string_path)]Here is the output:
And tab two :
