1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Export multiple queries to excel

Discussion in 'C#' started by jellybeannn, Aug 28, 2010.

  1. jellybeannn

    jellybeannn New Member

    Joined:
    Aug 28, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hi,
    I've got a door_order table which I export to excel, now I have to make it so that underneath each door_order the products are listed from door_product table, please not that this can be more than one product for that order.

    This is my code so far
    public DataTable GetData(SqlCommand cmd)
    {
    DataTable dt = new DataTable();
    SqlConnection con =
    new SqlConnection(@"Data Source=;Initial Catalog=bargaindoors;Persist Security Info=True;User ID=;Password=");
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
    con.Open();
    sda.SelectCommand = cmd;
    sda.Fill(dt);
    return dt;
    }
    catch (Exception ex)
    {
    throw ex;
    }
    finally
    {
    con.Close();
    sda.Dispose();
    con.Dispose();
    }
    }


    protected void Calculate_Click(object sender, EventArgs e)
    {
    string startDate = testinput.Text;
    string endDate = testinput2.Text;

    string strQuery = "SELECT O_ID AS ID, O_Datecreated AS 'Date Created', O_Ath AS Ath, Amount, Delivery, CU_Title AS Title, CU_F_Name AS Name, CU_L_Name AS Surname, CU_Address1 AS Address1, CU_Address2 AS Address2, CU_City AS City, CU_County AS County, CU_Country AS Country, CU_Postal AS Postal, PO, '\"' + Instructions + '\"', O_ID AS products FROM door_order WHERE O_Ath='A' AND " +
    "O_Datecreated BETWEEN '" + startDate + "' AND '" + endDate + "'";
    SqlCommand cmd = new SqlCommand(strQuery);
    //Response.Write(startDate);
    DataTable dt = GetData(cmd);

    string attachment = "attachment; filename=Enquiry.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-excel";
    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
    Response.Write(tab + dc.ColumnName);
    tab = "\t";
    }
    Response.Write("\n");
    int i;
    foreach (DataRow dr in dt.Rows)
    {
    tab = "";
    for (i = 0; i < dt.Columns.Count; i++)
    {
    Response.Write(tab + dr.ToString());
    tab = "\t";
    }
    Response.Write("\n");
    }
    Response.End();
    }
    }
     

Share This Page