1. ACF-Plugin Functions for Excel Spreadsheets

1. ACF-Plugin Functions for Excel Spreadsheets
   1. Introduction
2. Commands and Functions
   1. Functions for Creating and Managing Excel Files
      1. Excel_Create
      2. Excel_Open
      3. Excel_SetMetaData
      4. Excel_Close
      5. Excel_SaveAsWorkbook
   2. Functions for Working with Worksheets
      1. Excel_Add_Worksheet
      2. Exel_GetSheetID
      3. Excel_GetSheetName
      4. Excel_SetColumnWidth
   3. Functions for Populating Cells
      1. Excel_setCell
      2. Excel_setCellFormula
      3. Excel_SetColumns
      4. Excel_SetRows
      5. Excel_MergeCells
      6. Excel_SetConditionalFormatting
   4. Functions to retrive content from an Excel Workbook
      1. Excel_GetCell
      2. Excel_GetRowCount
      3. Excel_GetColCount
      4. Excel_GetRows
      5. Excel_GetColumns
      6. Excel_CountSheets
   5. Functions for Defining and Manipulating Styles
      1. Excel_AddStyle
      2. Excel_SetStyleOptions
      3. Excel_GetStyleOptions
      4. Excel_ListStyles
      5. Excel_CleanUnusedStyles
   6. Functions for Manipulating Rows and Columns
      1. Excel_InsertRows
      2. Excel_InsertColumns
      3. Excel_DeleteRows
      4. Excel_DeleteColumns
      5. Excel_ShiftRight
      6. Excel_ShiftLeft
      7. Complete example functions to manipulate rows
   7. Helper Functions
      1. Excel_SheetAddress
   8. Excel_ID and Sheet_ID

1.1. Introduction ↑

The ACF-Plugin introduces a robust set of functions and commands to work with Excel spreadsheets in the XLSX format. This functionality lets you create, manipulate, and style spreadsheets directly within your ACF workflows. There are two primary use cases:

1. Creating New Excel Spreadsheets: Fully generate new XLSX files with custom content and styles.
2. Opening and Altering Existing Spreadsheets Extend functionality to open existing spreadsheets for editing.

2. Commands and Functions ↑

2.1. Functions for Creating and Managing Excel Files ↑

2.1.1. Excel_Create ↑

• Syntax: int Excel_Create(string path {, NameOfSheet})
• Description: Creates a new Excel spreadsheet from scratch.
• Parameters:
  • path: The file path for the new spreadsheet. The file extension must be .xlsx, e.g., ~/Desktop/MyExcelFile.xlsx.
  • NameOfSheetOptional, Name of the first sheet in the workbook. If not given, it will be "Sheet1".
• Returns: An Excel_ID to reference the created spreadsheet.

Example (With some checking of overwrite an existing file):

Function TestExcel (string path)
    string res; 
    if ( path == "") then
        return "No path"; 
    end if
    if ( file_exists ( path)) then
        if ( confirm ("File exists, do you really want to overwrite it?")) then
            res = delete_file (path); 
        else
            return "File Exists";
        end if
    end if
    int wb = Excel_create ( path); 

2.1.2. Excel_Open ↑

• Syntax: int Excel_Open(string path, string Access mode)
• Description: Opens an existing spreadsheet for importing or manipulationg content.
• Parameters:
  • path: The file path for the new spreadsheet. The file extension must be .xlsx, e.g., ~/Desktop/MyExcelFile.xlsx.
  • Access mode The only supported mode is "r", read access. For Saving, use Excel_SaveWorkbookAs
• Returns: An Excel_ID to reference the opened spreadsheet.

Example:

Function TestOpenExcel (string path)
    string res; 
    if ( path == "") then
        return "No path"; 
    end if
    if ( ! file_exists ( path)) then
        throw "No such file: " + path
    end if
    int wb = Excel_Open ( path, "r"); 

2.1.3. Excel_SetMetaData ↑

• Note: This function is mandatory .

• Syntax: Excel_SetMetaData(int Excel_ID, JSON metadata)

