📊 Working with XLSX Spreadsheets

CLua provides a set of functions for loading, reading, creating and editing .xlsx files. These functions use a C# backend via the ClosedXML library and are accessible through a simple Lua API.

📁 File Structure

• Excel spreadsheets are located in the following folder:

  Tables/

• They are loaded on demand by individual macros.

📥 Loading and Creating a Workbook

Loading an existing workbook

local wb = Workbook("FileName.xlsx");

• Workbook(path) creates a workbook object (wb).
• The path must be relative to the Tables/ folder.

Creating a new workbook

local wb = NewWorkbook("NewWorkbook.xlsx");

• Creates a new empty workbook.
• If the file already exists, it returns an error.

Creating or loading a workbook

local wb = NewWorkbook("Workbook.xlsx", true);

• The second parameter true means: if the file exists, load it; otherwise create a new one.
• Useful for scripts that need to work with a workbook regardless of whether it exists.

📄 Accessing Sheets

local sheet = wb:Sheet("SheetName");

• Returns a sheet object you can read from or write to.

Sheet management

-- Add a new sheet
wb:AddSheet("NewSheet");

-- Rename a sheet
wb:RenameSheet("OldName", "NewName");

-- Delete a sheet
wb:DeleteSheet("UnneededSheet");

-- Get a list of all sheets
local sheetNames = wb:GetSheetNames();
for i, name in ipairs(sheetNames) do
    print(i, name);
end

🔎 Checking Existence

local isWb = WorkbookExists("workbookName.xlsx");
local isSheet = wb:ContainSheet("SheetName");

• Returns a boolean indicating whether the given workbook exists.
• Returns a boolean indicating whether the workbook contains the given sheet.

📋 Cell Objects

A Cell object represents a single cell in the spreadsheet and allows elegant manipulation of its content and formatting.

Creating a Cell object

local cell = sheet:Cell(5, 3);  -- row 5, column 3

Method chaining

Cell objects support chaining — multiple operations in sequence:

sheet:Cell(1, 1)
    :SetValue("Header")
    :SetBold(true)
    :SetFontSize(14)
    :SetBackgroundColor("#4472C4")
    :SetFontColor("#FFFFFF")
    :SetAlignment("Center", "Center");

🔍 Reading Values

Basic reading

-- String value
local value = sheet:GetCell(3, 2);  -- backward compatibility
local value = sheet:Cell(3, 2):GetS();

-- Typed value (number, string, boolean, etc.)
local value = sheet:Cell(3, 2):Get();

-- Via Cell object
local cell = sheet:Cell(3, 2);
local value = cell:Get();   -- typed
local text = cell:GetS();   -- string

• Note: Row and column indices start at 1 (as in Excel), not 0.

Reading formatting

local cell = sheet:Cell(5, 3);

-- Font
local isBold = cell:GetBold();
local isItalic = cell:GetItalic();
local isUnderline = cell:GetUnderline();
local isStrikethrough = cell:GetStrikethrough();
local fontSize = cell:GetFontSize();
local fontName = cell:GetFontName();

-- Colors (returns hex string, e.g. "#FF0000")
local bgColor = cell:GetBackgroundColor();
local fontColor = cell:GetFontColor();

-- Alignment
local hAlign = cell:GetHorizontalAlignment();  -- "Left", "Center", "Right", "Justify"
local vAlign = cell:GetVerticalAlignment();    -- "Top", "Center", "Bottom", "Justify"
local isWrapped = cell:GetWrapText();

-- Borders
local topBorder = cell:GetBorderTop();      -- "None", "Thin", "Medium", "Thick", etc.
local bottomBorder = cell:GetBorderBottom();
local leftBorder = cell:GetBorderLeft();
local rightBorder = cell:GetBorderRight();

-- Number format
local format = cell:GetNumberFormat();  -- e.g. "#,##0.00"

-- Formula
local formula = cell:GetFormula();  -- e.g. "=SUM(A1:A10)" or nil
local hasFormula = cell:HasFormula();

-- Merged cells
local isMerged = cell:IsMerged();
local mergedRange = cell:GetMergedRange();  -- e.g. "A1:C1" or nil

