Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Friends...
i have created excel file using C# at server side using following code.
C#
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.
Posted
Comments
Praveen Kumar Upadhyay 12-Dec-14 1:14am    
What is this. Do you think that someone should able to read your code. It has more than 1000 lines of code. Please mention only related code.
TAUSEEF KALDANE 12-Dec-14 1:31am    
there are multiple datasets writing in excel file... nd then 4 datasets for graph.... file is saving at particular location on server using worksheet.SaveAs(FilePath). We want to make it downloadable to any machine.

1 solution

I assume that the above code is working.

To download the file, you have to assign the file to the Response.
Refer - How to download a generated excel file from your asp.net application[^]. You also have to convert file to Byte Array.
 
Share this answer
 
Comments
TAUSEEF KALDANE 12-Dec-14 2:16am    
Sir, Please tell me how to convert excel file to byte array?
TAUSEEF KALDANE 12-Dec-14 2:48am    
thank you sir, your solution has worked properly.
Great news. :) Most welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900