• Description: Sets metadata for the Excel file, such as author, creation date, or modification date.

• Parameters:

  • Excel_ID: The ID of the Excel file.
  • metadata: JSON object containing metadata fields.

Example:

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

2.1.4. Excel_Close ↑

• Syntax: Excel_Close(int Excel_ID)
• Description: Writes the content into the Excel file and closes it. This is usually the last thing to do.
• Note: If the file was opened with Excel_Open with accessmode "r", the file is not saved.
• Parameters:
  • Excel_ID: The ID of the Excel file.

Example:

    Excel_Close(wb); 
    return "OK"; 
end

2.1.5. Excel_SaveAsWorkbook ↑

• Syntax: Excel_SaveAsWorkbook(int Excel_ID, string path)
• Description: Writes the content into a new Excel file without closing the current one. This is usually the last thing to do.
• Parameters:
  • Excel_ID: The ID of the Excel file.
  • path: The path of a new file. It cannot be the same as the original since it is still open.
• Note: The original file must still be closed with Excel_Close

Example:

    Excel_SaveAsWorkbook(wb, "~/Desktop/MyNewExcelFile.xlsx");
  Excel_Close (wb); 
    return "OK"; 
end

2.2. Functions for Working with Worksheets ↑

2.2.1. Excel_Add_Worksheet ↑

• Note: The Excel_Create function automatically adds one worksheet. Use EXCEL_GetSheetID to obtain its ID.
• Syntax: int Excel_Add_Worksheet(int Excel_ID, string name)
• Description: Adds a new worksheet to the Excel file.
• Parameters:
  • Excel_ID: The ID of the Excel file.
  • name: The name of the worksheet.
• Returns: A Worksheet_ID to reference the worksheet.

Example:

int s2 = Excel_Add_Worksheet(wb, "Second sheet");

2.2.2. Exel_GetSheetID ↑

• Syntax: int EXCEL_GetSheetID(int Excel_ID, int SheetNumber)
• Description: Retrieves the Sheet ID for a specific sheet within a workbook. The Excel_Create function automatically adds a sheet to the workbook. This function can be used to retrieve its SheetID.
• Parameters:
  • Excel_ID: The ID of the workbook containing the sheet.
  • SheetNumber: The number of the sheet to retrieve (1-based index).
• Returns: The SheetID corresponding to the specified sheet in the workbook.

Example:

int s1 = EXCEL_GetSheetID(wb, 1);

2.2.3. Excel_GetSheetName ↑

• Syntax: string Excel_GetSheetName ( SheetID )
• Parameters:
  • SheetID: The ID of the sheet
• Return Value:
  • A string containing the sheets name (name of the tab in Excel)

Example: acf string SheetName = Excel_GetSheetName (sheet);

2.2.4. Excel_SetColumnWidth ↑

• Form 1 Syntax: Excel_SetColumnWidth(int SheetID, array columnWidths)
• Form 2 Syntax: Excel_SetColumnWidth(int SheetID, int column, float columnWidth)
• Description: Sets the widths of the columns in the worksheet. Default width is 10.
• Parameters:
  • SheetID: The ID of the worksheet.
  • columnWidths: An array of integers or floats specifying the widths for each column.
  • column: Column number
  • columnWidth: a float for setting the with for a single column.

Example:

    array float cw = {10,30,40,10,15.5};
    Excel_SetColumnWidth (s1, cw); 

2.3. Functions for Populating Cells ↑

2.3.1. Excel_setCell ↑

• Syntax: Excel_setCell(int SheetID, int row, int column, value{, string styleName})
• Description: Sets the value and style for a specific cell in the worksheet.
• Parameters:
  • SheetID: The ID of the worksheet.
  • row: The row number of the cell.
  • column: The column number of the cell.
  • value: The value to insert into the cell.
  • The type of value determines the type of cell.
    • string value creates text cells,
    • Int, long or float creates numeric cells
    • Date, time, timestamp creates their corresponding cell types.
    • bool creates boolean cell.
  • styleName: The name of the style to apply to the cell. Default style is "Default". Numberformat, borders, fillcolor, color, alignment is all defined in the selected style.

