如何使文件excel文件在ASP.NET中可下载? [英] How to make file excel file as downloadable in ASP.NET?
本文介绍了如何使文件excel文件在ASP.NET中可下载?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello Friends ...
i使用以下代码在服务器端使用C#创建了excel文件。
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 si being generated on server but can not be downloaded on client machine. please help me to solve it.
File si being generated on server but can not be downloaded on client machine. please help me to solve it.
推荐答案
I assume that the above code is working.
To download the file, you have to assign the file to theResponse
.
Refer - How to download a generated excel file from your asp.net application[^]. You also have to convert file toByte
Array.
I assume that the above code is working.
To download the file, you have to assign the file to theResponse
.
Refer - How to download a generated excel file from your asp.net application[^]. You also have to convert file toByte
Array.
这篇关于如何使文件excel文件在ASP.NET中可下载?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文