HORNEKS ANS

ACF Library

SetDefaultExcelStyles

Back to List

Description: Set up default styles for your spreadsheet production

FileMaker Prototype: 

Set Variable [$res; ACF_Run("SetDefaultExcelStyles"; int_wb)]

Category: EXCEL

NOTE The Excel functions in the ACF-Plugin is only available from version 1.7.3.0, that can be downloaded from the download area. You can read more documentation for the Excel function in the reference: Excel Functions reference

The ExcelSetDefaultStyles Function creates a set of default styles for use in Excel spreadsheet production. The function can be tailored to the individual taste for the user. Having one such function that can be called from all the different excel reports makes it easier to produce the report functions.

Function source:

function SetDefaultExcelStyles (int wb )
   // Create the Excel styles for our WorkBook. 
   Excel_AddStyle ( wb, "H1"); // Cloned from Default
   Excel_SetStyleOptions (wb, "H1", JSON ("Font", "Areal", "FontSize", 20, "Decoration", "Bold"));  
   Excel_AddStyle ( wb, "H2", "H1"); // Cloned from H1
   Excel_SetStyleOptions ( wb, "H2", JSON ("FontSize", 14));
   // Right aligned sub_header (Set rightmost cell to get a flush-right text)
   Excel_AddStyle ( wb, "H2R", "H2"); // Cloned from H2
   Excel_SetStyleOptions ( wb, "H2R", JSON ("Alignment", "Right"));
   // Table column headers. 
   Excel_AddStyle ( wb, "CH", "H1"); 
   Excel_SetStyleOptions ( wb, "CH", 
      JSON ("FontSize", 12, "FillColor", "DDDDDD", "Borders",JSON("Bottom", linetype_thin)));
      
   Excel_Setstyleoptions ( wb, "Default", JSON ("Alignment", "Bottom", "Font", "Calibri", "FontSize", 12, "Color", "black")); 
   Excel_AddStyle ( wb, "s66", "Default"); 
   Excel_SetStyleOptions ( wb, "s66", JSON ("Alignment", "Bottom,Right", "FontSize", 14, "Decoration", "Bold"));
   Excel_AddStyle ( wb, "cNum", "Default"); 
   Excel_SetStyleOptions ( wb, "cNum", JSON ("NumFormat", "#,##0"));
   Excel_AddStyle ( wb, "s68", "Default"); 
   Excel_SetStyleOptions ( wb, "s68", JSON ("NumFormat", "dd/mm/yyyy;@"));
   Excel_AddStyle ( wb, "Percent", "Default"); 
   Excel_SetStyleOptions ( wb, "Percent", JSON ("NumFormat", "0.0\ %"));
   Excel_AddStyle ( wb, "PercentSum", "Percent"); 
   Excel_SetStyleOptions ( wb, "PercentSum", JSON ("Borders", JSON (
      "Top", linetype_thin,"TopColor","blue" ,"Bottom", linetype_thick,"BottomColor", "blue")));
   
   Excel_AddStyle ( wb, "SumLine", "PercentSum"); 
   Excel_SetStyleOptions ( wb, "SumLine", JSON ("NumFormat", "#,##0", "Decoration", "Bold"));
   
   Excel_AddStyle(wb, "data", JSON ("Font", "Calibri", "FontSize", 12, "Alignment", "Top"))
   Excel_AddStyle(wb, "wrap", "data"); 
   Excel_SetStyleOptions ( wb, "wrap", JSON ("TextWrap", true)); 
   
   Excel_AddStyle ( wb, "bold", "data"); 
   Excel_SetStyleOptions ( wb, "bold", JSON ("Decoration", "Bold"));
   Excel_AddStyle ( wb, "boldUnderline", "bold"); 
   Excel_SetStyleOptions ( wb, "boldUnderline", JSON ("Borders", JSON("Bottom", linetype_thin)));
   Excel_AddStyle ( wb, "greyBoldCenter", "bold"); 
   Excel_SetStyleOptions ( wb, "greyBoldCenter", JSON ("FillColor", "DDDDDD", "Alignment", "Center"));
   Excel_AddStyle ( wb, "leftbar", "data"); 
   Excel_SetStyleOptions ( wb, "leftbar", JSON ("Alignment","Center","Borders", JSON ("Left", linetype_thin,"LeftColor", "blue")));
   
   
   return "OK";
end

Example

Function GenerateWeeklyReport (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

    // Create the spreadsheet
    int wb = Excel_create ( path);

    // Meta data
    Excel_SetMetaData (wb, JSON ("Author", "Me(Ole)","Created", now(), "Changed", now())); 

    // Set up the default styles. 
    res = ExcelSetDefaultStyles(wb);

    // Get the Sheet ID for the first default sheet
    int s1 = EXCEL_GetSheetID(wb, 1);

    // Set the heading. 
    Excel_setCell ( s1, 1, 1, "Weekly report", "H1");
    Excel_setCell ( s1, 2, 1, "Development Department", "H2");
    Excel_setCell ( s1, 2, 10, "Week 51/2024", "H2R");
    ....
    ....

    EXCEL_Close(wb);
        return "OK"; 
END
Back to List