1. Creating an Excel Report from Claris FileMaker

Although there are several plugins capable of creating Excel documents, they often come with a high cost. The ACF plugin, as a low-cost alternative, is also capable of creating Excel reports. This tutorial is intended for Mac users and covers the use of ACF-Plugin version 1.7.0.19.

1. Creating an Excel Report from Claris FileMaker
   1. About the Excel XML Format
   2. The lib functions
   3. The report generation
   4. Finalizing the Report

This tutorial consists of two parts:

• A general XML library, featuring highly reusable code.
• Data collection, which involves producing the XML for the report using the above library and writing the result to a file.

There are several aspects of the ACF plugin that make it highly ideal for this task:

• The XML datatype in the plugin is ideal for creating XMLs.
• Its SQL implementation simplifies working with the result set from the report's data collection.
• The programming logic is straightforward and efficient for organizing data for the report.
• Developer tools like the XML/JSON dev-tool app facilitate the creation of accurate library functions for XML.

1.1. About the Excel XML Format ↑

Excel can work with various formats: XLSX, XLS, XML, and more. The XLSX format is a ZIP compressed file and is a bit more difficult to work with. The content in an XLSX archive comprises a bunch of XML files, so manipulating them is possible. However, this is not the focus of this tutorial. We will concentrate on the XML format, which is a text format. You can create such a file from Excel using the "Save as" function, and select the 2004/XML format.

The file can be opened in any text editor, and as you will see, it is a very detailed XML file. This file is the target for this tutorial.

With the ACF plugin, we create a source code file for some ACF functions. I have used the XML/JSON dev-tool to create most of the library functions below, each responsible for creating a small part of the XML. This approach ensures that the XML attributes are accurate for each part (XML attributes are in the XML start tag, before the closing bracket).

It might seem like a lot, but the XML format for Excel documents is very detailed, and every detail needs to be accurate to open the document in Excel. Most functions are general, reusable functions.

1.2. The lib functions ↑

