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.