Click here to Skip to main content
15,797,984 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 6-Apr-22 22:44pm
v2

1 solution

If you choose .NET then you can use the Microsoft.Office.Interop.Excel Namespace | Microsoft Docs[^] to process the Excel data.

Alternatively it may be possible just using Excel macros.
 
Share this answer
 
v2
Comments
Yakub Syed 2022 8-Apr-22 3:33am    
Thanks for the prompt response Sir. Could you please help me with the code if possible? Thanks.
Richard MacCutchan 8-Apr-22 3:53am    
Sorry, this site is here to help people resolve problems in the code that they write. We cannot write it for you, or provide hands on training.

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