F# Change my code to be in function/class

2
votes

I wrote code in F# (to convert XML file to Excel file) The program is running successfully. Now I added main function to be able to get the xml path - in order to run the program from a batch file (in the cmd line), I need to change my code to be in function or class that I can send the path as parameter to the logic.

I try to do it but I got many error....

My code (fs file):

//
module XML2Excel

open System
open System.IO
open Microsoft.Office.Interop
open System.Xml
open System.Xml.XPath
open System.Drawing;; //For Font
open System.Xml.Linq;
open FSharp.Data

let functionParseXmlToExcel (fileName:string) =
            //Function Body goes here


        ///////////////////////////////////////////////////Load XmlDoc
        let xmlDoc = new XmlDocument();;
        //////////////////////////////////////////////////Xml Path
        let mXmlPath = fileName
        //("C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\" + "NIMO.xml");;
        //////////////////////////////////////////////////Load XML
        xmlDoc.Load(mXmlPath);;


        //////////////////////////////Tables Nodes
        let tablePath="/OCEXPORT"
        let docTablesElement = xmlDoc.DocumentElement
        let nodeTablesList = docTablesElement.SelectSingleNode(tablePath).ChildNodes



        ////////////////////////////////// Start Excel
        let xlApp = new Excel.ApplicationClass()//(Visible = true)
        let xlWorkBookOutput = xlApp.Workbooks.Add()
        //xlApp.Visible <- true

        ////////////////////////////////////Load Excel Format
        let xlWorkBookFormat = xlApp.Workbooks.Open(@"C:\Users\rivkar\Desktop\My Projects\Zmira\XML2Excel\nimo2_types.xlsx")

        //////////////////////////////////// Loop on Tables (Excel Sheets)
        for tabs in 1 .. nodeTablesList.Count do   

            let tableNamePath="/OCEXPORT/TABLE"
            let docElement = xmlDoc.DocumentElement
            let nodeTableNameList = docElement.ChildNodes.[tabs-1].FirstChild

            let columnsPath= tableNamePath+"/"+nodeTableNameList.Name

            let nodeColumsList = docElement.SelectSingleNode(columnsPath).ChildNodes

            let xlWorkSheetOutput = xlWorkBookOutput.Worksheets.[tabs] :?> Excel.Worksheet
            xlWorkSheetOutput.Name <- nodeTableNameList.Name
            xlWorkSheetOutput.get_Range("A1:Z1").Interior.Color <- Color.SkyBlue

            /////////////////////////////////////Loop on Columns    
            for columns in 1.. nodeColumsList.Count do
                     let Node = "/OCEXPORT/TABLE/"+nodeTableNameList.Name+"/"+nodeColumsList.Item(columns-1).Name+"/text()"   
                     let data = [|
                          (xmlDoc.SelectNodes Node
                              |> Seq.cast<XmlNode>
                              |> Seq.map (fun node -> node.Value)
                              |> String.concat Environment.NewLine)
                              |]
                     xlWorkSheetOutput.Cells.[1, columns] <- nodeColumsList.Item(columns-1).Name
                     let rows = data.[0].Split '\n'// rows
                     let row = xlWorkBookFormat.
                     for j in 1 .. rows.Length do
                           xlWorkSheetOutput.Cells.[j+1, columns] <- rows.[j-1]
                           xlWorkSheetOutput.Range("A1:A100").NumberFormat <- "0.0"

            //if (tabs <= nodeTablesList.Count) then
            xlWorkBookOutput.Worksheets.Add(After=xlWorkBookOutput.Worksheets.[xlWorkBookOutput.Worksheets.Count])

        /////////////////////////////Delete Unnecessary Sheets
        //xlWorkBookOutput.Worksheets.Visible <- true
        //xlWorkBookOutput.Worksheets.Delete()


        ////////////////////////////Save the Excel File
        let excelPath = "C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\"+DateTime.Today.ToString("yyyy_dd_MM_BNA")+".xls"
        if File.Exists(excelPath) then
                 File.Delete(excelPath)

        xlWorkBookOutput.SaveAs excelPath, -4143
        File.Exists(fileName)

[<EntryPoint>]
let main(args) =    
    printfn "args: %A" args
    let fName = argv.[0]
    printfn "%A" (functionParseXmlToExcel fName) |> ignore
    Console.ReadLine()

    0

The Errors that I get:

  1. On the let xmlDoc = new XmlDocument();;
    • Block following this let is unfinished. Expect an expression
  2. let mXmlPath = fileName the value or constructor fileNmae is not defined

and etc. I think that I missing something.

Could anyone help me please??

