ACF Library

ExportACFlibList2Excel

Description: Export ACF-Library to Excel

FileMaker Prototype:

Set Variable [$res; ACF_Run("ExportACFlibList2Excel"; string_path)]

Category: EXCEL

Dependencies

• SetDefaultExcelStyles: Set up default styles for your spreadsheet production
• CountLines: Count number of lines in a text string

Function source:

function ExportACFlibList2Excel (string path)
   
   string res; 
   array string ac_FunctionName;
   array string ac_Description;
   array string ac_Category, ac_code;
   array int ac_RemoteID;
   array string ac_dependencies;
   
   if ( path == "") then
      path = save_file_dialogue ("Velg excel export fil", desktop_directory(), "ACF-Lib-listing.xlsx"); 
   end if
   if ( path == "") then
      return "Empty 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 wb = Excel_create ( path); 
   Excel_SetMetaData (wb, JSON ("Author", "Me(Ole)","Created", now(), "Changed", now())); 
   
   // Get Sheet ID
   int s = EXCEL_GetSheetID ( wb, 1); 
   res = SetDefaultExcelStyles(wb);
   
   
   array int cw = {30, 15, 40, 40, 6, 40,10,12};
   Excel_SetColumnWidth (s, cw); 
   
   Excel_setCell ( s, 1, 1, "ACF-Library list as pr. "+string(date(now()),"%d.%m.%Y"), "H1"); 
   Excel_setCell ( s, 2, 1, "Current plugin version is: 1.7.3.1", "H2"); 
   array string columnHD = 
   {"Function name","Category", "Short Description", "Dependencies", "Lib-ID", "URL", "Dep-list"};
   
   Excel_SetColumns ( s, 4, 1, columnHD, "CH"); 
   Excel_setCell ( s, 4, 8, "Code-Lines", "CH,leftbar");
   
   string sql = "SELECT FunctionName, Description, Category, RemoteID, dependencies, code
   FROM ACF_Functions 
   ORDER BY Category ASC, FunctionName ASC
   INTO :ac_FunctionName, :ac_Description, :ac_Category, :ac_RemoteID, :ac_dependencies, :ac_code";
   
   res = ExecuteSQL ( sql); 
   string dep; 
   array string depNames; 
   int lines,i,count = sizeof (ac_FunctionName);
   int currentRow, firstRow = 4; 
   for (i=1, count)
      currentRow = i+firstRow;
      lines = CountLines(ac_code[i]); 
      Excel_setCell ( s, currentRow, 1, ac_FunctionName[i], "bold"); 
      Excel_setCell ( s, currentRow, 2, ac_Category[i], "data"); 
      Excel_setCell ( s, currentRow, 3, ac_Description[i], "wrap"); 
      if ( ac_dependencies[i] != "") then
         dep = substitute (ac_dependencies[i], "#", ""); 
         clear(depNames); 
         res = ExecuteSQL ( "SELECT FunctionName   FROM ACF_Functions 
         WHERE RemoteID IN ("+dep+")
         INTO :depNames");
         Excel_setCell ( s, currentRow, 4, implode (", ", depNames), "wrap"); 
      end if
      Excel_setCell ( s, currentRow, 5, ac_RemoteID[i], "data"); 
      Excel_setCell ( s, currentRow, 6, "https://horneks.no/acf-library?id=" + ac_RemoteID[i] , "data"); 
      Excel_setCell ( s, currentRow, 7, ac_dependencies[i], "wrap"); 
      Excel_setCell ( s, currentRow, 8, lines, "leftbar"); 
   end for
   currentRow++; 
   Excel_setCell ( s, currentRow, 1, "Number of functions: " + count, "SumLine"); 
   Excel_mergecells ( s,currentRow,1,0,7); 
   string formula = format("SUM(%s)", excel_sheetaddress (firstRow,8,currentRow-1,8)); 
   Excel_setCellFormula ( s, currentRow, 8, formula, "SumLine,leftbar"); 
   
   excel_close(wb); 
   return "OK";
end

Set Variable [$res; ACF_Run("ExportACFlibList2Excel"; string_path)]