Gecko [ nrupathungauniversityblr.ac.in ]

Name	Size	Permission
.pkexec	[ DIR ]	drwxr-xr-x
GCONV_PATH=.	[ DIR ]	drwxr-xr-x
fpdf	[ DIR ]	drwxrwxrwx
phpexcel	[ DIR ]	drwxrwxrwx
phpmailer	[ DIR ]	drwxrwxrwx
reports	[ DIR ]	drwxrwxrwx
.mad-root	0 B	-rw-r--r--
api_sendmail.php	2.22 KB	-rwxrwxrwx
api_sendmail1.php	2.03 KB	-rwxrwxrwx
api_sendsms.php	1.08 KB	-rwxrwxrwx
attendance.php	13.66 KB	-rwxrwxrwx
attendance_report.php	40.23 KB	-rwxrwxrwx
attendance_report_attn_stat_mn...	16.79 KB	-rw-rw-r--
attendance_report_attn_stat_sa...	18.77 KB	-rw-rw-r--
attendance_report_datewise.php	5.59 KB	-rwxrwxrwx
attendance_report_excel.php	14.66 KB	-rwxrwxrwx
attendance_report_month_detail...	2.76 KB	-rwxrwxrwx
attendance_report_month_summar...	4.01 KB	-rwxrwxrwx
attendance_report_monthwise_in...	6.85 KB	-rwxrwxrwx
attendance_report_monthwise_su...	5.09 KB	-rwxrwxrwx
attendance_report_monthwise_su...	4.13 KB	-rwxrwxrwx
attendance_report_yearwise_sum...	4.56 KB	-rwxrwxrwx
attendance_statement_2023-03.x...	11.39 KB	-rw-r--r--
attendance_statement_2024-01.x...	60.03 KB	-rw-r--r--
attendance_statement_2024-02.x...	48.48 KB	-rw-r--r--
attendance_statement_2024-03.x...	10.73 KB	-rw-r--r--
attendance_statement_2024-04.x...	10.56 KB	-rw-r--r--
attendance_statement_2024-05.x...	10.63 KB	-rw-r--r--
attendance_statement_2024-06.x...	10.63 KB	-rw-r--r--
attendance_statement_2024-07.x...	47.43 KB	-rw-r--r--
attendance_statement_2024-08.x...	11.54 KB	-rw-r--r--
attendance_statement_2024-09.x...	43.78 KB	-rw-r--r--
attendance_statement_2024-10.x...	11.15 KB	-rw-r--r--
attendance_statement_2024-11.x...	43.35 KB	-rw-r--r--
attendance_statement_2024-12.x...	9.32 KB	-rw-r--r--
attendance_statement_2025-01.x...	33.21 KB	-rw-r--r--
attendance_statement_salary_.x...	7.04 KB	-rw-r--r--
attendance_statement_salary_20...	7.06 KB	-rw-r--r--
attendance_statement_salary_20...	7.36 KB	-rw-r--r--
attendance_statement_salary_20...	19.89 KB	-rw-r--r--
attendance_statement_salary_20...	18.32 KB	-rw-r--r--
attendance_statement_salary_20...	18.06 KB	-rw-r--r--
attendance_statement_salary_20...	17.78 KB	-rw-r--r--
attendance_statement_salary_20...	17.63 KB	-rw-r--r--
attendance_statement_salary_20...	7.72 KB	-rw-r--r--
attendance_statement_salary_20...	7.67 KB	-rw-r--r--
attendance_statement_salary_20...	7.72 KB	-rw-r--r--
attendance_statement_salary_20...	7.52 KB	-rw-r--r--
attendance_statement_salary_20...	7.06 KB	-rw-r--r--
attendance_statement_salary_20...	16.64 KB	-rw-r--r--
attendance_statement_salary_20...	7.54 KB	-rw-r--r--
attendance_status.php	20.13 KB	-rwxrwxrwx
attendance_status_deepak.php	9.38 KB	-rw-rw-r--
attendance_status_ihf.php	9.38 KB	-rwxrwxrwx
backup_data.php	903 B	-rwxrwxrwx
control.php	5.78 KB	-rwxrwxrwx
departments.php	8.79 KB	-rwxrwxrwx
departments_logo_upload.php	679 B	-rwxrwxrwx
documents.php	4.16 KB	-rwxrwxrwx
employees.php	17.28 KB	-rwxrwxrwx
employees2.php	14.04 KB	-rw-rw-r--
employees_form.php	20.19 KB	-rw-rw-r--
employees_photo_upload.php	647 B	-rwxrwxrwx
employees_print_id_card.php	2.76 KB	-rw-rw-r--
employees_print_nda_form.php	12.86 KB	-rw-rw-r--
employees_report_excel.php	5.07 KB	-rw-rw-r--
expense.php	10.79 KB	-rwxrwxrwx
expense_advance.php	4.69 KB	-rwxrwxrwx
expense_advance2.php	4.55 KB	-rw-rw-r--
expense_report.php	3.26 KB	-rwxrwxrwx
expense_report_daywise_details...	4.46 KB	-rwxrwxrwx
expense_report_daywise_summary...	5.27 KB	-rwxrwxrwx
expense_report_details_date.ph...	5.32 KB	-rwxrwxrwx
expense_report_details_date_su...	5.43 KB	-rwxrwxrwx
expense_report_details_head.ph...	22.11 KB	-rwxrwxrwx
expense_report_details_head_su...	22.13 KB	-rwxrwxrwx
expense_report_headwise_detail...	18.32 KB	-rwxrwxrwx
expense_report_headwise_summar...	18.32 KB	-rwxrwxrwx
expense_report_monthwise_detai...	3.3 KB	-rwxrwxrwx
expense_report_monthwise_summa...	3.3 KB	-rwxrwxrwx
expense_report_statement.php	5.69 KB	-rwxrwxrwx
expense_report_statement_date....	6.15 KB	-rwxrwxrwx
expense_report_statement_excel...	19.74 KB	-rw-rw-r--
expense_report_statement_excel...	32.6 KB	-rwxrwxrwx
expense_report_statement_excel...	32.79 KB	-rw-rw-r--
expense_report_statement_mnth....	4.88 KB	-rwxrwxrwx
expense_report_statement_year....	4.86 KB	-rwxrwxrwx
expense_report_summary.php	18.43 KB	-rwxrwxrwx
expense_statement_monthly_.xls...	6.04 KB	-rw-r--r--
expense_statement_monthly_2023...	10.08 KB	-rw-r--r--
expense_statement_monthly_2024...	10 KB	-rw-r--r--
expense_statement_monthly_2024...	8.99 KB	-rw-r--r--
expense_statement_monthly_2024...	9.16 KB	-rw-r--r--
expense_statement_monthly_2024...	8.87 KB	-rw-r--r--
expense_statement_monthly_2024...	30.05 KB	-rw-r--r--
expense_statement_monthly_2024...	9 KB	-rw-r--r--
expense_statement_monthly_2024...	9.61 KB	-rw-r--r--
expense_statement_monthly_2024...	11.03 KB	-rw-r--r--
expense_statement_monthly_2024...	10.92 KB	-rw-r--r--
expense_statement_monthly_2024...	9.65 KB	-rw-r--r--
expense_statement_monthly_2024...	9.66 KB	-rw-r--r--
expense_statement_monthly_2024...	21.38 KB	-rw-r--r--
grievance_status.php	5.44 KB	-rw-rw-r--
header_menu.php	3.46 KB	-rwxrwxrwx
header_note.php	5.06 KB	-rwxrwxrwx
holidays.php	6.19 KB	-rwxrwxrwx
home_appraisals.php	185 B	-rwxrwxrwx
home_attendance.php	26.81 KB	-rwxrwxrwx
home_clients.php	10.62 KB	-rwxrwxrwx
home_dashboard.php	236 B	-rwxrwxrwx
home_employees.php	9.47 KB	-rwxrwxrwx
home_events.php	9.71 KB	-rwxrwxrwx
home_expenses.php	27.27 KB	-rwxrwxrwx
home_flash.php	2.38 KB	-rwxrwxrwx
home_inventory.php	3.14 KB	-rwxrwxrwx
home_movements.php	9.03 KB	-rwxrwxrwx
home_note_count.php	6.75 KB	-rw-rw-r--
home_salary.php	12.81 KB	-rwxrwxrwx
home_task.php	6.15 KB	-rwxrwxrwx
home_userlogs.php	2.02 KB	-rw-rw-r--
home_work_entry.php	15.08 KB	-rwxrwxrwx
home_work_flash.php	2.36 KB	-rwxrwxrwx
home_work_view.php	11.96 KB	-rwxrwxrwx
login.php	3.3 KB	-rwxrwxrwx
logout.php	261 B	-rwxrwxrwx
mc_print.php	9.54 KB	-rwxrwxrwx
mcf_status.php	2.87 KB	-rw-rw-r--
office_logisys3_oums_2024-09-2...	20.57 MB	-rw-r--r--
oums_sms_push.php	18.18 KB	-rwxrwxrwx
oums_sms_send.php	5.39 KB	-rwxrwxrwx
pg_app_edit.php	5.17 KB	-rwxrwxrwx
popup_empl_events.php	3.53 KB	-rw-rw-r--
popup_empl_salary.php	2.67 KB	-rw-rw-r--
popup_work_entry.php	3.14 KB	-rw-rw-r--
popup_work_review.php	1.55 KB	-rw-rw-r--
pwnkit	10.99 KB	-rwxr-xr-x
salary.php	24.44 KB	-rwxrwxrwx
salary_statement.php	40.09 KB	-rw-rw-r--
salary_statement_2024-05.xlsx	18.57 KB	-rw-r--r--
salary_statement_2024-06.xlsx	18.43 KB	-rw-r--r--
salary_statement_2024-07.xlsx	17.89 KB	-rw-r--r--
salary_statement_2024-08.xlsx	17.46 KB	-rw-r--r--
salary_statement_2024-09.xlsx	16.97 KB	-rw-r--r--
salary_statement_2024-10.xlsx	16.94 KB	-rw-r--r--
salary_statement_2024-11.xlsx	17.36 KB	-rw-r--r--
salary_statement_2024-12.xlsx	17.58 KB	-rw-r--r--
salary_statement_month_excel.p...	21.36 KB	-rw-rw-r--
send_mail.php	383 B	-rwxrwxrwx
send_sms.php	203 B	-rwxrwxrwx
sendsmsapi.php	759 B	-rwxrwxrwx
staff_move.php	8.55 KB	-rwxrwxrwx
status_report.php	23.7 KB	-rwxrwxrwx
status_report_03022018.php	14.76 KB	-rwxrwxrwx
status_report_import_attendanc...	3.27 KB	-rwxrwxrwx
sys_connect.php	492 B	-rwxrwxrwx
sys_connect_old.php	123 B	-rwxrwxrwx
sys_connect_pget.php	311 B	-rwxrwxrwx
sys_employees.php	2.55 KB	-rwxrwxrwx
sys_grid_data.php	805 B	-rwxrwxrwx
sys_grid_navbar.php	1.72 KB	-rwxrwxrwx
sys_mainphp.php	6.92 KB	-rwxrwxrwx
sys_mode.php	780 B	-rwxrwxrwx
sys_module.php	580 B	-rwxrwxrwx
sys_modules.php	2.47 KB	-rwxrwxrwx
sys_session.php	1.04 KB	-rwxrwxrwx
task_reminder.php	16.44 KB	-rwxrwxrwx
task_review.php	11.37 KB	-rwxrwxrwx
upcoming_events.php	8.79 KB	-rw-rw-r--
user_departments.php	4.09 KB	-rwxrwxrwx
user_logs.php	5.8 KB	-rwxrwxrwx
user_password.php	3.67 KB	-rwxrwxrwx
user_password_request.php	2.09 KB	-rwxrwxrwx
user_rights.php	7.34 KB	-rwxrwxrwx
user_setup.php	10.79 KB	-rwxrwxrwx
user_validate.php	3.07 KB	-rwxrwxrwx
work_assign.php	5.86 KB	-rw-rw-r--
work_done_report.xlsx	7.91 KB	-rw-r--r--
work_done_report_empl.xlsx	6.57 KB	-rw-r--r--
work_entry.php	9.04 KB	-rwxrwxrwx
work_entry_org.php	3.48 KB	-rwxrwxrwx
work_entry_report.php	2.71 KB	-rwxrwxrwx
work_entry_report_appraisal.ph...	8.79 KB	-rw-rw-r--
work_entry_report_assn_work_ex...	8.79 KB	-rw-rw-r--
work_entry_report_assn_work_vi...	9.03 KB	-rw-rw-r--
work_entry_report_statement.ph...	4.63 KB	-rwxrwxrwx
work_entry_report_work_done_as...	9.34 KB	-rw-rw-r--
work_entry_report_work_done_as...	9.46 KB	-rw-rw-r--
work_entry_report_work_done_em...	8.91 KB	-rw-rw-r--
work_entry_report_work_done_em...	8.91 KB	-rw-rw-r--
work_entry_report_work_done_ex...	8.79 KB	-rw-rw-r--
work_entry_report_work_done_li...	9.43 KB	-rw-rw-r--
work_entry_report_work_done_li...	8.91 KB	-rw-rw-r--
work_entry_report_work_done_st...	4.65 KB	-rw-rw-r--
work_review.php	6.08 KB	-rwxrwxrwx
workentry_report.php	9.43 KB	-rw-rw-r--
xattendance_report_01.php	7.77 KB	-rwxrwxrwx
xattendance_report_02.php	4.49 KB	-rwxrwxrwx
xattendance_report_03.php	6 KB	-rwxrwxrwx
xattendance_report_04.php	5.18 KB	-rwxrwxrwx
xattendance_report_05.php	4.84 KB	-rwxrwxrwx
xget_master_info.php	801 B	-rwxrwxrwx

Code Editor : expense_report_statement_excel - Copy.php

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

include("sys_session.php");
include("sys_connect.php");
include("sys_mainphp.php");
$resp_mesg = "";
$resp_stat = "";
$resp_file = "";

require_once("phpexcel/Classes/PHPExcel.php");
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Logisys")
    ->setLastModifiedBy("Logisys")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Report File");

$mysql = "SET SESSION group_concat_max_len = 1000000;";
$myres = mysqli_query($mycon, $mysql);
$myqry = "";
if ($type_id != "ADMIN" || $type_id != "CHIEF") {
    $myqry = " and ex.fdeptcode like '$_POST[dept_code]' ";
} else {
    $myqry = " and ex.femplcode like '$empl_id' ";
}

$mysql = "select 
date_format(fexpndate,'%Y-%m') as fexpnmnth, 
date_format(fexpndate,'%M %Y') as fdispmnth, 
ex.fdeptcode,dp.fdeptname,dp.fdeptshrt,dp.fdepthead,femplname,
date_format(ex.fupdttime,'%d/%m/%Y') as fsystdate,
date_format(ex.fexpndate,'%d/%m/%Y') as fexpndate,
hd.fheadtype,ex.fheaddesc,fexpnamnt,fexpnremk,fclntcode,ex.facnttype
from expenses ex
left join masdept dp on ex.fdeptcode = dp.fdeptcode
left join mashead hd on ex.fheaddesc = hd.fheaddesc and ex.facnttype = hd.facnttype
left join masempl em on dp.fdepthead = em.femplcode 
where date_format(fexpndate,'%Y-%m') = '$_POST[expn_mnth]' " . $myqry . "
order by ex.fdeptcode,fexpndate,fheaddesc";
$myres = mysqli_query($mycon, $mysql);
$i = 1;
$dept_code = "";
$ShtNo = 0;
while ($myrow = mysqli_fetch_object($myres)) {
    if ($dept_code != $myrow->fdeptcode) {
        $expn_mnth = $myrow->fexpnmnth;
        $disp_mnth = $myrow->fdispmnth;
        $dept_code = $myrow->fdeptcode;
        $dept_name = $myrow->fdeptname;
        $dept_shrt = $myrow->fdeptshrt;
        $dept_head = $myrow->fdepthead;
        $head_name = $myrow->femplname;

//Create sheet for expense display 
        $ShtNo++;
        $objPHPExcel->createSheet($ShtNo);
        $objPHPExcel->setActiveSheetIndex($ShtNo);
        $objPHPExcel->getActiveSheet()->setTitle($dept_code);

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 2);

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(10);
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);

$objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1.0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.5);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1.0);

$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HMonthly Expense Statment');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . '&CPage &P of &N');

$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(25);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);

$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('A1', 'Expense Statement for the period ' . $disp_mnth);
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal('center')->setVertical('center');

$objPHPExcel->getActiveSheet()->mergeCells('A2:B2');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('A2', 'Client ');
        $objPHPExcel->getActiveSheet()->mergeCells('C2:H2');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('C2', '[' . $dept_code . '] ' . $dept_name);
        $objPHPExcel->getActiveSheet()->getStyle('A2:H2')->getAlignment()->setHorizontal('left')->setVertical('center');

$objPHPExcel->getActiveSheet()->mergeCells('A3:B3');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('A3', 'Incharge Name');
        $objPHPExcel->getActiveSheet()->mergeCells('C3:E3');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('C3', '[' . $dept_head . '] ' . $head_name);
        $objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getAlignment()->setHorizontal('left')->setVertical('center');
        $objPHPExcel->getActiveSheet()->mergeCells('F3:G3');
        $advn_amnt = 0;
        $mysql1 = "select fdeptcode,sum(fadvnamnt) as fadvnamnt from expenses_advance 
        where fdeptcode ='$dept_code' and date_format(fadvndate,'%Y-%m')=date_format(DATE_ADD('" . $_POST['expn_mnth'] . "-01',interval 1 month),'%Y-%m')";
        $myres1 = mysqli_query($mycon, $mysql1);
        while ($myrow1 = mysqli_fetch_assoc($myres1)) {
            $advn_amnt = $myrow1['fadvnamnt'];
        }
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('F3', 'Advance for Next Month');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('H3', $advn_amnt);
        $objPHPExcel->getActiveSheet()->getStyle('F3:H3')->getAlignment()->setHorizontal('center')->setVertical('center');