Example:

Excel_setCell ( s, 1, 1, "Excel sheet header", "H1");

Note: Setting a cell with an empty string creates an empty cell, still having the style applied. This is useful for colored areas with no content, in this example, we have a long text in the first column that is styled with a grey background. The content float over to the next cells. To have the cells in column 2-5 styled with the same color, we use this technique.

    Excel_setCell ( s, 2, 1, "Excel sheet subheader ", "Ctitles"); 
    Excel_setCell ( s, 2, 2, "", "Ctitles"); 
    Excel_setCell ( s, 2, 3, "", "Ctitles"); 
    Excel_setCell ( s, 2, 4, "", "Ctitles"); 
    Excel_setCell ( s, 2, 5, "", "Ctitles"); 

2.3.2. Excel_setCellFormula ↑

• Syntax: Excel_setCellFormula(int SheetID, int row, int column, string formula, string styleName)
• Description: Sets a formula for a specific cell in the worksheet.
• Parameters:
  • SheetID: The ID of the worksheet.
  • row: The row number of the cell.
  • column: The column number of the cell.
  • formula: The formula expression (e.g., SUM(A1:A10)).
  • styleName: The name of the style to apply to the cell.

Example:

    currentRow++;i++; 
    Excel_setCell ( s1, currentRow, 1, "SUM for this order", "SumLine");
    Excel_setCell ( s1, currentRow, 2, "", "SumLine"); 
    Excel_setCell ( s1, currentRow, 3, "", "SumLine"); 
    Excel_setCell ( s1, currentRow, 4, "", "SumLine"); 
    string formula = format("SUM(%s)", Excel_SheetAddress (3,5,currentRow-1,5)); 
    Excel_setCellFormula ( s1, currentRow, 5, formula, "SumLine"); 

2.3.3. Excel_SetColumns ↑

• Syntax: Excel_SetColumns ( SheetID, row, startColumn, array Values, styleName );

• Description: Sets the values of multiple cells in a row, starting from a specific column.

• Parameters:

  • SheetID: The ID of the worksheet.
  • row: The row number where the values will be set.
  • startColumn: The starting column for the values.
  • Values: An array of values to be written into the cells.
  • styleName: The name of the style to apply to the cells.

Example:

array string values = {"Value1", "Value2", "Value3"};
Excel_SetColumns(1, 2, 1, values, "BoldStyle");

2.3.4. Excel_SetRows ↑

• Syntax: Excel_SetRows ( SheetID, StartRow, Column, array Values, styleName );

• Description: Sets the values of multiple cells in a column, starting from a specific row.

• **Parameters **:

  • SheetID: The ID of the worksheet.
  • StartRow: The starting row for the values.
  • Column: The column where the values will be set.
  • Values: An array of values to be written into the cells.
  • styleName: The name of the style to apply to the cells.

  Example:

  array int values = {1,2,3};
  Excel_SetRows(sheet, 1, 2, values, "ItalicStyle");
  

2.3.5. Excel_MergeCells ↑

• Syntax: Excel_MergeCells (int SheetID, int row, int col, int rowspan, int colspan);

• Description: Merges a range of cells into a single cell.

• Parameters

  :

  • SheetID: The ID of the worksheet.
  • row: The starting row of the range to merge.
  • col: The starting column of the range to merge.
  • rowspan: The number of rows to include in the merge.
  • colspan: The number of columns to include in the merge.

  Example:

  // merging B2:D4 Rowspan = 3, Colspan = 2. 
  Excel_MergeCells(sheet, 2, 2, 3, 2);
  

2.3.6. Excel_SetConditionalFormatting ↑

• Syntax: Excel_SetConditionalFormatting ( Excel_ID, JSON Options );
• Description: Applies conditional formatting to a worksheet based on provided JSON options.

