Excel Programming With C# -

Posted by Sharpcnet under C# on 11/18/2013 | Points: 10 | Views : 2289 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts