How to make file excel file as downloadable in ASP.NET?

Hello Friends...
i have created excel file using C# at server side using following code.

protected void btnexport_Click(object sender, EventArgs e)

{

Excel.Application excelApp = new Excel.Application();

excelApp.Workbooks.Add();

Excel._Worksheet workSheet = excelApp.ActiveSheet;

//string FilePath = @"C:\Mori-2014\Report\DailyReport.xls";

string FilePath = @"C:\Mori-2014\Mori-2014\Reports\DailyReport.xls";

Excel.Range rng1, rng2, range3,rng4,rng5,rng6,rng7;

SqlConnection ObjConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString());

SqlCommand ObjCommand = new SqlCommand("spgetproduction", ObjConnection);

ObjConnection.Open();

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

DataSet ds = new DataSet();

SqlDataAdapter adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.get_Range("A1", "H1").Merge(true);

workSheet.get_Range("A1:L1").RowHeight = 39;

workSheet.get_Range("A1:L1").EntireRow.VerticalAlignment = 2;

workSheet.get_Range("A1:L1").EntireRow.HorizontalAlignment = 3;

workSheet.get_Range("A1:L1").EntireRow.Font.Size = 26;

workSheet.get_Range("A2:L51").EntireRow.Font.Size = 16;

//workSheet.get_Range("I13:J17").EntireRow.Font.Size = 14;

//workSheet.get_Range("K29:K32").EntireRow.Font.Size = 14;

rng1 = workSheet.get_Range("B1", "D1").EntireColumn;

rng1.HorizontalAlignment = 3;

workSheet.Cells[1, 1] = "DAILY OPERATING REPORT - HINDALCO MURI";

//workSheet.get_Range("A1", "D1").Font.Bold = true;

//workSheet.get_Range("A2", "D2").Font.Bold = true;

//-------- Logo Image ------------

workSheet.Shapes.AddPicture(@"C:\Mori-2014\Mori-2014\Image\muri.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 57, 38);

rng2 = workSheet.get_Range("F1", "H1").EntireColumn;

rng2.HorizontalAlignment = 3;

//--------- Date Part --------------------

workSheet.Cells[1, 9] = String.Format("{0:dd-MMM-yyyy}", Convert.ToDateTime(txtdate1.Text));

workSheet.get_Range("I1", "L1").Merge(true);

// ---------Production ------------

workSheet.Cells[2, 1] = "Production (MT)";

workSheet.Cells[2, 2] = "Today";

workSheet.Cells[2, 3] = "Avg.";

workSheet.Cells[2, 4] = "Todate";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r+3, c+1] = ds.Tables[0].Rows[r][c].ToString();

}

//--------------- Efficiencies -----------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetefficiencies";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.get_Range("A6", "D6").Merge(true);

workSheet.get_Range("A7", "D7").Font.Bold = true;

workSheet.Cells[7, 1] = "Efficiencies";

workSheet.Cells[7, 2] = "Target";

workSheet.Cells[7, 3] = "Today";

workSheet.Cells[7, 4] = "Avg.(Todate)";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 8, c + 1] = ds.Tables[0].Rows[r][c].ToString();

}

// -------- Consumption Factors --------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetconsumptionfactors";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.get_Range("A20", "D20").Merge(true);

workSheet.get_Range("A21", "D21").Font.Bold = true;

workSheet.Cells[21, 1] = "Consumption Factors";

workSheet.Cells[21, 2] = "Target";

workSheet.Cells[21, 3] = "Today";

workSheet.Cells[21, 4] = "Avg.(Todate)";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 22, c + 1] = ds.Tables[0].Rows[r][c].ToString();

}

// ---------- Stock ----------------

workSheet.get_Range("A30", "D30").Merge(true);

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetstock";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[31, 1] = "Stock";

workSheet.Cells[31, 3] = "Todate";

workSheet.get_Range("A31", "B31").Merge(true);

workSheet.get_Range("C31", "D31").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 32, c + 1] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("B32", "B41").Clear();

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 32, c + 3] = ds.Tables[0].Rows[r][1].ToString();

}

workSheet.get_Range("D32", "D41").Clear();

workSheet.get_Range("A32", "B41").Merge(true);

workSheet.get_Range("C32", "D41").Merge(true);

workSheet.get_Range("A31", "D31").Font.Bold = true;

// ---------- PDS Slurry ----------------

