excel301.fs

System.IO.Directory.SetCurrentDirectory (__SOURCE_DIRECTORY__)

#r "Microsoft.Office.Interop.Excel"
#r "office"
open System
open Microsoft.Office.Interop.Excel

let xlApp = new ApplicationClass()
let workbook = xlApp.Workbooks.Open(@"C:\Dev\ExcelRedirects\ExcelRedirects\redirects.xls")
let sheet = workbook.Worksheets.[1] :?> Worksheet

let getValue row col = string (sheet.Cells.[row,col] :?> Range).Value2

let getRelative (absolute:string) =
    let index = absolute.IndexOf(".com/")
    if index > 0 then
        absolute.Substring(index + 5)
    else
        ""

let readRow i = 
    let a = getValue i 1 |> getRelative
    let b = getValue i 2 |> getRelative
    (a,b)

let readRows list =
    list |> List.map readRow

let removeBlanks (a,b) = a <> "" && b <> ""

let removeDupes ((a:string),(b:string)) = a.ToLower() <> b.ToLower()

let toWebConfigRule (a,b) = String.Format(@"<rule name=""{0}"" patternSyntax=""ExactMatch"" stopProcessing=""true""><match url=""{0}"" /><action type=""Redirect"" url=""{1}"" appendQueryString=""true"" /></rule>", a, b)

let rows = [2..85] |> List.map readRow 
let nodupes = rows |> List.filter removeBlanks |> List.filter removeDupes 
let rules = nodupes |> List.map toWebConfigRule

System.IO.File.WriteAllLines(@"C:\Dev\ExcelRedirects\ExcelRedirects\redirects.txt", rules)