Download multiple excel files from the DataTable

Question

I am trying to execute a query using for loop. For each loop it should download an excel file. The solution works perfectly, but only the first file is downloaded and the other two files are not downloaded. I have also attached the complete code below.

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Btn_Click(object sender, EventArgs e)
    {
        DataTable it = GetList();
        foreach(DataRow dr in it.Rows)
        {
            string a = dr[0].ToString();
            for (int i = 0; i < 3; i++) 
            {
                string inm = it.Rows[i][0].ToString();
                DataTable gt = GetData(inm);
                ExportToSpreadsheet(gt, "Samples");
            }
        }
    }

    public DataTable GetData(string i)
    {

        SqlCommand command = null;
        SqlConnection conn = null;
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        conn.Open();
        command = new SqlCommand("SELECT id, name, class FROM  StudentTable WHERE (id = " + i + ") ORDER BY name";
        DataTable dt = new DataTable();
        SqlDataAdapter ada = new SqlDataAdapter(command);
        ada.Fill(dt);
        return dt;
    }

    public static void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;

        context.Response.ClearContent();
        context.Response.ContentType = "text/vnd.ms-excel";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".xls");

        string tab = "";
        foreach (DataColumn dc in table.Columns)
        {
            context.Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        context.Response.Write("\n");
        context.Response.Write("\n");
        int i;
        foreach (DataRow dr in table.Rows)
        {
            tab = "";
            for (i = 0; i < table.Columns.Count; i++)
            {
                context.Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            context.Response.Write("\n");
        }
        context.Response.End();
    }

I have seen posts similar to this. Some people have recommended that, zip files can be created on the server and download multiple excel files in "zip" folder. If it is possible, how can I implement it to the above solution?

score 1 · Accepted Answer · edited May 23 '17 at 11:58

You're calling response.end after creating the first file - which aborts the rest of the process.

I don't think you'll be able to create 3 excel files for download using this method.

As an alternative you could create 3 CSV files on disk, then use a zip library to zip them up. see zipping files

Or you could use JET with an Excel connection string and use SQL Insert statements to push your data into an empty excel file. And use a different worksheet for each of your tables

write to excel with JET (but this limits you to 32bit)

Or you could use a third part control to write an excel file with the three tables as worksheets

Infragistics excel control

But if I were you - I'd just present the user with 3 different links they could click on.

Download multiple excel files from the DataTable

1 Answers1