Hi

I need to read data from an Excel spreadsheet / CSV file and then create the relevant objects in my XAF application. Any suggestions on the best approach. I could not find any examples or documentation on this.

I would like to display the content of the file in a listview for the user to verify and select the rows he wants to import. If anyone has done this before I would realy appreciate some help.

Basically you need to do the following:

- Import the info from excel using something like http://www.dotnetjohn.com/articles.aspx?articleid=54 or this http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

- Create a loop and inside the loop create the your Business Object per row and insert this on list

- Bind your list to a listview

- Allow the user to select the rows to import

- Commit the selected business objects information to database

Let me know if I can help you more.

Regards

Pieter,

As for getting the data from Excel, I think there's an easier way.

Are you managing large numbers of spreadsheets in batches, or are your users simply processing one spreadsheet at a time?

There are many ways to do this, but in the simple case where you are processing one spreadsheet, just try the following:

1) Create a page in your Windows or Web app with a text area and a buton.

2) Have the user open the spreadsheet, select all, copy the contents.

3) Paste into the text area in the app (Win or Web).

4) Click the button.

5) Your code does the following:

a) Trim the string (delete leading and trailing spaces).

b) Split by CRLF.  This gives you the rows.

c) Get the first row.  Split by tabs.  This gives you the headers.

d) For each row after the first row, split by tabs to give you a list of values.

You can write some simple code to:

1) Check that all required columns are present, by comparing the list of column names to a list of required names.

2) Convert a column name to an index in the lists, so the user can move columns around and your code still works.

There are numerous ways to operate Excel, but if all you need is the text contents, better not to take on this level of complexity.

If your code needs to batch-process files, consider having the code that produces that files export them to txt or csv files, then read the files as text and process as above, still without using Excel.

Hope this makes sense and hope it helps.

Best regards,

Adam Leffert

Hi,

I tried some time ago importing excel based list to my current xaf list using a view controller and an action like this:

private void saImportExcelDocument_Execute(object sender, SimpleActionExecuteEventArgs e)
        {
            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=C:\Temp\Test1.xls;Extended Properties=Excel 8.0"))
            {
                using (OleDbCommand command = new OleDbCommand("SELECT * FROM [MyData$]", connection))
                {
                    OleDbDataReader dr;
                    connection.Open();
                    dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                    using (DataTable excelData = new DataTable("ExcelData"))
                    {
                        excelData.Load(dr);
                        Project p;
                        foreach (DataRow row in excelData.Rows)
                        {
                            p = new Project(this.View.ObjectSpace.Session);
                            p.Name = row["Code"].ToString();
                            p.Description = row["Description"].ToString();
                            ((ListView)this.View).CollectionSource.Collection.Add(p);
                        }
                    }
                }
            }
        }

This example uses a Project BO with at least Name and Description properties (for simplicity) and an excel sheet that has at least Code and Description columns.

This ofcourse is just a draft that you can improve to fit your needs.

HTH

Mohsen

 Hi Pieter

If this is something you need to do regularly or must be integrated as a more flexible tool take a look at this library:-

http://exceldatareader.codeplex.com/

It might give you a more flexible way to read data

John

 Mohsen

Thanks a lot, it is exactly what I was looking for.

I'm going to go with a CSV file at it seems that the files will be coming from various suppliers on a monthly basis and we need to get this information into our XAF application. We need the user to check and verify the information, therefore the step to import it first into a list view from where the user can filter and select the records he needs. We then build various BO from the data and store it in the database.

Regards

Pieter

 Very usefull library. I will investigate it as an option

Thanks John

Hi,

Happy that it did help.

For your scenario, you could replace Project BO in sample by your temporary Import BO and add an action to generate all kind of real BOs from that temporary imported data following user feed back and/or special validations.

You could also use a custom view based on XtraWizard control provided by somebody (I can't remember now) on a forum to guide the user throw the process of import and transfer in DB.

Mohsen

I used this recently in an ETL module :-

http://filehelpers.sourceforge.net/

Dave

Thanks for posting this - looks promising for something that I need to do next week.

I have contributed an "Excel Importer" module to eXpandFrameowork.  ImportWizard module. 
The module can map and import Excel files to a XAF application. 

You can see how it works in those links: Video on how it works
How to include the module into existing XAF Application 

I would be happy to hear your feedback and to improve it further

Martys