workSheet.get_Range("A42", "D42").Merge(true);

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetpdsslurry";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[43, 1] = "Reasons(s) for Flow Cut:";

workSheet.Cells[43, 2] = "Normal Flow";

workSheet.get_Range("B43", "C43").Merge(true);

workSheet.Cells[43, 4] = "Today's Flow";

workSheet.get_Range("A43", "D43").Font.Bold = true;

if (ds.Tables[0].Rows.Count > 0)

{

workSheet.Cells[44, 1] = ds.Tables[0].Rows[0][0].ToString();

workSheet.Cells[44, 2] = ds.Tables[0].Rows[0][1].ToString();

workSheet.get_Range("B44", "C44").Merge(true);

workSheet.Cells[44, 4] = ds.Tables[0].Rows[0][2].ToString();

}

workSheet.Cells[45, 1] = "Reasons(s) for Flow Cut:";

workSheet.get_Range("A45", "D47").Font.Bold = true;

workSheet.get_Range("A45:D47").VerticalAlignment = 1;

workSheet.get_Range("A45:D47").Merge();

workSheet.Cells[48, 1] = "Note: Daily basis declared production,efficiencies,receipt and dispatch numbers are only tentative. It can be verified with month-end physical inventory.";

workSheet.get_Range("A48:D49").Merge();

workSheet.get_Range("A48:D49").VerticalAlignment = 1;

workSheet.get_Range("A48:D49").WrapText = true;

workSheet.get_Range("A48:D49").Font.Bold = true;

workSheet.get_Range("A48:D49").Font.Size = 14;

//-------------Rain Fall------------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetrainfall";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[50, 1] = "Rain Fall (mm)";

workSheet.get_Range("A50:B51").Merge();

workSheet.get_Range("A50:B51").VerticalAlignment = 2;

workSheet.get_Range("A50:B51").HorizontalAlignment = 3;

workSheet.get_Range("A50:B51").Font.Bold = true;

workSheet.Cells[50, 3] = "Today";

workSheet.Cells[50, 4] = "Todate";

workSheet.Cells[51, 3] = ds.Tables[0].Rows[0][1].ToString();

workSheet.Cells[51, 4] = ds.Tables[0].Rows[0][2].ToString();

workSheet.get_Range("C50", "D50").Font.Bold = true;

//-------P & B Target for Hydrate--------

workSheet.Cells[2, 5] = "P & B Target for Hydrate(MT)";

workSheet.get_Range("E2", "G2").Merge();

workSheet.get_Range("E2", "G2").HorizontalAlignment = 3;

workSheet.get_Range("E2", "G2").VerticalAlignment = 2;

workSheet.get_Range("E2", "H2").Font.Bold = true;

workSheet.Cells[2, 8] = "29000";

workSheet.Cells[3, 5] = "Revised target for Hydrate(MT)";

workSheet.get_Range("E3","G3").Merge();

workSheet.get_Range("E3", "G3").HorizontalAlignment = 3;

workSheet.get_Range("E3", "G3").VerticalAlignment = 2;

workSheet.Cells[3, 8] = "29000";

//---------- Bauxite Quality ------------

workSheet.get_Range("E4", "H4").Merge(true);

workSheet.Cells[5, 5] = "Bauxite Quality (%)";

workSheet.Cells[5, 6] = "Target";

workSheet.Cells[5, 7] = "Today";

workSheet.Cells[5, 8] = "Avg.(todate)";

workSheet.get_Range("E5", "H5").Font.Bold = true;

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetbauxitequality";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 6, c + 5] = ds.Tables[0].Rows[r][c].ToString();

}

//-------------- Loss Times ------------

workSheet.get_Range("E11", "H11").Merge(true);

workSheet.Cells[12, 5] = "Loss Times(Hrs)";

workSheet.get_Range("E12", "F12").Merge();

workSheet.Cells[12, 7] = "Today";

workSheet.Cells[12, 8] = "ToDate";

workSheet.get_Range("E12", "H12").Font.Bold = true;

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetlosstimes";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 13, c + 5] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("F13", "F17").Clear();

workSheet.get_Range("E13","F17").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 13, c + 7] = ds.Tables[0].Rows[r][1].ToString();

}

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 13, c + 8] = ds.Tables[0].Rows[r][2].ToString();

}

workSheet.get_Range("I13:I17").Clear();

workSheet.get_Range("J13:J17").Clear();

