Guide to Excel Reporting with MS SQL data using Crystal Report

First of all you need to create a New Data Set object from the Add new Item. Inside the Data set you need to create a new Table Adapter which will Fill data to a Data Table.

Then you need to create a new Crystal Report File from Add new Item. In the Crystal report, from the Field Explorer -> Database Fields, now you will able to see the created Data Table with all the Column Elements selected.

Select the Column Elements needed, and add them to your Crystal Report and arrange them to your specific need. Using Crystal Reports->Design->Printer Setup, you can adjust the size of the Crystal Report according to your need. Also to make it a template for Excel, you need to Suppress the Page Footer from the Section Expert.

Then the below given Block of code is used for Excel Reporting.

try {

TempDataSetTableAdapters.TempTableAdapter temp = new MyProject.TempDataSetTableAdapters.TempTableAdapter();
TempDataSet.TempDataTable tempTable = new TempDataSet.TempDataTable();

DataTable DT = tempTable;
temp.Fill(tempTable);

ReportDocument rpt = new ReportDocument();
rpt.Load(Server.MapPath("~") + "\\TempReport.rpt");
rpt.SetDataSource(DT);

Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();

rpt.ExportToHttpResponse(ExportFormatType.Excel, Response, true, "Report");

}

catch (Exception ex){

}