Esempio n. 1
0
        protected void ExportDataButton_Click(object sender, EventArgs e)
        {
            string fiscalYear = FinancialYearDropDownList.SelectedValue;

            StoredProcedures sp = new StoredProcedures();

            DataTable summaryTbl = sp.DisplayRevenueExpenseSummaryReport(FinancialYearDropDownList.SelectedValue);

            DataTable grossRevenueTbl = sp.DisplayGrossTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
            DataTable exportedRevenueTbl = sp.DisplayExportedTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
            DataTable netRevenueTbl = sp.DisplayNetTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
            DataSet differentialRevenueTbl = sp.DisplayTuitionDifferentialRevenue(FinancialYearDropDownList.SelectedValue);

            DataSet tuitionRevenueGrossUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Gross/Undergraduate");
            DataSet tuitionRevenueGrossGrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Gross/Graduate");
            DataSet tuitionRevenueExportedUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Exported/Undergraduate");
            DataSet tuitionRevenueExportedgrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Exported/Graduate");
            DataSet tuitionRevenueNetUndergrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Net/Undergraduate");
            DataSet tuitionRevenueNetGrad = sp.DisplayTuitionRevenueByCategory(FinancialYearDropDownList.SelectedValue, "Net/Graduate");

            DataSet assessments = sp.GetAssessmentsGenerateReport(FinancialYearDropDownList.SelectedValue);

            DataTable displayDirectExpenses = sp.DisplayDirectExpenses(FinancialYearDropDownList.SelectedValue);
            DataSet instarctionalCompensation = sp.DisplayCompensation(FinancialYearDropDownList.SelectedValue, "Instractional");
            DataSet noninstarctionalCompensation = sp.DisplayCompensation(FinancialYearDropDownList.SelectedValue, "NonInstractional");
            DataSet noncompensationExpenses = sp.DisplayNonCompensationExpenses(FinancialYearDropDownList.SelectedValue);
            DataSet miscellaneous = sp.GetMiscellaneousExpenses(FinancialYearDropDownList.SelectedValue);

            DataTable allocatedCostsSummary = sp.DisplayAllocatedCostsSummary(FinancialYearDropDownList.SelectedValue);

                string path = Server.MapPath("exportedfiles\\");

                if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                {
                    Directory.CreateDirectory(path);
                }

                File.Delete(path + fiscalYear + "RCMModel.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.

            if (summaryTbl.Rows.Count > 0)
            {
                // ADD A WORKBOOK USING THE EXCEL APPLICATION.
                Excel2.Application workbook = new Excel2.Application();
                workbook.Workbooks.Add("");

                // ADD A WORKSHEET.
                Excel2.Worksheet sheet = default(Excel2.Worksheet);
                sheet = (Excel2.Worksheet)workbook.Sheets["Sheet1"];
                sheet.Name = "Summary Report";

                // ROW ID FROM WHERE THE DATA STARTS SHOWING.
                int iRowCnt = 5;

                // SHOW THE HEADER.
                sheet.Cells[1, 1] = "RCM Model " + fiscalYear;
                Excel2.Range range = sheet.Cells[1, 1] as Excel2.Range;
                range.EntireRow.Font.Bold = true;
                range.EntireRow.Font.Size = 20;

                sheet.Cells[2, 1] = "Summary Report";
                Excel2.Range range2 = sheet.Cells[2, 1] as Excel2.Range;
                range2.EntireRow.Font.Bold = true;
                range2.EntireRow.Font.Size = 18;

                sheet.Range["A1:B1"].MergeCells = true;       // MERGE CELLS OF THE HEADER.
                sheet.Range["A2:B2"].MergeCells = true;
                // SHOW COLUMNS ON THE TOP.
                sheet.Cells[iRowCnt - 1, 1] = "Revenue / Expense";
                sheet.Cells[iRowCnt - 1, 2] = "Amount";

                for (int i = 0; i <= summaryTbl.Rows.Count - 1; i++)
                {
                    sheet.Cells[iRowCnt, 1] = summaryTbl.Rows[i][2];
                    sheet.Cells[iRowCnt, 2] = summaryTbl.Rows[i][1];

                    iRowCnt = iRowCnt + 1;
                }

                Excel2.Worksheet sheet2;
                sheet2 = (Excel2.Worksheet)workbook.Worksheets.Add();
                sheet2.Name = "Revenue";

                sheet2.Cells[1, 1] = "RCM Model " + fiscalYear;
                Excel2.Range range4 = sheet2.Cells[1, 1] as Excel2.Range;
                range4.EntireRow.Font.Bold = true;
                range4.EntireRow.Font.Size = 20;

                sheet2.Cells[2, 1] = "Revenue Report";
                Excel2.Range range5 = sheet2.Cells[2, 1] as Excel2.Range;
                range5.EntireRow.Font.Bold = true;
                range5.EntireRow.Font.Size = 18;

                sheet2.Range["A1:E1"].MergeCells = true;
                sheet2.Range["A2:E2"].MergeCells = true;

                iRowCnt = 6;

                sheet2.Cells[iRowCnt - 2, 1] = "Gross Revenue";
                sheet2.Cells[iRowCnt - 1, 1] = "Category";
                sheet2.Cells[iRowCnt - 1, 2] = "Amount";

                for (int i = 0; i <= grossRevenueTbl.Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = grossRevenueTbl.Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = grossRevenueTbl.Rows[i][1];

                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = "Exported Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Category";
                sheet2.Cells[iRowCnt + 2, 2] = "Amount";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= exportedRevenueTbl.Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = exportedRevenueTbl.Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = exportedRevenueTbl.Rows[i][1];

                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = "Net Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Category";
                sheet2.Cells[iRowCnt + 2, 2] = "Amount";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= netRevenueTbl.Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = netRevenueTbl.Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = netRevenueTbl.Rows[i][1];

                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = "Differential Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Category";
                sheet2.Cells[iRowCnt + 2, 2] = "Students/CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= differentialRevenueTbl.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = differentialRevenueTbl.Tables[0].Rows[i][4];
                    sheet2.Cells[iRowCnt, 2] = differentialRevenueTbl.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = differentialRevenueTbl.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = differentialRevenueTbl.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = "Gross Undergraduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueGrossUndergrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueGrossUndergrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = "Gross Graduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueGrossGrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueGrossGrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueGrossGrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueGrossGrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueGrossGrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = " Exported Undergraduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueExportedUndergrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueExportedUndergrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = " Exported Graduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueExportedgrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueExportedgrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueExportedgrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueExportedgrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueExportedgrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = " Net Undergraduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueNetUndergrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueNetUndergrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                sheet2.Cells[iRowCnt + 1, 1] = " Net Graduate Tuition Revenue";
                sheet2.Cells[iRowCnt + 2, 1] = "Term";
                sheet2.Cells[iRowCnt + 2, 2] = "CHG";
                sheet2.Cells[iRowCnt + 2, 3] = "Rate";
                sheet2.Cells[iRowCnt + 2, 4] = "Total";

                iRowCnt = iRowCnt + 3;

                for (int i = 0; i <= tuitionRevenueNetGrad.Tables[0].Rows.Count - 1; i++)
                {
                    sheet2.Cells[iRowCnt, 1] = tuitionRevenueNetGrad.Tables[0].Rows[i][0];
                    sheet2.Cells[iRowCnt, 2] = tuitionRevenueNetGrad.Tables[0].Rows[i][1];
                    sheet2.Cells[iRowCnt, 3] = tuitionRevenueNetGrad.Tables[0].Rows[i][2];
                    sheet2.Cells[iRowCnt, 4] = tuitionRevenueNetGrad.Tables[0].Rows[i][3];
                    iRowCnt = iRowCnt + 1;
                }

                Excel2.Worksheet sheet3;
                sheet3 = (Excel2.Worksheet)workbook.Worksheets.Add();
                sheet3.Name = "Assessments";

                sheet3.Cells[1, 1] = "RCM Model " + fiscalYear;
                Excel2.Range range17 = sheet3.Cells[1, 1] as Excel2.Range;
                range17.EntireRow.Font.Bold = true;
                range17.EntireRow.Font.Size = 20;

                sheet3.Cells[2, 1] = "Assessments Report";
                Excel2.Range range18 = sheet3.Cells[2, 1] as Excel2.Range;
                range18.EntireRow.Font.Bold = true;
                range18.EntireRow.Font.Size = 18;

                sheet3.Range["A1:B1"].MergeCells = true;
                sheet3.Range["A2:B2"].MergeCells = true;

                iRowCnt = 5;

                sheet3.Cells[iRowCnt - 1, 1] = "Assessments";
                sheet3.Cells[iRowCnt - 1, 2] = "Undergarduate Rate";
                sheet3.Cells[iRowCnt - 1, 3] = "Undergraduate Amount";
                sheet3.Cells[iRowCnt - 1, 4] = "Graduate Rate";
                sheet3.Cells[iRowCnt - 1, 5] = "Garduate Amount";

                for (int i = 0; i <= assessments.Tables[0].Rows.Count - 1; i++)
                {
                    sheet3.Cells[iRowCnt, 1] = assessments.Tables[0].Rows[i][0];
                    sheet3.Cells[iRowCnt, 2] = assessments.Tables[0].Rows[i][1];
                    sheet3.Cells[iRowCnt, 3] = assessments.Tables[0].Rows[i][2];
                    sheet3.Cells[iRowCnt, 4] = assessments.Tables[0].Rows[i][3];
                    sheet3.Cells[iRowCnt, 5] = assessments.Tables[0].Rows[i][4];

                    iRowCnt = iRowCnt + 1;
                }

                Excel2.Worksheet sheet4;
                sheet4 = (Excel2.Worksheet)workbook.Worksheets.Add();
                sheet4.Name = "Direct Expenses";

                sheet4.Cells[1, 1] = "RCM Model " + fiscalYear;
                Excel2.Range range20 = sheet4.Cells[1, 1] as Excel2.Range;
                range20.EntireRow.Font.Bold = true;
                range20.EntireRow.Font.Size = 20;

                sheet4.Cells[2, 1] = "Direct Expenses Report";
                Excel2.Range range21 = sheet4.Cells[2, 1] as Excel2.Range;
                range21.EntireRow.Font.Bold = true;
                range21.EntireRow.Font.Size = 18;

                sheet4.Range["A1:B1"].MergeCells = true;
                sheet4.Range["A2:B2"].MergeCells = true;

                iRowCnt = 5;

                sheet4.Cells[iRowCnt - 1, 1] = "Expense Type";
                sheet4.Cells[iRowCnt - 1, 2] = "Amount";

                for (int i = 0; i <= displayDirectExpenses.Rows.Count - 1; i++)
                {
                    sheet4.Cells[iRowCnt, 1] = displayDirectExpenses.Rows[i][0];
                    sheet4.Cells[iRowCnt, 2] = displayDirectExpenses.Rows[i][1];

                    iRowCnt = iRowCnt + 1;
                }

                sheet4.Cells[iRowCnt + 1, 1] = "Instractional Compensation Expenses";
                sheet4.Cells[iRowCnt + 2, 1] = "Expense";
                sheet4.Cells[iRowCnt + 2, 2] = "Budget";
                sheet4.Cells[iRowCnt + 2, 3] = "Fringe Benefit Rate";
                sheet4.Cells[iRowCnt + 2, 4] = "Benefit";

                iRowCnt = iRowCnt + 3;
                int instracCompStartRow = iRowCnt;

                for (int i = 0; i <= instarctionalCompensation.Tables[0].Rows.Count - 1; i++)
                {
                    sheet4.Cells[iRowCnt, 1] = instarctionalCompensation.Tables[0].Rows[i][1];
                    sheet4.Cells[iRowCnt, 2] = instarctionalCompensation.Tables[0].Rows[i][3];
                    sheet4.Cells[iRowCnt, 3] = instarctionalCompensation.Tables[0].Rows[i][4];
                    sheet4.Cells[iRowCnt, 4] = instarctionalCompensation.Tables[0].Rows[i][5];
                    iRowCnt = iRowCnt + 1;
                }

                sheet4.Cells[iRowCnt + 1, 1] = "Non-Instractional Compensation Expenses";
                sheet4.Cells[iRowCnt + 2, 1] = "Expense";
                sheet4.Cells[iRowCnt + 2, 2] = "Budget";
                sheet4.Cells[iRowCnt + 2, 3] = "Fringe Benefit Rate";
                sheet4.Cells[iRowCnt + 2, 4] = "Benefit";

                iRowCnt = iRowCnt + 3;
                int noninstracCompStartRow = iRowCnt;

                for (int i = 0; i <= noninstarctionalCompensation.Tables[0].Rows.Count - 1; i++)
                {
                    sheet4.Cells[iRowCnt, 1] = noninstarctionalCompensation.Tables[0].Rows[i][1];
                    sheet4.Cells[iRowCnt, 2] = noninstarctionalCompensation.Tables[0].Rows[i][3];
                    sheet4.Cells[iRowCnt, 3] = noninstarctionalCompensation.Tables[0].Rows[i][4];
                    sheet4.Cells[iRowCnt, 4] = noninstarctionalCompensation.Tables[0].Rows[i][5];
                    iRowCnt = iRowCnt + 1;
                }

                sheet4.Cells[iRowCnt + 1, 1] = "Non-Compensation Expenses";
                sheet4.Cells[iRowCnt + 2, 1] = "Expense";
                sheet4.Cells[iRowCnt + 2, 2] = "Budget";

                iRowCnt = iRowCnt + 3;
                int nonCompStartRow = iRowCnt;

                for (int i = 0; i <= noncompensationExpenses.Tables[0].Rows.Count - 1; i++)
                {
                    sheet4.Cells[iRowCnt, 1] = noncompensationExpenses.Tables[0].Rows[i][1];
                    sheet4.Cells[iRowCnt, 2] = noncompensationExpenses.Tables[0].Rows[i][3];

                    iRowCnt = iRowCnt + 1;
                }

                sheet4.Cells[iRowCnt + 1, 1] = "Miscellaneous";
                sheet4.Cells[iRowCnt + 2, 1] = "Expense";
                sheet4.Cells[iRowCnt + 2, 2] = "Account";
                sheet4.Cells[iRowCnt + 2, 3] = "Budget";

                iRowCnt = iRowCnt + 3;
                int miscellaneousStartRow = iRowCnt;

                for (int i = 0; i <= miscellaneous.Tables[0].Rows.Count - 1; i++)
                {
                    sheet4.Cells[iRowCnt, 1] = miscellaneous.Tables[0].Rows[i][1];
                    sheet4.Cells[iRowCnt, 2] = miscellaneous.Tables[0].Rows[i][2];
                    sheet4.Cells[iRowCnt, 3] = miscellaneous.Tables[0].Rows[i][3];

                    iRowCnt = iRowCnt + 1;
                }

                Excel2.Worksheet sheet5;
                sheet5 = (Excel2.Worksheet)workbook.Worksheets.Add();
                sheet5.Name = "Allocated Costs";

                sheet5.Cells[1, 1] = "RCM Model " + fiscalYear;
                Excel2.Range range27 = sheet5.Cells[1, 1] as Excel2.Range;
                range27.EntireRow.Font.Bold = true;
                range27.EntireRow.Font.Size = 20;

                sheet5.Cells[2, 1] = "Allocated Costs Report";
                Excel2.Range range28 = sheet5.Cells[2, 1] as Excel2.Range;
                range28.EntireRow.Font.Bold = true;
                range28.EntireRow.Font.Size = 18;

                sheet5.Range["A1:B1"].MergeCells = true;
                sheet5.Range["A2:B2"].MergeCells = true;

                iRowCnt = 5;

                sheet5.Cells[iRowCnt - 1, 1] = "Allocated Cost";
                sheet5.Cells[iRowCnt - 1, 2] = "Total";

                for (int i = 0; i <= allocatedCostsSummary.Rows.Count - 1; i++)
                {
                    sheet5.Cells[iRowCnt, 1] = allocatedCostsSummary.Rows[i][0];
                    sheet5.Cells[iRowCnt, 2] = allocatedCostsSummary.Rows[i]["Amount"];

                    iRowCnt = iRowCnt + 1;
                }

                // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                Excel2.Range range1 = sheet.Cells[4, 1] as Excel2.Range;
                range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range6 = sheet2.Cells[5, 1] as Excel2.Range;
                range6.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range8 = sheet2.Cells[10, 1] as Excel2.Range;
                range8.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range9 = sheet2.Cells[16, 1] as Excel2.Range;
                range9.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range10 = sheet2.Cells[22, 1] as Excel2.Range;
                range10.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range11 = sheet2.Cells[27, 1] as Excel2.Range;
                range11.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range12 = sheet2.Cells[34, 1] as Excel2.Range;
                range12.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range13 = sheet2.Cells[41, 1] as Excel2.Range;
                range13.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range14 = sheet2.Cells[48, 1] as Excel2.Range;
                range14.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range15 = sheet2.Cells[55, 1] as Excel2.Range;
                range15.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range16 = sheet2.Cells[62, 1] as Excel2.Range;
                range16.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
                range16.ColumnWidth = 35;

                Excel2.Range range16a = sheet2.Cells[62, 2] as Excel2.Range;
                range16a.ColumnWidth = 15;

                Excel2.Range range19 = sheet3.Cells[5, 1] as Excel2.Range;
                range19.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range22 = sheet4.Cells[5, 1] as Excel2.Range;
                range22.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range23 = sheet4.Cells[instracCompStartRow, 1] as Excel2.Range;
                range23.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range24 = sheet4.Cells[noninstracCompStartRow, 1] as Excel2.Range;
                range24.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range25 = sheet4.Cells[nonCompStartRow, 1] as Excel2.Range;
                range25.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);

                Excel2.Range range26 = sheet4.Cells[miscellaneousStartRow, 1] as Excel2.Range;
                range26.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
                range26.ColumnWidth = 35;

                Excel2.Range range26a = sheet4.Cells[miscellaneousStartRow, 2] as Excel2.Range;
                range26a.ColumnWidth = 15;

                Excel2.Range range29 = sheet5.Cells[5, 1] as Excel2.Range;
                range29.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatReport1);
                range29.ColumnWidth = 35;

                // SAVE THE FILE IN A FOLDER.
                sheet.SaveAs(path + fiscalYear + "RCMModel.xlsx");

                // CLEAR.
                workbook.Workbooks.Close();
                workbook.Quit();
                workbook = null;
                sheet = null;
                sheet2 = null;
                sheet3 = null;
                sheet4 = null;
                sheet5 = null;
            }

            DownLoadFile();

            int a = 0;
        }
Esempio n. 2
0
        protected void DisplayTuitionRevenueBreakdown(String category)
        {
            StoredProcedures sp = new StoredProcedures();
            DataTable tbl = null;

            if (category == "Gross")
            {
                tbl = sp.DisplayGrossTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
                TuitionBreakdownByLevelLabel.Text = "Gross Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;

                Session["chkRevenueTbl"] = 1;
            }
            else if (category == "Exported")
            {
                tbl = sp.DisplayExportedTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
                TuitionBreakdownByLevelLabel.Text = "Exported Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;

                Session["chkRevenueTbl"] = 2;

            }
            else if (category == "Net")
            {
                tbl = sp.DisplayNetTuitionRevenueBreakDown(FinancialYearDropDownList.SelectedValue);
                TuitionBreakdownByLevelLabel.Text = "Net Revenue Breakdown by Level " + FinancialYearDropDownList.SelectedValue;

                Session["chkRevenueTbl"] = 3;

            }

            TuitionBreakdownByLevelPanel.Visible = true;

            TuitionBreakdownByLevelGridView.DataSource = tbl;
            TuitionBreakdownByLevelGridView.DataBind();
        }