$i = 4;
        $objPHPExcel->setActiveSheetIndex($ShtNo)
            ->setCellValue('A' . $i, 'Sl. No.')
            ->setCellValue('B' . $i, 'Date')
            ->setCellValue('C' . $i, 'Expense Date')
            ->setCellValue('D' . $i, 'Expense Head')
            ->setCellValue('E' . $i, 'Expense Desc')
            ->setCellValue('F' . $i, 'Received')
            ->setCellValue('G' . $i, 'Expense')
            ->setCellValue('H' . $i, 'Balance');
        $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getStyle('A1:H4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A4:H4')->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->freezePane('A5');

$prev_recp = 0;
        $prev_payt = 0;
        $blnc_amnt = 0;
        $mysql2 = "select 
        sum(case when facnttype='receipt' then fexpnamnt end) as fprevrecp,
        sum(case when facnttype='payment' then fexpnamnt end) as fprevpayt,
        sum(case when facnttype='receipt' then fexpnamnt end)-sum(case when facnttype='payment' then fexpnamnt end) as fprevblnc
        from expenses where fdeptcode like '$dept_code' and fexpndate < str_to_date('$expn_mnth','%Y-%m-%d')";
        $myres2 = mysqli_query($mycon, $mysql2);
        while ($myrow2 = mysqli_fetch_assoc($myres2)) {
            $prev_recp = $myrow2['fprevrecp'];
            $prev_payt = $myrow2['fprevpayt'];
            $blnc_amnt = $myrow2['fprevblnc'];
        }
        $i = 5;
        $objPHPExcel->setActiveSheetIndex($ShtNo)
            ->setCellValue('A' . $i, 1)
            ->setCellValue('B' . $i, "-")
            ->setCellValue('C' . $i, "-")
            ->setCellValue('D' . $i, "-")
            ->setCellValue('E' . $i, "Opening Balance")
            ->setCellValue('F' . $i, $prev_recp)
            ->setCellValue('G' . $i, $prev_payt)
            ->setCellValue('H' . $i, $blnc_amnt);
        $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':A' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('B' . $i . ':B' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('C' . $i . ':C' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('D' . $i . ':D' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('E' . $i . ':E' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('F' . $i . ':F' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('G' . $i . ':G' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->getStyle('H' . $i . ':H' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');

$i++;
    }

//Display expense for each dept. 
    $dept_code = $myrow->fdeptcode;
    $recp_amnt = 0;
    $payt_amnt = 0;

$recp_amnt = $myrow->facnttype == 'RECEIPT' ? $myrow->fexpnamnt : 0;
    $payt_amnt = $myrow->facnttype == 'PAYMENT' ? $myrow->fexpnamnt : 0;
    $blnc_amnt = ($recp_amnt - $payt_amnt) + $blnc_amnt;
    $objPHPExcel->setActiveSheetIndex($ShtNo)
        ->setCellValue('A' . $i, $i - 4)
        ->setCellValue('B' . $i, $myrow->fsystdate)
        ->setCellValue('C' . $i, $myrow->fexpndate)
        ->setCellValue('D' . $i, $myrow->fheadtype)
        ->setCellValue('E' . $i, $myrow->fheaddesc . " " . $myrow->fexpnremk)
        ->setCellValue('F' . $i, $recp_amnt)
        ->setCellValue('G' . $i, $payt_amnt)
        ->setCellValue('H' . $i, $blnc_amnt);
    $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':A' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('B' . $i . ':B' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('C' . $i . ':C' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('D' . $i . ':D' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('E' . $i . ':E' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('F' . $i . ':F' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('G' . $i . ':G' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('H' . $i . ':H' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension($i + 1)->setRowHeight(25);
    $i++;
}

//start of advance summary sheet
$myqry = "";
if ($type_id != "ADMIN" || $type_id != "CHIEF") {
    $myqry = " and ex.fdeptcode like '$_POST[dept_code]' ";
} else {
    $myqry = " and ex.femplcode like '$empl_id' ";
}

$mysqlx = "select 
date_format(fexpndate,'%Y-%m') as fexpnmnth, 
date_format(fexpndate,'%M %Y') as fdispmnth, 
ex.fdeptcode,dp.fdeptname,dp.fdeptshrt,dp.fdepthead,femplname,
sum(case when facnttype='RECEIPT' then fexpnamnt else 0 end) as ftotlrecp,
sum(case when facnttype='PAYMENT' then fexpnamnt else 0 end) as ftotlpayt,
tadvnamnt
from expenses ex
inner join masdept dp on ex.fdeptcode = dp.fdeptcode
left join masempl em on dp.fdepthead = em.femplcode 
left join (select sum(ifnull(fadvnamnt,0)) as tadvnamnt, fdeptcode,fadvndate from expenses_advance where date_format(fadvndate,'%Y-%m')=date_format(date_add('" . $_POST['expn_mnth'] . "-01',interval 1 month),'%Y-%m') group by fdeptcode) ea on ex.fdeptcode = ea.fdeptcode and date_format(fadvndate,'%Y-%m')=date_format(date_add('" . $_POST['expn_mnth'] . "-01',interval 1 month),'%Y-%m')
where date_format(fexpndate,'%Y-%m') = '$_POST[expn_mnth]' " . $myqry . " 
group by dp.fdepthead";
$myresx = mysqli_query($mycon, $mysqlx);
$i = 1;
$dept_head = "";
$ShtNo = 0;
while ($myrowx = mysqli_fetch_object($myresx)) {
    if ($dept_head == "") {
        $expn_mnth = $myrowx->fexpnmnth;
        $disp_mnth = $myrowx->fdispmnth;

$objPHPExcel->setActiveSheetIndexByName('Worksheet');
        $objPHPExcel->getActiveSheet()->setTitle('Expense Summary');

$objPHPExcel->getActiveSheet()->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 2);

$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(10);
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);

$objPHPExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(false);

$objPHPExcel->getActiveSheet()->setShowGridlines(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);

$objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->setActiveSheetIndex($ShtNo)->setCellValue('A1', 'Expense Summary for the period ' . $disp_mnth);
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal('center')->setVertical('center');

$i = 2;
        $objPHPExcel->setActiveSheetIndex($ShtNo)
            ->setCellValue('A' . $i, 'Sl. No.')
            ->setCellValue('B' . $i, 'Location')
            ->setCellValue('C' . $i, 'Incharge Name')
            ->setCellValue('D' . $i, 'Open Balance')
            ->setCellValue('E' . $i, 'Received')
            ->setCellValue('F' . $i, 'Expenses')
            ->setCellValue('G' . $i, 'Close Balance')
            ->setCellValue('H' . $i, 'Advance');

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
        $objPHPExcel->getActiveSheet()->getStyle('A1:H2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1:H2')->getAlignment()->setHorizontal('center')->setVertical('center');
        $objPHPExcel->getActiveSheet()->freezePane('A3');
        $i = 3;
    }
    $dept_code = $myrowx->fdeptcode;
    $dept_name = $myrowx->fdeptname;
    $dept_shrt = $myrowx->fdeptshrt;
    $dept_head = $myrowx->fdepthead;
    $head_name = $myrowx->femplname;
    $totl_recp = $myrowx->ftotlrecp;
    $totl_payt = $myrowx->ftotlpayt;
    $advn_amnt = $myrowx->tadvnamnt;
    $open_blnc = 0;
    $clse_blnc = 0;

$mysql3 = "select ifnull(sum(case when facnttype='receipt' then fexpnamnt end),0) - ifnull(sum(case when facnttype='payment' then fexpnamnt end),0) as fopenblnc 
    from expenses 
    where fdeptcode in (select fdeptcode from masdept where fdepthead = '$dept_head') and fexpndate < str_to_date('$_POST[expn_mnth]','%Y-%m-%d')";
    $myres3 = mysqli_query($mycon, $mysql3);
    while ($myrow3 = mysqli_fetch_assoc($myres3)) {
        $open_blnc = $myrow3['fopenblnc'];
    }
    $clse_blnc = $open_blnc + $totl_recp - $totl_payt;
    $objPHPExcel->setActiveSheetIndex($ShtNo)
        ->setCellValue('A' . $i, $i - 2)
        ->setCellValue('B' . $i, '[' . $dept_code . ']' . $dept_shrt)
        ->setCellValue('C' . $i, '[' . $dept_head . ']' . $head_name)
        ->setCellValue('D' . $i, $open_blnc)
        ->setCellValue('E' . $i, $totl_recp)
        ->setCellValue('F' . $i, $totl_payt)
        ->setCellValue('G' . $i, $clse_blnc)
        ->setCellValue('H' . $i, $advn_amnt);
    $objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':A' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('B' . $i . ':B' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('C' . $i . ':C' . $i)->getAlignment()->setHorizontal('left')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('D' . $i . ':D' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('E' . $i . ':E' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('F' . $i . ':F' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('G' . $i . ':G' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');
    $objPHPExcel->getActiveSheet()->getStyle('H' . $i . ':H' . $i)->getAlignment()->setHorizontal('center')->setVertical('center');

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(25);
    $objPHPExcel->getActiveSheet()->getRowDimension($i + 1)->setRowHeight(25);
    $i++;
}
//end of advance summary sheet

$file_name = "expense_statement_monthly_" . $expn_mnth . ".xlsx";
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $file_name . '"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($file_name);
$resp_file = $file_name;
echo json_encode(array("mesg" => $resp_mesg, "stat" => $resp_stat, "file" => $resp_file));

${this.title}

Code Editor : expense_report_statement_excel - Copy.php