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;

            }