SilverRay SilverRay - 6 months ago 63
Ajax Question

Codeigniter PHPExcel Download via ajax call

I am having problems in sending data from view to my phpexcel function via ajax. I tried the following the solutions "PHPExcel download using ajax call" but seems like I am still missing something. I add a console log to see if the data are transferred and yes it has but it only prints the title and not the contents from the database. Please help... Here is the code of my ajax:

$('#btnExport').click(function(){
var fdate = $('#dateFrom').val();
var tdate = $('#dateTo').val();
var dept = $('#txtdept').val();
var item1 = $('#txtsearch3').val();
var formData;
var page = "<?php echo site_url('tms/exportToExcel');?>";

//formData = $('#formData1').serialize();

if(fdate == "" || tdate == "")
{
$('#no-dates').modal('show');
}
else if(fdate == "" && tdate == "")
{
$('#no-dates').modal('show');
}
else
{

if(dept != "")
{
$.ajax({
type: 'POST',
url: page,
data: {dept:dept,fdate:fdate,tdate:tdate},
success: function(response){
console.log(response);
window.open(page,'_blank');
},
});
}

else if(item1 != "")
{
$.ajax({
type: 'POST',
url: "<?php echo site_url('tms/exportsolo')?>",
data: {item1:item1,fdate:fdate,tdate:tdate},
success: function(){
window.open(page,'_blank');
},
});
}
//console.log(dept+" "+fdate+" "+tdate);
}
//window.open(page);

});


this is one of the functions that create excel file:

