vendredi 11 septembre 2015

Design pattern for reading excel data

We have an application that reads data from an Excel document. There are many sheets; each sheet corresponds to a C# object definition, and each row in each sheet corresponds to an object. The objects can have references to other rows in other sheets.

MySheet

      Min Max Cost
      ------------
row1 | 1   10  100

These sheets are called "BASE" sheets. There's one special sheet, CASE, that contains overrides for sheets. Leaving a cell null means that the value is inherited from BASE. For example:

CASE 1
------------------
MySheet

      Min Max Cost
      ------------
row1 | 5     

------- 

MySecondSheet

      Price Quantity 
      ------------
row1 | 500  15  
------------------

In C#, we control get/set access by requiring that the CASE context be supplied. row1.GetMin(1) means we should get 5 while row1.GetMin(0) should give us 1. row2.SetPrice(1, 500) means that we're setting the Price property to 500 but only for Case1.

The logic for reading each sheet is implemented in separate classes. Each reader class implements a void CreateEntities(int caseNum = 0) method (caseNum = 0 corresponds to reading BASE data). The workflow for importing the data is as follows: read all the BASE data, then, for each case, reread the table again and set the object properties according to the current case.

The reader logic can be fairly involved for reading the data the first time (reading the BASE data), and the interface right now doesn't really separate reading BASE data from reading CASE data. Is there a more desirable interface for the reader classes? Is there a design pattern that better addresses this problem?

Aucun commentaire:

Enregistrer un commentaire