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;
}
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();
}