public function exportToExcel()
{
//set_time_limit(0);
$dept = $this->input->post('dept');
$from = @date('Y-m-d',@strtotime($this->input->post('fdate')));
$to = @date('Y-m-d',@strtotime($this->input->post('tdate')));

//echo $dept." ".$from." ".$to;

$result['excelData'] = $this->DBmodel->get_excelData($dept);

$this->excel->setActiveSheetIndex(0); // active sheet
$this->excel->getActiveSheet()->setTitle('Attendance Logs'); // sheet title
$this->excel->getActiveSheet()->getStyle('B1')->getFont()->setName('Verdana'); // default font
$this->excel->getActiveSheet()->SetCellValue('B1','EMPLOYEE ATTENDANCE LOGS'); // title
$this->excel->getActiveSheet()->getPageSetUp()->setRowstoRepeatAtTopByStartAndEnd(1,3); // rows to repeat
$this->excel->getActiveSheet()->getRowDimension(1)->setRowHeight(29.25);
$this->excel->getActiveSheet()->mergeCells('B1:T1');
$this->excel->getActiveSheet()->mergeCells('C4:D4');
$this->excel->getActiveSheet()->mergeCells('E8:F8');

$this->excel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('B1')->getFont()->setSize(18);
$this->excel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('#333');

$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(3.14);
$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(0.67);
$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(10.86);
$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(1.43);
$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(9.71);
$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(1.43);
$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(10.7);
$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(1.57);
$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(2);
$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(9);
$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(0.58);
$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(0.92);
$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(9);
$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(1.71);
$this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(2.43);
$this->excel->getActiveSheet()->getColumnDimension('P')->setWidth(6.14);
$this->excel->getActiveSheet()->getColumnDimension('Q')->setWidth(3);
$this->excel->getActiveSheet()->getColumnDimension('R')->setWidth(9.29);
$this->excel->getActiveSheet()->getColumnDimension('S')->setWidth(4);
$this->excel->getActiveSheet()->getColumnDimension('T')->setWidth(0.67);
$this->excel->getActiveSheet()->getColumnDimension('U')->setWidth(0.67);

$rowCount = 4;
foreach($result['excelData'] as $val)
{
$rowTitle = $rowCount + 2; // column
$rowTitle1 = $rowCount + 4; // row
//data label
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('C'.($rowCount).':D'.($rowCount));
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('C'.$rowCount,'ID No:');

$this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('C'.($rowTitle).':D'.($rowTitle));
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('C'.$rowTitle,'Name:');

$this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('I'.($rowCount).':J'.($rowCount));
$this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('I'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('I'.$rowCount,'Dept:');

$this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('C'.($rowTitle1).':D'.($rowTitle1));
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('C'.$rowTitle1)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('C'.$rowTitle1,'Section:');

$this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('I'.($rowTitle1).':J'.($rowTitle1));
$this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('I'.$rowTitle1)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('I'.$rowTitle1,'Line:');

//data contents
$this->excel->getActiveSheet()->mergeCells('E'.($rowCount).':G'.($rowCount));
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->SetCellValue('E'.$rowCount,$val['EmpID']);
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setName('Arial');

$this->excel->getActiveSheet()->mergeCells('E'.($rowTitle).':S'.($rowTitle));
$this->excel->getActiveSheet()->SetCellValue('E'.$rowTitle,$val['Lastname'] . ', ' . $val['Firstname']);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle)->getFont()->setName('Arial');

$this->excel->getActiveSheet()->mergeCells('L'.($rowCount).':S'.($rowCount));
$this->excel->getActiveSheet()->SetCellValue('L'.$rowCount,$val['Department']);
$this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('L'.$rowCount)->getFont()->setName('Arial');

$this->excel->getActiveSheet()->SetCellValue('E'.$rowTitle1,$val['Section']);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('E'.$rowTitle1)->getFont()->setName('Arial');

$this->excel->getActiveSheet()->SetCellValue('L'.$rowTitle1,$val['Line']);
$this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('L'.$rowTitle1)->getFont()->setName('Arial');
$rowCount++;

$result['exceltime'] = $this->DBmodel->get_excelTime($val['EmpID'],$from,$to);

$titleRow = $rowCount + 5;
$rowCount += 6;

foreach($result['exceltime'] as $val)
{
if($val['TimeIn'] == "00:00:00")
{
$TimeIn = "";
}
else
{
$TimeIn = $val['TimeIn'];
}

if($val['LunchOut']=="00:00:00")
{
$LunchOut = "";
}
else
{
$LunchOut = $val['LunchOut'];
}

if($val['LunchIn']=="00:00:00")
{
$LunchIn = "";
}
else
{
$LunchIn = $val['LunchIn'];
}

if($val['TimeOut']=="00:00:00")
{
$TimeOut = "";
}
else
{
$TimeOut = $val['TimeOut'];
}

if($val['RNDOUT']=="00:00:00")
{
$Adjusted = "";
}
else
{
$Adjusted = $val['RNDOUT'];
}
$this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('C'.$titleRow)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('C'.$titleRow,'Date');

$this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('E'.$titleRow)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('E'.$titleRow,'TimeIn');

$this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->mergeCells('G'.($titleRow).':H'.($titleRow));
$this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getAlignment()->setWrapText(true);
$this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('G'.$titleRow)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('G'.$titleRow,'LunchOut');

$this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('J'.$titleRow)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('J'.$titleRow,'LunchIn');

$this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setSize(11);
$this->excel->getActiveSheet()->getStyle('M'.$titleRow)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('M'.$titleRow,'TimeOut');

$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle('C'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('C'.$rowCount,$val['dt']);

$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle('E'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('E'.$rowCount,$TimeIn);

$this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle('G'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('G'.$rowCount,$LunchOut);

$this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle('J'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('J'.$rowCount,$LunchIn);

$this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getFont()->setSize(10);
$this->excel->getActiveSheet()->getStyle('M'.$rowCount)->getFont()->setName('Arial');
$this->excel->getActiveSheet()->SetCellValue('M'.$rowCount,$TimeOut);

$rowCount++;
}
$rowCount+=1;
$newRow = $this->excel->getActiveSheet()->getHighestRow();
$this->excel->getActiveSheet()->setBreak('A'.$newRow,PHPExcel_WorkSheet::BREAK_ROW);
}
$excelWriter = PHPExcel_IOFactory::createWriter($this->excel,'Excel2007');
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename="'.$dept.'.xlsx"');
header('Cache-Control:max-age=0');
ob_end_clean();
$excelWriter->save('php://output');
}


I don't know if there is need to revise this because it's working 100% when I don't use ajax......

Answer

I solved my problem by tweaking a little bit of RatHat code answer and this is what saves me:

$('#btnExport').click(function(){
var fdate = $('#dateFrom').val();
var tdate = $('#dateTo').val();
var dept = $('#txtdept').val();
var item = $('#txtsearch3').val();
var form1 = $('#formData1');
var form2 = $('#formData2');

$('#txtdept').val(dept);
$('#txtfdate1').val(fdate);
$('#txtTdate1').val(tdate);

$('#txtItem').val(item);
$('#txtfdate2').val(fdate);
$('#txtTdate2').val(tdate);   



if(fdate == "" || tdate == "")
{
    $('#no-dates').modal('show');
}
else if(fdate == "" && tdate == "")
{
    $('#no-dates').modal('show');
}
else
{
    if(dept != "")
    {
        form1.attr('action',"<?php echo site_url('tms/exportToExcel');?>");
        form1.attr('method','POST');
        form1.submit();
    }
    else if(item != "")
    {
        form2.attr('action',"<?php echo site_url('tms/exportsolo');?>");
        form2.attr('method','POST');
        form2.submit();
    }
}
});

Thanks to all who leave comments to my post....