Excel layout for hierarchical data

Question

I am working on an excel bulk upload feature which has to be parsed using Java API.
My problem is designing its layout. The data is hierarchical with 4 levels and one to many relationship at each level.

1-other data for node 1
  -2-other data for node 2
   -3-other data for node 3
    -4-other data for node 4

And this repeats

e.g. -

Organisation id - Organisation name - Address etc...
 - Department id - Department name - etc...(multiple departments for each organisation)
   - Manager id - Manager name - etc...(multiple managers for each department)
     - Employee id - Employee name - etc...(multiple employees for each manager)

How can this be designed in excel layout so that it's easy for a non technical user to input data as well as for the program to parse and establish parent child relationship?

Answer 1

Focus on supporting the non-technical users. With Java you have a powerful tool to parse even the weirdest data sources ;)

Assuming, the relations are strict one-to-many, I suggest the following approach:

(1) create four spreadsheets for origanisation, department, manager and employee. Those spreadsheets reflect the database tables

(2) create a fifth spreadsheet to represent the relations, like:

 |A    |B    |C    |D    |
-+-----+-----+-----+-----+    
1| emp | mgr | dep | org |
-+-----+-----+-----+-----+
2|    v|    v|    v|    v|
-+-----+-----+-----+-----+
3|    v|    v|    v|    v|
-+-----+-----+-----+-----+

the 'v' letters just represent combo boxes where the user can select organisations, etc. The values come from the other spreadsheets, the cells will store the id and display the readable name.

Edit

For clarifiction - each row represents a data set for an employee. I changed the order of the columns to make it clearer. First enter all entities (organisations, departments, ...) in the four sheets, then use the combo boxes to choose an employee, his/her manager, the department and the organisation. To me it's pretty intuitive and easy.

excel supports those types of combo boxes where the values come from a spreadsheet.