-- Cell address
local address = cell:GetAddress();  -- e.g. "C5"

Detailed cell information

local cellInfo = sheet:CellEx(5, 3);

-- cellInfo contains all possible properties:
-- value, format, formula, dataType, isBold, isItalic,
-- fontColor, bgColor, hAlign, vAlign, borders,
-- richText, comment, etc.

print(cellInfo.value);
print(cellInfo.fontName);
if cellInfo.isBold then
    print("Cell is bold");
end

✍️ Writing Values

Basic writing

-- String
sheet:SetCell(3, 2, "Text"); -- backward compatibility
sheet:Cell(3, 2):SetS("Text");

-- Typed value
sheet:Cell(3, 2):Set(123);      -- number
sheet:Cell(3, 2):Set(45.67);    -- float
sheet:Cell(3, 2):Set(true);     -- boolean
sheet:Cell(3, 2):Set("Text");   -- string

-- Via Cell object
local cell = sheet:Cell(3, 2);
cell:SetValue(123);
cell:SetS("Text");  -- string variant

Text formatting

local cell = sheet:Cell(1, 1);

-- Font
cell:SetBold(true);
cell:SetItalic(true);
cell:SetUnderline(true);
cell:SetStrikethrough(true);
cell:SetFontSize(14);
cell:SetFontName("Arial");

Colors

local cell = sheet:Cell(1, 1);

-- Hex format
cell:SetBackgroundColor("#FFFF00");
cell:SetFontColor("#FF0000");

-- RGB format
cell:SetBackgroundColor(RGB(255, 255, 0));
cell:SetFontColor(RGB(255, 0, 0));

-- Theme colors
cell:SetBackgroundColorTheme("Accent1");
cell:SetBackgroundColorTheme("Accent2", 0.5);  -- with tint

-- Indexed colors
cell:SetBackgroundColorIndexed(10);

Available theme colors:

• "Dark1", "Light1", "Dark2", "Light2"
• "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6"
• "Hyperlink", "FollowedHyperlink"
• "Text1", "Text2", "Background1", "Background2"

Alignment

local cell = sheet:Cell(1, 1);

-- Horizontal and vertical alignment
cell:SetAlignment("Center", "Center");

-- Options:
-- Horizontal: "Left", "Center", "Right", "Justify", "General"
-- Vertical: "Top", "Center", "Bottom", "Justify"

-- Text wrapping
cell:SetWrapText(true);  -- text wraps to multiple lines

Borders

local cell = sheet:Cell(1, 1);

-- All sides at once
cell:SetBorder("Thin");

-- Outside border only
cell:SetBorderOutside("Medium");

-- Individual sides
cell:SetBorderTop("Thick");
cell:SetBorderBottom("Thin");
cell:SetBorderLeft("Dashed");
cell:SetBorderRight("Dotted");

-- Styles: "None", "Thin", "Medium", "Thick", "Double", "Dotted", "Dashed"

Number format

local cell = sheet:Cell(1, 1);

cell:SetNumberFormat("#,##0.00");      -- thousands with comma, 2 decimal places
cell:SetNumberFormat("0.00%");         -- percentage
cell:SetNumberFormat("dd.mm.yyyy");    -- date
cell:SetNumberFormat("hh:mm:ss");      -- time

Formula

local cell = sheet:Cell(1, 1);

cell:SetFormula("=SUM(A1:A10)");
cell:SetFormula("=IF(B1>100, \"High\", \"Low\")");

-- Check
if cell:HasFormula() then
    print("Formula:", cell:GetFormula());
end

Clearing content

local cell = sheet:Cell(1, 1);

cell:Clear();           -- clears everything (value + formatting)
cell:ClearContents();   -- clears value only, formatting remains
cell:ClearFormats();    -- clears formatting only, value remains

📐 Working with Ranges

Note: ERange is a shorthand alias for Excel.Range — both can be used interchangeably.

ERange (alias Excel.Range) can be created in several ways:

ERange(2, 1, 10, 5)       -- row1, col1, row2, col2
ERange("A1:E10")           -- string notation
ERange("A1", "E10")        -- two addresses
ERange(cell1, cell2)       -- two Cell objects

-- Set background color for a range
sheet:SetRangeBackgroundColor(ERange(2, 1, 10, 5), "#E0E0E0");

-- RGB variant
sheet:SetRangeBackgroundColor(ERange(2, 1, 10, 5), RGB(224, 224, 224));

-- Bold text for a range
sheet:SetRangeBold(ERange(1, 1, 1, 5), true);

-- Range border
sheet:SetRangeBorder(ERange(1, 1, 10, 5), "Thin");

-- Merge cells
sheet:MergeRange(ERange(1, 1, 1, 3));  -- merges row 1, columns 1-3

📏 Column Width and Row Height

-- Set column width
sheet:SetColumnWidth(1, 25);

-- Auto-fit column width
sheet:AutoFitColumn(1);

-- Get column width
local width = sheet:GetColumnWidth(1);

-- Set row height
sheet:SetRowHeight(1, 30);

-- Get row height
local height = sheet:GetRowHeight(1);

➕➖ Inserting and Deleting Rows/Columns

-- Insert a row above row 5
sheet:InsertRowAbove(5);

-- Delete row 10
sheet:DeleteRow(10);

-- Insert a column before column 3
sheet:InsertColumnBefore(3);

-- Delete column 5
sheet:DeleteColumn(5);

🔍 Searching

All search functions return Cell objects or tables of Cell objects.

Searching the entire sheet

-- Find the first occurrence
local cell = sheet:Find("Total");
if cell then
    print("Found at:", cell:GetAddress());
    cell:SetBold(true);
end

-- Find all occurrences
local cells = sheet:FindAll("Error");
for _, cell in ipairs(cells) do
    cell:SetFontColor("#FF0000");
    print("Error at:", cell:GetAddress());
end

Searching in a range

-- Numeric range
local cell = sheet:FindInRange("Sum", ERange(1, 1, 10, 5));

-- String range
local cell = sheet:FindInRange("Total", ERange("A1:E10"));
local cell = sheet:FindInRange("Total", ERange("A1", "E10"));

-- Two Cell objects forming opposite corners
local cell = sheet:FindInRange("Sum", ERange(cell1, cell2));

-- All occurrences in range
local cells = sheet:FindAllInRange("Error", ERange("A1:Z100"));

Searching in a row or column

-- Find in column 1
local cell = sheet:FindInColumn("Total", 1);
local cell = sheet:FindInColumn("Control", 1, 5);  -- start from row 5

-- Find in row 1
local cell = sheet:FindInRow("Name", 1);
local cell = sheet:FindInRow("Price", 1, 3);  -- start from column 3

-- All occurrences
local cells = sheet:FindAllInColumn("X", 1);
local cells = sheet:FindAllInRow("Header", 1);

📊 Sheet Range

local rows, cols = sheet:GetSheetRange();
print("Sheet has", rows, "rows and", cols, "columns");

-- Iterate all cells
for row = 1, rows do
    for col = 1, cols do
        local value = sheet:Cell(row, col):Get();
        print(row, col, value);
    end;
end;

💾 Saving and Closing

wb:Save();
wb:SaveAs("path/newName.xlsx");
wb:Close();

• wb:Save() saves all changes in the given Workbook object. (Changes are not reflected until you save.)
• Overwrites the original .xlsx file.
• wb:SaveAs() saves all changes to a new file.
• New workbooks must be saved using SaveAs.
• wb:Close() closes the open workbook and its sheets and frees memory.

🧰 Excel Module Helper Functions

-- Convert address to row, col
local row, col = Excel.AddressToRowCol("C5");  -- 5, 3

-- Convert row, col to address
local address = Excel.RowColToAddress(5, 3);  -- "C5"

-- Parse a range
local row1, row2, col1, col2 = Excel.ParseStringRange("A1:C10");

-- Column index to letter
local letter = Excel.ColumnIndexToLetter(3);  -- "C"

-- Letter to column index
local index = Excel.LetterToColumnIndex("C");  -- 3