//----------Operating Flows -----------

workSheet.get_Range("E18", "H18").Merge(true);

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetoperatingflows";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[19, 5] = "Operating Flows";

workSheet.Cells[19, 6] = "Target";

workSheet.Cells[19, 7] = "Today";

workSheet.Cells[19, 8] = "Avg.(Todate)";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 20, c + 5] = ds.Tables[0].Rows[r][c].ToString();

}

workSheet.get_Range("E19", "H19").Font.Bold = true;

workSheet.get_Range("E27").Clear();

//-------------CGPP Report -----------

workSheet.get_Range("E27", "H27").Merge(true);

workSheet.Cells[28, 5] = "CGPP Report";

workSheet.Cells[28, 6] = "Target";

workSheet.Cells[28, 7] = "Today";

workSheet.Cells[28, 8] = "Avg.(Todate)";

workSheet.get_Range("E28", "H28").Font.Bold = true;

workSheet.get_Range("F28", "H28").HorizontalAlignment = 3;

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetcgpp";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 29, c + 5] = ds.Tables[0].Rows[r][c].ToString();

}

////----------Process Steam Consumption---------

//workSheet.get_Range("E33", "H33").Merge(true);

workSheet.Cells[33, 5] = "Process Steam Consumption (TPH)";

workSheet.get_Range("E33", "F33").Merge();

workSheet.get_Range("E33", "H33").Font.Bold = true;

workSheet.Cells[33, 7] = "Today";

workSheet.Cells[33, 8] = "Avg.(Todate)";

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetprocesssteamconsumption";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 1)

break;

workSheet.Cells[r + 34, c + 5] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("E34", "F37").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 34, c + 7] = ds.Tables[0].Rows[r][1].ToString();

}

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 34, c + 8] = ds.Tables[0].Rows[r][2].ToString();

}

//workSheet.get_Range("I35:K32").Clear();

workSheet.Cells[38, 5] = "Total Process Steam (TPH)";

workSheet.Cells[38, 7] = "=SUM(G34:G37)";

workSheet.Cells[38, 8] = "=SUM(H34:H37)";

workSheet.get_Range("E38", "F38").Merge();

workSheet.get_Range("E38", "H38").Font.Bold = true;

////-------------- Receipts ----------

// workSheet.get_Range("E40", "H40").Merge(true);

workSheet.Cells[39, 5] = "Receipts";

workSheet.get_Range("E39", "F39").Merge();

workSheet.get_Range("E39", "H39").Font.Bold = true;

workSheet.Cells[39, 7] = "Today";

workSheet.Cells[39, 8] = "Todate";

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetreciept";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 1)

break;

workSheet.Cells[r + 40, c + 5] = ds.Tables[0].Rows[r][0].ToString();

}

//workSheet.get_Range("F35", "F41").Clear();

workSheet.get_Range("E40", "F46").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 2)

break;

workSheet.Cells[r + 40, c + 7] = ds.Tables[0].Rows[r][c+1].ToString();

}

//------------- Reason(s) For Loss Time

workSheet.Cells[47, 5] = "Reasons for Loss Times";

workSheet.get_Range("E47", "L47").Merge(true);

workSheet.get_Range("E47", "L47").Font.Bold = true;

workSheet.Cells[48, 5] = "LT";

workSheet.Cells[49, 5] = "HT";

workSheet.Cells[50, 5] = "CFBC";

workSheet.Cells[51, 5] = "Microfiner";

workSheet.get_Range("F48", "L51").Merge(true);

workSheet.Cells[51, 6] = "Feed Material Shortage.";

workSheet.get_Range("F48", "L51").HorizontalAlignment = 1;

//---------P & B Target---------

workSheet.Cells[2, 9] = "P & B Target for Std.Caln. (MT)";

workSheet.get_Range("I2", "K2").Merge(true);

workSheet.get_Range("I2", "K2").HorizontalAlignment = 3;

workSheet.get_Range("I2", "K2").VerticalAlignment = 2;

workSheet.get_Range("I2", "L2").Font.Bold = true;

workSheet.Cells[2, 12] = "27600";

workSheet.Cells[3, 9] = "Reviseed target for Std. Caln. (MT)";

workSheet.get_Range("I3", "K3").Merge(true);

workSheet.get_Range("I3", "K3").HorizontalAlignment = 3;

