Writing Excel FIle through Erlang

Question

i am trying to write excel file through erlang. I used the following code to write the excel file

-module(excel).
-export([start/1]).

start(Val)->
        case  file:open("office-test.xls",[append]) of
        {ok,Fd} -> io:format(" file created"),
                io:fwrite(Fd,"~p\t~p\t~p~n", ["Name","Date","Number"]),
                export(Fd,Val),
                file:close(Fd);
        {error,_} ->  io:format("~nerror in creation of file")
        end.


export(_,0)->
        ok;

export(Fd,Val) ->
        io:fwrite(Fd, "~p\t~p\t~p\t~n" ,["B123","2012/10/11 12:12:12","val"++integer_to_list(Val)]),
        export(Fd,Val-1).

It was able to write successfully but when i open in LibreOffice. I got up a pop-up window asking the data seperated by. I dont want the end user to work on it.

1) Is there any way such that the office(ms office or libre office) will automatically parse it.??

2) Is there any other way to write the excel sheets through erlang..??

Answer 1

you have to write a CSV , Comma delimited text file. You would have to save it with .csv file extension. You write to this file line, by line. make sure that each line ends with \r\n. This file can be read very well from excel.

You make sure that the headings appear on the first line, like this:

Name,Sex,Project\r\n
Joe Armstrong,Male,Erlang\r\n
Klacke Wickstrom,Male,Yaws\r\n
Rusty R,Male,Nitrogen\r\n
Bill Gates,Male,\r\n
Muzaaya Joshua,Male,ZeePay\r\n

Also, the file encoding matters. ANSI encoding is better. You can as well process Excel files in Erlang by first converting/re-saving the file as .csv , comma delimited file using excel.
Then use this csv file parser module

%%% --- csv parser in Erlang. ------
%%% To help process large csv files without loading them into
%%% memory. Similar to the xml parsing technique of SAX

-module(csv).
-compile(export_all).

parse(FilePath,ForEachLine,Opaque)->
    case file:open(FilePath,[read]) of
        {_,S} ->
            start_parsing(S,ForEachLine,Opaque);
        Error -> Error
    end.

start_parsing(S,ForEachLine,Opaque)->
    Line = io:get_line(S,''),
    case Line of
        eof -> {ok,Opaque};
        "\n" -> start_parsing(S,ForEachLine,Opaque);
        "\r\n" -> start_parsing(S,ForEachLine,Opaque);
        _ -> 
            NewOpaque = ForEachLine(scanner(clean(clean(Line,10),13)),Opaque),
            start_parsing(S,ForEachLine,NewOpaque)
    end.

scan(InitString,Char,[Head|Buffer]) when Head == Char -> 
    {lists:reverse(InitString),Buffer};
scan(InitString,Char,[Head|Buffer]) when Head =/= Char ->
    scan([Head|InitString],Char,Buffer);
scan(X,_,Buffer) when Buffer == [] -> {done,lists:reverse(X)}.
scanner(Text)-> lists:reverse(traverse_text(Text,[])).

traverse_text(Text,Buff)->
    case scan("",$,,Text) of
        {done,SomeText}-> [SomeText|Buff];
        {Value,Rem}-> traverse_text(Rem,[Value|Buff])
    end.

clean(Text,Char)-> 
    string:strip(string:strip(Text,right,Char),left,Char).

How to use this module to parse csv files from Excel. An example of our simple csv file above, in shell

C:\Windows\System32>erl
Eshell V5.9  (abort with ^G)
1> ForEachLine = fun(Line,Buffer)-> io:format("Line: ~p~n",[Line]),Buffer end.
#Fun<erl_eval.12.111823515>
2> InitialBuffer = [].
[]
3> csv:parse("E:/erlang_projects.csv",ForEachLine,InitialBuffer).
Line: ["Name","Sex","Project"]
Line: ["Joe Armstrong","Male","Erlang"]
Line: ["Klacke Wickstrom","Male","Yaws"]
Line: ["Rusty R","Male","Nitrogen"]
Line: ["Bill Gates","Male",[]]
Line: ["Muzaaya Joshua","Male","ZeePay"]
{ok,[]}
4> ForEachLine2 = fun(Line,Buffer)-> io:format("Line: ~p~n",[Line]),[Line|Buffer] end.
#Fun<erl_eval.12.111823515>
5> csv:parse("E:/erlang_projects.csv",ForEachLine2,InitialBuffer).
Line: ["Name","Sex","Project"]
Line: ["Joe Armstrong","Male","Erlang"]
Line: ["Klacke Wickstrom","Male","Yaws"]
Line: ["Rusty R","Male","Nitrogen"]
Line: ["Bill Gates","Male",[]]
Line: ["Muzaaya Joshua","Male","ZeePay"]
{ok,[["Muzaaya Joshua","Male","ZeePay"],
     ["Bill Gates","Male",[]],
     ["Rusty R","Male","Nitrogen"],
     ["Klacke Wickstrom","Male","Yaws"],
     ["Joe Armstrong","Male","Erlang"],
     ["Name","Sex","Project"]]}
6>

So you can use this module later on to parse your csv files from Excel as well. Now, just learn how to write a csv file line by line, read the pragmatic Erlang Programming Book in the files chapter, or the erlang documentation.