Excel file to array widget

[priyanka....@flowian.com]

Hello team,

I am trying below functionality..

1.I want to upload Excel file through upload widget.

2.When I upload such excel file then i want to display the file's contents and columns as it is in the array widget.

So,please suggest me regarding this.

[Jeremy Le Pré]

Hello,

What you probably need is to focus on :
http://docs.runmyprocess.com/API_Reference/FM/File.html#read_excel_cells

then you store the result and interpret it a JSON array. You can use it at ease both in Freemarker or later in your JS.

You can for example delete/store your elements in a collection:

http://docs.runmyprocess.com/Developer_Guide/Collection/Collection_JS_Freemarker#update-objects

Then you will be able to call the collection in a list to display it in your web interface.

Regards,

[priyanka....@flowian.com]

Hello,

Thanks for your reply.

But I am not getting how to implement this.

Will you please explain me with the example?

Thank you.

Regards,

Priyanka

[priyanka....@flowian.com]

Hello,

I got error as below-

Error while processing {read_excel_cells(fileid,cells)} Unexpected error: null

(Note : where fileid contains the id of the uploaded file)

How to solve this?

Please suggest me regarding this.

[Bidisha Das]

Hi Priyanka,

I can give a workaround for this: Instead of using excel sheet,you can use a csv file.Just save your excel file with .csv extension,nothing changes at all and then upload it into RMP. So you can use ${import_objects("file id", "Col_name", ",", "true")} to insert the csv file data into Collection. After that you can easily retrieve all the datas from collection and store it in an array.The columns of the csv file will be predefined in the array,only you need to set the values dynamically in it using javascript. It would be much easier than read_excel_cells which can be a bit complicated.

Thanks & Regards

Bidisha

[Bidisha Das]

Hi Priyanka,

You can try with the following freemarker code for reading excel cells: <#assign cells = [{"worksheet":"Sheet1","cells":[{"col":"1","row":"1"},{"col":"2","row":"1"},{"col":"3","row":"1"},                                                                                                       {"col":"4","row":"1"},{"col":"2","row":"2"},{"col":"3","row":"3"}]}] >

                                                                                                      ${read_excel_cells("file_id",cells)}

Thanks & Regards

Bidisha

[priyanka....@flowian.com]

Hi Bidisha,

Thanks for your reply.

It works well.But it is only for particular columns and rows.But if i want all the columns and rows as it is from excel file,then how can we do that?

Thank you.

Regards,
Priyanka

[Bidisha Das]

Hi Priyanka,

For that you need to specify each row and column in the above code which would be quite time consuming. So only I asked you to use CSV file rather than an excel.It would be much easier and simpler.

Thanks & Regards

Bidisha

[priyanka....@flowian.com]

Hi Bidisha,

Thank you.

It works well using CSV file.

Now i want to display the data of collection in the report widget.So how is it possible?

Please suggest.

Thank you.

[Bidisha Das]

Hi Priyanka,

You need to use the same field names as in Collection in the property names of the report widget.

Thanks & Regards

Bidisha

[priyanka....@flowian.com]

Hi Bidisha,

Thank you so much.

It is working

Bt suppose if i have CSV file which contains Main Header and Sub Headers then how to implement this?

I am sending you my CSV file in which the main header is "Student Report dated on date" and sub headers are "No","Student Name","Hobbies" and "Subjects".

So is it possible to display such records of CSV file as it is same in the RMP Report widget?

Please suggest.

Thank you.

Regards,
Priyanka.

[priyanka....@flowian.com]

[Murali]

Hi Priyanka,

Report Widget needs to be used to display data from collection (which can't contain data with sub headers).

A few work around are :
1) Maintain different CSV/Collection for all sub headers and display them in different Report Widgets.
2) Include Date as additional column for all student records and display in single Report Widget, use sort/filter functionality on Date column in Report Widget.

Hope it helps to implement your requirement.

Regards,
Murali

[priyanka....@flowian.com]

Hi Team,

I have implemented this and its working properly.

Thank you so much for your help.

Regards,
Priyanka.

[priyanka....@flowian.com]

Hi Team,

Thanks for your support.

I have one more question for you.

Suppose If I have one column in CSV file which will uniquely identify the particular record in file.

So while importing data from csv to collection, How can I check for duplicate records using that unique identifier column.

Is there any other way which avoid duplicate entries to collection from CSV file.

Regards,
Priyanka

[Bidisha Das]

Hi Priyanka,

There is no way to avoid duplicate values while importing from CSV into collection since import_objects will import all the data from collection irrespective of duplicacy. So you can only remove the duplicate values after it gets saved into collection using js or freemarker codes.

Thanks & Regards

Bidisha