UPDATE: Ladies and gentlemen: conditional formatting!

(and everything in between)

base/src/new_empty.rs

@@ -68,6 +68,7 @@ impl Model {
             frozen_rows: 0,
             show_grid_lines: true,
             views,
+            conditional_formatting: vec![]
         }
     }
 

base/src/types.rs

@@ -117,6 +117,26 @@ pub struct Worksheet {
     pub views: HashMap<u32, WorksheetView>,
     /// Whether or not to show the grid lines in the worksheet
     pub show_grid_lines: bool,
+    pub conditional_formatting: Vec<ConditionalFormatting>
+}
+
+#[derive(Encode, Decode, Debug, PartialEq, Clone)]
+pub struct ColorScale {
+
+}
+
+#[derive(Encode, Decode, Debug, PartialEq, Clone)]
+pub enum CfRule {
+    ColorScale {
+        priority: u32,
+    },
+    CellIs,
+}
+
+#[derive(Encode, Decode, Debug, PartialEq, Clone)]
+pub struct ConditionalFormatting {
+    sqref: String,
+    cf_rule: Vec<CfRule>
 }
 
 /// Internal representation of Excel's sheet_data

docs/src/.vitepress/config.mts

@@ -58,30 +58,6 @@ export default defineConfig({
             text: "Error Types",
             link: "/features/error-types",
           },
-          {
-            text: "Value Types",
-            link: "/features/value-types",
-          },
-          {
-            text: "Optional arguments",
-            link: "/features/optional-arguments",
-          },
-          {
-            text: "Units",
-            link: "/features/units",
-          },
-          {
-            text: "Implicit Intersection",
-            link: "/features/implicit-intersection",
-          },
-          {
-            text: "Arrays and array formulas",
-            link: "/features/arrays",
-          },
-          {
-            text: "Dynamic Arrays",
-            link: "/features/dynamic-arrays",
-          },
           {
             text: "Unsupported Features",
             link: "/features/unsupported-features",

docs/src/features/arrays.md

@@ -1,15 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Arrays
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-::: warning
-**Note:** This feature is not yet available on IronCalc 🚧
-:::

docs/src/features/dynamic-arrays.md

@@ -1,15 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Dynamic Arrays
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-::: warning
-**Note:** This feature is not yet available on IronCalc 🚧
-:::

docs/src/features/error-types.md

@@ -10,36 +10,28 @@ lang: en-US
 **Note:** This page is in construction 🚧
 :::
 
-The result of a formula is sometimes an _error_. In some situations those errors are expected and your formulas might be dealing with them.
-The error `#N/A` might signal that there is no data to evaluate the formula yet. Maybe the payrol has not been introduced for that month just yet.
+When working with formulas, you may encounter these common errors:
 
-Some other errors like `#SPILL!`, `#CIRC!` or `#ERROR!` signal an error in your spreadsheet logic and must be corrected.
+---
 
-The first kind of errors or 'common errors' are found in other spreadsheet engines like Excel while other errrors like `#ERROR!` or `#N/IMPL` are particular to IronCalc.
+### **`#ERROR!`**
 
-## Common Errors
+**Cause:** General formula issue, like syntax errors or invalid references.  
+**Fix:** Check the formula for mistakes or invalid cell references.
+
+---
 
 ### **`#VALUE!`**
 
-It might be caused by mismatched data types (e.g., text used where numbers are expected):
+**Cause:** Mismatched data types (e.g., text used where numbers are expected).  
+**Fix:** Ensure input types are correct; convert text to numbers if needed.
 
-```
-5+"two"
-```
-
-The engine doesn't know how to add the number `5` to the string `two` resulting in a `#VALUE!`.
-
-It is an actual error in your spreadsheet. It indicates that the formula isn’t working as intended.
+---
 
 ### **`#DIV/0!`**
 
-Division by zero or an empty cell.
-
-```
-=1/0
-```
-
-Usually this is an error. However, in cases where a denominator might be blank (e.g., data not yet filled in), this could be expected. Use `IFERROR` or `IF` to handle it.
+**Cause:** Division by zero or an empty cell.  
+**Fix:** Ensure the denominator isn’t zero or blank. Use `IF` to handle such cases:
 
 ```
 =IF(B1=0, "N/A", A1/B1)
@@ -47,42 +39,23 @@ Usually this is an error. However, in cases where a denominator might be blank (
 
 ### **`#NAME?`**
 
-Found when a name is not recognized. Maybe a misspeled name for a function. Could be a referenceto defined name that has been deleted.
-
-```
-=UNKOWN_FUNCTION(A1)
-```
-
-This indicates an error in your spreadsheet logic.
+**Cause:** Unrecognized text in the formula (e.g., misspelled function names or undefined named ranges).  
+**Fix:** Correct spelling or define the missing name.
 
 ### **`#REF!`**
 
-Indicates an invalid cell reference, often from deleting cells used in a formula.
-
-They can appear as a result of a computation or in a formula. Examples:
-
-```
-=Sheet34!A1
-```
-
-If `Sheet34` doesn't exist it will return `#REF!`
-
-This is a genuine error. It indicates that part of your formula references a cell or range that is missing.
+**Cause:** Invalid cell reference, often from deleting cells used in a formula.  
+**Fix:** Update the formula with correct references.
 
 ### **`#NUM!`**
 
-Invalid numeric operation (e.g., calculating a square root of a negative number).  
-Adjust the formula to ensure valid numeric operations.
-
-Sometimes a `#NUM!` might be expected signalling the user that some parameter is out of scope.
+**Cause:** Invalid numeric operation (e.g., calculating a square root of a negative number).  
+**Fix:** Adjust the formula to ensure valid numeric operations.
 
 ### **`#N/A`**
 
-A value is not available, often in lookup functions like VLOOKUP.
-
-This is frequnly not an error in your spreadsheetlogic.
-
-You can produce a prettier answer using the [`IFNA`](/functions/information/isna) formula:
+**Cause:** A value is not available, often in lookup functions like VLOOKUP.  
+**Fix:** Ensure the lookup value exists or use IFNA() to handle missing values:
 
 ```
 =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
@@ -90,68 +63,17 @@ You can produce a prettier answer using the [`IFNA`](/functions/information/isna
 
 ### **`#NULL!`**
 
-Incorrect range operator in a formula (e.g., missing a colon between cell references).
+**Cause:** Incorrect range operator in a formula (e.g., missing a colon between cell references).  
+**Fix:** Use correct range operators (e.g., A1:A10).
 
-### **`#SPILL!`**
-
-A cell in a formula will overwrite content in other cells.
-This cannot happen riht now in IronCalc as formulas don't spill yet.
 
 ### **`#CIRC!`**
 
-Circular reference. This is an error is your spreadsheet and must be fixed.
-Means that during teh course of a computation a circular dependency was found.
-
-A circular dependency is a dependency of a formula on itself.
-
-For instance in the cell `A1` the formula `=A1*2` is a circular dependency.
-
-Other spreadsheet engines use circular dependencies to do "loop computations", run "sensitivity analysis" or "goal seek".
-
-IronCalc doesn't support any of those at the moment.
-
-## IronCalc specific errors
-
----
-
-### **`#ERROR!`**
-
-General formula issue, like syntax errors or invalid references.
-In general Excel does not let you enter incorrect formulas but IronCalc will.
-
-This will make your workbook imcompatible with Excel
-
-For instace an incomplete formula
-
-```
-=A1+
-```
-
-### **`#N/IMPL!`**
-
-A particular feature is not yet implemented in IronCalc
-
-Look if there is a Github ticket or contact us via email, Discord or bluesky
-
-## Error propagation
-
-Some errors a created by some formulas. For instance the function `SQRT` can create the error `#NUM!` but can't ceate the error `#DIV/0`.
-
-Once an error is created it is normally _propagated_ by all the formulas. So if cell `C3` evaluates to `#ERROR!` then the formula
-`=SQRT(C3)` will return `#ERROR!`.
-
-Not all functions propagate errors in their arguments. For instancethe function `IF(condition, if_true, if_false)` will only propagate an error in the `if_false` argument if the `condition` is `FALSE`. This is called _lazy evaluation_, the function `IF` is _lazy_, it only evaluates the arguments when needed. The opposite of lazy evaulaution is called _eager evaluation_.
-
-Some functions also expect an error as an argument like [`ERROR.TYPE`](/functions/information/error.type) and will not propagate the error.
+**Cause:** Circular reference.  
+**Fix:** Remove the circular reference.
 
 
-## See also
+### **`#####`**
 
-The following functions are convenient when working with errors
-
-- [`ISERR(ref)`](/functions/information/iserr), `TRUE` if `ref` is any error type except the `#N/A` error.
-- [`ISERROR(ref)`](/functions/information/iserror), `TRUE` if `ref` is any error.
-- [`ISNA(ref)`](/functions/information/isna), `TRUE` if ref is `#N/A`.
-- [`ERROR.TYPE`](/functions/information/error.type) returns the numeric code for a given error.
-- [`IFERROR(ref, value)`](/functions/logical/iferror) returns `value` if the content of `ref` is an error.
-- [`IFNA(ref, value)`](/functions/logical/ifna) return `value` if `ref` is #N/A errors only.
+**Cause:** The column isn’t wide enough to display the value.  
+**Fix:** Resize the column width to fit the content.

docs/src/features/implicit-intersection.md

@@ -1,15 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Implicit Intersection
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-::: warning
-**Note:** This feature is not yet available on IronCalc 🚧
-:::

docs/src/features/optional-arguments.md

@@ -1,26 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Error Types
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-
-Some functions have optional arguments. For instance:
-
-XLOOKUP(lookup_value, lookup_array, return_array, if_not_found="#N/A", match_mode=0, search_mode=1)
-
-The three first arguments are mandatory and the last three are optional.
-Optional argumenst must have a default value.
-
-In this example if you don't want to specify the if_not_found and match_mode arguments you can leave them out:
-
-XLOOKUP(lookup_value, lookup_array, return_array, , , -2)
-
-That would use the default arguments for if_not_found and match_mode arguments.
-

docs/src/features/units.md

@@ -1,13 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Units
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-Some IronCalc functions return values that have units like currencies, percentage or dates.

docs/src/features/value-types.md

@@ -1,67 +0,0 @@
----
-layout: doc
-outline: deep
-lang: en-US
----
-
-# Value types
-
-::: warning
-**Note:** This page is in construction 🚧
-:::
-
-In IronCalc a value, a result of a calculation can be one of:
-
-## Numbers
-
-Numbers in IronCalc are [IEEE 754 double precission](https://en.wikipedia.org/wiki/Double-precision_floating-point_format).
-
-Numbers are only displayed up to 15 significant figures. That's why '=0.1+0.2' is actually '0.3'
-
-Also numbers are compared up to 15 significant figures. So `=IF(0.1+0.2=0.3, "Valid", "Invalid")` will return `Valid`.
-
-However `=0.3-0.2-0.1` will not result in `0` in IronCalc.
-
-### Casting into numbers
-
-Strings and booleans are sometimes coverted to numbers
-
-`=1+"2"` => 3
-
-Some functions cast in weird ways:
-
-SUM(1, TRUE) => 2
-SUM(1, "1") => 2
-
-But SUM(1, A1) => 1 (where A1 is TRUE or "1")
-
-
-Sometimes the conversion happens like => "123"+1 is actually 124 and the SQRT("4") is 2 or the SQRT(TRUE) is 1.
-
-Some functions, however are more strict BIN2DEC(TRUE) is #VALUE!
-
-### Dates
-
-On spreadsheets a date is just the number of days since January 1, 1900.
-
-
-## Strings
-
-
-### Complex numbers
-
-On IronCal a complex number is just a string like "1+j3".
-
-
-## Booleans
-
-### Casting from numbers
-
-## Errors
-
-
-### Casting from strings
-
-"#N/A" => #N/A
-
-## Arrays

docs/src/functions/financial/fv.md

@@ -13,84 +13,37 @@ FV can be used to calculate future value over a specified number of compounding
 If your interest rate varies between periods, use the [FVSCHEDULE](/functions/financial/fvschedule) function instead of FV.
 ## Usage
 ### Syntax
-**FV(<span title="Number" style="color:#1E88E5">rate</span>, <span title="Number" style="color:#1E88E5">nper</span>, <span title="Number" style="color:#1E88E5">pmt</span>, <span title="Number" style="color:#1E88E5">pv</span>=0, <span title="Boolean" style="color:#43A047">type</span>=false) => <span title="Number" style="color:#1E88E5">fv</span>**
+**FV(rate, nper, pmt, pv, type)**
 ### Argument descriptions
-* *rate*. ([number](/features/value-types)) The fixed percentage interest rate or yield per period.
-* *nper*. ([number](/features/value-types)) The number of compounding periods to be taken into account. While this will often be an integer, non-integer values are accepted and processed.
-It stands for <u>n</u>umber of <u>per</u>iods.
-* *pmt*. ([number](/features/value-types)) The fixed amount paid or deposited each compounding period. Short for <u>p</u>ay<u>m</u>en<u>t</u>
-* *pv* ([number](/features/value-types), optional). The present value or starting amount of the asset (default 0). Short for <u>p</u>resent <u>v</u>alue.
-* *type* ([boolean](/features/value-types), optional). A logical value indicating whether the payment due dates are at the end (0) of the compounding periods or at the beginning (any non-zero value). The default is 0 when omitted.
-
-### Returned value
-
-The retruned value is a [number](/features/value-types) with [currency units](/features/units)
-
-### Errors
-
-* If any of the arguments is an error returns the error
-* If any of the argumets is not a number (or cannot be converted to a number) it returns `#VALUE!`
-* Some ranges of the parameters produce `#NUM!` error. For instnace `=FV(-3,1/2,1)`.
-* Some ranges of the parameters produce the `#DIV/0!` error. For instance `=FV(-1, -1, 1)`
-
-
+* *rate*. The fixed percentage interest rate or yield per period.
+* *nper*. The number of compounding periods to be taken into account. While this will often be an integer, non-integer values are accepted and processed.
+* *pmt*. The fixed amount paid or deposited each compounding period.
+* *pv* (optional). The present value or starting amount of the asset (default 0).
+* *type* (optional). A logical value indicating whether the payment due dates are at the end (0) of the compounding periods or at the beginning (any non-zero value). The default is 0 when omitted.
 ### Additional guidance
 * Make sure that the *rate* argument specifies the interest rate or yield applicable to the compounding period, based on the value chosen for *nper*.
 * The *pmt* and *pv* arguments should be expressed in the same currency unit. The value returned is expressed in the same currency unit.
 * To ensure a worthwhile result, one of the *pmt* and *pv* arguments should be non-zero.
 * The setting of the *type* argument only affects the calculation for non-zero values of the *pmt* argument.
-<!--@include: ../markdown-snippets/error-type-details.txt-->
+<!--@include: ../markdown-snippets/error-type-details.md-->
 
 ## Details
-* If $\text{type} \neq 0$, $\text{fv}$ is given by the equation:
-$$ \text{fv} = -\text{pv} \times (1 + \text{rate})^\text{nper} - \dfrac{\text{pmt}\times\big({(1+\text{rate})^\text{nper}-1}\big) \times(1+\text{rate})}{\text{rate}}$$
-
-* If $\text{type} = 0$
-$$ \text{fv} = -\text{pv} \times (1 + \text{rate})^{\text{nper}} - \dfrac{\text{pmt}\times\big({(1+\text{rate})^\text{nper}-1}\big)}{\text{rate}}$$
-
-* In both cases, in the limmit $\text{rate} = 0$, fv is given by the equation:
-$$ \text{fv} = -\text{pv} - (\text{pmt} \times \text{nper}) $$
-
-## Formula derivation
-
-The money you have now might grow in a bank by _[compound interest](https://en.wikipedia.org/wiki/Compound_interest)_. Say you have $100, that is the present value, and your bank gives you 10% interest rate yearly.
-
-At the end of 1 year you will have $110. In general that is $\text{pv}\times (1 + \text{rate})$. At the end of two years (the second _[annuity](https://en.wikipedia.org/wiki/Annuity)_) you will have 10\% more of the $110. That is the _compound_ part. You will have at the end of the second period $121 or in general if you invest  an ammount $\text{pv}$ at an interest $\text{rate}$ and wait for $\text{nper}$ periods the future value of this _[lump sum](https://en.wikipedia.org/wiki/Lump_sum)_ will be:
-
-$$\text{fv}_\text{ls} = \text{pv} \times (1 + \text{rate})^\text{nper}$$
-
-Note that the periods may be years, months or anything else.
-
-Now, supose that you also make regular payments of ammount $\text{pmt}$ each period.
-To find the future value of these payments, you sum the future value of each payment at the end of the investment horizon.
-
-There are two posibilities here:
-
-* You make the payments at the end of the periods (type 0). This is also called an _ordinary annuity_.
-* You make the payments at the beginning of each period (type 1). This is the _annuity due_ case.
-
-To derive the formula for either of them we need to add an geometric progression. To simplify things.
-Say we are at the end of period 5 and we are making the payments at the end of the period.
-
+* If *rate* = 0, FV is given by the equation:
 $$
-\text{pmt}\times  (1 + \text{rate})^4+\text{pmt}\times  (1 + \text{rate})^3+\text{pmt}\times  (1 + \text{rate})^2 +\text{pmt}
+FV = -pv - (pmt \times nper)
 $$
 
-This is because the first payment has been around for 4 periods, and the second payment has been around for 3 periods...
-The general formula for the sum of $\text{nper}$ terms in a geometric progression is given by $a(1 - r^n) / (1 - r)$, where $a = \text{pmt}$ and $r = 1 + \text{rate}$
-
+* If *rate* <> 0 and *type* = 0, FV is given by the equation:
+$$ FV = -pv \times (1 + rate)^{nper} - \dfrac{pmt\times\big({(1+rate)^{nper}-1}\big)}{rate}
 $$
-\text{pmt}\times\dfrac{ (1+\text{rate})^{\text{nper}}-1}{\text{rate}}
+* If *rate* <> 0 and *type* <> 0, FV is given by the equation:
+$$ FV = -pv \times (1 + rate)^{nper} - \dfrac{pmt\times\big({(1+rate)^{nper}-1}\big) \times(1+rate)}{rate}
 $$
-
-
-
 ## Examples
 [See this example in IronCalc](https://app.ironcalc.com/?example=fv).
 
 ## Links
 * For more information about the concept of "future value" in finance, visit Wikipedia's [Future value](https://en.wikipedia.org/wiki/Future_value) page.
-* [Investorpedia](https://www.investopedia.com/terms/f/futurevalue.asp) has a nice article on the future value.
 * See also IronCalc's [NPER](/functions/financial/nper), [PMT](/functions/financial/pmt), [PV](/functions/financial/pv) and [RATE](/functions/financial/rate) functions.
 * Visit Microsoft Excel's [FV function](https://support.microsoft.com/en-gb/office/fv-function-2eef9f44-a084-4c61-bdd8-4fe4bb1b71b3) page.
 * Both [Google Sheets](https://support.google.com/docs/answer/3093224) and [LibreOffice Calc](https://wiki.documentfoundation.org/Documentation/Calc_Functions/FV) provide versions of the FV function.

xlsx/src/import/worksheets.rs

@@ -148,6 +148,65 @@ fn load_columns(ws: Node) -> Result<Vec<Col>, XlsxError> {
     Ok(cols)
 }
 
+
+// https://c-rex.net/samples/ooxml/e1/Part4/OOXML_P4_DOCX_cfRule_topic_ID0EFKO4.html
+fn load_conditional_formatting(ws: Node) -> Result<Vec<String>, XlsxError> {
+    // Conditional Formatting
+    // <conditionalFormatting sqref="B1:B9">
+    //     <cfRule type="colorScale" priority="1">
+    //         <colorScale>
+    //             <cfvo type="min"/>
+    //             <cfvo type="max"/>
+    //             <color rgb="FFF8696B"/>
+    //             <color rgb="FFFCFCFF"/>
+    //         </colorScale>
+    //     </cfRule>
+    // </conditionalFormatting>
+    let mut conditional_formatting = Vec::new();
+    let conditional_formatting_nodes = ws
+        .children()
+        .filter(|n| n.has_tag_name("conditionalFormattng"))
+        .collect::<Vec<Node>>();
+    for format in conditional_formatting_nodes {
+        let reference = get_attribute(&format, "sqref")?.to_string();
+        // cfRule
+        let cf_rule_nodes = ws
+            .children()
+            .filter(|n| n.has_tag_name("cfRule"))
+            .collect::<Vec<Node>>();
+        if cf_rule_nodes.len() == 1 {
+            let cf_rule = cf_rule_nodes[0] ;
+            let cf_type = get_attribute(&cf_rule, "type")?.to_string();
+            match cf_type.as_str() {
+                "colorScale" => {
+                    todo!()
+                }
+                "aboveAverage" => {
+                    let dxf_id = get_attribute(&cf_rule, "dxfId")?.to_string();
+                    todo!()
+                }
+                "notContainsBlanks" => {
+                    let dxf_id = get_attribute(&cf_rule, "dxfId")?.to_string();
+
+                }
+                _ => {}
+                
+            }
+        }
+        // priority
+
+        // if type is avobeAverage then avobeAverage (and equlAverage)
+
+        // dxfId for some types((Differential Formatting Id) What style to apply when the criteria are met
+
+        // Posible children
+        // formula
+        // colorScales
+    }
+
+    Ok(conditional_formatting)
+}
+
 fn load_merge_cells(ws: Node) -> Result<Vec<String>, XlsxError> {
     // 18.3.1.55 Merge Cells
     // <mergeCells count="1">
@@ -945,17 +1004,7 @@ pub(super) fn load_sheet<R: Read + std::io::Seek>(
 
     let merge_cells = load_merge_cells(ws)?;
 
-    // Conditional Formatting
-    // <conditionalFormatting sqref="B1:B9">
-    //     <cfRule type="colorScale" priority="1">
-    //         <colorScale>
-    //             <cfvo type="min"/>
-    //             <cfvo type="max"/>
-    //             <color rgb="FFF8696B"/>
-    //             <color rgb="FFFCFCFF"/>
-    //         </colorScale>
-    //     </cfRule>
-    // </conditionalFormatting>
+    let conditional_formatting = load_conditional_formatting(ws)?;
     // pageSetup
     // <pageSetup orientation="portrait" r:id="rId1"/>