Sharing Asp.net articles

how to create an excel file from sql servers resultset and download it on a button click…

the code for the same is :

protected void btnDownload_Click(object sender, EventArgs e){try

{SqlConnection con = new SqlConnection();con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;con.Open();string query = “select b.model_id as ModelID,a.modfname as ‘FirstName’ ,a.modlname as ‘LastName’,count(*) as ‘No of Votes’ from tbl_votes as b inner join tbl_model as a on a.modelid= b.model_id group by b.model_id,a.modfname,a.modlname”;

SqlCommand cmd = new SqlCommand(query, con);SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);OWC10.

Spreadsheet spread = new OWC10.SpreadsheetClass();// reset countersint rowCount = 0;rowCount++;// write out field headers

for (int colCount = 0; colCount < dr.FieldCount; colCount++){spread.Cells[rowCount, colCount + 1] = dr.GetName(colCount);

}

// write out the actual data by looping thru

// the dataReaderwhile (dr.Read()){rowCount++;

for (int colCount = 0; colCount < dr.FieldCount; colCount++){// filter out any embedded \r & \n in

// the field data

spread.Cells[rowCount, colCount + 1] = dr.GetValue(colCount).ToString().Replace(“\r”, “”).Replace(“\n”, “”);}}

// generate the XLS file

string fileName = Server.MapPath(“voteDetails.xls”);spread.Export(fileName, OWC10.SheetExportActionEnum.ssExportActionNone , OWC10.SheetExportFormat.ssExportAsAppropriate);// de-reference the SpreadSheet Control spread = null;//downloading file on users computer

Response.ClearHeaders();Response.ContentType = “application/vnd.ms-excel”;Response.Clear();Response.AppendHeader(

“Content-Disposition”, “attachment;Filename=voteDetails.xls”);Response.TransmitFile(“voteDetails.xls”);Response.End();// clean-up the file

//File.Delete(fileName);}

catch (Exception ex){}

}