Click here to Skip to main content
15,882,017 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have created a generic excel data importer.

You pass it "rules" and a POCO so it can populate an instance and return it.

The rules dictate what property to fill, what column to get the data from and what conditions must be in place.

EG:

For ContractStart Length I might write a rule as follows:

C#
new PropertySelector(
   detail => detail.Header == "Contract_Start", //Identify the column
   o=>FromExcelSerialDate(o),                   //Format the data
   DateTime.Now,                                //default value (optional)
   detail => detail.Header == "Type" 
        && detail.Cell.ToString()=="Starter"    //condition (params)
) 

This would go into a dictionary with key=PropertyInfo of POCO, value is array of PropertySelectors. If the conditions of the first PropertySelector in the array are not met then the next PropertySelector is tested and so on. There are rarely more than 1 or 2 PropertySelectors.

The generic Excel Import returns a new instance of the POCO for each row in the workbook. This has worked fine so far.

So for each 'supplier' I have a Wrapper class that specifies it's own rules, depending on how the suppliers present their data. Some suppliers don't use the tabular format so I have had to make entirely custom Excel readers for those >_<<br mode="hold" />

My issue is this:
I have a supplier that uses a tabular format, but each product is spread across several rows. One row will have Price A, the next will have price B and so on.

I am looking for a way to use the generic excel importer to process this file.

Here are the choices I have worked out so far:
Post Process:
- Read in each row as a separate POCO then group them after they are returned to the Wrapper. I would have to select out the values if the exist - Con: looong processing time

Pre Process:
- manipulate the worksheet before passing it the to excel importer - con: I don't really know how


Sorry for the long post but I'm really looking for a third option if anyone can thing of one, or an elegant solution I'm missing here? Any thoughts are welcome ^_^

Thanks
Andy
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900