-- Checks
local isCell = Excel.IsCellAddress("C5");     -- true
local isRange = Excel.IsRange("A1:C10");  -- true

-- Expand range to individual addresses
local cells = Excel.ExpandRange("A1:B2");  -- {"A1", "A2", "B1", "B2"}

-- Normalize range (top-left to bottom-right)
local r1, r2, c1, c2 = Excel.NormalizeRange(10, 1, 5, 3);  -- 1, 10, 3, 5

-- Heuristic type from format
local type = Excel.FormatToType("#,##0.00");  -- "number"

-- Create string range from row/col
local range = Excel.RangeToString(1, 10, 1, 5);  -- "A1:E10"

Example 1: Processing data with search

local wb = Workbook("Units.xlsx");
local sheet = wb:Sheet("EngCon");

-- Find the cell with "Control"
local controlCell = sheet:FindInColumn("Control", 1);
if controlCell then
    -- Write a value to the row below it
    local dataCell = sheet:Cell(controlCell.row + 1, 5);
    dataCell:SetValue("Workshop");
    dataCell:SetBackgroundColor("#90EE90");

    print("Unit written:", dataCell:GetValue());
else
    wb:Close();
    error("Control not found.");
end

-- Find all cells with "Error" and highlight them
local errorCells = sheet:FindAll("Error");
for _, cell in ipairs(errorCells) do
    cell:SetFontColor("#FF0000")
        :SetBold(true)
        :SetBackgroundColor("#FFE0E0");
    print("Error at:", cell:GetAddress());
end

wb:Save();
wb:Close();

Example 2: Formatting a table header

local wb = Workbook("Report.xlsx");
local sheet = wb:Sheet("Data");

-- Find the header row
local headerCells = sheet:FindAllInRow("Name", 1);

-- Format the entire header row
for _, cell in ipairs(headerCells) do
    local row = cell.row;

    for col = 1, 10 do
        sheet:Cell(row, col)
            :SetBold(true)
            :SetBackgroundColor("#4472C4")
            :SetFontColor("#FFFFFF")
            :SetAlignment("Center", "Center")
            :SetBorder("Thin");
    end;

    break;  -- Only the first match needed
end;

-- AutoFit all columns
for col = 1, 10 do
    sheet:AutoFitColumn(col);
end;

wb:Save();
wb:Close();

Example 3: Working with Formulas

local cell = sheet:Cell(1, 1);

cell:SetFormula("=SUM(A1:A10)");
cell:SetFormula("=IF(B1>100, \"High\", \"Low\")");

-- Check
if cell:HasFormula() then
    print("Formula:", cell:GetFormula());
end

⚠️ Important note about formulas:

CLua uses ClosedXML, which does not recalculate formulas automatically. Formulas are written to the file, but their results will be calculated when a value is requested (via cell:Get()) or when the file is opened in Excel, LibreOffice, or another program.

sheet:Cell(1, 1):SetValue(10);
sheet:Cell(1, 2):SetValue(20);
sheet:Cell(1, 3):SetFormula("=A1+B1");

local result = sheet:Cell(1, 3):Get();

🧪 Full Function Reference

Workbook functions

Sheet functions - Basic operations

Sheet functions - Search

All return a Cell object or a table of Cell objects.

Sheet functions - Columns and rows

Sheet functions - Row formatting

Sheet functions - Column formatting

Sheet functions - Ranges

Sheet functions - Freeze panes

Cell functions - Values

Cell functions - Font formatting

Cell functions - Colors

Cell functions - Alignment

Cell functions - Borders

Cell functions - Number format

Cell functions - Reading formatting

Excel helper functions

RGB color helpers

local color = RGB(255, 200, 100)  -- {red=255, green=200, blue=100}
local black = BLACK()
local white = WHITE()
local gray  = GRAY(192)

Note: Although it's not strictly necessary to manually close the workbook, it is recommended when working in larger or repeated macros. CLua keeps the workbook in memory, and if you call Workbook("filename.xlsx") again, it will return the existing reference unless it was previously closed. Using Program → Reset CLua will also unload all open workbooks from memory.