• Parameters:

  • Excel_ID: The ID of the worksheet.
  • JSON Options: A JSON object defining the conditional formatting rules.

  Examples:

  // Example 1: 
  JSON threeColorSliding = JSON (
      "Type", "ThreeColorSliding",
      "TargetRange", excel_sheetaddress (firstrow,4,lastrow,4), 
      "MinColor", "yellow", 
      "MediumColor", "red",
      "MaxColor", "blue", 
      "MinType", "value", 
      "MinValue", 1.0, 
      "MediumType", "value",
      "MediumValue", 100.0, 
      "MaxType", "value",  
      "MaxValue", 200.0, 
      "Transparency", 10); // Percent 
  Excel_SetConditionalFormatting (s, threeColorSliding);
  
  // Example 2:     
  JSON twoColorSliding = JSON (
    "Type", "TwoColorSliding",
    "TargetRange", "D4:D5", 
    "MinColor", "yellow", 
    "MaxColor", "blue", 
    "MinType", "min", 
    "MaxType", "max", 
    "Transparency", 10);
  Excel_SetConditionalFormatting (s, twoColorSliding);
  
  // Example 3:
  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);
  
  

2.4. Functions to retrive content from an Excel Workbook ↑

For importing existing Excel spreadsheets with Excel_Open the following functions can be used to retrive content and style information.

2.4.1. Excel_GetCell ↑

• Syntax: string Excel_GetCell ( Sheet_ID, row, column );
• Description: Retrieves the value of a specific cell in the worksheet.
• Parameters:
  • Sheet_ID: The ID of the worksheet.
  • row: The row number of the cell.
  • column: The column number of the cell.

• Return value:

  • String containing the cell content. Numbers are formatted with period as decimal point. You can use the function float or int to convert it to a number if the cell contians a number.

  Example:

  string value = Excel_GetCell(sheet, 2, 3);
  

2.4.2. Excel_GetRowCount ↑

• Syntax: int Excel_GetRowCount ( SheetID );
• Description: Returns the number of rows in the worksheet.
• Parameters:
  • SheetID: The ID of the worksheet.

• Return value:

  • Int: containing the number of rows.

  Example:

  int rowCount = Excel_GetRowCount(sheet);
  

2.4.3. Excel_GetColCount ↑

• Syntax: int Excel_GetColCount ( SheetID );
• Description: Returns the number of columns in the worksheet.
• Parameters:
  • SheetID: The ID of the worksheet.

• Return value:

  • int containing the number of columns.

  Example:

  int colCount = Excel_GetColCount(sheet);
  

2.4.4. Excel_GetRows ↑

• Syntax: array string Excel_GetRows ( Sheet_ID, col, FromRow, ToRow );
• Description: Retrieves the values from multiple rows in a specific column.
• Parameters:
  • Sheet_ID: The ID of the worksheet.
  • col: The column number to read values from.
  • FromRow: The starting row number.
  • ToRow: The ending row number.

• Return value:

  • An string array with the values in the rows for a spesific column.

  Example:

  array string rows; 
  // retrive 10 first rows of column 2
  rows = Excel_GetRows(sheet, 2, 1, 10);
  

2.4.5. Excel_GetColumns ↑

• Syntax: array string Excel_GetColumns ( Sheet_ID, row, FromCol, ToCol );
• Description: Retrieves the values from multiple columns in a specific row.
• Parameters:
  • Sheet_ID: The ID of the worksheet.
  • row: The row number to read values from.
  • FromCol: The starting column number.
  • ToCol: The ending column number.

• Return value:

  • An array of strings with the values in the columns for a spesific row.

  Example:

  array string columns;
  // Return the 5 first cells of row 3
  columns = Excel_GetColumns(sheet, 3, 1, 5);
  

2.4.6. Excel_CountSheets ↑

• Syntax: int Excel_CountSheets ( Excel_ID );
• Description: Returns the number of sheets in the workbook.
• Parameters:
  • Excel_ID: The ID of the workbook.

• Return value:

  • int containing the number of sheets in the workbook

  Example:

  int sheetCount = Excel_CountSheets(wb);
  

