Just Code‎ > ‎

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

posted Nov 16, 2010, 1:38 AM by Peter Henell   [ updated Nov 16, 2010, 1:44 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();
            }
        }
    }
}
Comments