ACF Library

RetrieveDataFromExcel

Description: Return a two-dimensional JSON array from the content of a Excel Spreadsheet

FileMaker Prototype:

Set Variable [$res; ACFU_RetrieveDataFromExcel( string_path;  int_sheet;  int_rowstart;  int_rowend;  int_colstart;  int_colend;  string_textColAstart;  string_textColAend)]

Category: EXCEL

Function source:

Package GetExcelData "Package to retrieve data from a Excel spreadsheet into an two-dimensional JSON array. ";

function RetrieveDataFromExcel (string path, int sheet, int rowstart, int rowend, 
      int colstart, int colend, string textColAstart, string textColAend)
      
   FunctionID 3412; // makes ACFU_RetrieveDataFromExcel from FM Calc dialog. 

   if (path == "") then
      path = select_file ("Choose Excel Document?", desktop_directory()); 
      if (path == "") then // User canceled
         return JSON ("ok", false, "message", "User canceled file open dialog");
      end if
   end if

   int wb = Excel_Open ( path, "r");
   sheet = (sheet==0?1:sheet); 
    
   int sh = EXCEL_GetSheetID ( wb, sheet); 
   int lastRow = Excel_GetRowCount (sh);
   int lastCol = Excel_GetColCount (sh); 
   json SheetData; 

   // Sanitizing the parameters.
   rowstart = (rowstart==0?1:rowstart);
   colstart = (colstart==0?1:colstart); 
   rowend = (rowend>lastrow?lastrow:rowend); 
   colend = (colend>lastcol?lastcol:colend); 
   rowend = (rowend == 0?lastrow:rowend); 
   colend = (colend == 0?lastcol:colend); 

   textColAstart = lower(textColAstart); 
   textColAend = lower (textColAend); 

   if ( rowstart > rowend || colstart > colend) then
      excel_close(wb); 
      return JSON ("ok", false, "message", "Start column greater than end column or start row greater than end row");
   end if
   int i; 

   int ResRowStart, resRowEnd, resColStart = colstart, resColeEnd = colend; 

   if (lastRow == 0) then
      Excel_close(wb); 
      return JSON ("ok", false, "message", "Sheet is empty"); 
   end if
   // Find the start row is given a filter value for start 
   if ( textColAstart !="") then
      // iterate over rows matching text in columnA from rowstart to rowend
      string dta; 
      for (i = rowstart, rowend)
         dta = Excel_GetCell(sh, i, 1); 
         if (trimboth(lower(dta)) == textColAstart) then
            break; 
         end if
      end for
      rowstart = i+1; 
    end if

    // loop all the rows
    ResRowStart = rowstart; 
    for (i=rowstart, rowend)
       if (textColAend != "") then
          dta = Excel_GetCell(sh, i, 1); 
          if (trimboth(lower(dta)) == textColAend) then
             break; 
          end if
       end if
       SheetData["data[]"] = Excel_GetColumns ( sh, i, colstart, colend ); 
       resRowEnd = i; 
    end for
    SheetData["ok"] = true; 
    SheetData["selection"] = JSON( 
       "columns", resColeEnd - resColStart+1, 
       "rows", resRowEnd - ResRowStart + 1, 
       "FirstColumn", resColStart, 
       "FirstRow", ResRowStart, 
       "path", path); 
    excel_close(wb); 
    return SheetData; 
end

ACFU_RetrieveDataFromExcel( 
    ""; // Path - empty gives file selection dialog
    1; // Sheet number
    1; // First row
    0; // Last Row (0= number of rows in sheet)
    1; // First Colum
    0; // Last Column (0 = all columns)
    "Date"; // Start collection after "Date" in A-cell
    "Total time used:" // End collection before "Total time used:" in A-cell. 
    )

{
    "data": [
        [
            "20/09/2025",
            "Here is the plan for tomorrow",
            "Admin System",
            "Consultant Work",
            "01:30:00",
            "1.500000",
            "1.000000",
            "1.500000"
        ],
        [
            "19/09/2025",
            "Testing scripts",
            "Admin System",
            "Consultant Work",
            "02:30:00",
            "2.500000",
            "1.000000",
            "2.500000"
        ],
        [
            "12/09/2025",
            "Script Work",
            "Demo Project",
            "Consultant Work",
            "02:00:00",
            "2.000000",
            "1.000000",
            "2.000000"
        ],
        [
            "11/09/2025",
            "Progress meeting with Einar. \nShown layouts, demo functionality",
            "Demo Project",
            "Meeting",
            "01:00:00",
            "1.000000",
            "0.600000",
            "0.600000"
        ],
        [
            "12/09/2025",
            "Editing layouts, etc",
            "Demo Project",
            "Consultant Work",
            "01:30:00",
            "1.500000",
            "1.000000",
            "1.500000"
        ],
        [
            "18/09/2025",
            "Project work - Programming module C\nTesting layouts - Created test data\nRun API tests",
            "Admin System",
            "Consultant Work",
            "03:00:00",
            "3.000000",
            "1.000000",
            "3.000000"
        ],
        [
            "09/09/2025",
            "Creating DB structure",
            "Demo Project",
            "Consultant Work",
            "03:30:00",
            "3.500000",
            "1.000000",
            "3.500000"
        ]
    ],
    "ok": 1,
    "selection": {
        "FirstColumn": 1,
        "FirstRow": 12,
        "columns": 8,
        "path": "/Users/ole/Documents/CustomerReports/Acme_Consulting_Inc.-2025-09-22_090923.xlsx",
        "rows": 7
    }
}