2.5. Functions for Defining and Manipulating Styles ↑

The function use a JSON object to define the style parameters. This is documentet on a separate page

2.5.1. Excel_AddStyle ↑

• Syntax: Excel_AddStyle(int Excel_ID, string name, {string clonedFrom | JSON styleOptions})
• Description: Adds a new style to the workbook.
• Parameters:
  • Excel_ID: The ID of the Excel file.
  • name: The name of the style.
  • clonedFrom (optional): The name of an existing style to clone.
  • styleOptions (optional): A JSON object defining the style properties.

Example:

    Excel_AddStyle ( wb, "H1"); // Cloned from Default
    Excel_SetStyleOptions (wb, "H1", JSON ("Font", "Areal", "FontSize", 20, "Decoration", "Bold, Italic"));  
    Excel_AddStyle ( wb, "Ctitles", "H1"); // Cloned from H1
    Excel_SetStyleOptions (wb, "Ctitles", 
        JSON ("FontSize", 16, "Decoration", "Bold", "FillColor", "DDDDDD", 
            "Borders",JSON("Bottom", linetype_thin)));
    Excel_AddStyle(wb, "data", JSON ("Font", "Century Gotic", "FontSize", 12, "Alignment", "Top"))
    Excel_AddStyle(wb, "wrap", "data"); 
    Excel_SetStyleOptions ( wb, "wrap", JSON ("TextWrap", true)); 
    Excel_AddStyle (wb, "num", "data"); 
    Excel_SetStyleOptions ( wb, "num", JSON ("NumFormat", "### ##0.00")); 
    Excel_AddStyle (wb, "SumLine", "num"); 
    Excel_SetStyleOptions ( wb, "SumLine", 
        JSON ("Decoration", "Bold", 
        "Borders", JSON ("Top", linetype_thin,"TopColor", "blue",  
            "Bottom", linetype_double, "BottomColor", "blue"))); 
    Excel_AddStyle (wb, "cdata", "data"); 
    Excel_SetStyleOptions ( wb, "cdata", JSON ("Alignment", "Top, center")); 

2.5.2. Excel_SetStyleOptions ↑

• Syntax: Excel_SetStyleOptions(int Excel_ID, string styleName, JSON styleOptions)
• Description: Modifies the properties of an existing style.
• Parameters:
  • Excel_ID: The ID of the Excel file.
  • styleName: The name of the style to modify.
  • styleOptions: JSON object defining the style properties.

Example: See example for Add style above.

2.5.3. Excel_GetStyleOptions ↑

• Syntax: Excel_GetStyleOptions(int Excel_ID, string styleName)
• Description: Retrives the properties of an existing style.
• Parameters:
  • Excel_ID: The ID of the Excel file.
  • styleName: The name of the style to modify.
• Returns:
  • styleOptions: JSON object describing the style properties. Compatible with ExcelStyleOptions

Example:

JSON style; 
style = Excel_GetStyleOptions(wb, "Ctitles")

Returns:

{
  "Font":"Areal",
  "FontSize": 16,
  "FillColor": "DDDDDD",
  "FillPattern": "solid",
  "Decoration": "Bold",
  "Borders": {
    "Bottom":"thin",
    "BottomColor":"000000"
  },
  "StyleName": "Ctitles"
}

2.5.4. Excel_ListStyles ↑

• Syntax: array string Excel_ListStyles ( ExcelID );
• Description: Returns an arry of all the tyle names used in the Workbook.
• Parameters:
  • ExcelID: The ID of the Excel file.
• Return Value:
  • An array of strings containing the style name of all the styles defined in the sheet.
• Note: When the workbook is saved, only the styles used in the Workbook is saved on the file. The plugin keeps track of the reference count for all styles. If the reference count is zero, no style information is saved in the stylesheet. However, the style-ID is still defined as an empty style. This behaviour might be changed in furure releases of the plugin.

Example:

