Monday, October 22, 2007

Flex 2 - Read from MS Excel

As we know that Flex 2 cannot connect to any database directly, we do so by using Web Services, or writing assemblers that connect to the database and return results in XML form. This can become a tedious task especially when there's not much data to be maintained, where a simple CSV file created in MS Excel would suffice. With this example, I will show how to extract data from a CSV file and populate it onto a DataGrid element.

Step 1: Create a CSV file.

Open Microsoft Excel and prepare the following structure with three fields or columns.

Name Phone Country

And enter some values for these fields. While saving the file, in the file type, change it to CSV format. We will
name this file as TextExcel.

Step 2: Create a Flex 2 project with the following structure.

Now copy the CSV file that you have created into this project folder.
Create a DataGrid inside a Panel with three columns. We will add a button "Read" which when clicked will read the CSV file and set the values in the DataGrid.

Now, we need to write a fucntion called readExcel which will read the CSV file and populate the DataGrid.

Make a note of the import statements which you need to write before you cab access the URLRequest, URLLoader classes.
To the loader object, we have associated two event handlers, for which methods need to be written as well. The Event.COMPLETE will contain most of the code needed. We can do away with the other event, if we can be sure that there will never be an IO_ERROR, i,e. we are always able to access the CSV file.

The code for the Event.COMPLETE method - eventComplete and for the Event.onIOError - onIOError

When we read the CSV, all of the content is passed on to the variable result as a single String constant. so we have to split the string and create a 2-D array(records) that will contain the structure of the DataGrid.

When the spliting and creating of the records array is done, the final step will be setting this array to the Datagrid's dataProvider.

And we are done!!!!.

Now we are all set to run the project. When the browser opens up, you just have to click the button to show up the data in the DataGrid. Here is a screenshot of the ouput.

NOTE : If you are not able to see the code in the pictures, click on the image and it will open up in a new window.


Anonymous said...

Can I get the source files, I am not able to see the screenshots (images)

Anonymous said...

Can you please add a zip file for the source mxml file that will be really helpful

Anonymous said...

Reading the article seems you have really done a good job and saved time for many ppl but can u add a sourcezip?

vamsi said...

thanks for u r post.plz send me the zip file of the mxmlfolder.its really help fulin my job

Alexis said...

Yesterday I saw a lot of tools on a soft blog.And one of it was very interesting-how to recover damaged xls file.Software helped my friend as far as I remember.And to my surprise it recovered all my old damaged xls files absolutely free.Besides I knew how recovers Excel information if somehow it was corrupted.

munna said...

its giving io error wht to do pls help me