Click here to Skip to main content
15,900,815 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how to export gridview data in excel based checkbox checked value


i am getting in excel as two rows for single record

What I have tried:

ALTER PROCEDURE [dbo].[PMA_SendMailAlert_Project_Created_PMT]	
@Project_Id VARCHAR(50)
AS
BEGIN

-----------project type=internal,project nature=integrated-------------------
declare @cnt int;
set @cnt=0;
select @cnt=count(*) from Project_master where (project_id=@Project_Id) and project_type_id in (select Project_Type_Id from Project_Type where Project_Type_Name='Integrated') and Project_Nature in (select Project_Nature_Id from Project_Nature where Project_Nature='Internal');

print convert(nvarchar(max),@cnt);
-----------project type=internal,project nature=integrated-------------------

---------Vertical table------------

SELECT    
       p.Project_Name as "Project Name"
	  ,p.Project_Code as "Project Code"
	  ,c.Client_Name as "Client"
	  ,pt.Project_Type_Name as "Project Type"	
	  ,p.WO_PO as "WO / PO Number"
	  ,CONVERT(VARCHAR(11),p.WO_PO_Date,106) as "WO / PO Date"	
	  ,p.WO_PO_Value+' '+p.Currency as "WO / PO Amount"
	  ,p.Currency_INR as "WO / PO Amount (INR)"
      
  FROM Project_Master p
    inner join Client_Master c on p.Client_Id=c.Client_Id
	inner join Project_Type pt on p.Project_Type_Id=pt.Project_Type_Id
  WHERE Project_Id = @Project_Id;

  ---------Vertical table------------

  ---------Horizental table------------

  SELECT 
	   fs.Function_Section_Name "Function Name"
	  ,pf.Initial_Budget_Allocation+' '+pf.Initial_Budget_Currency as "Initial Budget Allocation"
	  ,pf.Initial_Budget_INR as "Initial Budget(INR)"
	  ,pf.No_Of_FTE as "Budgeted FTE"
	  ,e_fHod.Employee_Name "HOD / VP"
	  ,e_pc.Employee_Name "Project Leader"
	  
  FROM Project_Master p
	left outer join Project_Function pf on pf.Project_Id=p.Project_Id
	inner join Function_Section fs on fs.Function_Section_Id=pf.Function_Id	
	left outer join Employee e_pc on pf.Project_Coordinator=e_pc.Employee_id
	left outer join Employee e_fHod on pf.HOD_Id=e_fHod.Employee_id		
 WHERE p.Project_Id = @Project_Id and pf.IsDeleted=0; 

 ---------Horizental table------------

 ---------Mail table------------

 select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('PMT') )	and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
----CSO
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('CSO') ) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
----IPR
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('IPR') ) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
----Finance
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('Finance') ) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
--SENIOR MANAGEMENT:-(Business Head,Finance Head)
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('SENIOR MANAGEMENT') ) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
----SCM---
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('SCM') ) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
union
----Project and function wise HOD ----
select distinct Comp_Email_ID from Employee where Employee_id in (select p_f.HOD_Id from project_master p_m inner join Project_Function p_f on p_m.Project_Id=p_f.Project_Id where p_f.project_id=@Project_Id) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )-- and p_f.Function_Id=@Function_Id)				
union
----Project and function wise Project Leader ----
select distinct Comp_Email_ID from Employee where Employee_id in (select p_f.Project_Coordinator from project_master p_m inner join Project_Function p_f on p_m.Project_Id=p_f.Project_Id where p_f.project_id=@Project_Id) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )-- and p_f.Function_Id=@Function_Id)				
union
-------Function wise HOD(Mail should go to Saravanakumar Dhakshinamoorthy for this functions('Invitro - Biology','Invivo - Biology','Structural Biology','Animal Facility'))---
SELECT distinct  e.Comp_Email_ID FROM Project_Master p
              inner join Project_Function pf on pf.Project_Id=p.Project_Id
              inner join Function_Section fs on fs.Function_Section_Id=pf.Function_Id
              inner join Mail_Function_HOD_Map m_f_h on m_f_h.Function_Id=pf.Function_Id
              inner join employee e on e.Employee_id=m_f_h.HOD_Id
       WHERE p.Project_Id = @Project_Id and pf.IsDeleted=0 and e.Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )

union
-------Project wise Project Leader assign for Mail alert ----
--select Comp_Email_ID from Employee where Employee_id in (select plm.ProjectLeader_Id from Project_Function_ProjectLeader_Mail plm left outer join Project_Function pf on plm.Project_Function_Id =pf.Project_Function_Id left outer join Project_Master p on pf.Project_Id=p.Project_Id left outer join Employee e_pfPLMail on plm.ProjectLeader_Id=e_pfPLMail.Employee_id where pf.project_id=@Project_Id) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )-- and pf.Function_Id=@Function_Id)	
------Region User(BD)
--union
select distinct E.Comp_Email_ID from project_master p_m
		inner join Client_Master c_m on p_m.Client_Id=c_m.Client_Id
		inner join ClientAddress ca on c_m.Client_Id=ca.Client_Id
		inner join Country co on co.Country_Id=ca.Country_Id
		inner join City city on city.City_Id=ca.Client_City_Id
		inner join Region r on r.Region_Id=city.Region_Id and p_m.Client_Address_Id=ca.Client_Add_ID
		inner join Region_Users r_u on r_u.Region_Id=r.Region_Id
		inner join Employee E on E.Employee_id=r_u.User_Id
		where project_id=@Project_Id and p_m.Project_Nature=(SELECT [Project_Nature_ID] FROM [Project_Nature] where Project_nature='External') and E.Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )

