Tuesday, August 6, 2013

Create a predefined format   dynamically for a Excel sheet & display the data by  fetching data from the MSSQL database. 


This coding is use to generate a Environmental  Impact  Assessment Report  for Leopold  Matrix.
Here  this report has predefine format and data are varies according to the available details for particular project.Assesment is generate using MSExel according to the client request.

Leopold Environmental Assement  format:

Sum of the main components total weighted averages should equals to 1.


















  •     First you have to import Excel Library to as project reference.

Ceate 3 objects called,
        Excel.Application oXL;   //Create an object for micrsoft Excel application.
        Excel._Workbook oWB;     //Create an object for set of Excel Sheets.
        Excel._Worksheet oSheet; //Create an object for 1 Excel sheet.

            oXL = new Excel.Application();
            oXL.Visible = true;

            //Get a new workbook.
            oWB = (Excel._Workbook)(oXL.Workbooks.Add());

            //Get a new worksheet.
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;


// ---- Header Row ---
You can customize the excel sheet by creating borders,          

  •  To customize cell value in excel sheet use

                      Cells[Row index ,Column index]
Ex-:
          oSheet.Cells[3, 1] = "Category";

  • To custmize a cell, directly pass cell name to

    range(cell name) function
Ex-:

            oSheet.get_Range("A3").ColumnWidth = 15;
            oSheet.get_Range("A3").Borders.Weight = 1;
            oSheet.get_Range("A3").Borders.LineStyle = 6;

  •   You can merge set  of cells,

To custmize a cell directly pass range of cell indexes to
          get_Range(From cell index,To cell index) function
EX-:
oSheet.get_Range(Start cell name, End cell name).Merge();                                              oSheet.get_Range("A3", "D3").Font.Bold = true;            oSheet.get_Range("A3", "D3").VerticalAlignment = VerticalAlignment.Center;




  •   Get the values to the list by calling the stored procedure  ”Get Leopold Activities


  Stored Procedure ”Get Leopold Activities”


ALTER proc [dbo].[Get_Leopold_Activities]
(
      @proj_reference as varchar (10)
)

AS
BEGIN

SELECT DISTINCT
        A.actv_reference
      , A.actv_name
      , A.actv_phase , A.actv_id   
FROM  LeopoldMatrixValues AS L
INNER JOIN Projects AS P ON L.proj_reference = P.proj_reference
INNER JOIN Impacts AS I ON L.impt_reference = I.impt_reference
INNER JOIN Components AS C ON I.impt_component = C.cmpt_reference
INNER JOIN Activities AS A ON L.actv_reference = A.actv_reference
WHERE P.proj_reference = @proj_reference

ORDER BY A.actv_phase , A.actv_id , A.actv_reference,A.actv_name 


END


  •          Store the values to the list by passing project reference to the stored procedure.


List<Get_Leopold_Activities_Result> lstActivities = ObjProjctsFunc.GetLeopoldActivities(labelProjrefrnceGenrated.Content.ToString());



  •          Retrieve the data inside the list to the excel Sheet.




            // --- MATRIX DATA ----
string startColName, endColName;
int startColNo, endColNo;


    foreach (Get_Leopold_Activities_Result oRstAct in lstActivities)
                {
                  endColNo = startColNo + 1;
                  endColName = GetColName(endColNo) + rowNo;

       






  •    Numeric values get from the MS SQL assign to the excel sheet.

var filter = (from i in lstMatrice
  where i.actv_reference == oRstAct.actv_reference && i.impt_reference ==             oRstImpacts.impt_reference
              select i).ToList();

               
    if (filter.Count() > 0)
                    {
                        oSheet.Cells[rowNo, z] = filter[0].mtrx_magnitude;
                        oSheet.Cells[rowNo + 1, z + 1] = filter[0].mtrx_importance;

   Total = Total + (int)(filter[0].mtrx_magnitude * filter[0].mtrx_importance);
                    }

          oSheet.get_Range(startColName, endColName).ColumnWidth = 2.5;

   DrawCellBorders(rowNo, z + 1, rowNo, z + 1, true, false, false, true, true);
   DrawCellBorders(rowNo + 1, z, rowNo + 1, z, false, true, true, false, true);
   DrawCellBorders(rowNo + 1, z + 1, rowNo + 1, z + 1, false, true, false, true, false);

                    startColNo = endColNo + 1;
                    startColName = GetColName(startColNo) + rowNo;

                    z = z + 2;
                }



  •   Calculate and assign the valus to the Excel cell

                oSheet.Cells[rowNo, z] = Total;
                endColName = GetColName(startColNo) + (rowNo + 1);
                oSheet.get_Range(startColName, endColName).Merge();


                GrandTotal = GrandTotal + Total;
                rowNo = rowNo + 2;
                LineNO = LineNO + 1;
            }
            oSheet.Cells[4, z] = "Total";
            oSheet.Cells[rowNo, z] = GrandTotal;

  •         Calculate the weighted average


            decimal WAGrandTotal = 0;
            decimal CatWiseWATotal = 0;
            int catStrLNo = 5;

            oSheet.Cells[4, z + 1] = "Weighted Average";

            for (int Lno = 5; Lno <= rowNo - 1; Lno++)
            {
                var cell = (Excel.Range)oSheet.Cells[Lno, z];

                if (Convert.ToString(cell.Value2) != null)
                {
                    if ((int)cell.Value2 != 0)
                    {
               oSheet.Cells[Lno, z + 1] =  (int)cell.Value2/(decimal)GrandTotal ;
               WAGrandTotal = WAGrandTotal +  (int)cell.Value2/(decimal)GrandTotal ;
                    }
                    else {
                        oSheet.Cells[Lno, z + 1] = 0;
                        WAGrandTotal = WAGrandTotal + 0;
                   
                         }

  •          Calculate the total weighted average

                    if ((int)cell.Value2 != 0)
                    {
            CatWiseWATotal = CatWiseWATotal + ( (int)cell.Value2/(decimal)GrandTotal );
                    }

                    else
                    {
                        CatWiseWATotal = CatWiseWATotal + 0;
                   
                    }
                    oSheet.Cells[catStrLNo, z + 2] = CatWiseWATotal;
                   
if (((Convert.ToString(((Excel.Range)oSheet.Cells[Lno + 2, 1]).Value2)) != null) && (Lno != 5))
                    {
                        catStrLNo = Lno + 2;
                        CatWiseWATotal = 0;
                    }
                }
            }
            oSheet.Cells[catStrLNo, z + 2] = CatWiseWATotal;
            oSheet.Cells[rowNo, z + 1] = WAGrandTotal;


            oXL.Visible = true;
            oXL.UserControl = true;

        }




  •            Draw cell borders.


private void DrawCellBorders(int frmrowNo, int frmColNo, int TorowNo, int ToColNo, bool top, bool bottom, bool left, bool right, bool diagup)
        {
            Excel.Borders b = null;
            Excel.Borders fb = null;

            b = (Excel.Borders)(oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;
            fb = (oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;


     if (diagup == true)
            {
 fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight;
            }
        


   if (top == true)
            {
fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight;
            }
   if (bottom == true)
            {
fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight;
            }
   if (left == true)
            {
fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight;
            }

   if (right == true)
            {
fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight;
            }




No comments:

Post a Comment