2
f#
I tried to insert all my code to be in function that I will able to call it from the main and send a parameter (the xml path) The errors in the code function in the let syntax and etc. it seems that in the function body the program doesn't know all the values and the code I wrote... I think I need to change something but I don't know what :( - MRah
The errors you've shown have typos, which means they weren't copied&pasted. Did you do the same with the code we're seeing here? - ildjarn
all my code exists in my question... What do you need else? - MRah
Thank you very much!!!! Do you want I can I run it from the cmd - how do I write the parameter - path of xml file? - MRah
I think you should mark the answer s952163 as solution - FoggyFinder

2 Answers

2
votes

Thank you for your answers.

The following code is working:

//
module XML2Excel

open System
open System.IO
open Microsoft.Office.Interop
open System.Xml
open System.Xml.XPath
open System.Drawing;; //For Font
open System.Xml.Linq;
open FSharp.Data

let functionParseXmlToExcel (fileName:string) =
            //Function Body goes here


        ///////////////////////////////////////////////////Load XmlDoc
        let xmlDoc = new XmlDocument()
        //////////////////////////////////////////////////Xml Path
        let mXmlPath = fileName
        //("C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\" + "NIMO.xml");;
        //////////////////////////////////////////////////Load XML
        xmlDoc.Load(mXmlPath)


        //////////////////////////////Tables Nodes
        let tablePath="/OCEXPORT"
        let docTablesElement = xmlDoc.DocumentElement
        let nodeTablesList = docTablesElement.SelectSingleNode(tablePath).ChildNodes



        ////////////////////////////////// Start Excel
        let xlApp = new Excel.ApplicationClass()//(Visible = true)
        let xlWorkBookOutput = xlApp.Workbooks.Add()
        //xlApp.Visible <- true

        ////////////////////////////////////Load Excel Format
        //let xlWorkBookFormat = xlApp.Workbooks.Open(@"C:\Users\rivkar\Desktop\My Projects\Zmira\XML2Excel\nimo2_types.xlsx")

        //////////////////////////////////// Loop on Tables (Excel Sheets)
        for tabs in 1 .. nodeTablesList.Count do   

            let tableNamePath="/OCEXPORT/TABLE"
            let docElement = xmlDoc.DocumentElement
            let nodeTableNameList = docElement.ChildNodes.[tabs-1].FirstChild

            let columnsPath= tableNamePath+"/"+nodeTableNameList.Name

            let nodeColumsList = docElement.SelectSingleNode(columnsPath).ChildNodes

            let xlWorkSheetOutput = xlWorkBookOutput.Worksheets.[tabs] :?> Excel.Worksheet
            xlWorkSheetOutput.Name <- nodeTableNameList.Name
            xlWorkSheetOutput.get_Range("A1:Z1").Interior.Color <- Color.SkyBlue

            /////////////////////////////////////Loop on Columns    
            for columns in 1.. nodeColumsList.Count do
                     let Node = "/OCEXPORT/TABLE/"+nodeTableNameList.Name+"/"+nodeColumsList.Item(columns-1).Name+"/text()"   
                     let data = [|
                          (xmlDoc.SelectNodes Node
                              |> Seq.cast<XmlNode>
                              |> Seq.map (fun node -> node.Value)
                              |> String.concat Environment.NewLine)
                              |]
                     xlWorkSheetOutput.Cells.[1, columns] <- nodeColumsList.Item(columns-1).Name
                     let rows = data.[0].Split '\n'// rows

                     //let row = xlWorkBookFormat.

                     for j in 1 .. rows.Length do
                           xlWorkSheetOutput.Cells.[j+1, columns] <- rows.[j-1]
                           xlWorkSheetOutput.Range("A1:A100").NumberFormat <- "0.0"

            //if (tabs <= nodeTablesList.Count) then
            xlWorkBookOutput.Worksheets.Add(After=xlWorkBookOutput.Worksheets.[xlWorkBookOutput.Worksheets.Count])

        /////////////////////////////Delete Unnecessary Sheets
        //xlWorkBookOutput.Worksheets.Visible <- true
        //xlWorkBookOutput.Worksheets.Delete()


        ////////////////////////////Save the Excel File
        let excelPath = "C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\"+DateTime.Today.ToString("yyyy_dd_MM_BNA")+".xls"
        if File.Exists(excelPath) then
                 File.Delete(excelPath)

        xlWorkBookOutput.SaveAs excelPath, -4143
        File.Exists(fileName)

[<EntryPoint>]
let main(args) =    
    //printfn "args: %A" args.[0]
    let fName = args.[0]
    //"C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\NIMO.XML"
    printfn "%A" (functionParseXmlToExcel fName) |> ignore
    //Console.ReadLine()

    0

Thank you!

0
votes

If you want to keep it super-simple, you just need to wrap your xml related code into a function (in this case functionExample). Then call that function from main. Whatever argument you pass to main can be passed down to your function:

open System
open System.IO

let functionExample (fileName:string) =
    //Function Body goes here
    File.Exists(fileName)

[<EntryPoint>]
let main argv = 
    printfn "%A" argv.[0]
    let fName = argv.[0]
    printfn "%A" (functionExample fName) |> ignore
    0 // return an integer exit code

Now if you call this exe and pass a path it will tell you if the file exists (as I have a test.csv file in my tmp directory):

$ ./StackOverflow3.exe "c:\\tmp\\test.csv"
"c:\tmp\test.csv"
true

EDIT: If you follow Foggy's advice the error about the unfinished block will go away.

After that you have two ways to run this:

[<EntryPoint>]
let main argv = 
    printfn "%A" argv.[0]
    //let fName = argv.[0]
    (functionParseXmlToExcel "C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\NIMO.xml") |> ignore
    0 // return an integer exit code

This will hardcode the file so pretty useless in my opinion you should try this. Change your main to this. 

[<EntryPoint>]
let main(args) =    
    printfn "args: %A" args
    let fName = argv.[0]
    (functionParseXmlToExcel fName) |> ignore
    Console.ReadLine()
    0

Than right-click on your project in Visual Studio and Select Build.

Build the exe

It will build an executable file, somewhere under ProjectName\Debug\bin. Mine is called StackOverflow3.exe. It could be under Release\bin but you can check in VS where is the exe saved.

Path to Exe

Yours will be whatever you named your project. Then you can just pass the filename to this exe like this: StackOverflow3.exe "C:\\Users\\rivkar\\Desktop\\My Projects\\Zmira\\XML2Excel\\NIMO.xml"

You can call this exe from a batch file, loop through this, and pass different file names to be processed. This is not the best way to structure it, but it's the simplest.