workSheet.get_Range("I3", "K3").VerticalAlignment = 2;

workSheet.get_Range("I3", "L3").Font.Bold = true;

workSheet.Cells[3, 12] = "27600";

//--------------- Operating Parameters -------------

workSheet.Cells[4, 9] = "Operating Parameters";

workSheet.Cells[4, 11] = "Today";

workSheet.get_Range("I4", "J4").Merge(true);

workSheet.get_Range("I4", "L4").Font.Bold = true;

workSheet.get_Range("I4", "L4").HorizontalAlignment = 3;

workSheet.get_Range("K4", "L4").Merge(true);

//--------------- Red Area 1--------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "sp_getoperatingparameters1";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[5, 9] = "Red Area";

workSheet.Cells[5, 10] = "Target";

workSheet.Cells[5, 11] = "Conc.";

workSheet.Cells[5, 12] = "Ratio";

workSheet.get_Range("I5", "L5").Font.Bold = true;

workSheet.get_Range("J5", "L5").HorizontalAlignment = 3;

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 6, c + 9] = ds.Tables[0].Rows[r][c].ToString();

}

workSheet.get_Range("J5", "L27").HorizontalAlignment = 3;

//-------------Red Area 2 --------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetoperatingparameters2";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 12, c + 9] = ds.Tables[0].Rows[r][c].ToString();

}

workSheet.get_Range("K12", "L27").Merge(true);

workSheet.get_Range("I13:J17").EntireRow.Font.Size = 16;

//----------------------DMS Running Hours---------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetdmsrunninghours";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[28, 9] = "DMS Running Hours";

workSheet.Cells[28, 11] = "Today";

workSheet.Cells[28, 12] = "Todate";

workSheet.get_Range("I28", "L28").Font.Bold = true;

workSheet.get_Range("K28", "L28").HorizontalAlignment = 3;

workSheet.get_Range("I28", "J28").Merge(true);

workSheet.get_Range("J29", "J32").Clear();

workSheet.get_Range("I29", "J32").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

workSheet.Cells[r + 29, c + 9] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("K29", "K32").Clear();

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 2)

break;

workSheet.Cells[r + 29, c + 11] = ds.Tables[0].Rows[r][c + 1].ToString();

}

workSheet.get_Range("K29", "L32").HorizontalAlignment = 3;

workSheet.get_Range("K29:K32").EntireRow.Font.Size = 16;

//----------- White Area -----------------

workSheet.Cells[33, 9] = "White Area";

workSheet.Cells[33, 11] = "Today";

workSheet.get_Range("I33", "J33").Merge(true);

workSheet.get_Range("K33", "L33").Merge(true);

workSheet.get_Range("K33", "L33").HorizontalAlignment = 3;

workSheet.get_Range("I33", "L33").Font.Bold = true;

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "sp_getwhitearea";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 1)

break;

workSheet.Cells[r + 34, c + 9] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("I34", "J37").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 1)

break;

workSheet.Cells[r + 34, c + 11] = ds.Tables[0].Rows[r][c+1].ToString();

}

workSheet.get_Range("K34", "L37").Merge(true);

workSheet.get_Range("K34", "L37").HorizontalAlignment=3;

//--------------- Dispatches -----------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetdispatch";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

workSheet.Cells[38, 9] = "Dispatches (MT)";

workSheet.Cells[38, 11] = "Today";

workSheet.Cells[38, 12] = "Todate";

workSheet.get_Range("I38", "J38").Merge(true);

workSheet.get_Range("I38", "L38").Font.Bold = true;

workSheet.get_Range("K38", "L46").HorizontalAlignment = 3;

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 1)

break;

workSheet.Cells[r + 39, c + 9] = ds.Tables[0].Rows[r][0].ToString();

}

workSheet.get_Range("J39", "J45").Clear();

workSheet.get_Range("I39", "J46").Merge(true);

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

if (c == 2)

break;

workSheet.Cells[r + 39, c + 11] = ds.Tables[0].Rows[r][c+1].ToString();

}

workSheet.Cells[46, 9] = "Total Dispatch";

workSheet.Cells[46, 11] = "=SUM(K39:K45)";

workSheet.Cells[46, 12] = "=SUM(L39:L45)";

workSheet.get_Range("I46", "L46").Font.Bold = true;

//---------- New Excel Sheet for graph data -----------

var xlSheets = excelApp.Sheets as Excel.Sheets;

