Excel Programming With C# -

Posted by Sharpcnet under C# on 11/18/2013 | Points: 10 | Views : 980 | Status : [Member] | Replies : 1
New to Excel programming. There is so much stuff like LinqToExcel, excellibrary.dll, Microsoft.Interop, that I am confused on where and how to start, and what to choose.
Here is what I need to do
1. Open an excel sheet for the user on a button click that is of the template
Code Name AL CL SL ML
--------------------------
eo1 abc
e02 def

The code & name are already in database. The other columns are leave types, also stored in one table.

2. The user will fill the no. of days and upload it. (Uploading and taking it to a DataTable, thankfully, I'm aware).

3. Transpose that datatable like this and save to DB
code leavetype days
--------------------
eo1 al 2
eo1 cl 3
eo2 sl 4

I'm not asking for answers/code, but , Could someone guide me with the logical approach,tools/pointers for the tasks 1 ,3 so that I can fight from here.




Responses

Posted by: Bandi on: 11/20/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,
the SQL server queries looks like below for 3rd point:

declare @excel table(code varchar(10), Name varchar(10), AL int, CL int, SL int, ML int)
insert into @excel values('eo1', 'abc', 2, 3, NULL, NULL), ('e02', 'def', NULL, NULL, 4, NULL)

SELECT Code, Name, LeaveType, Days
FROM @excel
UNPIVOT
(Days For LeaveType in ( [AL] , [CL] , [SL] , [ML] ))unPvt

/* OUTPUT:
code Name cat LeaveType
e02 def 4 SL
eo1 abc 2 AL
eo1 abc 3 CL
*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response