// Add a sample sheet for the styles defined in the Workbook. 

    array string styles = Excel_ListStyles ( wb);
    int i, stylecount = sizeof ( styles ); 
    int s2 = Excel_add_Worksheet ( wb, "Sample Styles"); 

    array float cw2 = {10,50};
    Excel_SetColumnWidth (s2, cw2); 
    
    Excel_setCell ( s2, 1, 1, "Sample styles defined in this Workbook"); 
    for (i = 1, stylecount)
        Excel_setCell ( s2, i+3, 1, i); 
        Excel_setCell ( s2, i+3, 2, styles[i], styles[i]);
    end for
    outpath = substitute ( path, ".xlsx", "-edited.xlsx"); 
    excel_SaveAsWorkbook ( wb, outpath); 
    excel_close (wb); 
    return "OK";

2.5.5. Excel_CleanUnusedStyles ↑

• Syntax: Excel_CleanUnusedStyles(int ExcelID);
• Description: Removes unused styles from a workbook. Useful if spreadsheet is modified several times, the style table can build up unused styles. When importing, the styles references are counted. Any unused styles can be removed with this command.
• Parameters:
  • ExceelID The ID of the Excel file.

Example:

Excel_CleanUnusedStyles(wb);

2.6. Functions for Manipulating Rows and Columns ↑

The functions for manipulating rows and columns do the following:

• Update all afected formulas to reference new location of referenced cells that has been moved.
• For deletion: If a formula reference a cell inside the deleted area, the formula becomes invalid and is deleted.
• Clone the style for inserted row or columns from the row or column spesificed in the parameters.
• For columns, shift the column-width with the insertion or deletion. New columns default to 10.

2.6.1. Excel_InsertRows ↑

• Syntax: Excel_InsertRows(int sheetID, int row {, int numberofrows {, bool insertabove}});
• Description: Inserts new rows into a sheet at the specified location. By default, rows are inserted below the specified row. This can be changed by setting insertabove to true.
• Parameters:
  • sheetID The ID of the sheet where rows will be inserted.
  • row The 1-based index of the row at which the new rows will be inserted.
  • numberofrows (Optional) The number of rows to insert. Default is 1.
  • insertabove (Optional) If true, the rows are inserted above the specified row. Default is false.

Example:

// Insert 3 rows below row 4
Excel_InsertRows (sheetID, 4, 3, false);

2.6.2. Excel_InsertColumns ↑

• Syntax: Excel_InsertColumns(int sheetID, int column {, int numberofcols {, bool insertleft}});
• Description: Inserts new columns into a sheet at the specified location. By default, columns are inserted to the right of the specified column. This can be changed by setting insertleft to true.
• Parameters:
  • sheetID The ID of the sheet where columns will be inserted.
  • column The 1-based index of the column at which the new columns will be inserted.
  • numberofcols (Optional) The number of columns to insert. Default is 1.
  • insertleft (Optional) If true, the columns are inserted to the left of the specified column. Default is false.

Example:

// Insert 2 columns to the right of column 5
Excel_InsertColumns(sheetID, 5, 2, false);

2.6.3. Excel_DeleteRows ↑

• Syntax: Excel_DeleteRows(int sheetID, int fromRow, int toRow);
• Description: Deletes a range of rows from a sheet. All rows within the specified range, inclusive of fromRow and toRow, are removed, and data below the deleted rows is shifted up.
• Parameters:
  • sheetID The ID of the sheet from which rows will be deleted.
  • fromRow The 1-based index of the first row to delete.
  • toRow The 1-based index of the last row to delete. Use -1 to delete all the remaining rows.

Example:

// Delete rows 10 to 12
Excel_DeleteRows (sheetID, 10, 12);

2.6.4. Excel_DeleteColumns ↑

• Syntax: Excel_DeleteColumns(int sheetID, int fromCol, int toCol);
• Description: Deletes a range of columns from a sheet. All columns within the specified range, inclusive of fromCol and toCol, are removed. If toCol is set to -1, all columns to the right of fromCol are deleted.
• Parameters:
  • sheetID The ID of the sheet from which columns will be deleted.
  • fromCol The 1-based index of the first column to delete.
  • toCol The 1-based index of the last column to delete. Use -1 to delete all columns to the right.

