Extract data from multiple excel files.

See more:

Java

.NET

I was basically working on automation of an excel report that I share on a daily basis with the employees. The report gives details on whether or not the employee is adhering to the allocated tasks for a particular day. I manually prepare this report daily and it is prepared from 3 other Excel files. First excel file has the allocated targets with employee I'd, employee name and the targets allocated in a particular region(ex. India, Us,etc.,) I get this data on daily basis through a mail, so I'll have to manually paste it in the first file. The second file consists of work done and it is pulled out from an SQL query. I apply a pivot and collate the numbers and apply conditional formatting to show if the employee has met the allocated targets. If the allocated targets are met I put a netural color to field, if employee has not met the targets then I put a bad cell and if employee has done more than the allocated numbers then a good cell. The third sheet consists of managers mapping to check which employee is mapped to which manager. The common field in first and third sheet is employee I'd, i apply a vlookup and collate the data.

The output file consists of three sheets:
1st sheet consists of employee wise tagged data. (Note: I tried to apply formatting to the example data but it somehow disappeared when I pasted the data here, basically conditional formatting should be applied to all the three sheets of the output file)

Employee id	employee name	manager	allocated india	completed india	allocated us	completed us	total allocated	total completed
1	A	x	10	16	5	3	15	19
2	B	y	20	20	16	26	36	46
3	c	Z	15	4	10	10	25	14

The second file consists of manager wise adherence.

Manager Total allocated total Complete
X.          1200.          780
Y.          500.           600

Third file consists adherence region wise.

It basically consists the same data as in Sheet one but there are few additional rows added to it which will calculate the percentage of the region depending on allocated and tagged number.

Employee id	employee name	manager	allocated india	completed india	allocated us	completed us	total allocated	total completed
1	A	x	10	16	5	3	15	19
2	B	y	20	20	16	26	36	46
3	c	Z	15	4	10	10	25	14

Region
Total allocated 
Total tagged
Regionwise %

This are the 3 sheets that my output file should consist. I was looking for some help on how to proceed with it, can this automation be implemented in .net preferably or Java?

Thanks in advance.

What I have tried:

Did not start the project yet.