Processing
 

How to create download option for dynamically generated workbook

10/03/2013 07:57#1

Joined at: 5 months ago

Post: 2

Thank: 0

Thanked: 0

I an using below code to convert sql data to excel workbook..by using this code .xls file is created on C:// drive. How to make it downloadable so,the clients can download it? Help me. I am using asp.net with c#. Below code is working fine for storing .xls file in

c:/ drive.

try     {           myfilename = subject + "_Sessional" + sessional + "_Batch" + batch;          string sql = null;         string data = null;         int i = 1;         int j = 0;          Excel.Application xlApp;         Excel.Workbook xlWorkBook;         Excel.Worksheet xlWorkSheet;         object misValue = System.Reflection.Missing.Value;          xlApp = new Excel.ApplicationClass();         xlWorkBook = xlApp.Workbooks.Add(misValue);         xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);         xlWorkSheet.Cells[1, 1] = "Id";         xlWorkSheet.Cells[1, 2] = "RollNo";         xlWorkSheet.Cells[1, 3] = "Name";         xlWorkSheet.Cells[1, 4] = "Branch";         xlWorkSheet.Cells[1, 5] = "Sem";         xlWorkSheet.Cells[1, 6] = "Year";         xlWorkSheet.Cells[1, 7] = "Batch";         xlWorkSheet.Cells[1, 8] = "Subject";         xlWorkSheet.Cells[1, 9] = "Sessional";         xlWorkSheet.Cells[1, 10] = "Attendence";         xlWorkSheet.Cells[1, 11] = "OutOff";           using (cnn = new SqlConnection(strConnString))         {             cnn.Open();             sql = "SELECT DISTINCT [st_id], [st_rollno], [st_name], [branch_name], [sem_no], [batch_year] FROM [Student] WHERE (([st_rollno] >=@st_rollno) AND ([st_rollno] <=@st_rollno2) AND ([sem_no] = @sem_no) AND ([branch_name] = @branch_name)) ORDER BY [st_rollno]";              SqlDataAdapter dscmd = new SqlDataAdapter();             SqlCommand selectCMD = new SqlCommand(sql, cnn);             dscmd.SelectCommand = selectCMD;             selectCMD.Parameters.Add("@st_rollno", SqlDbType.VarChar, 10).Value = firstrollno.Text;             selectCMD.Parameters.Add("@st_rollno2", SqlDbType.VarChar, 10).Value = lastrollno.Text;             selectCMD.Parameters.Add("@sem_no", SqlDbType.Int).Value = dpsemno.SelectedItem.Text.ToString();             selectCMD.Parameters.Add("@branch_name", SqlDbType.VarChar, 5).Value = dpbranchname.SelectedItem.Text.ToString();               DataSet ds = new DataSet();             dscmd.Fill(ds);              for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)             {                 for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)                 {                      data = ds.Tables[0].Rows[i].ItemArray[j].ToString();                     xlWorkSheet.Cells[i + 2, j + 1] = data;                     xlWorkSheet.Cells[i + 2, 7] = batch;                     xlWorkSheet.Cells[i + 2, 8] = subject;                     xlWorkSheet.Cells[i + 2, 9] = sessional;                     xlWorkSheet.Cells[i + 2, 11] = outoff;                 }             }              xlWorkBook.SaveAs("c:\\" + myfilename + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);             xlWorkBook.Close(true, misValue, misValue);             xlApp.Quit();              releaseObject(xlWorkSheet);             releaseObject(xlWorkBook);             releaseObject(xlApp);             lbmsg.Text = "Excel file created,you can find the file in c:\\";             lbmsg.ForeColor = Color.Green;             lbmsg.Visible = true;          }     }     catch (SqlException ee)     {         lbmsg.Text = ee.Message;         lbmsg.ForeColor = Color.Green;         lbmsg.Visible = true;     }     finally     {         cnn.Close();     } }        private void releaseObject(object obj)         {             try             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);                 obj = null;             }             catch (Exception ex)             {                 obj = null;               Label2.Text= "Exception Occured while releasing object " + ex.ToString();               Label2.ForeColor = Color.Green;               Label2.Visible = true;              }          } 
10/03/2013 08:56Top#2

Joined at: 5 months ago

Post: 1

Thank: 0

Thanked: 0

very simple, place this code on download button click

 Response.Clear();  Response.ClearHeaders();  Response.ClearContent();  Response.AddHeader("content-disposition", "attachment; filename=" + fileName);  Response.AddHeader("Content-Type", "application/Excel");  Response.ContentType = "application/vnd.xls";  Response.AddHeader("Content-Length", file.Length.ToString());  Response.WriteFile(file.FullName);  Response.End(); 

hope this works.

Similar articles

Excel More conditions in one formula?

5 months ago - Reply: 2 - Views: 172

Get Workbook reference from Open File dialog

5 months ago - Reply: 2 - Views: 130

Add Calculated Field To Pivot Table

5 months ago - Reply: 0 - Views: 7

Nested IF Functions in Excel not working

6 months ago - Reply: 3 - Views: 164

Formulating Date in Excel 2013

6 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

6 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

6 months ago - Reply: 1 - Views: 23

excel search and copy in/from another file

6 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

6 months ago - Reply: 0 - Views: 6

Excel static conditional formatting area

6 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re