var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);

xlNewSheet.Name = "graphdata";

// ------------- productiongraph --------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetproductiongraph2";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

//xlNewSheet.Cells[1, 1] = "Production Graph Data";

//xlNewSheet.get_Range("A1", "C1").Merge(true);

xlNewSheet.Cells[1, 1] = "Date";

xlNewSheet.Cells[1, 2] = "Hydrate";

xlNewSheet.Cells[1, 3] = "Calcination";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

xlNewSheet.Cells[r + 2, c + 1] = ds.Tables[0].Rows[r][c].ToString();

}

range3 = xlNewSheet.get_Range("A2").EntireColumn;

range3.NumberFormat = "dd-MMM;@";

rng4 = xlNewSheet.get_Range("A2", "C2").CurrentRegion;

var charts = workSheet.ChartObjects() as

Microsoft.Office.Interop.Excel.ChartObjects;

var chartObject = charts.Add(0, 1095, 240, 300) as

Microsoft.Office.Interop.Excel.ChartObject;

var chart = chartObject.Chart;

// Set chart range.

var range = rng4; // worksheet.get_Range(topLeft, bottomRight);

chart.SetSourceData(range);

// Set chart properties.

chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

chart.ChartWizard(Source: range,SeriesLabels:"=B2:C2",

Title: "Production, MT",

CategoryTitle:null,

ValueTitle: null);

var xlAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

xlAxis.HasMajorGridlines = false;

xlAxis.HasMinorGridlines = false;

chart.ChartArea.Border.Color = System.Drawing.Color.Black;

chart.ChartArea.Border.Weight = Excel.XlBorderWeight.xlThin;

//----------- Productivities gpl graph ------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetefficienciesgraph2";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

//xlNewSheet.Cells[1, 1] = "Production Graph Data";

//xlNewSheet.get_Range("A1", "C1").Merge(true);

xlNewSheet.Cells[1, 5] = "Date";

xlNewSheet.Cells[1, 6] = "Sp. Liq. Prod";

xlNewSheet.Cells[1, 7] = "Digester Prod";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

xlNewSheet.Cells[r + 2, c + 5] = ds.Tables[0].Rows[r][c].ToString();

}

range3 = xlNewSheet.get_Range("E2").EntireColumn;

range3.NumberFormat = "dd-MMM;@";

rng5 = xlNewSheet.get_Range("E2", "G2").CurrentRegion;

var charts2 = workSheet.ChartObjects() as

Microsoft.Office.Interop.Excel.ChartObjects;

var chartObject2 = charts2.Add(242, 1095, 335, 300) as

Microsoft.Office.Interop.Excel.ChartObject;

var chart2 = chartObject2.Chart;

// Set chart range.

var range2 = rng5; // worksheet.get_Range(topLeft, bottomRight);

chart2.SetSourceData(range2);

// Set chart properties.

chart2.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

chart2.ChartWizard(Source: range2, SeriesLabels: "=F2:G2",

Title: "Productivities, gpl",

CategoryTitle: null,

ValueTitle: null);

var xlAxis2 = (Excel.Axis)chart2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

xlAxis2.HasMajorGridlines = false;

xlAxis2.HasMinorGridlines = false;

chart2.ChartArea.Border.Color = System.Drawing.Color.Black;

chart2.ChartArea.Border.Weight = Excel.XlBorderWeight.xlThin;

//-------------- TAA & Silica------------------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetbauxitequalitygraph2";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

//xlNewSheet.Cells[1, 1] = "Production Graph Data";

//xlNewSheet.get_Range("A1", "C1").Merge(true);

xlNewSheet.Cells[1, 9] = "Date";

xlNewSheet.Cells[1, 10] = "TAA";

xlNewSheet.Cells[1, 11] = "Silica";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

xlNewSheet.Cells[r + 2, c + 9] = ds.Tables[0].Rows[r][c].ToString();

}

range3 = null;

range3 = xlNewSheet.get_Range("I2").EntireColumn;

range3.NumberFormat = "dd-MMM;@";

rng6 = xlNewSheet.get_Range("I2", "K2").CurrentRegion;

var charts3 = workSheet.ChartObjects() as

Microsoft.Office.Interop.Excel.ChartObjects;

var chartObject3 = charts3.Add(0, 1400, 240, 300) as

Microsoft.Office.Interop.Excel.ChartObject;