Example:

// Delete columns 10 to 12
Excel_DeleteColumns (sheetID, 10, 12);

// Delete all columns to the right of column 5
Excel_DeleteColumns (sheetID, 5, -1);

2.6.5. Excel_ShiftRight ↑

• Syntax: Excel_ShiftRight (int sheetID, int column, int noOfColumns, int fromRow, int toRow);

• Description As Excel_InsertColumn, except that it works for a range of rows instead of the whole sheet. You can insert columns in one table in your workbook, without altering other rows in your sheet.

• Parameters:

  • sheetID The ID of the sheet from which columns will be shifted.
  • column The leftmost column in the shift.
  • noOfColumns The number of positions to shift right.
  • fromRow and toRow the range of the rows to shift.

2.6.6. Excel_ShiftLeft ↑

• Syntax: Excel_ShiftRight (int sheetID, int column, int noOfColumns, int fromRow, int toRow);

• Description As Excel_ShiftRight, except that it shifts left instead of right. You can remove columns in one table in your workbook, without altering other rows in your sheet.

• Parameters:

  • sheetID The ID of the sheet from which columns will be shifted.
  • column The leftmost column in the shift.
  • noOfColumns The number of positions to shift right.
  • fromRow and toRow the range of the rows to shift.

2.6.7. Complete example functions to manipulate rows ↑

Insert Rows and Delete Rows example

Here is a function to test the ExcelinsertRows and ExceldeleteRows functions:

function TestInsertAndDeleteRows (string path)
    string res, outpath; 
    if ( path == "" ) then
        return "No path"; 
    end if
    int wb = excel_create ( path, "Sheet1");
    Excel_SetMetaData (wb, JSON ("Author", "Me(Ole)","Created", now(), "Changed", now())); 
    res = ExcelSetDefaultStyles ( wb ); 
    int s = EXCEL_GetSheetID ( wb, 1); 
    Excel_setCell ( s, 1, 1, "Test Insert/Delete rows sheet"); 
    
    array int bb = {120,33,200,44,66,700,129,300,2000,18}; 
    int i, count = sizeof (bb); 
    
    for (i=1, count)
        Excel_setCell ( s, i+2, 1, bb[i], "dataNum2"); 
    end for
    
    // SumCell: 
    string formula = format ("SUM(%s)", excel_sheetaddress(3, 1, count+2,1));
    Excel_setCellFormula ( s, count+3, 1, formula, "SumLine"); 
    
    
    // Excel_InsertRows (int sheetID, int row {, int numberofrows {, bool insertabove}});
    excel_setCell ( s, 4, 2, "Row parameter, insert below this");
    excel_setCell ( s, 5, 2, "This should be moved 3 down.");
    
    Excel_InsertRows (s, 4, 3, false); 
    excel_setCell ( s, 11, 2, "Above deleted rows");
    excel_setCell ( s, 14, 2, "Below deleted rows");
    Excel_DeleteRows ( s, 12, 13); 
    
    Excel_CleanUnusedStyles (wb); 
    
    excel_close (wb); 
    return "OK"; 
end

Result:

Insert Columns and Delete Columns example

Here is another function to test Insert/delete columns:

