Project Interop Sample

gistfile1.fs

#r "Microsoft.Office.Interop.MSProject.dll"
#r "Microsoft.Office.Interop.Excel.dll"

open System
open Microsoft.Office.Interop.MSProject
open Microsoft.Office.Interop.Excel

[<AutoOpenAttribute>]
module ProjectInterop =

    type TaskAssignment =
        {
            ResourceName : string
            ResourceGroup : string
            TaskName : string
            Project : string
            Critical : string
            TaskDuration : float
            TaskProgress : float
            StartDate : DateTime
            AssignmentDuration : float
        }

    let constructTA project resource group task taskDuration progress isCritic startDate duration =
        {
            ResourceName = resource
            ResourceGroup = group
            TaskName = task
            Project = project
            Critical = isCritic
            TaskDuration = taskDuration
            TaskProgress = progress
            StartDate = startDate
            AssignmentDuration = duration
        }

    // Time scale data parameters.
    let patd = Microsoft.Office.Interop.MSProject.PjAssignmentTimescaledData.pjAssignmentTimescaledWork
    let ptu = PjTimescaleUnit.pjTimescaleDays

    /// Returns the day scaled values of an assignment (work / day).
    let dayScaledValues (assignment : Assignment) =
        assignment.TimeScaleData(assignment.Start, assignment.Finish, patd, ptu,1)
        |> Seq.cast<TimeScaleValue>
        |> Seq.toArray
        |> Array.map (fun x ->
            let startDate = DateTime.Parse(x.StartDate.ToString())
            let value = x.Value.ToString()
            startDate, value)
        |> Array.filter (fun (x, y) -> y <> "")
        |> Array.map (fun (x, y) -> x, float y / 60.)

    /// Returns a task's total duration.
    let getTaskDuration (task : Task) =
        task.Duration.ToString()
        |> float
        |> (fun x -> x / 60.)

    /// Returns a task's progress percentage.
    let getTaskProgress (task : Task) =
        task.PercentComplete.ToString()
        |> float
        |> (fun x -> x / 60. * 100.)
        |> (fun x -> Math.Round(x, 2))

    /// Gathers assignment related data.        
    let assignmentData (assignment : Assignment) project =
        let resource = assignment.Resource
        let resourceName = resource.Name
        let group = resource.Group
        let task = assignment.Task
        let taskName = task.Name
        let taskDuration = getTaskDuration task
        let critical = task.Critical.ToString()
        let progress = getTaskProgress task
        let dsvs = dayScaledValues assignment
        let f = constructTA project resourceName group taskName taskDuration progress critical
        dsvs |> Array.map (fun (startDate, duration) ->
            f startDate duration)

    /// Returns planned assignments for the given date.
    let whoDoesWhat (project : Project) date =
        let name = project.Name
        project.Tasks
        |> Seq.cast<Task>
        |> Seq.toArray
        |> Array.Parallel.map (fun x -> x.Assignments |> Seq.cast<Assignment> |> Seq.toArray)
        |> Array.concat
        |> Array.Parallel.map (fun x -> assignmentData x name)
        |> Array.concat
        |> Array.filter (fun x -> x.StartDate = date)

[<AutoOpenAttribute>]
module ExcelInterop =

    /// Launches Excel.
    let startExcel() =
        let excel = Microsoft.Office.Interop.Excel.ApplicationClass(Visible=true)
        let sheet = excel.Workbooks.Add().Worksheets.[1] :?> Worksheet
        sheet

    /// Formats a worksheet by inserting headers and autofitting columns.
    let formatWorksheet (sheet : Worksheet) =
            sheet.Cells.[1, 1] <- "Resource"
            sheet.Cells.[1, 2] <- "Group"
            sheet.Cells.[1, 3] <- "Task"
            sheet.Cells.[1, 4] <- "Project"
            sheet.Cells.[1, 5] <- "Critical"
            sheet.Cells.[1, 6] <- "Duration"
            sheet.Cells.[1, 7] <- "Progress"
            sheet.Cells.[1, 8] <- "Assignment"
            sheet.Range("A1", "H1").Font.Bold <- true
            sheet.Range("A1", "H1").EntireColumn.AutoFit() |> ignore

    /// Displays assignments in a worksheet.
    let displayAssignments (assignments : TaskAssignment []) (sheet : Worksheet) =
        assignments |> Array.iteri (fun idx x ->
            let idx' = idx + 2
            let strings = [|x.ResourceName; x.ResourceGroup; x.TaskName; x.Project; x.Critical|]
            let floats = [|x.TaskDuration; x.TaskProgress; x.AssignmentDuration|]
            let range =
                let col = sprintf "A%d" idx'
                let col' = sprintf "E%d" idx'
                sheet.Range(col, col')
            let range' =
                let col = sprintf "F%d" idx'
                let col' = sprintf "H%d" idx'
                sheet.Range(col, col')
            range.Value2 <- strings
            range'.Value2 <- floats)

module Main =

    printfn "Starting Project\n"
    let app = Microsoft.Office.Interop.MSProject.ApplicationClass(Visible = false)
    let path = "test.mpp"
    app.FileOpen path |> ignore

    printfn "Getting assignment data\n"
    let project = app.Projects.[1]
    let today = DateTime.Today.AddDays 1.
    let assignments = whoDoesWhat project today
    app.Quit(PjSaveType.pjDoNotSave)

    match assignments with
        | [||] ->
            printfn "No assignment data to display\n"
        | arr ->
            printfn "Displaying assignment data\n"
            let worksheet = startExcel()
            displayAssignments arr worksheet
            formatWorksheet worksheet
    
    printfn "Done."