union

-----------------internal-integrated-------------------------

----Strategy: internal-integrated ( for example:Rajeev Tyagi)
select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('Strategy') )  and (@cnt>0) and Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )

union

----VP: internal-integrated ('Takeshi Yura', 'Saravanakumar Dhakshinamoorthy' , 'Ramesh Mullangi') along with rajeev
SELECT distinct E.Comp_Email_ID FROM [Mail_Alert_Project_Internal_Integrated_VP] m 
	inner join Employee E on E.Employee_id=m.Employee_Id
	where (m.[IsDeleted] is null or m.[IsDeleted]=0) and (@cnt>0) and E.Emp_Status_Id=(select Emp_Status_Id from Employee_Status where upper(Emp_Status_Name)=upper('Active') )
-----------------internal-integrated-------------------------


---------Mail table------------

-------subject-----------------
SELECT
	  p.Project_Code as "Project Code"
	  ,p.WO_PO as "WO-PO Number"      
  FROM Project_Master p
    inner join Client_Master c on p.Client_Id=c.Client_Id
	inner join Project_Type pt on p.Project_Type_Id=pt.Project_Type_Id
  WHERE Project_Id = @Project_Id;

END











--USE [PMA_Dev_DtFn]
--GO
--/****** Object:  StoredProcedure [dbo].[PMA_SendMailAlert_Project_Created_PMT]    Script Date: 7/19/2017 5:51:56 PM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO


--ALTER PROCEDURE [dbo].[PMA_SendMailAlert_Project_Created_PMT]	
--@Project_Id VARCHAR(50)
--AS
--BEGIN

-----------Vertical table------------

--SELECT    
--       p.Project_Name as "Target / Study Name"
--	  ,p.Project_Code as "Project Code"
--	  ,c.Client_Name as "Client"
--	  ,pt.Project_Type_Name as "Project Type"	
--	  ,p.WO_PO as "WO / PO Number"
--	  ,CONVERT(VARCHAR(11),p.WO_PO_Date,106) as "WO / PO Date"	
--	  ,p.WO_PO_Value+' '+p.Currency as "WO / PO Amount"
--	  ,p.Currency_INR as "WO / PO Amount (INR)"
      
--  FROM Project_Master p
--    inner join Client_Master c on p.Client_Id=c.Client_Id
--	inner join Project_Type pt on p.Project_Type_Id=pt.Project_Type_Id
--  WHERE Project_Id = @Project_Id;

--  ---------Vertical table------------

--  ---------Horizental table------------

--  SELECT 
--	   fs.Function_Section_Name "Function Name"
--	  ,pf.Initial_Budget_Allocation+' '+pf.Initial_Budget_Currency as "Initial Budget Allocation"
--	  ,pf.Initial_Budget_INR as "Initial Budget(INR)"
--	  ,pf.No_Of_FTE as "Budgeted FTE"
--	  ,e_fHod.Employee_Name "HOD / VP"
	  
--  FROM Project_Master p
--	left outer join Project_Function pf on pf.Project_Id=p.Project_Id
--	inner join Function_Section fs on fs.Function_Section_Id=pf.Function_Id	
--	left outer join Employee e_fHod on pf.HOD_Id=e_fHod.Employee_id		
-- WHERE p.Project_Id = @Project_Id and pf.IsDeleted=0; 

-- ---------Horizental table------------

-- ---------Mail table------------

-- select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('PMT') )
	
--union
--	----CSO
--select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('CSO') )
--union
----Finance
--select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('Finance') )
--union
----SENIOR MANAGEMENT:-(Business Head,Finance Head)
--select Comp_Email_ID from Employee where Role_Id in (select Role_Id from aspnet_Roles where upper(RoleName)=upper('SENIOR MANAGEMENT') )

----Region User(BD)
--union
--select distinct E.Comp_Email_ID from project_master p_m
--		inner join Client_Master c_m on p_m.Client_Id=c_m.Client_Id
--		inner join ClientAddress ca on c_m.Client_Id=ca.Client_Id
--		inner join Country co on co.Country_Id=ca.Country_Id
--		inner join City city on city.City_Id=ca.Client_City_Id
--		inner join Region r on r.Region_Id=city.Region_Id
--		inner join Region_Users r_u on r_u.Region_Id=r.Region_Id
--		inner join Employee E on E.Employee_id=r_u.User_Id
--		where project_id=@Project_Id and p_m.Project_Nature=(SELECT [Project_Nature_ID] FROM [Project_Nature] where Project_nature='External')
-----------Mail table------------

---------subject-----------------
--SELECT
--	  p.Project_Code as "Project Code"
--	  ,p.WO_PO as "WO-PO Number"      
--  FROM Project_Master p
--    inner join Client_Master c on p.Client_Id=c.Client_Id
--	inner join Project_Type pt on p.Project_Type_Id=pt.Project_Type_Id
--  WHERE Project_Id = @Project_Id;

--END
Posted
Updated 29-Nov-17 23:58pm
v11

1 solution

Please check the solution given here.
Get Row Cell Value From Grid View of Checked Checkbox in ASP.Net[^]
In the button click, create a new table/list and export the same to excel sheet.
C#
protected void btnGetRecord_Click(object sender, EventArgs e)

List to excel:
Exporting Generic List<T> to Excel in C# Using Interop[^]
 
Share this answer
 
v3

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