Asp.Net - Quick n Dirty download SQL result as excel file to user

Post date: Nov 16, 2010 9:38:15 AM

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.SqlClient;using System.Data;using System.Text;using System.IO;namespace mrm.SendResultToUser { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button2_Click(object sender, EventArgs e) { CreateAndUploadFile("dbo.GetVaderPrognos" , @"c:\temp\prognos.csv" , CommandType.StoredProcedure); } private void CreateAndUploadFile(string query, string fileName, CommandType cmdType) { DataSet ds = GetData(query, cmdType); CreateFile(ds, fileName); } private static DataSet GetData(string query, CommandType cmdType) { DataSet ds = new DataSet(); using (SqlConnection con = new SqlConnection("")) { using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.CommandType = cmdType; cmd.Connection.Open(); SqlDataAdapter ad = new SqlDataAdapter(cmd); ad.Fill(ds); } } return ds; } public void CreateFile(DataSet ds, string filename) { Response.Clear(); Response.ContentType = "Application/x-msexcel"; System.IO.File.Delete(filename); if (ds.Tables[0].Rows.Count > 0) { FileStream stre = new FileStream(filename, FileMode.Create); StreamWriter writer = new StreamWriter(stre, Encoding.UTF8); // Lägg till rubrikerna på första raden foreach (DataColumn col in ds.Tables[0].Columns) { writer.Write(col.ColumnName + ";"); } // Lägg till rader med data foreach (DataRow dataRow in ds.Tables[0].Rows) { writer.WriteLine(); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { string s = dataRow[i].ToString(); writer.Write(s + ";"); } } writer.Flush(); writer.Close(); //Response.End(); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename)); Response.WriteFile(filename); Response.End(); } } }}