var chart3 = chartObject3.Chart;

// Set chart range.

//var rnge3 = rng6; // worksheet.get_Range(topLeft, bottomRight);

chart3.SetSourceData(rng6);

// Set chart properties.

chart3.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

chart3.ChartWizard(Source: rng6, SeriesLabels: "=J2:K2",

Title: "TAA & Silica, %",

CategoryTitle: null,

ValueTitle: null);

var xlAxis3 = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

xlAxis3.HasMajorGridlines = false;

xlAxis3.HasMinorGridlines = false;

chart3.ChartArea.Border.Color = System.Drawing.Color.Black;

chart3.ChartArea.Border.Weight = Excel.XlBorderWeight.xlThin;

//----------- Process Steam Consumption Graph -----------

ds = null; adt = null;

ObjCommand = new SqlCommand();

ObjCommand.CommandText = "spgetprocesssteamconsumptiongraph2";

ObjCommand.CommandType = CommandType.StoredProcedure;

ObjCommand.Connection = ObjConnection;

ObjCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate1.Text));

ds = new DataSet();

adt = new SqlDataAdapter();

adt.SelectCommand = ObjCommand;

adt.Fill(ds);

//xlNewSheet.Cells[1, 1] = "Production Graph Data";

//xlNewSheet.get_Range("A1", "C1").Merge(true);

xlNewSheet.Cells[1, 13] = "Date";

xlNewSheet.Cells[1, 14] = "Process Steam";

for (int r = 0; r < ds.Tables[0].Rows.Count; r++)

{

for (int c = 0; c < ds.Tables[0].Columns.Count; c++)

{

xlNewSheet.Cells[r + 2, c + 13] = ds.Tables[0].Rows[r][c].ToString();

}

Excel.Range range4 = xlNewSheet.get_Range("M2").EntireColumn;

range4.NumberFormat = "dd-MMM;@";

Excel.Range rng8 = xlNewSheet.get_Range("M2", "N2").CurrentRegion;

var charts4 = workSheet.ChartObjects() as

Microsoft.Office.Interop.Excel.ChartObjects;

var chartObject4 = charts4.Add(242, 1400, 335, 300) as

Microsoft.Office.Interop.Excel.ChartObject;

var chart4 = chartObject4.Chart;

// Set chart range.

//var range4 = rng5; // worksheet.get_Range(topLeft, bottomRight);

chart4.SetSourceData(rng8);

// Set chart properties.

chart4.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;

chart4.ChartWizard(Source: rng8, SeriesLabels: "=N2",

Title: "Process Steam Consumption, t/t",

CategoryTitle: null,

ValueTitle: null);

var xlAxis4 = (Excel.Axis)chart4.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

xlAxis4.HasMajorGridlines = false;

xlAxis4.HasMinorGridlines = false;

chart4.ChartArea.Border.Color = System.Drawing.Color.Black;

chart4.ChartArea.Border.Weight = Excel.XlBorderWeight.xlThin;

//--------- Saving Excel Sheet -----------;

//range3 = workSheet.get_Range("A1", "L51");

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle =

Excel.XlLineStyle.xlContinuous;

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle =

Excel.XlLineStyle.xlContinuous;

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle =

Excel.XlLineStyle.xlContinuous;

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle =

Excel.XlLineStyle.xlContinuous;

workSheet.get_Range("A1:L51").Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle =

Excel.XlLineStyle.xlContinuous;

rng7 = workSheet.get_Range("A2:L51");

rng7.RowHeight = 25;

rng7.VerticalAlignment = 2;

//range3.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);

workSheet.PageSetup.Zoom = false;

workSheet.PageSetup.FitToPagesWide = 1;

workSheet.PageSetup.FitToPagesTall = 1;

workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;

workSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;

workSheet.SaveAs(FilePath);

excelApp.Quit();

lblMsg.Text = "Report Generated Successfully!!!";

}
File is being generated on server but can not be downloaded on client machine. please help me to solve it.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Debasis Saha11y ago
You can use the below code by using datatable.

string strFileName = "DemoExcel.xls";
string attachment = "filename=" + strFileName + "";
Response.Clear();
Response.Buffer = true;
Response.ClearHeaders();
Response.ClearContent();
Response.Clear();
Response.AppendHeader("Content-Type", "vnd.ms-excel");
Response.AppendHeader("content-disposition", attachment);
string sTab = "";

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