function TestInsertAndDeleteColumns (string path)
    string res, outpath; 
    if ( path == "" ) then
        return "No path"; 
    end if
    int wb = excel_create ( path, "Sheet1");
    Excel_SetMetaData (wb, JSON ("Author", "Me(Ole)","Created", now(), "Changed", now())); 
    res = ExcelSetDefaultStyles ( wb ); 
    int s = EXCEL_GetSheetID ( wb, 1); 
    Excel_setCell ( s, 1, 1, "Test Insert/Delete Columns sheet"); 
    
    array int bb = {120,33,200,44,66,700,129,300,2000,18}; 
    int i, count = sizeof (bb); 
    string f; 
    Excel_setCell ( s, 3, count+1, "Line sum", "CH"); 
    for (i=1, count)
        Excel_setCell ( s, 3, i, "Col:"+string(i), "CH"); // Column headings are text cells
        Excel_setCell ( s, 4, i, bb[i], "dataNum2"); 
        Excel_setCell ( s, 5, i, bb[i]+100, "dataNum2"); 
        // Formulas below rows. 
        f = format ("SUM(%s)", excel_sheetaddress(4, i, 5,i));
        Excel_setCellFormula ( s, 6, i, f, "SumLine"); 
    end for
    
    // SumCell To the right of row 4 and 5: 
    string formula = format ("SUM(%s)", excel_sheetaddress(4, 1, 4,count));
    Excel_setCellFormula ( s, 4, count+1, formula, "SumLine"); 
    formula = format ("SUM(%s)", excel_sheetaddress(5, 1, 5,count));
    Excel_setCellFormula ( s, 5, count+1, formula, "SumLine"); 
    // Grand sum of the sums rightmost. 
    formula = format ("SUM(%s)", excel_sheetaddress(4, count+1, 5,count+1));
    Excel_setCellFormula ( s, 6, count+1, formula, "SumLine"); 
    // Excel_InsertColumns (int sheetID, int column {, int numberofcols {, bool insertleft}});
    
    excel_setCell ( s, 2, 5, "InsLeft");
    excel_setCell ( s, 2, 6, "InsRight");
    
    Excel_InsertColumns (s, 5, 2, false); 
    
    excel_setCell ( s, 2, 9, "DelLeft");
    excel_setCell ( s, 2, 12, "DelRight");
    Excel_DeleteColumns ( s, 10, 11); 
    
    // Move table 1 column right to make room for the line descriptions. 
    Excel_InsertColumns (s, 1, 1, true); // Insert column far left. 
    excel_setCell ( s, 4, 1, "Items-1");
    excel_setCell ( s, 5, 1, "Items-2");
    excel_setCell ( s, 6, 1, "Sum", "SumLine");
    excel_setColumnWidth ( s, 1, 20); // Make it twize as wide (def = 10)
    Excel_CleanUnusedStyles (wb); 
    
    excel_close (wb); 
    return "OK"; 
end

Here is the result:

2.7. Helper Functions ↑

2.7.1. Excel_SheetAddress ↑

Formulas in Excel spreadsheets use Excel style address. To create those dynamically, this function becomes handy.

• Syntax: string Excel_SheetAddress({string sheetName,}int row, int column {, int to_row, int to_column})
• Description: Converts numeric row/column values into an Excel-style address (e.g., "A1").
• Note: Negative row or column numbers makes absolute address, i.e. $A$1 for (-1,-1).
• Parameters:
  • sheetName (optional) the name of the sheet referenced. If left out, current sheet.
  • row: The starting row.
  • column: The starting column.
  • to_row (optional): The ending row (for a range).
  • to_column (optional): The ending column (for a range).
• Returns: An Excel-style address string (e.g., D3:D7, or 'Sheet2'!D3:D7).

Example:

string formula1 = Excel_SheetAddress ( 2,7 ) // G2
string formula2 = Excel_SheetAddress ( 2,7, 5,7 ) // G2:G5
string formula3 = Excel_SheetAddress ( "Sheet1", 2,7 ) // 'Sheet1'!G2
string formula4 = Excel_SheetAddress ( "Sheet2", 2,7, 5,7 ) // 'Sheet2'!G2:G5

2.8. Excel_ID and Sheet_ID ↑

Excel_ID identifies the workbook, while SheetID identifies a sheet within a workbook. The ID's are to some degrees interchangeable, since both contains the Excel_ID.

• The ID's are 32 bit integers.
• Excel_ID is the 16 upper bits in the integer.
• Sheet_ID is the 16 lower bits in the integer.
• For the first sheet, Excel_ID and Sheet_ID will be identical numbers, as we assign sheets from "0".

This means that all commands that require an Excel_ID will work with an Sheet_ID too.