Build status

Nuget packages

	WldMr.Excel.Core	WldMr.Excel.Functions
preview
stable

Build instructions

dotnet tool restore
dotnet paket restore
dotnet build

Excel Functions

Range manipulation

xlTrimNA, xlTrimEmpty, xlStackH, xlStackV, xlSlice

String operations

xlStringStartsWith, xlStringEndsWith, xlStringContains (with regex support and case-sensitivity options)

xlFormatA

xlRegexMatch

Boolean range operations

xlRangeAnd, xlRangeOr

Date operations

xlDateThirdWednesday, xlDateThirdFriday

xlToday(): non-volatile RTD-based variant of TODAY()

Library

Description

Usage

basic

open ExcelDna.Integration
open FsToolkit.ErrorHandling
open WldMr.Excel

[<ExcelFunction>]
let StringContains (text:xlObj, subString: xlObj): xlObj =
  result {
    let! text_ = text |> (XlObj.toString |> XlObjParser.withArgName "Text") 
    let! subString_ = text |> (XlObj.toString |> XlObjParser.withName "Substring")
    return text_.Contains(subString_) |> XlObj.ofBoolean
  } |> XlObj.ofResult

Vectorizing the function

[<ExcelFunction(Name="myStringContain2")>]
let myStringContainsWithRange (text:xlObj[,], subString: xlObj[,]): xlObj[,] =
  let stringContains (text: string) subString =
    text.Contains(subString) |> XlObj.ofBool
    
  ArrayFunctionBuilder
    .Add("Text", XlObj.toString, text)
    .Add("SubString", XlObj.toString, subString)
    .EvalFunction stringContains
  |> FunctionCall.eval

Add arguments with default value

[<ExcelFunction(Name="myStringContains3")>]
let myStringContainsWithCase (text:xlObj[,], substring: xlObj[,], ignoreCase: xlObj[,]): xlObj[,] =
  let stringContains (text: string) (substring: string) ignoreCase =
    if ignoreCase then
      text.ToLowerInvariant().Contains(substring.ToLowerInvariant()) |> XlObj.ofBool
    else
      text.Contains(subString) |> XlObj.ofBool

  ArrayFunctionBuilder
    .Add("Text", XlObj.toString, text)
    .Add("Substring", XlObj.toString, substring)
    .Add("IgnoreCase", XlObj.toBool |> XlObjParser.withDefault true, ignoreCase)
    .EvalFunction stringContains
  |> FunctionCall.eval