The functions are written in the ACF language on a single text file. The file is loaded and compiled using the ACF plugin. Then in a single script step: `Set variable[$result; ACF_Run("MakeSalesMenWeekStatReport")] the functions produce the reports.

A fully working demo can be downloaded at horneks.no/downloads

Here is the lib I created:

package ExcelReport "Functions for creating EXCEL reports";

/*
    ====================================================
    EXCEL report implementation - the excel lib
    ====================================================
*/

Function Excel_DocProp(string Author)
    timestamp createdZ = now(); 
    string CreatedS = string ( createdZ, "%Y-%m-%2023-12-12%h:%m:%sZ");
    XML xmlvar;
    xmlVar["DocumentProperties"] = 
            XML("Author",Author,"LastAuthor",Author,"Created",CreatedS
            ,"LastSaved",CreatedS,"Version","16.00");
    // Assign attributes to the XML...
    XMLattributes(xmlVar["DocumentProperties"], XML("xmlns","urn:schemas-microsoft-com:office:office");
    
    return XmlVar;
End

Function Excel_DocSettings()

    XML xmlvar;
    xmlVar["OfficeDocumentSettings"] = 
            XML("AllowPNG","");
    // Assign attributes to the XML...
    XMLattributes(xmlVar["OfficeDocumentSettings"], XML("xmlns","urn:schemas-microsoft-com:office:office");
    
    return XmlVar;
End


Function Excel_WorkBook()

    XML xmlvar;
    xmlVar["ExcelWorkbook"] = 
            XML("WindowHeight","17440","WindowWidth","28040","WindowTopX","11580"
            ,"WindowTopY","5400","ProtectStructure","False","ProtectWindows","False"
            );
    // Assign attributes to the XML...
    XMLattributes(xmlVar["ExcelWorkbook"], XML("xmlns","urn:schemas-microsoft-com:office:excel");
    
    return XmlVar;
End



Function Excel_Table(int col)

    XML xmlvar;
    xmlVar["Table"] = "";
    // Assign attributes to the XML...
    // removed : "ss:ExpandedRowCount","20",
    // to be added after all rows is added. 
    XMLattributes(xmlVar["Table"], XML("ss:ExpandedColumnCount",string(col),"x:FullColumns","1"
            ,"x:FullRows","1","ss:DefaultColumnWidth","65","ss:DefaultRowHeight","16");

    return XmlVar;
End

Function Excel_WorkSheetOptions()

    XML xmlvar;
    xmlVar["WorksheetOptions"] = 
            XML("Selected","","ProtectObjects","False","ProtectScenarios","False"
            );
    xmlVar["WorksheetOptions.PageSetup"] = 
            XML("Header","","Footer","","PageMargins",""
            );
    xmlVar["WorksheetOptions.Panes.Pane"] = 
            XML("Number","3","ActiveRow","13","ActiveCol","3"
            );
    // Assign attributes to the XML...
    XMLattributes(xmlVar["WorksheetOptions"], XML("xmlns","urn:schemas-microsoft-com:office:excel");
    XMLattributes(xmlVar["WorksheetOptions.PageSetup.Header"], XML("x:Margin","0.3");
    XMLattributes(xmlVar["WorksheetOptions.PageSetup.Footer"], XML("x:Margin","0.3");
    XMLattributes(xmlVar["WorksheetOptions.PageSetup.PageMargins"], XML("x:Bottom","0.75","x:Left","0.7","x:Right","0.7"
            ,"x:Top","0.75");
    
    return XmlVar;
End

Function Excel_Column(int index, int width)
    XML xmlvar;
    xmlVar["Column"] = "";
    // Assign attributes to the XML...
    XMLattributes(xmlVar["Column"], XML("ss:Index",string(index),"ss:AutoFitWidth","0","ss:Width",string(width));
    return XmlVar;
End

The functions described above help us create the document structure. However, we need a few more elements to complete the process.

Excel Styles

The functions below might need to be changed or expanded to reflect the actual styles in your reports. Some functions are designed to create a single style. These are used in the function at the bottom named setStyles. This function combines all the detailed styles into one comprehensive Styles structure.

Function Excel_Style()
    // Function to create a default Excel style
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Alignment", "", "Borders", "", "Font", "", "Interior", "", "NumberFormat", "", "Protection", "");
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", "Default", "ss:Name", "Normal"));
    XMLattributes(xmlVar["Style.Alignment"], XML("ss:Vertical", "Bottom"));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000"));
    
    return XmlVar;
End

Function Excel_RightStyle()
    // Function to create a right-aligned Excel style
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Alignment", "", "Font", "");
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", "s66"));
    XMLattributes(xmlVar["Style.Alignment"], XML("ss:Horizontal", "Right", "ss:Vertical", "Bottom"));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "14", "ss:Color", "#000000", "ss:Bold", "1"));
    
    return XmlVar;
End

Function Excel_NumberStyle()
    // Function to create a numeric Excel style
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Alignment", "", "Borders", "", "Font", "", "Interior", "", "NumberFormat", "", "Protection", "");
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", "cNum"));
    XMLattributes(xmlVar["Style.Alignment"], XML("ss:Vertical", "Bottom"));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000"));
    XMLattributes(xmlVar["Style.NumberFormat"], XML("ss:Format", "#,##0"));
    
    return XmlVar;
End

Function Excel_DateStyle()
    // Function to create a date format Excel style
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Alignment", "", "Borders", "", "Font", "", "Interior", "", "NumberFormat", "", "Protection", "");
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", "s68"));
    XMLattributes(xmlVar["Style.Alignment"], XML("ss:Vertical", "Bottom"));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000"));
    XMLattributes(xmlVar["Style.NumberFormat"], XML("ss:Format", "dd/mm/yyyy;@"));
    
    return XmlVar;
End

Function CreateColorStyle(string ssid, string back, string textcolor)
    // Function to create a colored Excel style
    XML xmlvar;
    xmlVar["Style"] = XML("Interior", "");
    XMLattributes(xmlVar["Style"], XML("ss:ID", ssid));
    XMLattributes(xmlVar["Style.Interior"], XML("ss:Color", back, "ss:Pattern", "Solid"));
    return xmlvar; 
end

Function Excel_SumColStyle(string style)
    // Function to create a sum column style in Excel
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Font", "", "NumberFormat", "", "Borders", XML("Border", "", "Border", ""));
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", style));
    XMLattributes(xmlVar["Style.Borders.Border[1]"], XML("ss:Position", "Bottom", "ss:LineStyle", "Double", "ss:Weight", "3"));
    XMLattributes(xmlVar["Style.Borders.Border[2]"], XML("ss:Position", "Top", "ss:LineStyle", "Continuous", "ss:Weight", "1"));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000", "ss:Bold", "1"));
    XMLattributes(xmlVar["Style.NumberFormat"], XML("ss:Format", "#,##0"));
    
    return XmlVar;
End

Function Excel_BoldTextStyle(string style)
    // Function to create a bold text style in Excel
    XML xmlvar;
    xmlVar["Style"] = 
            XML("Font", "");
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Style"], XML("ss:ID", style));
    XMLattributes(xmlVar["Style.Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000", "ss:Bold", "1"));
    
    return XmlVar;
End

function SetStyles()
    // Function to set various Excel styles
    XML styles; 
    // Standard font style
    styles["Styles"] = Excel_Style(); // Style "Default"
    
    // 14pt Bold style
    styles["Styles.Style[]"] = xml("Font", ""); 
    xmlAttributes(styles["Styles.Style[2]"], xml("ss:ID", "s64")); 
    xmlAttributes(styles["Styles.Style[2].Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "14", "ss:Color", "#000000", "ss:Bold", "1"));
        
    // Standard bold right justified style
    styles["Styles.Style[]"] = XML("Alignment", "", "Font", "");
    xmlAttributes(styles["Styles.Style[3]"], XML("ss:ID", "s66"));
    XMLattributes(styles["Styles.Style[3].Alignment"], XML("ss:Horizontal", "Right", "ss:Vertical", "Bottom"));
    xmlAttributes(styles["Styles.Style[3].Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "14", "ss:Color", "#000000", "ss:Bold", "1"));
            
    // Standard bold with grey background style
    styles["Styles.Style[]"] = XML("Font", "", "Interior", "");
    XMLattributes(styles["Styles.Style[4]"], XML("ss:ID", "s67"));
    XMLattributes(styles["Styles.Style[4].Font"], XML("ss:FontName", "Calibri", "x:Family", "Swiss", "ss:Size", "12", "ss:Color", "#000000", "ss:Bold", "1"));
    XMLattributes(styles["Styles.Style[4].Interior"], XML("ss:Color", "#E7E6E6", "ss:Pattern", "Solid"));
    
    // Other styles defined in their respective functions
    styles["Styles+"] = Excel_DateStyle(); 
    styles["Styles+"] = Excel_NumberStyle();
    styles["Styles+"] = CreateColorStyle("color1", "#70AC46", "#000000");
    styles["Styles+"] = Excel_SumColStyle("cSumRow");
    styles["Styles+"] = Excel_BoldTextStyle("cBoldText"); 
    
    return styles; 
end

Functions for Data Content

Additionally, we require functions for handling the data content in the report. These functions are essential for simplifying content generation by abstracting the XML-specific details from the process of creating the content.

// Function for creating the left and right heading row
Function Excel_headingRow1(string leftText, string rightText, int colcnt)

    XML xmlvar;
    xmlVar["Row.Cell[]"] = XML("Data", leftText);
    xmlVar["Row.Cell[]"] = XML("Data", rightText);
    
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Row"], XML("ss:Height", "19"));
    XMLattributes(xmlVar["Row.Cell[1]"], XML("ss:StyleID", "s64")); // Style for left cell
    XMLattributes(xmlVar["Row.Cell[1].Data"], XML("ss:Type", "String"));
    
    XMLattributes(xmlVar["Row.Cell[2]"], XML("ss:Index", string(colcnt), "ss:StyleID", "s66")); // Style for right cell
    XMLattributes(xmlVar["Row.Cell[2].Data"], XML("ss:Type", "String"));

    return xmlVar;
End

// Function for creating a single left heading row
Function Excel_headingRow2(string leftText)

    XML xmlvar;
    xmlVar["Row.Cell"] = XML("Data", leftText);
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Row"], XML("ss:Height", "19"));
    XMLattributes(xmlVar["Row.Cell"], XML("ss:StyleID", "s64"));
    XMLattributes(xmlVar["Row.Cell.Data"], XML("ss:Type", "String"));
    
    return xmlVar;
End

// Function for creating cells containing text
Function Excel_TextCell(string text, string style)
    XML xmlvar;
    xmlVar["Cell"] = XML("Data", text);
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Cell"], XML("ss:StyleID", style));
    XMLattributes(xmlVar["Cell.Data"], XML("ss:Type", "String"));
    
    return xmlVar;
End

// Function for creating cells containing numeric values
Function Excel_NumberCell(float number, string style)

    XML xmlvar;
    xmlVar["Cell"] = XML("Data", string(number));
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Cell"], XML("ss:StyleID", style));
    XMLattributes(xmlVar["Cell.Data"], XML("ss:Type", "Number"));
    
    return xmlVar;
End

// Function for creating cells containing dates
Function Excel_DateCell(date dato, string style)

    XML xmlvar;
    xmlVar["Cell"] = XML("Data", string(dato, "%Y-%m-%dT00:00:00.000"));
    // Assign attributes to the XML elements
    XMLattributes(xmlVar["Cell"], XML("ss:StyleID", style));
    XMLattributes(xmlVar["Cell.Data"], XML("ss:Type", "DateTime"));
    return xmlVar;
End

// Function for creating a cell with a sum formula
Function Excel_SumColCell(int col, int row, int fromRow, int toRow, string style)

    XML xmlvar;
    xmlVar["Cell"] = XML("Data", "");
    // Assign attributes to the XML elements
    
    string formula = format("=SUM(R[%d]C:R[%d]C)", fromRow - row + 1, toRow - row); 
    print "\n" + formula; 
    XMLattributes(xmlVar["Cell"], XML("ss:Index", string(col), "ss:StyleID", style, "ss:Formula", formula));
    XMLattributes(xmlVar["Cell.Data"], XML("ss:Type", "Number"));
    
    return xmlVar;
End

Some utility functions:

There is also a need for utility functions to facilitate the creation of the final report. One such function is required to search for a specific value in an array and return its index. It's possible that a future version of the ACF plugin will include this functionality built-in.

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

1.3. The report generation ↑

In this example, we will extract data from an order table named OrderHeader. This table will be used to generate a sales report, which will display one line for each salesperson, along with their sales amounts per week for the past 11 weeks, formatted as columns in the spreadsheet.

Main Function - We begin by querying for the report data after making some initial declarations.

function MakeSalesMenWeekStatReport()
// Set the date interval for the report (can also be parameterized)
    date dateTo = date(now()) - 1;  // Set 'dateTo' as yesterday
    int WeeksBack = 11;  // Define the number of weeks to look back
    date dateFrom = dateTo - WeeksBack * 7;  // Calculate 'dateFrom' as 11 weeks prior to 'dateTo'

// Declare arrays for storing query results
    array string uIDs, Perioder, PeriodeIdx;
    array int sAar, sUke; 
    array float ordSum; 

// SQL query definition
    string sql = "SELECT User_ID, Order_Year, Order_Week, sum(Sum_Order) FROM OrderHeader
    WHERE User_ID <> '1' AND Order_date BETWEEN :dateFrom AND :dateTo
    GROUP BY User_ID, Order_Year, Order_Week
    INTO :uIDs, :sAar, :sUke, :ordSum";
    
    // Execute the SQL query
    string res = ExecuteSQL(sql); 

As demonstrated in the snippet above, we calculate two dates: dateTo, set as yesterday, and dateFrom, set as 11 weeks prior.

Subsequently, we declare some arrays to receive the results from the SQL query. The OrderHeader table includes fields such as Order_date, a calculated field Order_Year, and another calculated field named Order_Week, which represents the week number. The WeekOfYearFiscal(Order_Date;2) function is utilized in these calculations to determine the week number.

The INTO part of the SQL query is a special feature of the ACF plugin. It specifies the arrays that will receive the query results. We obtain the results in four arrays: uIDs, sAar, sUke, and ordSum.

Additionally, the use of :dateFrom and :dateTo is specific to the ACF plugin. These placeholders represent actual data from those variables, which will be used at runtime.

To organize the data into weekly periods, we need to create an array of periods corresponding to each row in the other four arrays. We use the year and week numbers to create a period string formatted as YYYY-WW. These strings identify the columns that will receive the data.

int i, cnt = sizeof (uIDs); 
    string cmd = string(dateTo+1, "WeekOfYearFiscal(Date(%m;%d;%Y);2)"); 
    string firstPeriod = string (dateTo+1, "%Y") + format("-%02d", int(eval(cmd)));
    string per; 
    for (i=1, cnt)
        per =  format("%04d-%02d", sAar[i], sUke[i]);
        Perioder[] = per; 
        if ( per < firstPeriod) then
            firstPeriod = per; 
        end if
    end for

Next, we sort the arrays to ensure that all the user IDs are ordered correctly, which is essential for the subsequent handling of breaks. The sorting is done using the following ACF command:

sort_array ( < uIDs, <sAar, <sUke, ordSum, Perioder); 

We also need to create some column headings. In addition, we initialize a data array to store one line of data in the report. At the end of this process, we copy this data into an array named Empty. This approach allows us to easily reset the data array for each new salesperson.

  array float lineData; 
    array string lineHD; 
    date Sun; 
    for (i=1, WeeksBack)
        Sun = dateTo-(i-1)*7;
        cmd = string(Sun, "WeekOfYearFiscal(Date(%m;%d;%Y);2)"); 
        lineHD[] =  string (Sun, "%Y") + format("-%02d", int(eval(cmd)));
        linedata[] = 0; 
    end for
    array float Empty = linedata; 

The variable Sun represents the date of the Sunday corresponding to the week of the column. To obtain the week number for this Sunday, we create a FileMaker calculation. Although the ACF plugin doesn't have a direct function for this, it offers the eval function, which leverages the FileMaker calculation engine to evaluate expressions. The string function mentioned previously takes a date as its first parameter and a format string as the second argument. This format string is a FileMaker command, where %m, %d, and %Y are placeholders for the month, day, and year values, respectively.

Since the user names and signatures are not included in the data arrays, it's necessary to retrieve the user information to map them correctly in the final rows. The column Bruker_ID is numeric, but we need it in a text format. To achieve this, a short loop is used to convert and store these numeric IDs as text variants in the array Selger_IDs.

  array string selger_IDs, selger_Navn, selger_signatur; 
    array int selger_id_num; 
    
    // Grab users
    sql = "SELECT Bruker_ID, Navn, Signatur FROM Users
    ORDER BY Navn
    
    INTO :selger_id_num, :selger_Navn, :selger_signatur";
    res = ExecuteSQL ( sql ); 
    
    for ( i=1, sizeof ( selger_id_num))
        selger_IDs[] = string (selger_id_num[i] ); 
    end for

The Production of the Excel Document:

We start by declaring two variables: Excel as an XML datatype, and NumberOfRows to keep track of the row count in our report. It's important to update the XML with the row count once all the columns have been added.

XML excel; 
int NumberOfRows; 

Next, we construct the structural elements of the XML, utilizing the library functions discussed in the first part of this article:

    clear(excel); // We clear it up first.... 
    excel["Workbook"] = Excel_DocProp("John Doe Author");
    excel["Workbook"] = Excel_DocSettings();
    excel["Workbook"] = Excel_WorkBook();
    excel["Workbook"] = SetStyles();
    
    XMLattributes(excel["Workbook"], XML("xmlns","urn:schemas-microsoft-com:office:spreadsheet",
    "xmlns:o","urn:schemas-microsoft-com:office:office",
    "xmlns:x","urn:schemas-microsoft-com:office:excel",
    "xmlns:ss","urn:schemas-microsoft-com:office:spreadsheet",
    "xmlns:html","https://www.w3.org/TR/REC-html40");
    
    int TotalColumns = 3+WeeksBack; 
    XML worksheet; 
    worksheet["Worksheet"] = Excel_Table(TotalColumns);
        // Assign attributes to the XML...
    XMLattributes(worksheet["Worksheet"],XML("ss:Name","SalesWeekReport");
    
    // Define the column-with. All except A, B, and C should be 80. 
    array int cw = {65,200,65}; 
    int colw;
    for (i=1, TotalColumns)
        colw = (i>3)?80:cw[i]; 
        worksheet["Worksheet.Table+"] = Excel_Column(i,colw); 
    end for
     
    // Add a row for the headings, merging in the lineHD array created above
    array string headings = {"SelgerID", "Navn", "Signatur", lineHD};
     
    // Top two lines are report headings. 
    worksheet["Worksheet.Table+"] = Excel_headingRow2 ( "Salesperson report - Week statistics"); 
    worksheet["Worksheet.Table+"] = Excel_headingRow2 ( "To be run each monday ");
    // Next line has date interval left, and description for the data rows to the right, 
    worksheet["Worksheet.Table+"] = Excel_headingRow1 ( string (dateFrom, "%d.%m.%Y" +" - " + string (dateTo, "%d.%m.%Y")), "Year-Week number", TotalColumns);
    

Then we add the rows for the column headings:

// Create the heading rows for the table. 
    XML row; 
    clear ( row ); 
    for (i=1, sizeof(headings))
        row["Row+"] = Excel_TextCell(headings[i], "s67"); 
    end for
    worksheet["Worksheet.Table+"] = row;
    NumberOfRows = 5; // Next row is number 5
    
    excel["Workbook"] = worksheet; 
    excel["Workbook"] = Excel_WorkSheetOptions(); 

Now, we iterate through the arrays. The process involves a break upon each change in User-ID. For each new person identified, we append a new row to the Excel document. All the rows in our table corresponding to the same salesperson contribute data to the lineData array. During this process, we also retrieve each salesperson's name and signature to ensure this information is included.

int uix, j; 
string Name, Signature; 
slepSelger=uIDs[1]; 
float errorSum = 0; 
bool addData = False; 
// Produce data rows for the spreadsheet. 
for (i=1, cnt)
    // Check for a new salesperson. 
    if (uIDs[i] != slepSelger) then
        uix = find_in_array(selger_IDs, slepSelger); 
        if (uix > 0) then
            Name = selger_Navn[uix]; 
            Signature = selger_signatur[uix]; 
        else
            Name = "-"; 
            Signature = "-"; 
        end if
        
        // Add a new row to the Excel sheet.
        clear(row);
        row["Row+"] = Excel_TextCell(slepSelger, "Default");
        row["Row+"] = Excel_TextCell(Name, "Default");
        row["Row+"] = Excel_TextCell(Signature, "Default");
        
        for (j = 1, WeeksBack)
            row["Row+"] = Excel_NumberCell(linedata[j], "cNum");
        end for
        excel["Workbook.Worksheet.Table+"] = row; 
        NumberOfRows++; 
        
        linedata = Empty; 
        addData = False; 
        slepSelger = uIDs[i]; 
    end if
    
    // Accumulate data for the current salesperson.
    uix = find_in_array(lineHD, Perioder[i]); 
    if (uix > 0 && uix <= WeeksBack) then
        linedata[uix] = linedata[uix] + ordSum[i]; 
        addData = True; 
    else
        errorSum += ordSum[i]; 
    end if
end for

When we reach the last row in the table, there isn't a natural break because the loop is concluding. Therefore, we need to ensure that this final line is also added. The process for this involves replicating the code used in the previous conditional statement (the if statement). This step ensures that the data for the last salesperson is properly captured and included in the Excel document, just like for the preceding rows.

// Some data for the last sales person, not handled in the break above. 
    if ( addData ) then
        uix = find_in_array ( selger_IDs, slepSelger); 
        if ( uix > 0) then
            Name = selger_Navn[uix]; 
            Signature = selger_signatur[uix]; 
        else
            Name = "-"; 
            Signature = "-"; 
        end if
        
        // Add the last row to excel
        clear ( row );
        row["Row+"] = Excel_TextCell(slepSelger, "Default");
        row["Row+"] = Excel_TextCell(Name, "Default");
        row["Row+"] = Excel_TextCell(Signature, "Default");
    
        for (j = 1, WeeksBack)
            row["Row+"] = Excel_NumberCell(linedata[j], "cNum");
        end for
        excel["Workbook.Worksheet.Table+"] = row; 
        NumberOfRows ++; 
    end if

Adding a Sum Line Below the Table

It's beneficial to include a sum line below the table. Rather than calculating the sums directly, we insert Excel formulas for the summation. This approach allows users to manually edit data in the report while ensuring the sums remain accurate.

// Add a sum-line
     
    clear ( row );
    row["Row+"] = Excel_TextCell("", "cBoldText");
    row["Row+"] = Excel_TextCell("Sum alle selgere", "cBoldText");
    row["Row+"] = Excel_TextCell("", "cBoldText");
    for (i=1, WeeksBack)
        row["Row+"] = Excel_SumColCell(i+3, NumberOfRows, 4, NumberOfRows-1, "cSumRow"); 
    end for
    excel["Workbook.Worksheet.Table+"] = row; 
    NumberOfRows ++; 

1.4. Finalizing the Report ↑

To simplify the process, we create a folder on the user's desktop named "Monday_reports". Within this folder, we establish a file path:

// Crete a path for the report. 
    res = create_directory ( Desktop_directory () + "/Monday_reports/"); 
    string path = Desktop_directory () + "/Monday_reports/SelgerHistorikk_"+ string ( now(), "%Y%m%d_%h%i%s") + ".xml";
    

Update table element with the number of rows:

// Update table attributes to reflex the number of rows. 
    XMLattributes (excel["Workbook.Worksheet.Table"], xml("ss:ExpandedRowCount",string(NumberOfRows)); 

Then we save the report to the file, and returns.

// Save the report to the file. 
    string report = string (excel); // Stringify the XML
    clear (excel); 
    
    int x = open (path, "w" ); 
    write ( x, report); 
    close (x); 
    
    return "OK";

end

Here is the produced report opened in Excel