How to export the result set of a SQL Server stored procedure to an Excel spreadsheet using VS2005 for a windows application?

zimmer9
zimmer9 used Ask the Experts™
on
I am writing a C# windows application using VS2005. Do you know how I could rewrite the following routine to export the result of calling a SQL Server stored procedure to an Excel spreadsheet (Excel 2003).

private void FallQueryACSToExcel(string reportyr)
        {
            SqlConnection _conn = new System.Data.SqlClient.SqlConnection("Data Source=;");
            _conn.Open();
            System.Data.SqlClient.SqlCommand comm = _conn.CreateCommand();
            comm.CommandType = System.Data.CommandType.StoredProcedure;
            comm.CommandText = string.Format("procFlACSExclAAA");
            comm.Parameters.Add("@RptYear", SqlDbType.VarChar, 50).Value = reportyr;
            comm.CommandTimeout = 750;
            System.Data.DataSet dataSet1 = new System.Data.DataSet();
            System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(comm);
            sqlDataAdapter1.Fill(dataSet1);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi zimmer9,

Take a look here:

exporting sql statement to excel in c#
http://sql2005ted.blogspot.co.uk/2012/02/exporting-sql-statement-to-excel-in-c.html

Author

Commented:
But I'm executing a Stored Procedure. This is an example when executing a hard coded
SQL Statement.
Commented:
There is no difference, if you are executing SELECT statement or stored procedure. When you have filled DataSet (sqlDataAdapter1.Fill(dataSet1) in you code, dscmd.Fill(ds); in example), all further processing should be the same.
Systems Development / Support Specialist
Commented:
protected void Button1_Click(object sender, EventArgs e)
    {
        string strErrorMessage, sep, strFileName, SuccessFlag,strSuccessFlag;
        sep = "";
        SuccessFlag = "";
        strErrorMessage = "";
        strFileName = "Task_List";
        StringBuilder htmlcontent = new StringBuilder();
        int i;
        DataAccess objDataAccess1 = new DataAccess();
        SqlCommand objCommand1 = new SqlCommand();
        objDataAccess1.Connection_Open("BTB");
        try
        {
            objCommand1.Connection = objDataAccess1.m_sqlConnection;
            Label1.Text = objCommand1.Connection.State.ToString();
            objCommand1.CommandText = "fetch_col";
            objCommand1.CommandType = CommandType.StoredProcedure;
            reader = objCommand1.ExecuteReader();
        }
        catch
        {

        }
        
        int n;
        string sValue = String.Empty;
        if (reader.HasRows)
        {
            sValue = "\t";
            while (reader.Read())
            {
                
                for (n = 0; n <reader.FieldCount; n++)
                {
                    htmlcontent.Append(reader.GetValue(n) + sValue);
                }
            }
            htmlcontent.Append("\n");
        }
        reader = null;
        try
        {
            strSuccessFlag = "";
           strErrorMessage = "";
           DataAccess objDataAccess = new DataAccess();
           SqlCommand objCommand = new SqlCommand();
           try
           {
               objDataAccess.Connection_Open("BTB");
               objCommand.Connection=objDataAccess.m_sqlConnection;
               objCommand.CommandText = "test1";
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add(new SqlParameter("@p_emp_id", SqlDbType.VarChar, 10));
               objCommand.Parameters["@p_emp_id"].Value = strEmpId;
               reader=objCommand.ExecuteReader();
           }
           catch
           {

           }
           //finally 
           //{
           //    objCommand =null;
           //    objDataAccess = null;
           //    reader = null;
           //}
           //DataTable dt = reader.GetSchemaTable();
            
           if (reader.HasRows)
           {
               sValue = "\t";
               while (reader.Read())
               {

                   for (n = 0; n < reader.FieldCount; n++)
                   {
                       htmlcontent.Append(reader.GetValue(n) + sValue + sValue + sValue + sValue + sValue + sValue);
                   }
                   htmlcontent.Append("\n");
               }
               
           }

        }
        catch
        {
 
        }
        string strcontent, strcontent1;
        strcontent = htmlcontent.ToString();
        
        strcontent1 = fCleanString(strcontent);
        strcontent1 = strcontent.Replace("\n", System.Environment.NewLine);
        strcontent1 = strcontent1.Replace("\t", "    ");
       
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;" + "filename=" + strFileName + ".xls");

        //HttpContext.Current.Response.Write();
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.Close();

    }
    public string fCleanString(string sstring)
    {
        string sReturn;
         int i;
        sReturn = "";
        for(i=1;i<sstring.Length;i++)
        {   string  a=Mid(sstring, i, 1);
        byte[] asciiBytes = Encoding.ASCII.GetBytes(a);
           //j= Convert.ToInt32(a);
        if (asciiBytes[0] >= 32 && asciiBytes[0] <= 177)
            sReturn = sReturn + Mid(sstring, i, 1);

        }
           return sReturn;
    }
    public static string Mid(string param, int startIndex, int length)
    {
        //start at the specified index in the string ang get N number of
        //characters depending on the lenght and assign it to a variable
        string result = param.Substring(startIndex, length);
        //return the result of the operation
        return result;
    }

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial