sharmila k

sharmila k

  • NA
  • 62
  • 51.6k

Reg: Import sl data values to excel sheets (like Report generation)

Oct 16 2012 4:47 AM
Hi friend,
As i want to create web application for report generating.
In that report creating i need, sql data values import to excel sheets its working.
This is my Coding:
Payment details of every month report:
WITH allDays (calc_date)
AS
(SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP) - DAY(GETDATE()-1), 0)
UNION ALL
SELECT DATEADD(DAY, 1, calc_date)
FROM allDays
WHERE DATEADD(DAY, 1, calc_date) < DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())))
select
convert(varchar,calc_date,4) AS 'Date_',
convert(numeric(10,0),SUM(case when Course_Name = 'A+ ' then Amount_Paid else 0 end )) as 'ap',
convert(numeric(10,0),SUM(case when Course_Name = 'CCNA' then Amount_Paid else 0 end)) as 'ccna',
convert(numeric(10,0),SUM(case when Course_Name = 'Combo' then Amount_Paid else 0 end)) as 'combo',
convert(numeric(10,0),SUM(case
when Course_Name = 'A+ ' then Amount_Paid
when Course_Name = 'CCNA' then Amount_Paid
when Course_Name = 'Combo' then Amount_Paid
else 0 end)) as 'total'           
into #temp_
Coimbatore_col from tbl_receipt right outer join allDays on Tbl_Receipt.Branch_Name='Coimbatore' and           
Tbl_Receipt.Payment_Date >= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101)           
and convert(varchar,tbl_receipt.Payment_Date,4)=convert(varchar,allDays.calc_date,4)           
group by convert(varchar,tbl_receipt.Payment_Date,4),           
convert(varchar,allDays.calc_date,4)
Update after current date to convert null values:
update #temp_Coimbatore_col set
ap=null,
ccna=null,
combo=null,
total=null
where
convert(varchar,Date_,4) > convert(varchar,getdate(),4)
Calculate
the total Amount of that month:
SELECT date_ 'Date',
sum(ap) 'A+',
sum(ccna) 'CCNA',
sum(combo) 'Combo',
SUM(total) 'Total'
into #temp2_
Coimbatore_col FROM #temp_Coimbatore_col
GROUP BY date_
WITH ROLLUP

Import sql datavalues to excel:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\temp_auto\dr_rep\
Coimbatore_Daily_Report.xls;',
'select * FROM [Collection$]')
select * from #temp2_
Coimbatore_col

As i need, to display comma value of sql data values to excel and also display comment in excel format. How to write the sql query ?

Please any one help immediately solution for me..

I need immediately solution for this questions. Advance thanks for friends



Answers (2)