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
sharmila kPosted Oct 19, 2012, 1:59 AM
Thank u for your replay
Sharon WhitePosted Oct 17, 2012, 4:08 AM
http://www.codeproject.com/Tips/190144/Export-Database-to-Excel-PDF-HTML-RTF-XML-etc-for-.aspx
Or, you can try to some Data Export component.
But I don't think that it is easy to display comment after exporting data from SQL to Excel.